C# database error: Concurrency violation: the DeleteCommand affected 0 of the expected 1 records

Question

0.00/5 (No votes)

See more:

Hi ! Please help me !
I make the following c# program. The program work OK, except in one case: when Add, Save and immediately Deleted an record in DB, the programm have an error: "Concurrency violation: the DeleteCommand affected 0 of the expected 1 records" at the method: da.Update(ds1, "Workers");.

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 DbfProject
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
        int i = 0;
        int MaxRows = 0;
        DataSet ds1;
        System.Data.SqlClient.SqlDataAdapter da;

        private void Form1_Load(object sender, EventArgs e)
        {
           
         conn.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\MyWorkers.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
            //conn.Open();
            //MessageBox.Show("Database Open");
            ds1 = new DataSet();
            string sql = "SELECT * From tblWorkers";
            da = new System.Data.SqlClient.SqlDataAdapter(sql, conn);
            //conn.Close();
            da.Fill(ds1, "Workers");
          
            MaxRows = ds1.Tables["Workers"].Rows.Count;
            NavigateRecords();
          
        }
        private void NavigateRecords()
        {
            if (i >= 0)
            {
                DataRow dRow = ds1.Tables["Workers"].Rows[i];
                textBox1.Text = dRow.ItemArray.GetValue(1).ToString();
                textBox2.Text = dRow.ItemArray.GetValue(2).ToString();
                textBox3.Text = dRow.ItemArray.GetValue(3).ToString();
                label4.Text = "Record " + (i + 1).ToString() + " from " + (MaxRows).ToString();
             }
            else MessageBox.Show("No Records");
         
         }
        
        private void btnNext_Click(object sender, EventArgs e)
        {
            if (i < MaxRows-1)
            {
                i++;
                NavigateRecords();
            }
            else MessageBox.Show("No more rows");
        }
        private void btnPrevious_Click(object sender, EventArgs e)
        {
            if (i>0)
            {
                i--;
                NavigateRecords();
            }
            else MessageBox.Show("First record");
        }
        private void btnFirst_Click(object sender, EventArgs e)
        {
            if (i != 0)
            {
                i=0;
                NavigateRecords();
            }
            else MessageBox.Show("This is the first record");
        }
        private void btnLast_Click(object sender, EventArgs e)
        {
            if (i !=MaxRows-1)
            {
                i = MaxRows - 1;
                NavigateRecords();
            }
            else MessageBox.Show("This is the last record");
        }
        private void btnAddNew_Click(object sender, EventArgs e)
        {
            textBox1.Clear();
            textBox2.Clear();
            textBox3.Clear();
            btnUpdate.Enabled=btnDelete.Enabled = btnFirst.Enabled = btnLast.Enabled = btnNext.Enabled = btnPrevious.Enabled = false;
            btnSave.Enabled = true;
            label4.Text = "Record " + (MaxRows + 1).ToString() + " from " + (MaxRows + 1).ToString();
        }
        private void btnUpdate_Click(object sender, EventArgs e)
        {
            System.Data.SqlClient.SqlCommandBuilder cb;
            cb = new System.Data.SqlClient.SqlCommandBuilder(da);
            DataRow dRowUp = ds1.Tables["Workers"].Rows[i];
            dRowUp[1] = textBox1.Text;
            dRowUp[2] = textBox2.Text;
            dRowUp[3] = textBox3.Text;
            da.Update(ds1, "Workers");
            MessageBox.Show("Data Update");
            label4.Text = "Record " + (i + 1).ToString() + " from " + MaxRows.ToString();
        }
        private void btnSave_Click(object sender, EventArgs e)
        {
           System.Data.SqlClient.SqlCommandBuilder cb;
           cb = new System.Data.SqlClient.SqlCommandBuilder(da);
           
            DataRow dRow = ds1.Tables["Workers"].NewRow();
            dRow[1] = textBox1.Text;
            dRow[2] = textBox2.Text;
            dRow[3] = textBox3.Text;
            ds1.Tables["Workers"].Rows.Add(dRow);
            da.Update(ds1, "Workers");
            MaxRows = ds1.Tables["Workers"].Rows.Count;
            i = MaxRows; 
            btnSave.Enabled = false;
            btnUpdate.Enabled = btnDelete.Enabled=btnFirst.Enabled = btnLast.Enabled = btnNext.Enabled = btnPrevious.Enabled = true;
            label4.Text = "Record " + i.ToString() + " from " + MaxRows.ToString();
            i--;
        }
        private void btnDelete_Click(object sender, EventArgs e)
        {
            System.Data.SqlClient.SqlCommandBuilder cb;
            cb = new System.Data.SqlClient.SqlCommandBuilder(da);
            //MessageBox.Show(i.ToString());
            ds1.Tables["Workers"].Rows[i].Delete();
            da.Update(ds1, "Workers");            
            MaxRows--;
            i--;         
            NavigateRecords();
            label4.Text = "Record " + (i+1).ToString() + " from " + MaxRows.ToString();
            MessageBox.Show("Data Deleted");
            
        }
        private void label4_Click(object sender, EventArgs e)
        {
            
        }   
    }
}

Posted 26-Sep-11 10:22am

tommmyyy123

Add a Solution

Comments

André Kraak 26-Sep-11 16:47pm

Does your table contain an auto number field?

tommmyyy123 26-Sep-11 17:02pm

YES

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

André Kraak · Accepted Answer · 2011-09-26T11:11:00

Solution 1

When you add a new record to the DataSet and apply it to the database the new auto number value is not placed into the row of the DataSet.
Now when you call a delete on this row the generated SQL statement has a WHERE clause with an invalid auto number value. This means that the row to be deleted cannot be found and the DBConcurrencyException exception is thrown.

To solve this you need to retrieve the assigned auto number and store it in the DataSet row. Do this in your save function btnSave_Click after the da.Update(ds1, "Workers"); statement.

Have a look at this[^] on how to retrieve the new auto number value.

Posted 26-Sep-11 11:11am

André Kraak

Updated 26-Sep-11 11:16am

v2

Comments

tommmyyy123 26-Sep-11 17:20pm

But, this error was heppend ONLY when I Add + Save +Delete record. If I want to delete any record, but not the last Add, the program work.

André Kraak 27-Sep-11 1:11am

Have you tried debugging to verify the auto number value both just after adding a new row and just before trying to remove it?

tommmyyy123 27-Sep-11 2:29am

No, it's a good ideea

tommmyyy123 27-Sep-11 2:30am

I try this

tommmyyy123 27-Sep-11 3:53am

In btnSave_Click and btnUpdate_Click I put the following code:
private void btnDelete_Click(object sender, EventArgs e)
{
System.Data.SqlClient.SqlCommandBuilder cb;
cb = new System.Data.SqlClient.SqlCommandBuilder(da);

DataSet ds1 = new DataSet();
da.Fill(ds1, "Workers");
//MessageBox.Show(i.ToString());
ds1.Tables["Workers"].Rows[i].Delete();
da.Update(ds1, "Workers");

MaxRows--;
i--;
NavigateRecords();
label4.Text = "Record " + (i+1).ToString() + " from " + MaxRows.ToString();
MessageBox.Show("Data Deleted");

}

and

private void btnUpdate_Click(object sender, EventArgs e)
{
System.Data.SqlClient.SqlCommandBuilder cb;
cb = new System.Data.SqlClient.SqlCommandBuilder(da);
DataSet ds1 = new DataSet();
da.Fill(ds1, "Workers");
DataRow dRowUp = ds1.Tables["Workers"].Rows[i];
dRowUp[1] = textBox1.Text;
dRowUp[2] = textBox2.Text;
dRowUp[3] = textBox3.Text;
da.Update(ds1, "Workers");
MessageBox.Show("Data Update");
label4.Text = "Record " + (i + 1).ToString() + " from " + MaxRows.ToString();
}

and now it's OK. Thank's