February 3rd, 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.

  • Hope

    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!!

  • Meha rmahmood

    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.

  • ParvizGTA

    thank you man thanks you very much .

  • simpleman

    I had the exact same problem. Thanks to you, i solved my problem after hours of searching the internet.

  • http://profile.yahoo.com/CGX5JSQZ2W3DBJNMHHTYJ6QYHM YeaRight

    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

  • Cbmehta

    right answer
    Thanks a lot

  • Sir Zeit

    thanks a lot, your post is very helpful !

  • http://www.basic-signalprocessing.com/ Enquiry

    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

  • Saša Dragičević

    thank you so much for this post