Cut characters out of dataset before insert

c# dataset sql sqlbulkcopy

Question

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;
                    }

                }
            }
        }

    }

Accepted 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;
}


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why