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

问题描述

我是 C# 新手，并尝试在 Visual Studio 2010 中将 datagridview 绑定到 mssql 数据库.数据绑定正常，一切似乎都正常.除了一些奇怪的错误:

I am new to c# and try to bind a datagridview to a mssql database in visual studio 2010. The databindings are OK and everything seems to work. Except for a few strange errors:

我在以下之后收到主题中的错误:更新同一行 2 次，删除新插入的行，在删除另一行时更新一行后(单词更改为 DeleteCommand)

I get the error in the subject after: updating the same row 2 times, deleting a new inserted row, after updating a row when an other row was deleted (word changes to DeleteCommand)

我在 Google 上找到的所有解决方案都不适合我.我希望有人能帮我解决这个问题.下面是代码:

None of the solutions I found on Google workes for me. I hope someone can help me with this. Here is te code:

    private void fillDatagrid()
        {
            //fill datagrid ADO.NET
            conn = new SqlConnection(TestApp.Properties.Settings.Default.TestdatabaseConnectionString);
            cmd = conn.CreateCommand();
            conn.Open();
            cmd.CommandText = "SelectFrom";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@table", SqlDbType.NVarChar, 50).Value = "Countries";
            cmd.Parameters.Add("@filters", SqlDbType.NVarChar, 300).Value = "";

            adapt = new SqlDataAdapter(cmd);
            dt = new DataTable();
            adapt.Fill(dt);
            dt.TableName = "Countries";

            conn.Close();

            BindingSource src = new BindingSource();
            src.DataSource = dt;
            dt.RowChanged += new DataRowChangeEventHandler(dt_RowChanged);

            dgDatabaseGrid.DataSource = src;
            dgDatabaseGrid.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells);
            //dgDatabaseGrid.RowValidating += new DataGridViewCellCancelEventHandler(dgDatabaseGrid_RowValidating);

            //disable columns:
            dgDatabaseGrid.Columns[0].Visible = false;
            dgDatabaseGrid.Columns["date_insert"].Visible = false;
            dgDatabaseGrid.Columns["user_insert"].Visible = false;
            dgDatabaseGrid.Columns["date_change"].Visible = false;
            dgDatabaseGrid.Columns["user_change"].Visible = false;
            dgDatabaseGrid.Columns["deleted"].Visible = false;

            //auto size last column
            dgDatabaseGrid.Columns["remarks"].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;


            SqlCommandBuilder cb = new SqlCommandBuilder(adapt);
        }

        void dt_RowChanged(object sender, DataRowChangeEventArgs e)
        {
            try
            {
                adapt.Update(dt);
            }
            catch (SqlException ex)
            {
                Debug.WriteLine(ex.Message);
            }
        }

private void dgDatabaseGrid_UserDeletingRow(object sender, DataGridViewRowCancelEventArgs e)
        {
            if (!e.Row.IsNewRow)
            {


                DialogResult response = MessageBox.Show("Are you sure?", "Delete row?",
                                     MessageBoxButtons.YesNo,
                                     MessageBoxIcon.Question,
                                     MessageBoxDefaultButton.Button2);

                if (response == DialogResult.Yes)
                {

                    //ipv delete --> deleted=1
                    conn.Open();
                    cmd = conn.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "DeleteFrom";
                    cmd.Parameters.Add("@table", SqlDbType.NVarChar, 50).Value = "Countries";
                    cmd.Parameters.Add("@id", SqlDbType.Int).Value = e.Row.Cells[0].Value;
                    cmd.ExecuteNonQuery();
                    conn.Close();


                    //delete from datagrid:
                    dt.Rows[dgDatabaseGrid.SelectedCells[0].RowIndex].Delete();

                }

                //always cancel!
                e.Cancel = true;

            }
        }

推荐答案

更新同一行2次后

after updating the same row 2 times

是否有时间戳列(或在数据库服务器上更改/填充的任何其他列)?

Is there a Timestamp column (or any other column that is changed/filled on the Db server) ?

当内存行与数据库中的行不同时，您的问题可能会发生.并且因为您为 SelectCmd 使用了 SP，所以更新后(可能)不会刷新.

Your problem could happen when the in-memory row is different from what's in the Db. And because you use a SP for the SelectCmd there (probably) is no refresh after an update.

删除新插入的行后

类似，导致插入后无法获取新的Id

Similar, caused not fetching the new Id after an insert

在另一行被删除时更新一行后(单词更改为 DeleteCommand)

after updating a row when an other row was deleted (word changes to DeleteCommand)

完全不清楚.
但是为什么要手动"删除行而不是将其留给 adapt.Update() ?而且你确定不是两种方法都被执行了吗?

totally unclear.
But why do you Delete rows 'manually' instead of leaving it to the adapt.Update() ? And are you sure that not both methods are executed?

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