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

Question

I am trying to delete a record from a datagridview then updating it to a mysql server, however I keep receiving "Concurrency violation: the DeleteCommand affected 0 of the expected 1 records.". I have googled and messed around with it somewhat and cannot figure out a fix for love nor money. Where am I going wrong?

    private void Form1_Load(object sender, EventArgs e)
    {
        try
        {
            // create a connection to the server
            connection = new MySqlConnection("SERVER=" + Constants.SERVER + ";" + "DATABASE=" + Constants.DATABASE + ";UID=" + Constants.USERNAME + ";" + "PASSWORD=" + Constants.PASSWORD + ";");
            connection.Open();

            // create our default handler
            adapter = new MySqlDataAdapter();

            // set the default commands to do
            adapter.SelectCommand = new MySqlCommand("SELECT * FROM npc_drops", connection);
            MySqlCommand query = new MySqlCommand("DELETE FROM npc_drops WHERE id = @id", connection);
            query.Parameters.Add("@id", MySqlDbType.Int32, 10, "id");
            adapter.DeleteCommand = query;

            // create a table to put our data in and fill it with the results
            table = new DataTable();
            adapter.Fill(table);

            // bind both the data table and the datagridview togeather
            BindingSource source = new BindingSource();
            source.DataSource = table;
            dataGridView1.DataSource = source;
            dataGridView1.Columns["id"].Visible = false;
        }
        catch (MySqlException ex)
        {
            MessageBox.Show(ex.Message);
        }
    }

    private void button1_Click(object sender, EventArgs e)
    {
        try
        {
            table = table.GetChanges();
            adapter.Update(table);
        }
        catch (MySqlException ex)
        {
            MessageBox.Show(ex.Message);
        }
    }

Table:

CREATE TABLE `npc_drops` (
    `id` INT(10) NULL AUTO_INCREMENT,
    `npcs` VARCHAR(250) NULL DEFAULT NULL,
    `rate` INT(3) NULL DEFAULT NULL,
    `item` INT(5) NULL DEFAULT NULL,
    `amount` INT(10) NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
)

Answer 1

I know this is an old thread, but I was having the same problem and found the solution. Maybe someone else will see it here.

Using Visual Studio, in the .xsd file, there is a DELETE command in the data adapter. That command is in the form of SQL "DELETE from table where column1 = @Original_column1 AND column2 = @Original_column2 AND... " by default, it compares all the columns to the values they had when you read the db, and won't delete the record unless they all match. This is to avoid overwriting changes made by others between when you read the record and when you are trying to alter it.

Here's the issue. If any of those columns is NULL in the db, it won't match and errors out as you describe.

PS: I have had nothing but trouble allowing NULLs in the DB when using the VS db libraries. LINQ2SQL or whatever it is called...

Answer 2

A concurrency Exception happens when you try to delete a record with no match in the actual database , this happens with newly added records that have an identity column as the data set will not retrieve the identity column value immediately after adding. to fix this you need

1-to retrieve the identity column value from the database by executing a SQL command then update the record in the data set with this value.

or
2- you can accept changes on the database data table by calling :

table.AcceptChanges();

then right after refill you data table by calling

Adapter.Fill(table);