Updating and persisting dataset problem

Question

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

Answer 1

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