Question why do i get Error: Concurrency violation: the UpdateCommand affected 0 of the expected 1 records

[xto]

I save the changes in the database by clicking on the button, here is the code itself:

 else if (comboBox1.SelectedItem == "Работа")
{
label3.Text = "Работа";
string script = "SELECT id, name, Model_preparation_R_Hr, Time_for_preparation_hr, Time_for_post_processing_hr, YZV_work FROM rabota;";
mycon = new MySqlConnection(connect);
mycon.Open();
MySqlDataAdapter ms_data = new MySqlDataAdapter(script, connect);
var cb = new MySqlCommandBuilder(ms_data);
cb.GetInsertCommand();
DataSet ds = new DataSet();
ms_data.Update(dataGridView1.DataSource as DataTable);
ms_data.InsertCommand = cb.GetInsertCommand();
ms_data.InsertCommand.CommandText += "; select * from rabota where id = last_insert_id();";
ms_data.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;        
}

I have several blocks of such code for each ComboBox item
All of them are the same, but it gives an error on this one, why can this be so?

An example of a working block:

else if (comboBox1.SelectedItem == "Rash")
{
label3.Text = "rash";
string script = "SELECT id, name, Spirt_10_Litr, gloves, knife, detergent_PH11_Litr FROM rash;";
mycon = new MySqlConnection(connect);
mycon.Open();
MySqlDataAdapter ms_data = new MySqlDataAdapter(script, connect);
var cb = new MySqlCommandBuilder(ms_data);
cb.GetInsertCommand();
ms_data.Update(dataGridView1.DataSource as DataTable);
ms_data.InsertCommand = cb.GetInsertCommand();
ms_data.InsertCommand.CommandText += "; select * from rash where id = last_insert_id();";
ms_data.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
}

I also know that the error occurs in the line:

ms_data.Update(dataGridView1.DataSource as DataTable);

What am i doing wrong?

 

[jmcilhinney]

Please use CODE tags for blocks of code and ICODE (inline code) tags only for inline code, e.g. when you refer to type string in a sentence.

 

[jmcilhinney]

I would suggest that you don't use a command builder at all. Create a single data adapter when you retrieve the data and use that same object when saving the data. Write your own INSERT command there.

It's hard to say exactly what you're doing wrong because we don't have access to the data or other relevant code. A concurrency violation occurs when you try to save data where the original version of the data that you have does not match what is currently in the database. The legitimate reason for that happening is that it is a multi-user app and another user has saved changes since you retrieved the data. In that circumstance, you generally want to retrieve the current data from the data and have the user either start editing again or try to merge their changes. You don't want to simply overwrite as though the other user never saved anything.

It possible to mess up in a number of ways to wind up with an illegitimate concurrency violation. One of the most common is to create a record and save it to the database but not retrieve the auto-generated PK value from the database, then try to edit that record and save it again. The database will contain the correct ID but your DataTable will not, so a concurrency violation occurs. Looking at your code more closely, I suspect that that may be what you're doing because there's some nonsense there. This make little sense:

MySqlDataAdapter ms_data = new MySqlDataAdapter(script, connect);
var cb = new MySqlCommandBuilder(ms_data);
cb.GetInsertCommand();
ms_data.Update(dataGridView1.DataSource as DataTable);
ms_data.InsertCommand = cb.GetInsertCommand();
ms_data.InsertCommand.CommandText += "; select * from rash where id = last_insert_id();";
ms_data.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;

You create the data adapter, create the command builder, pointlessly get the INSERT command and then save the data. After saving, you then get the INSERT command again and put that to the into data adapter, then you add a SELECT statement to get the auto-generated PK. What's the point of adding that SELECT statement after you've already saved the changes? Think about the sequence of events you're trying to implement and then write code to implement that. Surely you need to add the SELECT command to the INSERT command before executing the INSERT command. This is an example of why beginners are taught to write out their algorithm first, then write code to implement it. If you have the steps written down then you always have that to compare your code to to make sure that it's not doing wacky things like a later step before an earlier one.

 

[Skydiver]

What is the type of ms_data?

As an aside, I see you opening connections but never closing or disposing them.

 

[jmcilhinney]

As an aside, I see you opening connections but never closing or disposing them.

One generally shouldn't open connections explicitly with data adapters anyway. Both Fill and Update will open a connection if necessary and then leave it in the state it found it. If you open a connection yourself then the data adapter will leave it open, whereas it will close it if it had to open it. You should only open the connection yourself if you are calling Fill and/or Update multiple times with the same connection, in which case you need to close it yourself too.

 

[xto]

It possible to mess up in a number of ways to wind up with an illegitimate concurrency violation. One of the most common is to create a record and save it to the database but not retrieve the auto-generated PK value from the database, then try to edit that record and save it again. The database will contain the correct ID but your DataTable will not, so a concurrency violation occurs. Looking at your code more closely, I suspect that that may be what you're doing because there's some nonsense there. This make little sense:

I dont understand why this exact table gives me an Error when i use this block of code for 6 different tables and they work with no issues.
And i also can insert new rows and update them but i cant update any old rows

 

[jmcilhinney]

The code is bad as it is so should definitely be changed. If it's working in some cases then that's just by accident. It should probably work to add and then edit new rows if there isn't already any data in the database, because the temporary PKs generated by the DataTable could match the final PKs generated by the database. Modifying records already in the database should probably work too, but adding and then editing new rows would not work if the database table already contained data.

 

[xto]

The code is bad as it is so should definitely be changed. If it's working in some cases then that's just by accident. It should probably work to add and then edit new rows if there isn't already any data in the database, because the temporary PKs generated by the DataTable could match the final PKs generated by the database. Modifying records already in the database should probably work too, but adding and then editing new rows would not work if the database table already contained data.

I tried to make an insertion, deletion and updating through my DataGridView and not using TextBoxes and this is the only "working" code i made and it was working until one moment...
as i remember it did even work on this exact table that doesnt work now.
This doesnt make any sense, i deleted all the data from this table and through the app i added 3 rows, it gives me an error on first 2 rows but it doesnt complain about the third one and i can update the third one without any problems, i just dont get it

 

[xto]

The code is bad as it is so should definitely be changed. If it's working in some cases then that's just by accident. It should probably work to add and then edit new rows if there isn't already any data in the database, because the temporary PKs generated by the DataTable could match the final PKs generated by the database. Modifying records already in the database should probably work too, but adding and then editing new rows would not work if the database table already contained data.

And I also now think, can there be a conflict between two different blocks of code because at first i fill my dataGridView with data from database with this code:

else if (comboBox1.SelectedItem == "Работа")
{
label3.Text = "Работа";
string script = "SELECT * FROM hardlight.rabota;";
mycon = new MySqlConnection(connect);
mycon.Open();
MySqlDataAdapter ms_data = new MySqlDataAdapter(script, connect);
SD.DataTable table = new SD.DataTable();
ms_data.Fill(table);
DataSet ds = new DataSet();
dataGridView1.DataSource = table;
mycon.Close();
ds.AcceptChanges();
}

And then i insert/delete/update data with the code that i added in my question before, so maybe this two different blocks cant work properly together in one code...
By the way the block with "insertion" is in Button_click
And this block is in comboBox1_SelectedIndexChanged