VeeWee VeeWee - 9 months ago 66
C# Question

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

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();
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();
dt.TableName = "Countries";


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

dgDatabaseGrid.DataSource = src;
//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)
catch (SqlException ex)

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

DialogResult response = MessageBox.Show("Are you sure?", "Delete row?",

if (response == DialogResult.Yes)

//ipv delete --> deleted=1
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;

//delete from datagrid:


//always cancel!
e.Cancel = true;


Answer Source

after updating the same row 2 times

Is there a Timestamp column (or any other column that is changed/filled on the Db server) ?

Your problem could happen when the in-memory row is different from what's in the Db. And because you use a SP for the SelectCmd there (probably) is no refresh after an update.

after deleting a new inserted row

Similar, caused not fetching the new Id after an insert

after updating a row when an other row was deleted (word changes to DeleteCommand)

totally unclear.
But why do you Delete rows 'manually' instead of leaving it to the adapt.Update() ? And are you sure that not both methods are executed?