更新和持久化数据集问题 [英] Updating and persisting dataset problem

问题描述

我想我错过了什么.这里很简单:我想更新一个数据集并将其推回它来自的数据库，但我不断得到一个:

I think I'm missing sth. trivial here : I want to update a dataset and push it back to the database where it came from, but I am keep getting a :

并发冲突:UpdateCommand 影响了 0 个预期 1 条记录.

Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.

这里是一些产生这个错误的代码:

Here's some code producing this error :

    public static void UpdateNorthWindWithDataset()
    {
        string connString =
            @"Data Source=localhost;Initial Catalog=NorthWind;Integrated Security=SSPI;";


        using (SqlConnection conn = new SqlConnection(connString))
        {
            conn.Open();


            // Declaring a DataAdapter and initiating it with a Select and updateCommand                
            SqlDataAdapter da = new SqlDataAdapter();

            SqlCommand selectCmd = new SqlCommand("SELECT CustomerId, City, Region " +
                                                  "FROM Customers"
                                                  , conn
                );

            da.SelectCommand = selectCmd;

            SqlCommand updateCmd = new SqlCommand(
                @"UPDATE Customers SET City='@City', Region='@Region'" +
                @"WHERE CustomerID = '@CustomerID'",
                conn
                );

            updateCmd.Parameters.AddRange(
                new SqlParameter[]
                    {
                        new SqlParameter()
                            {
                                ParameterName = "@CustomerID",
                                SourceColumn = "customerid"
                            },
                        new SqlParameter()
                            {
                                ParameterName = "@City",
                                SourceColumn = "city",
                                SqlDbType = SqlDbType.VarChar
                            },
                        new SqlParameter()
                            {
                                ParameterName = "@Region",
                                SourceColumn = "region",
                                SqlDbType = SqlDbType.VarChar
                            }
                    }
                );


            da.UpdateCommand = updateCmd;

            // filling dataset
            DataSet ds = new DataSet();
            da.Fill(ds, "srcCustomers");

            // declaring and editing datatable
            DataTable tblCustomers = ds.Tables["srcCustomers"];

            foreach (DataRow row in tblCustomers.Rows)
            {
                row["City"] = "justUpdated";
                row["Region"] = "justUpdated too";
            }

            da.Update(ds, "srcCustomers");
        }
    }

现在，我的最终目标是通过 OLEdb 将这种代码与 MsAccess 一起使用，但是因为我希望它尽可能清晰，所以我尝试了带有本机 .net 支持的 MSSQL(这里仍然是 2k)，但仍然出现错误...

Now, my endgoal is using this kind of code with MsAccess throug OLEdb, but because I wanted it as clear as possible, I tried MSSQL (still 2k here) with native .net support but still got the error...

推荐答案

更新失败，因为它找不到与提供的客户 ID 匹配的记录，我认为这是因为参数值未默认 -SQL 参数还有其他值可让您执行此操作.

The update is failing because it can't find a record that matches the customer ID supplied and I think that that is because the parameter value is not being defaulted - there are additional values for the SQL parameters that will allow you to do this.

如果您正在查看 OLEDB，则需要注意参数未命名(您可以并且可能应该命名它们，但它们将按照输入的顺序使用，而不是根据它们的名称 - 这也是意味着您不能两次使用相同的参数，这可能有点乏味).

If you're looking at OLEDB you need to be aware that the parameters are not named (you can and probably should name them, but they will be used in the order they are entered and not according to their names - this also means you can't use the same parameter twice which can be a bit tedious).

这篇关于更新和持久化数据集问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！