Tutorial :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;                }          }  


Solution:1

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?


Solution:2

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);  }  


Solution:3

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.


Solution:4

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.


Solution:5

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


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »