title: "Concurrency violation: the UpdateCommand affected 0 of the expected 1 records" when updating a new added record in MS Access database from C# app
body: <p>Please help to resolve my problem.
  I'm getting an error "Concurrency violation: the UpdateCommand affected 0 of the expected 1 records" when trying to update a newly added table record in MS Access (2000 format) database (.mdb) from my application written in C#.
  This error is <a href="https://stackoverflow.com/search?q=the+UpdateCommand+affected+0+of+the+expected+1+records">rather generic</a>, and I tried solutions suggested on different forums, but with no success.</p>
  
  <p>Here's what I do step by step:</p>
  
  <p>I have a table 'TRACKS' in mdb that has these columns among others:</p>
  
  <ul>
  <li>ID - type 'AutoNumber' (key column)</li>
  <li>Title - 'Text'</li>
  <li>FullTitle - 'Text'</li>
  <li><p>Length - 'Date/Time'</p>
  
  <ol>
  <li><p>I establish connection to database and get table records this way:</p>
  
  <pre><code>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 * from Tracks", dbConn);
  
          OleDbDataAdapter adapter = new OleDbDataAdapter();
  
          try
          {
              dbConn.Open();
              adTracks.SelectCommand = cmTracks;
              adTracks.Fill(dataset, "Tracks");
          }
          catch (Exception err)
          {
              MessageBox.Show(err.Message);
              return;
          }
          finally
          {
              dbConn.Close();
          }
  
          cboOriginal.DataSource = dataset.Tables["Tracks"];
          cboOriginal.DisplayMember = "FullTitle";
          cboOriginal.ValueMember = "ID";
          cboOriginal.SelectedIndex = -1;
  
          adTracks.RowUpdated += new OleDbRowUpdatedEventHandler(OnRowUpdated);
      }
  }
  </code></pre></li>
  <li><p>Then I add a new record to the table with this code (<code>txtTitle</code> and <code>txtGenTitle</code> controls contain values for the record):</p>
  
  <pre><code>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;
  }
  finally
  {
      dbConn.Close();
  }
  
  res = dt.Select("FullTitle = '" + txtGenTitle.Text.Replace("'", "''") + "'");
  if (res.Length != 0)
  {
      newID = (int)res[0]["ID"];
  
      // continue with newID
  }
  </code></pre>
  
  <p>This code is successfully executed: new record is added to the table, both local DataTable and actual table in mdb file. New auto-incremented value for key column is received in this handler:</p>
  
  <pre><code>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());
      }
  }
  </code></pre>
  
  <p>The row with this ID now has <code>RowState == Unchanged</code>, so everything seems OK.</p></li>
  <li><p>Now I want to update some values in this newly added record (from <code>txtLength</code> control):</p>
  
  <pre><code>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"] is DBNull))
      {
          DateTime date = (DateTime)res[0]["Length"];
          tsOld = date.TimeOfDay;
      }
  
      if (tsNew != TimeSpan.Zero &amp;&amp; (tsOld == TimeSpan.Zero || tsOld.CompareTo(tsNew) &lt; 0))
      {
          if (tsNew != TimeSpan.Zero &amp;&amp; (tsOld == TimeSpan.Zero || tsOld.CompareTo(tsNew) &lt; 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;
          }
          finally
          {
              dbConn.Close();
          }
      }
  }
  </code></pre>
  
  <p>And get an error on line <code>adTracks.Update(dt)</code> <em>"Concurrency violation: the UpdateCommand affected 0 of the expected 1 records"</em>. Database is not updated, neither is DataTable.</p></li>
  </ol></li>
  </ul>
  
  <p>This may probably mean that there's some error with record ID - that it's not updated with a correct value after insertion. But that's not the case here:
  ID is updated in <code>OnRowUpdated</code> handler on step 2 with a correct ID, and also record with this ID is added to to the table mdb file.
  On step 3 before calling <code>adTracks.Update</code>, <code>res[0]</code> also contains correct ID value and <code>RowState == Modified</code>. But I still get this error. What am I doing wrong?</p>
  
  <p>Adding <code>dt.AcceptChanges()</code> after <code>adTracks.Update(dt)</code> as suggested <a href="https://stackoverflow.com/questions/9521355/concurrency-violation-the-updatecommand-affected-0-of-the-expected-1-records-v">here</a> - doesn't help.</p>
  
  <hr>
  
  <p>UPDATE:</p>
  
  <p><strong>1. Trying approach suggested by <a href="https://stackoverflow.com/users/1684486/krish">krish</a>:</strong></p>
  
  <p>I added the following lines before <code>try/catch</code> block on step 3:</p>
  
  <pre><code>string cmd = "UPDATE TRACKS SET Length = '" + res[0]["Length"] + "' WHERE ID = " + res[0]["ID"];
  adTracks.UpdateCommand = new OleDbCommand(cmd, dbConn);
  </code></pre>
  
  <p>And it works! Database is updated and corresponding <code>DataRow</code> gets <code>RowState == Unchanged</code>.
  This is quite a workaround. But I'd still like to know why 'conventional' approach doesn't work here. Workaround is not really convenient when there's a need to update many columns. Also it seems only to be acceptable when I'm updating a single row, and I need the ability to update several rows at once.</p>
  
  <p><strong>2. Trying approach suggested by <a href="https://stackoverflow.com/users/1098359/hynsey">hynsey</a>:</strong></p>
  
  <p>I replaced the line <code>adTracks.Update(dt);</code> on step 3 with the following code:</p>
  
  <pre><code>using (OleDbDataAdapter da = new OleDbDataAdapter ("Select * from Tracks", dbConn))
  {
      OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
      da.RowUpdated += new OleDbRowUpdatedEventHandler(OnRowUpdated);
      da.Update(dt);
  }
  </code></pre>
  
  <p>Sadly, the behavior didn't change at all - same error "Concurrency violation: the UpdateCommand affected 0 of the expected 1 records".</p>
  
  <p>Here's a code that I use on all 3 steps (compare with the original):</p>
  
  <pre><code>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 * from Tracks", dbConn));
                      adTracks.Fill(dataset,"Tracks");    
                  }
                  catch (Exception err)
                  {
                      MessageBox.Show(err.Message);
                      return;
                  }
                  finally
                  {
                      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 * from 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;
          }
          finally
          {
              dbConn.Close();
          }
  
          res = dt.Select("FullTitle = '" + txtGenTitle.Text.Replace("'", "''") + "'");
          if (res.Length != 0)
          {
              newID = (int)res[0]["ID"];
  
              // continue with 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"] is DBNull))
              {
                  DateTime date = (DateTime)res[0]["Length"];
                  tsOld = date.TimeOfDay;
              }
  
              if (tsNew != TimeSpan.Zero &amp;&amp; (tsOld == TimeSpan.Zero || tsOld.CompareTo(tsNew) &lt; 0))
              {
                  if (tsNew != TimeSpan.Zero &amp;&amp; (tsOld == TimeSpan.Zero || tsOld.CompareTo(tsNew) &lt; 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 * from Tracks", dbConn)) 
                      {
                          OleDbCommandBuildercb = new OleDbCommandBuilder(da); 
                          da.Update(dataset , "Tracks"); 
                      }
                  }
                  catch (Exception err)
                  {
                      MessageBox.Show("Error updating track '" + txtGenTitle.Text + "':\n" + err.Message);
                      return;
                  }
                  finally
                  {
                      dbConn.Close();
                  }
              }
          }
  </code></pre>
  
  <p><strong>3. In order to investigate why my initial code doesn't work, I'm providing some more details on the error:</strong></p>
  
  <p><code>OnRowUpdated</code> handler is called despite the error, and I'm able to examine <code>args</code> parameter that is passed into handler. <code>args.Row</code> has <code>RowState == Modified</code> and <code>args.Command</code> has the following <code>CommandText</code> (I added line breaks for readability):</p>
  
  <pre><code>UPDATE Tracks SET Length = ? WHERE ((ID = ?) AND
  ((? = 1 AND Title IS NULL) OR (Title = ?)) AND
  ((? = 1 AND FullTitle IS NULL) OR (FullTitle = ?)) AND
  ((? = 1 AND GenreID IS NULL) OR (GenreID = ?)) AND
  ((? = 1 AND StyleID IS NULL) OR (StyleID = ?)) AND
  ((? = 1 AND SubStyleID IS NULL) OR (SubStyleID = ?)) AND
  ((? = 1 AND Length IS NULL) OR (Length = ?)) AND
  ((? = 1 AND UseOriginal IS NULL) OR (UseOriginal = ?))
  AND ((? = 1 AND Version IS NULL) OR (Version = ?)) AND
  ((? = 1 AND TrackID IS NULL) OR (TrackID = ?)) AND
  ((? = 1 AND SpecPresConjunctor IS NULL) OR (SpecPresConjunctor = ?)) AND
  ((? = 1 AND SpecFeatConjunctor IS NULL) OR (SpecFeatConjunctor = ?)) AND
  ((? = 1 AND FreeRecord IS NULL) OR (FreeRecord = ?)))
  </code></pre>
  
  <p>Can anyone tell what is wrong with this generated command? 'GenreID', 'StyleID', etc. are other columns in the 'TRACKS' table. I don't know what all these '?' mean.</p>
  
  <p>Also, when the exception occurs, here are the latest calls on the stack:</p>
  
  <pre><code>at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
  at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
  at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
  at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
  at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
  at *my code as above*
  </code></pre>