c# - Cut characters out of dataset before insert -
i running sql query stores results in dataset , if dataset has rows run query insert table. problem data coming out string , saved 001234 whereas want data stored in table 1234 sitting primary key. have thought tried each row in dataset , substring getting error saying:-
given value of type string data source cannot converted type int of specified target column.
trying store int in database table.
//runs rollid query , stores in dataset , 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 datatable , inserts table within database string consstring = configurationmanager.connectionstrings["rollplusconnectionstring"].connectionstring; using (sqlconnection con = new sqlconnection(consstring)) { using (sqlbulkcopy sqlbulkcopy = new sqlbulkcopy(con)) { //set database table name sqlbulkcopy.destinationtablename = "dbo.roll"; if (rolltable.rows.count > 0) { con.open(); sqlbulkcopy.writetoserver(rolltable); con.close(); } else { } return ds; } } } } }
not code paths return value error means method has return statement placed in code block might not executed.
so moving return ds out of if block end of method make 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 datatable , inserts table within database string consstring = configurationmanager.connectionstrings["rollplusconnectionstring"].connectionstring; using (sqlconnection con = new sqlconnection(consstring)) { using (sqlbulkcopy sqlbulkcopy = new sqlbulkcopy(con)) { //set database table name sqlbulkcopy.destinationtablename = "dbo.roll"; if (rolltable.rows.count > 0) { con.open(); sqlbulkcopy.writetoserver(rolltable); con.close(); } else { } } } } } return ds; }
Comments
Post a Comment