Feb 3, 2011

Concurrency violation: the UpdateCommand affected 0 of the expected 1 records

I was updating database using DataAdapter.Update method and got following error:

System.Data.DBConcurrencyException: Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.

I was updating 2-3 columns of tables (having 20 columns) and expected to update those columns only. I fixed the problem and decided to write on it.

Why:

The UPDATE statement created by the CommandBuilder object uses the values stored in the copy of the DataRow with a DataRowVersion value of Original to identify and update the appropriate row in the database. the CommandBuilder creates a SQL statement that looks for a row which exactly matches all of the original values stored in the DataSet means the value of each and every column in the row is checked. if you are trying to update a row that no longer exists in the database, the update from the DataAdapter will fail with the above exception.

Solution:

You need to create Command objects for the UpdateCommand of each of the DataAdapters.
Write Update SQL query, create command object and set it as a dataadapter updatecommand.
See following sample for MSAccess database in VB.NET:

Dim updateCommand As New OleDb.OleDbCommand("Update MyTable Set [Field 1]=@Field1 Where [Field 2]= @Field2 and  [Field 3] =@Field3", DBCon)

updateCommand.Parameters.Add("@Field2", OleDb.OleDbType.VarChar, 50, "Field 2")
updateCommand.Parameters.Add("@Field3", OleDb.OleDbType.VarChar, 50, "Field 3")
updateCommand.Parameters.Add("@Field1", OleDb.OleDbType.VarChar, 50, "Field 1")
DataAdapter1.UpdateCommand = updateCommand
		....DataTable1 Changes...
DataAdapter1.Update(DataTable1)

I did above and It was not working … I got same error… I spent 3 Hrs to fix this.
The problem was due to sequence of parameter declaration of update command. The working code is following:

Dim updateCommand As New OleDb.OleDbCommand("Update MyTable Set [Field 1]=@Field1 Where [Field 2]= @Field2 and  [Field 3] =@Field3", DBCon)

updateCommand.Parameters.Add("@Field1", OleDb.OleDbType.VarChar, 50, "Field 1")
updateCommand.Parameters.Add("@Field2", OleDb.OleDbType.VarChar, 50, "Field 2")
updateCommand.Parameters.Add("@Field3", OleDb.OleDbType.VarChar, 50, "Field 3")
DataAdapter1.UpdateCommand = updateCommand
		....DataTable1 Changes...
DataAdapter1.Update(DataTable1)

Note: we need to define parameter in same order as they in query.

Hope, It helps.

10 comments

  1. this leads me to find out the field name mismatch in SQL and command parameter, i.e. code vs productcode. should be both productcode.
    thanks

  2. I was trying to build an update command and got to this page as part of the bugs i had in it.
    it has been 3 ^%$^ months!!! and you solved it in 3 min!!!!!!!!
    THANK YOU

  3. Dear Brij
    Thanks for your tip,
    I was also encountering same problem and were looking around for same days a solution, My parameters’s list was in the same order as per the updated command object. I Just define it as per that , and it works fine.

  4. Oh! thx! a lot of hours trying to fix it! I'm a Delphi developer and all this Microsoft things are very very .. 'rares' … for me.
    Thx!!

Leave a Reply

Your email address will not be published. Required fields are marked *