.net - "Concurrency violation: the UpdateCommand affected 0 of the expected 1 records" when updating a new added record in MS Access database from C# app -

please resolve problem. i'm getting error "concurrency violation: updatecommand affected 0 of expected 1 records" when trying update newly added table record in ms access (2000 format) database (.mdb) application written in c#. error rather generic, , tried solutions suggested on different forums, no success.

here's step step:

i have table 'tracks' in mdb has these columns among others:

• id - type 'autonumber' (key column)
• title - 'text'
• fulltitle - 'text'

• length - 'date/time'

  1. i establish connection database , table records way:

     public partial class mainform : form {     public oledbconnection dbconn = new oledbconnection();     public dataset dataset = new dataset();      protected oledbdataadapter adtracks = new oledbdataadapter();      protected oledbcommandbuilder cmb;      arraylist arrartists = new arraylist();      public mainform(string strfilename)     {         initializecomponent();         cmb = new oledbcommandbuilder(adtracks);     }      private void onload(object sender, eventargs e)     {         dbconn.connectionstring = properties.settings.default.dbconnectionstring;         oledbcommand cmtracks = new oledbcommand("select * tracks", dbconn);          oledbdataadapter adapter = new oledbdataadapter();          try         {             dbconn.open();             adtracks.selectcommand = cmtracks;             adtracks.fill(dataset, "tracks");         }         catch (exception err)         {             messagebox.show(err.message);             return;         }                 {             dbconn.close();         }          cbooriginal.datasource = dataset.tables["tracks"];         cbooriginal.displaymember = "fulltitle";         cbooriginal.valuemember = "id";         cbooriginal.selectedindex = -1;          adtracks.rowupdated += new oledbrowupdatedeventhandler(onrowupdated);     } } 

  2. then add new record table code (txttitle , txtgentitle controls contain values record):

     datatable dt; datarow dr; int newid;  dt = dataset.tables["tracks"]; dr = dt.newrow(); dr["title"] = txttitle.text; dr["fulltitle"] = txtgentitle.text;  dt.rows.add(dr);  try {     dbconn.open();     adtracks.update(dt); } catch (exception err) {     messagebox.show("error adding new track '" + txtgentitle.text + "':\n" + err.message);     return; } {     dbconn.close(); }  res = dt.select("fulltitle = '" + txtgentitle.text.replace("'", "''") + "'"); if (res.length != 0) {     newid = (int)res[0]["id"];      // continue newid } 

     this code executed: new record added table, both local datatable , actual table in mdb file. new auto-incremented value key column received in handler:

     protected void onrowupdated(object sender, oledbrowupdatedeventargs args) {     if (args.statementtype == statementtype.insert)     {         oledbcommand idcmd = new oledbcommand("select @@identity", dbconn);         args.row["id"] = (int)(idcmd.executescalar());     } } 

     the row id has rowstate == unchanged, seems ok.

  3. now want update values in newly added record (from txtlength control):

     datatable dt; datarow dr; datarow[] res;  dt = dataset.tables["tracks"]; res = dt.select("fulltitle = '" + txtgentitle.text.replace("'", "''") + "'");  if (res.length != 0) {     timespan tsnew = timespan.zero, tsold = timespan.zero;     if (txtlength.text != string.empty) tsnew = timespan.parse(txtlength.text);     if (!(res[0]["length"] dbnull))     {         datetime date = (datetime)res[0]["length"];         tsold = date.timeofday;     }      if (tsnew != timespan.zero && (tsold == timespan.zero || tsold.compareto(tsnew) < 0))     {         if (tsnew != timespan.zero && (tsold == timespan.zero || tsold.compareto(tsnew) < 0)) res[0]["length"] = txtlength.text;          if (string.compare((string)res[0]["title"], txttitle.text, true) != 0)         {             res[0]["title"] = txttitle.text;             res[0]["fulltitle"] = txtgentitle.text;         }          try         {             dbconn.open();             adtracks.update(dt);         }         catch (exception err)         {             messagebox.show("error updating track '" + txtgentitle.text + "':\n" + err.message);             return;         }                 {             dbconn.close();         }     } } 

     and error on line adtracks.update(dt) "concurrency violation: updatecommand affected 0 of expected 1 records". database not updated, neither datatable.

this may mean there's error record id - it's not updated correct value after insertion. that's not case here: id updated in onrowupdated handler on step 2 correct id, , record id added to table mdb file. on step 3 before calling adtracks.update, res[0] contains correct id value , rowstate == modified. still error. doing wrong?

adding dt.acceptchanges() after adtracks.update(dt) suggested here - doesn't help.

---

update:

1. trying approach suggested krish:

i added following lines before try/catch block on step 3:

string cmd = "update tracks set length = '" + res[0]["length"] + "' id = " + res[0]["id"]; adtracks.updatecommand = new oledbcommand(cmd, dbconn); 

and works! database updated , corresponding datarow gets rowstate == unchanged. quite workaround. i'd still know why 'conventional' approach doesn't work here. workaround not convenient when there's need update many columns. seems acceptable when i'm updating single row, , need ability update several rows @ once.

2. trying approach suggested hynsey:

i replaced line adtracks.update(dt); on step 3 following code:

using (oledbdataadapter da = new oledbdataadapter ("select * tracks", dbconn)) {     oledbcommandbuilder cb = new oledbcommandbuilder(da);     da.rowupdated += new oledbrowupdatedeventhandler(onrowupdated);     da.update(dt); } 

sadly, behavior didn't change @ - same error "concurrency violation: updatecommand affected 0 of expected 1 records".

here's code use on 3 steps (compare original):

1.          public partial class mainform : form         {             public oledbconnection dbconn = new oledbconnection();             public dataset dataset = new dataset();              protected oledbdataadapter adtracks = new oledbdataadapter();              arraylist arrartists = new arraylist();              public mainform(string strfilename)             {                 initializecomponent();             }              private void onload(object sender, eventargs e)             {                 dbconn.connectionstring = properties.settings.default.dbconnectionstring;                  try                 {                     dbconn.open();                     adtracks = new oledbdataadapter("select * tracks", dbconn));                     adtracks.fill(dataset,"tracks");                     }                 catch (exception err)                 {                     messagebox.show(err.message);                     return;                 }                                 {                     dbconn.close();                 }                  cbooriginal.datasource = dataset.tables["tracks"];                 cbooriginal.displaymember = "fulltitle";                 cbooriginal.valuemember = "id";                 cbooriginal.selectedindex = -1;                  adtracks.rowupdated += new oledbrowupdatedeventhandler(onrowupdated);             }         }  2.         datatable dt;         datarow dr;         int newid;          dt = dataset.tables["tracks"];         dr = dt.newrow();         dr["title"] = txttitle.text;         dr["fulltitle"] = txtgentitle.text;          dt.rows.add(dr);          try         {             dbconn.open();             using (oledbdataadapter da = new oledbdataadapter ("select * tracks", dbconn))              {                 oledbcommandbuilder cb = new oledbcommandbuilder(da);                  da.rowupdated += new oledbrowupdatedeventhandler(onrowupdated);                 da.update(dataset, "tracks");              }         }         catch (exception err)         {             messagebox.show("error adding new track '" + txtgentitle.text + "':\n" + err.message);             return;         }                 {             dbconn.close();         }          res = dt.select("fulltitle = '" + txtgentitle.text.replace("'", "''") + "'");         if (res.length != 0)         {             newid = (int)res[0]["id"];              // continue newid         }          3.         datatable dt;         datarow dr;         datarow[] res;          dt = dataset.tables["tracks"];         res = dt.select("fulltitle = '" + txtgentitle.text.replace("'", "''") + "'");          if (res.length != 0)         {             timespan tsnew = timespan.zero, tsold = timespan.zero;             if (txtlength.text != string.empty) tsnew = timespan.parse(txtlength.text);             if (!(res[0]["length"] dbnull))             {                 datetime date = (datetime)res[0]["length"];                 tsold = date.timeofday;             }              if (tsnew != timespan.zero && (tsold == timespan.zero || tsold.compareto(tsnew) < 0))             {                 if (tsnew != timespan.zero && (tsold == timespan.zero || tsold.compareto(tsnew) < 0)) res[0]["length"] = txtlength.text;                  if (string.compare((string)res[0]["title"], txttitle.text, true) != 0)                 {                     res[0]["title"] = txttitle.text;                     res[0]["fulltitle"] = txtgentitle.text;                 }                  try                 {                     dbconn.open();                     using (oledbdataadapter da = new oledbdataadapter ("select * tracks", dbconn))                      {                         oledbcommandbuildercb = new oledbcommandbuilder(da);                          da.update(dataset , "tracks");                      }                 }                 catch (exception err)                 {                     messagebox.show("error updating track '" + txtgentitle.text + "':\n" + err.message);                     return;                 }                                 {                     dbconn.close();                 }             }         } 

3. in order investigate why initial code doesn't work, i'm providing more details on error:

onrowupdated handler called despite error, , i'm able examine args parameter passed handler. args.row has rowstate == modified , args.command has following commandtext (i added line breaks readability):

update tracks set length = ? ((id = ?) , ((? = 1 , title null) or (title = ?)) , ((? = 1 , fulltitle null) or (fulltitle = ?)) , ((? = 1 , genreid null) or (genreid = ?)) , ((? = 1 , styleid null) or (styleid = ?)) , ((? = 1 , substyleid null) or (substyleid = ?)) , ((? = 1 , length null) or (length = ?)) , ((? = 1 , useoriginal null) or (useoriginal = ?)) , ((? = 1 , version null) or (version = ?)) , ((? = 1 , trackid null) or (trackid = ?)) , ((? = 1 , specpresconjunctor null) or (specpresconjunctor = ?)) , ((? = 1 , specfeatconjunctor null) or (specfeatconjunctor = ?)) , ((? = 1 , freerecord null) or (freerecord = ?))) 

can tell wrong generated command? 'genreid', 'styleid', etc. other columns in 'tracks' table. don't know these '?' mean.

also, when exception occurs, here latest calls on stack:

at 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) @ *my code above* 

replace onload method this:

    private void onload(object sender, eventargs e)     {         dbconn.connectionstring = properties.settings.default.dbconnectionstring;      try     {         dbconn.open();         adtracks = new oledbdataadapter("select * tracks", dbconn));         adtracks.fill(dataset,"tracks");         }     catch (exception err)     {         messagebox.show(err.message);         return;     }         {         dbconn.close();     }      cbooriginal.datasource = dataset.tables["tracks"];     cbooriginal.displaymember = "fulltitle";     cbooriginal.valuemember = "id";     cbooriginal.selectedindex = -1;      adtracks.rowupdated += new oledbrowupdatedeventhandler(onrowupdated); } 

and code updating dataset: (note corrected incorrect reference adtracks present in comment above.)

using (oledbdataadapter da = new oledbdataadapter ("select * tracks", dbconn))  { oraclecommandbuilder cb = new oraclecommandbuilder(da);  da.update(dataset , "tracks");  }