Alexandria Alexandria - 4 months ago 24
SQL Question

Cut characters out of dataset before insert

I am running a sql query which stores results in dataset and then if dataset has rows it will run a query to insert into table. The problem is the data that is coming out is string and is saved as 001234 whereas I want the data to be stored in my table as 1234 as it is sitting as a primary key. I have thought tried to do it with a for each row in dataset and a substring but I am getting an error saying:-




The given value of type String from the data source cannot be converted to type int of the specified target column.


I am trying to store it as int in the database table.

//Runs rollID query and stores in dataset and datatable
public DataSet GetDataSet(string sqlCommand, string ConnectionString)
{
string connectionString = (ConfigurationManager.ConnectionStrings["datConnectionString"].ConnectionString);
DataSet ds = new DataSet();
using (SqlCommand cmd = new SqlCommand(
sqlCommand, new SqlConnection(connectionString)))
{
cmd.Connection.Open();
DataTable rollTable = new DataTable();
rollTable.Load(cmd.ExecuteReader());
ds.Tables.Add(rollTable);

if (rollTable.Rows.Count > 0)
{
foreach (DataRow rw in rollTable.Rows)
{
//Get StartTime in Time format
string StaffID = rw["staff_code"].ToString();

if (string.IsNullOrEmpty(StaffID) == true)
{
//Do nothing
}
else
{
string ShortStaffID = StaffID.Substring(2);
rw["staff_code"] = ShortStaffID.ToString();
}

}

//Gets data from datatable and inserts it into table within database
string consString = ConfigurationManager.ConnectionStrings["rollPlusConnectionString"].ConnectionString;
using (SqlConnection con = new SqlConnection(consString))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{

//Set the database table name
sqlBulkCopy.DestinationTableName = "dbo.roll";

if (rollTable.Rows.Count > 0)
{
con.Open();
sqlBulkCopy.WriteToServer(rollTable);
con.Close();
}
else
{
}
return ds;
}

}
}
}

}

Answer

Not all code paths return a value error means that your method has a return statement placed in a code block which might not be executed.

So simply moving return ds out of if block to the end of your method will make it work.

UPDATE:

public DataSet GetDataSet(string sqlCommand, string ConnectionString)
{
    string connectionString = (ConfigurationManager.ConnectionStrings["datConnectionString"].ConnectionString);
    DataSet ds = new DataSet();
    using (SqlCommand cmd = new SqlCommand(sqlCommand, new SqlConnection(connectionString)))
    {
        cmd.Connection.Open();
        DataTable rollTable = new DataTable();
        rollTable.Load(cmd.ExecuteReader());
        ds.Tables.Add(rollTable);

        if (rollTable.Rows.Count > 0)
        {
            foreach (DataRow rw in rollTable.Rows)
            {
                //Get StartTime in Time format
                string StaffID = rw["staff_code"].ToString();

                if (string.IsNullOrEmpty(StaffID) == true)
                {
                    //Do nothing
                }
                else
                {
                    string ShortStaffID = StaffID.Substring(2);
                    rw["staff_code"] = ShortStaffID.ToString();
                }

            }

            //Gets data from datatable and inserts it into table within database 
            string consString = ConfigurationManager.ConnectionStrings["rollPlusConnectionString"].ConnectionString;
            using (SqlConnection con = new SqlConnection(consString))
            {
                using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                {

                    //Set the database table name
                    sqlBulkCopy.DestinationTableName = "dbo.roll";

                    if (rollTable.Rows.Count > 0)
                    {
                        con.Open();
                        sqlBulkCopy.WriteToServer(rollTable);
                        con.Close();
                    }
                    else
                    {
                    }
                }
            }
        }
    }
    return ds;
}