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

问题描述

我认为我错过了。我想更新一个数据集并将其推回到数据库来源，但我不断得到：

并发冲突：
UpdateCommand影响
中的0个预期的1个记录。

这是一些代码产生此错误：

  public static void UpdateNorthWindWithDataset（）
 {
 string connString = 
 @Data Source = localhost; Initial Catalog = NorthWind; Integrated Security = SSPI;; 
 
 
 using（SqlConnection conn = new SqlConnection（connString））
 {
 conn.Open（）; 
 
 
 //声明一个DataAdapter并使用Select和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; 
 
 // fill dataset 
 DataSet ds = new DataSet（）; 
 da.Fill（ds，srcCustomers）; 
 
 //声明和编辑datatable 
 DataTable tblCustomers = ds.Tables [srcCustomers]; 
 
 foreach（tblCustomers.Rows中的DataRow行）
 {
 row [City] =justUpdated; 
 row [Region] =justUpdated too; 
} 
 
 da.Update（ds，srcCustomers）; 
} 
} 
  

现在，我的endgoal正在使用这种代码MsAccess通过OLEdb，但是因为我想要尽可能的清楚，我尝试使用本机.net支持的MSSQL（这里仍然是2k），但仍然有错误...

解决方案

更新失败，因为它找不到与提供的客户ID匹配的记录，我认为这是因为参数值不是默认值 - SQL还有其他值参数将允许您这样做。

如果您正在查看OLEDB，您需要注意参数未命名（您可以或可能应该将其命名，但它们将按照他们输入的顺序使用，而不是按照他们的名字使用 - 这也意味着您不能使用两次相同的参数，这可能有点乏味）。

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 :

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");
        }
    }

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...

解决方案

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.

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屋！