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

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

  • Comments 6

 

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"

http://msdn.microsoft.com/en-us/library/cs6hb8k4.aspx

".NET Framework Developer's Guide - Optimistic Concurrency (ADO.NET)"

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

 

 

 

 

 

 

 

 

 

Leave a Comment
  • Please add 5 and 1 and type the answer here:
  • Post
  • 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

  • Thats not the only thing that will cause this error.

  • 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...

  • 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.

  • 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.

  • 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

Page 1 of 1 (6 items)