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

Question

I am new to c# and try to bind a datagridview to a mssql database in visual studio 2010. The databindings are OK and everything seems to work. Except for a few strange errors:

I get the error in the subject after: updating the same row 2 times, deleting a new inserted row, after updating a row when an other row was deleted (word changes to DeleteCommand)

None of the solutions I found on Google workes for me. I hope someone can help me with this. Here is te code:

    private void fillDatagrid()
        {
            //fill datagrid ADO.NET
            conn = new SqlConnection(TestApp.Properties.Settings.Default.TestdatabaseConnectionString);
            cmd = conn.CreateCommand();
            conn.Open();
            cmd.CommandText = "SelectFrom";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@table", SqlDbType.NVarChar, 50).Value = "Countries";
            cmd.Parameters.Add("@filters", SqlDbType.NVarChar, 300).Value = "";

            adapt = new SqlDataAdapter(cmd);
            dt = new DataTable();
            adapt.Fill(dt);
            dt.TableName = "Countries";

            conn.Close();

            BindingSource src = new BindingSource();
            src.DataSource = dt;
            dt.RowChanged += new DataRowChangeEventHandler(dt_RowChanged);

            dgDatabaseGrid.DataSource = src;
            dgDatabaseGrid.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells);
            //dgDatabaseGrid.RowValidating += new DataGridViewCellCancelEventHandler(dgDatabaseGrid_RowValidating);

            //disable columns:
            dgDatabaseGrid.Columns[0].Visible = false;
            dgDatabaseGrid.Columns["date_insert"].Visible = false;
            dgDatabaseGrid.Columns["user_insert"].Visible = false;
            dgDatabaseGrid.Columns["date_change"].Visible = false;
            dgDatabaseGrid.Columns["user_change"].Visible = false;
            dgDatabaseGrid.Columns["deleted"].Visible = false;

            //auto size last column
            dgDatabaseGrid.Columns["remarks"].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;


            SqlCommandBuilder cb = new SqlCommandBuilder(adapt);
        }

        void dt_RowChanged(object sender, DataRowChangeEventArgs e)
        {
            try
            {
                adapt.Update(dt);
            }
            catch (SqlException ex)
            {
                Debug.WriteLine(ex.Message);
            }
        }

private void dgDatabaseGrid_UserDeletingRow(object sender, DataGridViewRowCancelEventArgs e)
        {
            if (!e.Row.IsNewRow)
            {


                DialogResult response = MessageBox.Show("Are you sure?", "Delete row?",
                                     MessageBoxButtons.YesNo,
                                     MessageBoxIcon.Question,
                                     MessageBoxDefaultButton.Button2);

                if (response == DialogResult.Yes)
                {

                    //ipv delete --> deleted=1
                    conn.Open();
                    cmd = conn.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "DeleteFrom";
                    cmd.Parameters.Add("@table", SqlDbType.NVarChar, 50).Value = "Countries";
                    cmd.Parameters.Add("@id", SqlDbType.Int).Value = e.Row.Cells[0].Value;
                    cmd.ExecuteNonQuery();
                    conn.Close();


                    //delete from datagrid:
                    dt.Rows[dgDatabaseGrid.SelectedCells[0].RowIndex].Delete();

                }

                //always cancel!
                e.Cancel = true;

            }
        }

Answer 1

I know it's very late but maybe it will help someone.

Made the following changes to your code:

try
{
    adapt.Update(dt);

Put these lines here and use your variable

    Me.yourTableAdapter.Update(Me.yourDataSet.yourTable)
    Me.yourDataSet.youTable.AcceptChanges()
    Me.yourTableAdapter.Fill(Me.yourDataSet.yourTable)

it worked like a charm for me hope it will work for you.

}
catch (SqlException ex)
{
    Debug.WriteLine(ex.Message);
}

Answer 2

I have been chasing this error in my application for weeks! I finally found my issue.

What I found in my application...

I have many textboxes, comboboxes, etc. bound with databindings. Some of these fields are being updated from combinations of other fields. This all works great with one exception

If one of the calculated fields gets re-calculated after you EndEdit and before you Update, this will cause a dbconcurrency violation.

This error doesn't have to mean that the row doesn't exist any more; it simply means that it didn't update a row for some reason. My reason was that the data had three different states so it thought that someone else had changed the data before I called the Update.

BTW, this is a single MDF located on the users computer so no one else has access to it to change it during the Update. One user, One Update. My code was the "other" user.

Hope this can help point someone else in the right direction for their application.

Answer 3

If I may add my two cents worth.

I have struggled with this for a while. In our application we have calculated columns where the column is the result of some calculation of two or more other columns.

This Recalc threw the Adapter.

I had to set SqlCommandBuilder.ConflictOption = ConflictOption.OveriteChanges to get around this issue.

I do not know if there is an option to tell the Adapter to ignore read only columns when it does the checking.

Answer 4

To resolve it I simply turned Optimistic Concurrency on the Table Adaptor:
- Open the Dataset
- Right-click the Table Adaptor, choose Configure...
- Click the Advanced Options button
- Deselect 'Use optimistic concurrency', click OK
- Click through the rest of the wizard.

Answer 5

Different solution for different situation. I got the same concurrency error but it was happening due to auto-generated update command being incorrect. Most likely, it happened because I might be missing keys on table when I created the table adapter from existing table.

so, my update command was looking to update was comparing every single column in where clause which will never match if there are differences.

e.g. following will return 0 for table x having 3 columns if you change @a,@b or @c.

Update table [x] 
set a = @a, b = @b, c = @c 
where a = @a, b = @b, c = @c

Answer 6

Simple answer:

This simply means that if you are trying to update a row that no longer exists in the database.

More details could be found here: http://blogs.msdn.com/b/spike/archive/2010/04/07/concurrency-violation-the-updatecommand-affected-0-of-the-expected-1-records.aspx