Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.
This exception still seems to catch people out when updating, for example, DataSets:
System.Data.DBConcurrencyException: Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.
This is simply because DataSet (or ADO.Net rather) uses Optimistic Concurrency by default.
This means that when updating, the whole row (rather than the changed column only) is used for comparison with the data in the database.
This then means that if you are trying to update a row that no longer exists in the database, the update from the DataAdapter the update will fail with the exception above.
Typical scenarios when this may happen is that you get your data into the disconnected DataSet, you do some work on it and then try the update.
However, between you selecting the data into the client and sending the update, another user is deleting this row from his application.
Or it can be that you are deleting the data from somewhere else in your application.
So, bottom-line, you will get this exception when trying to update are row that does not exist.
As an example. First create a table in your database (you will have to drop and recreate this for each run of the application):
--drop table UpdateTable
--go
create table UpdateTable(id int primary key, txt nvarchar(20))
insert into UpdateTable values (1, 'One')
insert into UpdateTable values (2, 'Two')
insert into UpdateTable values (3, 'Three')
Then create a new C# console application:
static void Main(string[] args)
{
using (SqlConnection con = new SqlConnection(<your connectionstring>))
{
string selectStmt = "SELECT id, txt FROM UpdateTable";
string deleteStmt = "DELETE FROM UpdateTable WHERE id = 1";
try
{
con.Open();
// STEP # 1
SqlCommand selectCommand = con.CreateCommand();
selectCommand.CommandText = selectStmt;
// STEP # 2
SqlDataAdapter da = new SqlDataAdapter(selectCommand);
DataSet dataset = new DataSet();
da.Fill(dataset);
da.UpdateCommand = new SqlCommandBuilder(da).GetUpdateCommand();
// STEP # 3
SqlCommand deleteCommand = con.CreateCommand();
deleteCommand.CommandText = deleteStmt;
deleteCommand.ExecuteNonQuery();
// STEP # 4
DataRow[] r = dataset.Tables[0].Select("Id = 1");
Console.WriteLine("Id in datatable in dataset: {0}\n", r[0]["Id"]);
// STEP # 5
dataset.Tables[0].Rows[0][1] = "Something";
// STEP # 6
da.RowUpdating += delegate(object sender, SqlRowUpdatingEventArgs e)
{
if (e.Command != null)
{
Console.WriteLine("Command text: {0}\n", e.Command.CommandText);
Console.WriteLine("\nParameters:");
foreach (SqlParameter p in e.Command.Parameters)
{
Console.WriteLine("\t{0} - {1}", p.ParameterName, p.Value);
}
}
};
// STEP # 7
da.Update(dataset);
con.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
}
}
This is what happens:
Step # 1: Creates the Select command that will be used to fill the dataset via the dataadapter.
Step # 2: Creates the DataAdapter with the Select command. Then fills the dataset that will be used for the update.
Step # 3: Deletes the row with Id = 1 directly from the database, i.e. we do not use the dataset here.
This is emulating another user deleting the row with Id = 1 from another application. Or some other part in your code deleting the row with Id = 1
Step # 4: Selects out the row with Id = 1 from the dataset, this is just to show that it is still there even though we have deleted it from the database itself.
Step # 5: Edits the txt column in the row with Id = 1 in the dataset. This has to be done, otherwise the call to Update will ignore this row when updating.
Step # 6: This will output the command that will be executed when updating, it will also show the parameters and their respective values. In this case:
UPDATE [UpdateTable] SET [txt] = @p1 WHERE (([id] = @p2) AND ((@p3 = 1 AND [txt] IS NULL) OR ([txt] = @p4)))
Step # 7: Executes the update, this will throw the exception since we are trying to update a row that (no longer) exists in the database.
So, running this will produce the following output.
Id in datatable in dataset: 1
Command text: UPDATE [UpdateTable] SET [txt] = @p1 WHERE (([id] = @p2) AND ((@p3 = 1 AND [txt] IS NULL) OR ([txt] = @p4)))
Parameters:
@p1 - Something
@p2 - 1
@p3 - 0
@p4 - One
System.Data.DBConcurrencyException: Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.
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(DataSet dataSet, String srcTable)
I hope this explains the above.
Some references:
"Data Access in Client and Middle-Tier Programming - Introduction to Data Concurrency in ADO.NET"
https://msdn.microsoft.com/en-us/library/cs6hb8k4.aspx
".NET Framework Developer's Guide - Optimistic Concurrency (ADO.NET)"