Databases
StackOverflow2013
Postsdbo
POHow do I get rid of the concurrency issue with update statements with an auto-incrementing key?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow do I get rid of the concurrency issue with update statements with an auto-incrementing key?
    primarykey
    data
    text
    <p>I have run into the following error message at run-time with my novice's Sql / Visual C# database program:</p> <p>Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.</p> <p>I have done some searching around about this issue and have seen several threads about it, but they aren't enough for me to resolve the issue. I noticed people talking about how it can often occur when you're using an auto-incrementing primary key. That is the case in this program. Also there is no multithreading or anything else like that that I am coding into this program. Therefore I think the auto-increment is possibly the only problem.</p> <p>That being said, how do I get the update command to work, despite the auto-increment? I can hardly stand on two feet when it comes to database programming, so please be detailed, if you don't mind. Also the command I'm using is via a SqlCommandBuilder object. I have set that object to new SqlCommandBuilder(DataAdapter), and I have not done anything special with it.</p> <p>Thanks.</p> <hr> <p>This edit is the second one. The first one is below.</p> <p>Due to my inexperience with database programming, I am unable to say this for sure. However I have good reason to believe that the problem I am experiencing has to do with new rows not getting added to the database completely until the program terminates. I do not understand why they are waiting until program termination to do that, or if they are waiting until then, just what exactly what about the program's termination causes them to suddenly get saved completely. However I have forgotten to mention that this error only occurs on rows that I have added during that specific execution of the program. If the row was already added on a previous execution or through pre-existing table data, everything's fine. I am getting the same error with the delete method, and it also only occurs with new rows.</p> <p>How do I get these rows to be fully saved to everything so that this doesn't happen? What about the program's termination is causing these rows to get fully saved? Thanks!</p> <hr> <p>Due to a request, I have left here two code snippets. The first one will be the method in which the the problem occurs. The next one will include the entire class. There are only two classes in the entire program, and the other class doesn't seem important to me in this particular issue.</p> <pre><code> private void btnUpdate_Click(object sender, EventArgs e) { if (recordShown) { con.Open(); currentRow[1] = tbFirstName.Text; currentRow[2] = tbMiddleName.Text; currentRow[3] = tbLastName.Text; currentRow[4] = tbSuffix.Text; currentRow[5] = tbHomePhone.Text; currentRow[6] = tbCellPhone.Text; currentRow[7] = tbOtherPhone.Text; currentRow[8] = tbStreetAddress.Text; currentRow[9] = tbCityAndState.Text; currentRow[10] = tbCountry.Text; currentRow[11] = tbEmail.Text; dAdapter.Update(dataset, "Contacts"); con.Close(); } else { MessageBox.Show("Please locate/add a record first."); } } </code></pre> <p>Next snippet:</p> <pre><code>using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using System.Data.SqlClient; namespace Dakota { public partial class Form1 : Form { SqlConnection con; DataSet dataset; SqlDataAdapter dAdapter; DataRow currentRow; string primaryKey; SqlCommandBuilder cmdBuilder; bool recordShown = false; public Form1() { InitializeComponent(); } private void Form1_Load(object sender, EventArgs e) { dataset = new DataSet(); con = new SqlConnection(); con.ConnectionString = "Data Source=.\\SQLEXPRESS;" + "AttachDbFilename=C:\\Users\\Sterling\\Documents\\Contacts.mdf;" + "Integrated Security=True;Connect Timeout=30;User Instance=True"; con.Open(); string getData = "SELECT * FROM tblContacts"; dAdapter = new SqlDataAdapter(getData, con); dAdapter.Fill(dataset, "Contacts"); cmdBuilder = new SqlCommandBuilder(dAdapter); cmdBuilder.ConflictOption = ConflictOption.OverwriteChanges; con.Close(); } private void clearTextBoxes() { tbFirstName.Clear(); tbMiddleName.Clear(); tbLastName.Clear(); tbSuffix.Clear(); tbHomePhone.Clear(); tbCellPhone.Clear(); tbOtherPhone.Clear(); tbStreetAddress.Clear(); tbCityAndState.Clear(); tbCountry.Clear(); tbEmail.Clear(); } private void fillTextBoxes(int row) { DataRow dr = dataset.Tables["Contacts"].Rows[row]; tbFirstName.Text = dr.ItemArray.GetValue(1).ToString(); tbMiddleName.Text = dr.ItemArray.GetValue(2).ToString(); tbLastName.Text = dr.ItemArray.GetValue(3).ToString(); tbSuffix.Text = dr.ItemArray.GetValue(4).ToString(); tbHomePhone.Text = dr.ItemArray.GetValue(5).ToString(); tbCellPhone.Text = dr.ItemArray.GetValue(6).ToString(); tbOtherPhone.Text = dr.ItemArray.GetValue(7).ToString(); tbStreetAddress.Text = dr.ItemArray.GetValue(8).ToString(); tbCityAndState.Text = dr.ItemArray.GetValue(9).ToString(); tbCountry.Text = dr.ItemArray.GetValue(10).ToString(); tbEmail.Text = dr.ItemArray.GetValue(11).ToString(); } private void fillTextBoxes(DataRow dr) { tbFirstName.Text = dr.ItemArray.GetValue(1).ToString(); tbMiddleName.Text = dr.ItemArray.GetValue(2).ToString(); tbLastName.Text = dr.ItemArray.GetValue(3).ToString(); tbSuffix.Text = dr.ItemArray.GetValue(4).ToString(); tbHomePhone.Text = dr.ItemArray.GetValue(5).ToString(); tbCellPhone.Text = dr.ItemArray.GetValue(6).ToString(); tbOtherPhone.Text = dr.ItemArray.GetValue(7).ToString(); tbStreetAddress.Text = dr.ItemArray.GetValue(8).ToString(); tbCityAndState.Text = dr.ItemArray.GetValue(9).ToString(); tbCountry.Text = dr.ItemArray.GetValue(10).ToString(); tbEmail.Text = dr.ItemArray.GetValue(11).ToString(); } private void btnSearch_Click(object sender, EventArgs e) { string searchFor = tbSearchFor.Text; string column; if (rbFirstName.Checked) { column = "firstName"; } else { column = "lastName"; } DataRow[] rows = dataset.Tables["Contacts"].Select(column + "='" + searchFor + "'"); int number = rows.Length; if (number == 0) { MessageBox.Show("No such records were found."); } else if (number &gt; 1) { string[] strings = new string[rows.Length]; for (int i = 0; i &lt; strings.Length; i++) { bool hasFirst = false; bool hasMiddle = false; strings[i] = ""; if (rows[i].ItemArray.GetValue(1).ToString() != "") { hasFirst = true; strings[i] += rows[i].ItemArray.GetValue(1).ToString(); } if (rows[i].ItemArray.GetValue(2).ToString() != "") { hasMiddle = true; if (hasFirst) { strings[i] += " "; } strings[i] += rows[i].ItemArray.GetValue(2).ToString(); } if (rows[i].ItemArray.GetValue(3).ToString() != "") { if ((hasFirst &amp;&amp; !hasMiddle) || (hasMiddle)) { strings[i] += " "; } strings[i] += rows[i].ItemArray.GetValue(3).ToString(); } if (rows[i].ItemArray.GetValue(4).ToString() != "") { strings[i] += " " + rows[i].ItemArray.GetValue(4).ToString(); } } // int choice; Form2 form2 = new Form2(strings); if (form2.ShowDialog(this) == DialogResult.OK) { primaryKey = rows[form2.choice].ItemArray.GetValue(0).ToString(); // choice = form2.choice; fillTextBoxes(rows[form2.choice]); currentRow = rows[form2.choice]; recordShown = true; } } else { primaryKey = rows[0].ItemArray.GetValue(0).ToString(); currentRow = rows[0]; fillTextBoxes(rows[0]); recordShown = true; } } private void btnAdd_Click(object sender, EventArgs e) { con.Open(); DataRow row = dataset.Tables["Contacts"].NewRow(); row[1] = tbFirstName.Text; row[2] = tbMiddleName.Text; row[3] = tbLastName.Text; row[4] = tbSuffix.Text; row[5] = tbHomePhone.Text; row[6] = tbCellPhone.Text; row[7] = tbOtherPhone.Text; row[8] = tbStreetAddress.Text; row[9] = tbCityAndState.Text; row[10] = tbCountry.Text; row[11] = tbEmail.Text; currentRow = row; dataset.Tables["Contacts"].Rows.Add(row); dAdapter.Update(dataset, "Contacts"); recordShown = true; con.Close(); } private void btnUpdate_Click(object sender, EventArgs e) { if (recordShown) { con.Open(); currentRow[1] = tbFirstName.Text; currentRow[2] = tbMiddleName.Text; currentRow[3] = tbLastName.Text; currentRow[4] = tbSuffix.Text; currentRow[5] = tbHomePhone.Text; currentRow[6] = tbCellPhone.Text; currentRow[7] = tbOtherPhone.Text; currentRow[8] = tbStreetAddress.Text; currentRow[9] = tbCityAndState.Text; currentRow[10] = tbCountry.Text; currentRow[11] = tbEmail.Text; dAdapter.Update(dataset, "Contacts"); con.Close(); } else { MessageBox.Show("Please locate/add a record first."); } } private void btnDelete_Click(object sender, EventArgs e) { con.Open(); currentRow.Delete(); dAdapter.Update(dataset, "Contacts"); clearTextBoxes(); recordShown = false; con.Close(); } } } </code></pre> <p>Thanks!</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USPanzercrisis
    1. USPanzercrisis
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow do I get rid of the concurrency issue with update statements with an auto-incrementing key?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHow do I get rid of the concurrency issue with update statements with an auto-incrementing key?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POHow do I get rid of the concurrency issue with update statements with an auto-incrementing key?
      1. This table or related slice is empty.
      1. VTDownMod
    1. COCan you show us the code that is causing the problem?
      singulars
      1. POHow do I get rid of the concurrency issue with update statements with an auto-incrementing key?
      1. USBeth Whitezel
    2. COI'm having a little trouble following you. I looks like you're saying something about how the auto-increment should work just fine, as long as weird stuff isn't done. But are you saying that that isn't the problem, or that my code mandates me turning off the auto-increment and manually coding my own?
      singulars
      1. POHow do I get rid of the concurrency issue with update statements with an auto-incrementing key?
      1. USPanzercrisis
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload