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