C#数据库错误:并发冲突:DeleteCommand影响预期的1条记录中的0条 [英] C# database error: Concurrency violation: the DeleteCommand affected 0 of the expected 1 records

问题描述

嗨！请帮帮我！
我制作以下c#程序.该程序运行正常，但在以下情况下除外:在DB中添加，保存并立即删除记录时，程序在方法da.Update()中出现错误:并发冲突:DeleteCommand影响了预期的1条记录中的0条". ds1，工人");.

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)
        {
            
        }   
    }
}

推荐答案

将新记录添加到DataSet并将其应用于数据库时，新的自动编号值不会放入DataSet.
现在，当您在此行上调用删除操作时，生成的SQL语句将具有WHERE子句，该子句的自动编号值无效.这意味着找不到要删除的行，并且抛出了DBConcurrencyException异常.

为了解决这个问题，您需要检索分配的自动编号并将其存储在DataSet行中.在da.Update(ds1, "Workers");语句之后，在保存函数btnSave_Click中执行此操作.

看看此 [

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.

这篇关于C#数据库错误:并发冲突:DeleteCommand影响预期的1条记录中的0条的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！