Share via

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

  • Article

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)"

https://msdn.microsoft.com/en-us/library/aa0416cz.aspx

Comments

  • Anonymous
    February 03, 2011
    Good Article!!! One more thing I used my own sql query for updatecommand, but still got same error. See following how i fixed this. techbrij.com/.../concurrency-violation-the-updatecommand-affected-0-of-the-expected-1-records

  • Anonymous
    September 29, 2011
    Thats not the only thing that will cause this error.

  • Anonymous
    November 13, 2011
    Hi, I am issue for updating Two different table which are in same also in Different Database. When using following code, it is not updating second table and saying "concurrency violation the updatecommand affected 0 of the expected" error. Please note in following code I am updating Grid one column and updating the Database. "mySymbolMastertable" table is already populated with changes. PLEASE HELP... private void UpdateDatabases() { strAddResult =new StringBuilder (); try { DataTable dtsec= new DataTable () ; dtsec= mySymbolMastertable.GetChanges(); if (connList != null ) { for (int i = 0; i < connList.Count; i++) { string connectionName; string sqlString; DataTable dt = new DataTable (); connectionName = connList[i].ToString(); connectionString = System.Configuration.ConfigurationSettings .AppSettings[connectionName]; SqlConnection mySQLConnection = new SqlConnection (connectionString);   if (connectionName == "SecMasterConnectionSec" ) { sqlString = "SELECT * FROM [SymbolMaster_Secondary]" ; } else { sqlString ="SELECT * FROM [SymbolMaster]" ; } SqlDataAdapter myDataAdapter = new SqlDataAdapter (sqlString, mySQLConnection); SqlCommandBuilder myCmdBuilder = new SqlCommandBuilder (myDataAdapter); dt = dtsec; // dt = mySymbolMastertable.GetChanges();   //Add Handler //myDataAdapter.RowUpdating += new SqlRowUpdatingEventHandler(myDataAdapter_RowUpdating); myDataAdapter.RowUpdated +=new SqlRowUpdatedEventHandler (myDataAdapter_RowUpdated); myDataAdapter.ContinueUpdateOnError =true ; //myDataAdapter.InsertCommand = myCmdBuilder.GetInsertCommand(); //myDataAdapter.UpdateCommand = myCmdBuilder.GetUpdateCommand(); myDataAdapter.Update(dt); //Add Handler // myDataAdapter.RowUpdating += new SqlRowUpdatingEventHandler(myDataAdapter_RowUpdating); myDataAdapter.RowUpdated +=new SqlRowUpdatedEventHandler (myDataAdapter_RowUpdated); myCmdBuilder =null ; myDataAdapter =null; mySQLConnection =null ; }} } catch (Exception ex) {   ''Catching Exception }} PLEASE HELP...

  • Anonymous
    December 08, 2011
    If you are using the wizards in Visual Studio to create SQL commands that include optimistic concurrency then this problem may occur if you change the schema of the table you are updating. In particular if you make a field nullable that wasn't previously it is essential that you regenerate the commands as they WILL fail because the old SQL didn't allow for the possible NULL value. This applies both when generating stored procedures and inline SQL.

  • Anonymous
    October 31, 2013
    This is not always the case.  We just ran into a case where the row and data existed but still received the error.  The problems was with the compare of a float column.  The shown value in the DB was 0.1 and the update failed the where clause (WHERE column = 0.1)  I believe this is due to a floating point rounding error.   So if you have floats/doubles as columns in the DB they may (not always) cause this error even though the data looks the same.

  • Anonymous
    June 25, 2014
    A concurrency exception occurs if the update command calls a database procedure to do the update, e.g., call update_customer (ID, Name), even if the update is successful. Get around this by including a version or timestamp in the procedure call, e.g., call update_customer (ID, Name, Version), and update the version or timestamp in the procedure. The parameter must be Input/Output direction

  • Anonymous
    September 02, 2015
    (VS2012 Update 4, F/W 4.5.51209) I came across a flaw (bug) in the interface between the TableAdapter's handling of Nullable columns and the logic inserted in the generated stored procedures (when using Strongly Typed DataSets with generated code and allowing the wizard to generate the stored procs). The generated UPDATE (and DELETE) stored procedures will contain the following predicate for each Nullable column: AND ((@IsNull_MyNullableColumn = 1 AND [MyNullableColumn] IS NULL) OR ([MyNullableColumn] = @Original_MyNullableColumn)) However, the problem is that the value for parameter @IsNull_MyNullableColumn (as handed off by the TableAdapter) contains the value indicating whether the UPDATED value of MyNullableColumn is NULL - NOT whether the ORIGINAL value was NULL.  This causes the update target row to not be found, and a Concurrency Violation exception being raised when updating a column from a NULL to a non-NULL value. A simple fix is to modify the UPDATE (and DELETE) stored procedure predicates for nullable columns to the form: AND (([MyNullableColumn] IS NULL) OR ([MyNullableColumn] = @Original_MyNullableColumn)) (simply removing the "@IsNull_MyNullableColumn = 1 AND " portion of the statement is a quick and easy fix).  However, that is not necessarily bulletproof. A more general fix would be to modify the predicates to this form: AND ((@Original_MyNullableColumn IS NULL AND [MyNullableColumn] IS NULL) OR ([MyNullableColumn] = @Original_MyNullableColumn)) This will match when the table value and the original value are either both NULL or are both non-NULL and have the same value.  The additional @IsNull_MyNullableColumn is really just a distraction in the generated SPs.

  • Anonymous
    September 04, 2015
    More commonly than missing data, I think people must run into the situation I resolved.  The automatically built update command can build as many as three parameters for each field in the SELECT command.  It builds one for the new version of the data, a second to for the original version of the data, and a third for which I don't understand the purpose.  The first parameter (p1) is used in the SET clause of the update command to push in the new value.  The second and third - if they exist - are used in the WHERE clause in a series of AND statements like: AND ((@p3 = 1 AND [Blah] IS NULL) OR ([Blah] = @p2)) This provides plenty of opportunity to go wrong.  As the author points out, missing rows are a problem.  But so are rows where one of your values don't match either the original or the new value or the null/default condition of the field.  Here is a list of ways you can go wrong and it's probably not the complete list:

  1. Somebody else has gotten to the row before you and changed one or more of the values
  2. You have created one or more columns in your database that allow null values, AND specified a Default Value or Binding that is NOT null (one of my dumb mistakes).  You need to do one or the other because the command builder will put in the AND [blah] IS NULL condition because the column can have them.  But your default/binding value will never be null so the row will never be found
  3. You like to get all hilarious in your SELECT queries and fill in default values for stuff that is null in the table (another dumb mistake I was making).  A statement like this should do the trick: COALESCE([Blah], 'Blank').  Since the value the DataTable thinks the 'original' is now 'Blank' instead of null like it is in the database table, the row will never be found.
  4. You are using a different SELECT statement in the SqlAdapter build than you used to generate your table.  You can actually get away with this once you understand how the commands are built from the SELECT statement - something kind of poorly explained here: msdn.microsoft.com/.../tf579hcz(v=vs.90).aspx What I don't understand is, if you supply the primary key in the table, why doesn't the update command just use that?
  • Anonymous
    October 25, 2015
    My solution was; Instead of '= 1 AND HomePage IS NULL OR HomePage = @p49&#39; I use 'IS NULL OR = 1 AND HomePage IS NULL OR HomePage = @p49&#39;