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:

[vb]

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)

[/vb]

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: