使用数据适配器更新记录时发生并发错误 [英] Getting concurrency error on updating record with data adapter

问题描述

这是我的表：

学生： StudentId int PK自动增量，名称varchar（20） code>

Student:StudentId int PK autoincrement,Name varchar(20)

当我尝试更新最后添加的记录时，我会发生错误：

When i am trying to update last added records then i am geting error:

错误：并发违规：UpdateCommand影响预期1条记录的0。

这是我的代码：

using (var connection = new SqlConnection("MyConnectionstring"))
            {
                connection.Open();
                SqlDataAdapter adapter = new SqlDataAdapter();
                SqlCommandBuilder builder = new SqlCommandBuilder(adapter);

                adapter.SelectCommand = new SqlCommand("select * from Student", connection);


                DataTable dt = new DataTable();
                adapter.Fill(dt);

                DataRow row = dt.NewRow();
                row["Name"] = "Abc";
                dt.Rows.Add(row);
                var addedRecords = dt.GetChanges(DataRowState.Added);
                adapter.Update(dt);
                dt.AcceptChanges();
                DataRow lastRow = dt.Rows[dt.Rows.Count - 1];

                row["Name"] = "Pqr";
                adapter.Update(dt); //Error Here
                connection.Close();
            }  

有人可以告诉我为什么会发生这种情况，这个问题??

Can anybody please tell me why this is happening and what can be the workaround for this problem??

推荐答案

如使用CommandBuilders生成命令 MSDN主题，命令构建器生成的自动命令不会检索插入记录的标识字段：

As described in the Generating Commands with CommandBuilders MSDN topic, the automatic commands generated by the command builders do not retrieve the identity fields for the inserted records:

您可能希望将输出参数映射回DataSet的更新行。一个常见的任务是从数据源检索自动生成的标识字段或时间戳的值。默认情况下，DbCommandBuilder 不会将输出参数映射到更新行中的列。在这种情况下，您必须明确指定您的命令。

You might want to map output parameters back to the updated row of a DataSet. One common task would be retrieving the value of an automatically generated identity field or time stamp from the data source. The DbCommandBuilder will not map output parameters to columns in an updated row by default. In this instance you must specify your command explicitly.

查看检索身份或自动编号值主题，原来您基本上需要手动生成插入命令。

Looking at Retrieving Identity or Autonumber Values topic, it turns out that basically you need to generate the insert command manually.

以下是您可以如何处理表格（请参阅代码中的注释）：

Here is how you can do that for your table (see the comments inside the code):

using (var connection = new SqlConnection("MyConnectionstring"))
{
    connection.Open();

    // Create data adapter with the specified SelectCommand
    var adapter = new SqlDataAdapter("select * from Student", connection);

    // Build InsertCommand    
    var insertCommand = new SqlCommand(
        "insert into Student (Name) values (@Name) SET @Id = SCOPE_IDENTITY()", 
        connection);
    insertCommand.Parameters.Add("@Name", SqlDbType.VarChar, 20, "Name");
    var parameter = insertCommand.Parameters.Add("@Id", SqlDbType.Int, 0, "StudentId");
    parameter.Direction = ParameterDirection.Output;
    insertCommand.UpdatedRowSource = UpdateRowSource.OutputParameters;
    adapter.InsertCommand = insertCommand;

    // Auto build outher commands
    var builder = new SqlCommandBuilder(adapter);

    // Read the data
    var dt = new DataTable();
    adapter.Fill(dt);

    // Insert a new record
    var row = dt.NewRow();
    row["Name"] = "Abc";
    dt.Rows.Add(row);

    adapter.Update(dt);

    // Update the just inserted record
    row["Name"] = "Pqr";
    adapter.Update(dt);

    connection.Close();
}  

这篇关于使用数据适配器更新记录时发生并发错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！