并发冲突:DeleteCommand或UpdateCommand影响预期的1条记录中的0条. [英] Concurrency violation: the DeleteCommand or UpdateCommand affected 0 of the expected 1 records.

问题描述

使用SqlDAtaAdapter，UpdateCommand和DeleteCommand删除和更新数据库中的记录时遇到问题.

任何建议都非常有帮助.谢谢

这些是带有适配器的命令

I have trouble in deleting and updating records in the database using SqlDAtaAdapter, UpdateCommand and DeleteCommand.

Any suggestions are very helpful. Thanks

these are the commands with adapter

class SQLServer
{
    public static SqlDataAdapter daFaculty = new SqlDataAdapter();                
                // FOR UPDATING
                using(SqlCommand cmdUpdate = new SqlCommand())
                {
                    cmdUpdate.Connection = SQLServer.attendanceDB;
                    cmdUpdate.CommandText = "UPDATE tblFaculty SET idno = @idno, lastname = @lastname, firstname = @firstname, middlename = @middlename, fieldSpecs = @fieldspecs WHERE idno = '@idnoref'";
                    cmdUpdate.Parameters.Add("@idno", SqlDbType.VarChar, 0, "idno");
                    cmdUpdate.Parameters.Add("@lastname", SqlDbType.VarChar, 0, "lastname");
                    cmdUpdate.Parameters.Add("@firstname", SqlDbType.VarChar, 0, "firstname");
                    cmdUpdate.Parameters.Add("@middlename", SqlDbType.VarChar, 0, "middlename");
                    cmdUpdate.Parameters.Add("@fieldspecs", SqlDbType.VarChar, 0, "fieldspecs");
                    SqlParameter paramUpdate = cmdUpdate.Parameters.Add("@idnoref", SqlDbType.VarChar, 0, "idno");
                    paramUpdate.SourceVersion = DataRowVersion.Original;
                    daFaculty.UpdateCommand = cmdUpdate;
                }

                // FOR DELETING
                using (SqlCommand cmdDelete = new SqlCommand())
                {
                    cmdDelete.Connection = SQLServer.attendanceDB;
                    cmdDelete.CommandText = "DELETE FROM tblFaculty WHERE idno = '@oldidno'";
                    SqlParameter paramDelete = new SqlParameter();
                    paramDelete = cmdDelete.Parameters.Add("@oldidno", SqlDbType.VarChar, 0, "IDno");
                    paramDelete.SourceVersion = DataRowVersion.Original;
                    daFaculty.DeleteCommand = cmdDelete;
                }
}

这些是用于更新和删除的功能

These are the function for updating and deleting

public static void DeleteFaculty(string idno)
        {
            General.dtFaculty.Select("idno = '" + idno + "'")[0].Delete();
            SQLServer.daFaculty.Update(General.dtFaculty);
        }

        public static void UpdateFaculty(string idReference, string idno, string lastname, string firstname, string middlename, string fieldspecs)
        {
            DataRow[] row = General.dtFaculty.Select("idno = '" + idReference + "'");

            foreach (DataRow item in row)
            {
                item["lastname"] = lastname;
                item["firstname"] = firstname;
                item["middlename"] = middlename;
                item["fieldspecs"] = fieldspecs;
            }

            SQLServer.daFaculty.Update(General.dtFaculty);
        }

这是更新和删除事件

and this is the event for updating and deleting

private void btnSave_Click(object sender, EventArgs e)
{
    General.UpdateFaculty(idsel, txtID.Text, txtLname.Text, txtFname.Text, txtMname.Text, txtFieldSpec.Text);
}

        private void btnDelete_Click(object sender, EventArgs e)
        {
General.DeleteFaculty(idsel);
}

推荐答案

参考-

Refer - Concurrency violation: the UpdateCommand affected 0 of the expected 1 records[^].

报价:

为什么:

由CommandBuilder对象创建的UPDATE语句使用DataRow副本中存储的值以及DataRowVersion值为Original的值来标识和更新数据库中的适当行. CommandBuilder创建一条SQL语句，该语句查找与存储在DataSet中的所有原始值完全匹配的行，这意味着将检查该行中每一列的值.如果您尝试更新数据库中不再存在的行，则由于上述异常，DataAdapter的更新将失败.

Why:

The UPDATE statement created by the CommandBuilder object uses the values stored in the copy of the DataRow with a DataRowVersion value of Original to identify and update the appropriate row in the database. the CommandBuilder creates a SQL statement that looks for a row which exactly matches all of the original values stored in the DataSet means the value of each and every column in the row is checked. if you are trying to update a row that no longer exists in the database, the update from the DataAdapter will fail with the above exception.

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