c# - ADO.Net INSERT not inserting data -

i've got c#.net 4.5 console application , i'm trying insert data datatable sql server 2008r2 database table. no errors, no data gets inserted. here's code:

int32 newid = 0; datatable dtmaxuserid = generalclasslibrary.generaldataaccesslayer.executeselect("select max(userid)+1 newid tiuser", false, "trackitcn"); newid = convert.toint32(dtmaxuserid.rows[0]["newid"].tostring());  //get new users alluserdata datacolumn dcrowid = new datacolumn("rowid", typeof(int32)); //dcrowid.allowdbnull = false; dcrowid.autoincrement = true; dcrowid.autoincrementseed = 1; dcrowid.autoincrementstep = 1; //dcrowid.unique = true; //dcrowid.columnname = "rowid"; datatable dtnewusers = new datatable(); dtnewusers.columns.add(dcrowid); dtnewusers.columns.add("maxid"); dtnewusers.columns.add("userid"); dtnewusers.columns.add("fullname"); dtnewusers.columns.add("title"); dtnewusers.columns.add("phone"); dtnewusers.columns.add("emailaddr"); dtnewusers.columns.add("fax"); dtnewusers.columns.add("dept"); dtnewusers.columns.add("dept_num"); dtnewusers.columns.add("location"); dtnewusers.columns.add("userdef_1"); dtnewusers.columns.add("userdef_2"); dtnewusers.columns.add("login"); dtnewusers.columns.add("password"); dtnewusers.columns.add("passwordflags"); dtnewusers.columns.add("languageid"); dtnewusers.columns.add("ntauthentication"); dtnewusers.columns.add("ntaccount"); dtnewusers.columns.add("sid"); dtnewusers.columns.add("selfserviceaccess"); dtnewusers.columns.add("imagepath"); dtnewusers.columns.add("compflag"); dtnewusers.columns.add("employee_id"); dtnewusers.columns.add("sessionid"); string strquery = "select " + newid + " maxid, lname" + ", " + "fname fullname, title, phone, email emailaddr, fax, department dept, office location, [login] employee_id [user] startdate >= cast(datepart(year,getdate()) varchar) + '-' + cast(datepart(month,getdate()) varchar) + '-' + cast(datepart(day,getdate())-1 varchar)"; datatable dttemp = generalclasslibrary.generaldataaccesslayer.executeselect(strquery, false, "alluserdatacn"); foreach (datarow row in dttemp.rows)     dtnewusers.importrow(row);  sqltransaction tran = null;     try     {         connection.open();         tran = connection.begintransaction();          //insert new users tiuser         sqldataadapter tiuser_adapter = new sqldataadapter();         string querystring = "select * tiuser 1 = 0";         tiuser_adapter.selectcommand = new sqlcommand(querystring, connection, tran);         tiuser_adapter.fill(dtnewusers);         dtnewusers.acceptchanges();         tiuser_adapter.update(dtnewusers);          tran.commit();     }     catch (system.exception ex)     {         tran.rollback();         throw ex;     }         {         connection.close();     } 

generalclasslibrary class library use here number of things; here executes select statement on database. tiuser database table. datatable, dtnewusers contains 1 row. i've verified debugging code , inspecting datatable after importrow done.

---

after following reply user3787557 (thank you!), i'm closer, i'm getting concurrency violation. i'm working on development database, , i'm doing inserting record, have no idea why there'd concurrency violation. 1 possibility: alter structure of datatable dtnewusers adding 2 columns. however, before update, remove columns. insertcommand fine; i've checked in ssms , parses. here's new code:

using (sqlconnection connection = new sqlconnection(generalclasslibrary.generalconfigurationmanager.connectionstrings["trackitcn"].connectionstring))         {             sqltransaction tran = null;             connection.open();             tran = connection.begintransaction();              try             {                 //create empty tiuser data adapter                 sqldataadapter tiuser_adapter = new sqldataadapter();                 sqlcommandbuilder tiuser_builder = new sqlcommandbuilder(tiuser_adapter);                 string querystring = "select * tiuser 1 = 0";                 tiuser_adapter.selectcommand = new sqlcommand(querystring, connection, tran);                 tiuser_adapter.insertcommand = tiuser_builder.getinsertcommand();                 tiuser_adapter.updatecommand = tiuser_builder.getupdatecommand();                  //get new users alluserdata                 datatable dtnewusers = new datatable();                 tiuser_adapter.fill(dtnewusers);                 datacolumn dcrowid = new datacolumn("rowid", typeof(int32));                 dcrowid.autoincrement = true;                 dcrowid.autoincrementseed = 1;                 dcrowid.autoincrementstep = 1;                 dtnewusers.columns.add(dcrowid);                 dtnewusers.columns.add("maxid");                                        string strquery = "select " + newid + " maxid, lname + ', ' + fname fullname, title, phone, email emailaddr, fax, department dept, office location, [login] employee_id [user] startdate >= cast(datepart(year,getdate()) varchar) + '-' + cast(datepart(month,getdate()) varchar) + '-' + cast(datepart(day,getdate())-1 varchar)";                 datatable dttemp = generalclasslibrary.generaldataaccesslayer.executeselect(strquery, false, "alluserdatacn");                 foreach (datarow row in dttemp.rows)                     dtnewusers.importrow(row);                  //make sure new users aren't in trackit                 foreach (datarow row in dtnewusers.rows)                 {                     row["userid"] = (convert.toint32(row["rowid"]) + convert.toint32(row["maxid"])).tostring();                     datatable dtoverlap = generalclasslibrary.generaldataaccesslayer.executeselect("select * tiuser employee_id = '" + row["employee_id"].tostring() + "'", false, "trackitcn");                     if (dtoverlap.rows.count > 0)                         dtnewusers.rows.remove(row);                 }                      //remove maxid , rowid columns                 dtnewusers.columns.remove("maxid");                 dtnewusers.columns.remove("rowid")                  tiuser_adapter.update(dtnewusers);                 tran.commit();             }             catch (system.exception ex)             {                 generalclasslibrary.generalemail.exceptionnotification(system.reflection.assembly.getexecutingassembly().tostring(), ex.message, ex.stacktrace);                 tran.rollback();                 throw ex;             }                         {                 connection.close();             }         } 

the stack trace is:

   @ system.data.common.dbdataadapter.updatedrowstatuserrors(rowupdatedeventargs rowupdatedevent, batchcommandinfo[] batchcommands, int32 commandcount)   @ system.data.common.dbdataadapter.updatedrowstatus(rowupdatedeventargs rowupdatedevent, batchcommandinfo[] batchcommands, int32 commandcount)    @ system.data.common.dbdataadapter.update(datarow[] datarows, datatablemapping tablemapping)    @ system.data.common.dbdataadapter.updatefromdatatable(datatable datatable, datatablemapping tablemapping)    @ system.data.common.dbdataadapter.update(datatable datatable)    @ updatetrackitusers.program.main(string[] args) in c:\users\06717\documents\visual studio 2012\projects\updatetrackitusersconsole\updatetrackitusersconsole\program.cs:line 91 

line 87 line:

tiuser_adapter.update(dtnewusers); 

this code worked me. you're missing on code use sqlcommandbuilder create insertcommand , updatecommand. rid of acceptchanges(). going cause new row not sent database. 1 last thing: make sure mark mdf database file (if you're using one) properties "do not copy" otherwise database gets overridden every time compile , can't see changes.

using (sqlconnection connection = new sqlconnection(connectionstring)) {     sqldataadapter adapter = new sqldataadapter();     sqlcommandbuilder builder = new sqlcommandbuilder(adapter);      adapter.selectcommand = new sqlcommand(querystring, connection);     adapter.insertcommand = builder.getinsertcommand();     adapter.updatecommand = builder.getupdatecommand();     adapter.deletecommand = builder.getdeletecommand();      datatable dt = new datatable();     adapter.fill(dt);      datarow row = dt.newrow();     row["regionid"] = 5;     row["regiondescription"] = "some region";     dt.rows.add(row);      //dt.acceptchanges();      int counter = adapter.update(dt);  }