Why am getting concurrency violation error?

Question

I've MS Access db, datagridview that displays items, two checkbox columns that represents Yes/No columns in the db, and refresh/del buttons.

When I try to delete a row that its checkboxes hasn't been modified, the row delets just fine, also when I modify the checkbox value, press refresh button, then delete, the row deletes fine too.

However when I try to delete a row right after modifying its checkbox value, I get concurrency violation exception error.

When checkbox value changed code:

private void dataGridView1_CellValueChanged(object sender, DataGridViewCellEventArgs e)
    {

        if (dataGridView1.Columns[e.ColumnIndex].Name == "sales")
        {

            DataGridViewCheckBoxCell checkCell = (DataGridViewCheckBoxCell)dataGridView1.Rows[e.RowIndex].Cells["sales"];
            bool _pSale = (Boolean)checkCell.Value;

            string connstring = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}", Path.Combine(Directory.GetCurrentDirectory(), "MyDatabase01.accdb"));
            OleDbConnection conn = new OleDbConnection(connstring);
            conn.Open();

            string sqlqry = "UPDATE Items SET pSale = " + _pSale + " WHERE p_Name = '" + this._pName + "'";
            OleDbCommand upd = new OleDbCommand(sqlqry, conn);
            upd.ExecuteNonQuery();
            conn.Close();
            //dataGridView1.Invalidate();

        }
}

Refresh button code:

public void Refreshdgv()
        {
            this.categoriesItemsBindingSource.EndEdit();
            this.itemsTableAdapter.Fill(myDatabase01DataSet.Items);
            this.dataGridView1.Refresh();
        }

Delete button code:

private void delBtn_Click(object sender, EventArgs e)
    {
            try
            {

                int cnt = dataGridView1.SelectedRows.Count;
                for (int i = 0; i < cnt; i++)
                {
                    if (this.dataGridView1.SelectedRows.Count > 0)
                    {
                        this.dataGridView1.Rows.RemoveAt(this.dataGridView1.SelectedRows[0].Index);
                    }
                }


                this.Validate();
                this.categoriesItemsBindingSource.EndEdit();
                this.itemsTableAdapter.Update(this.myDatabase01DataSet.Items);
                this.myDatabase01DataSet.AcceptChanges();

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
    }

To resolve this issue I can call Refreshdgv() method in place of dataGridView1.Invalidate(). But I don't want the dgv refresh for each checkbox click!

Answer 1

The delete command of your DataSet is likely checking the original values. Since you are updating your database manually in the CellValueChanged event, the values in your database won't match the original values in your DataSet. If you modify the CellValueChanged event to use the update command in your DataSet, the values should match up when you call Delete.

Alternatively, you could change your delete command to use a less exclusive where clause (e.g., WHERE KeySegment0 = @keySegment0 AND KeySegment1 = @keySegment1 ...).