CODEWITHSUNDEEP
c#sql-server
Shanmugaraja_K
Shanmugaraja_K

Reputation: 2392

SQL Server : data updated same row values in all rows while using update command

SQL Server data updated same row values in all rows while using update command.

try {
    SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["NORTHWNDConnectionString"].ToString());
    myConnection.Open();

    foreach (var i in ord) {
        SqlCommand sqlcm = new SqlCommand("update Orders Set CustomerID = @CustomerID, EmployeeID = @EmployeeID, ShipVia = @ShipVia, ShipName = @ShipName, ShipAddress = @ShipAddress, ShipCity = @ShipCity, ShipCountry = @ShipCountry", myConnection);

        sqlcm.Parameters.AddWithValue("@CustomerID", i.CustomerID);
        sqlcm.Parameters.AddWithValue("@EmployeeID", i.EmployeeID);
        sqlcm.Parameters.AddWithValue("@ShipVia", i.ShipVia);
        sqlcm.Parameters.AddWithValue("@ShipName", i.ShipName);
        sqlcm.Parameters.AddWithValue("@ShipAddress", i.ShipAddress);
        sqlcm.Parameters.AddWithValue("@ShipCity", i.ShipCity);
        sqlcm.Parameters.AddWithValue("@ShipCountry", i.ShipCountry);

        sqlcm.ExecuteNonQuery();
        sqlcm.Dispose();
    }

    myConnection.Close();
}

Using this C# code, the result will be same data in all rows:

10248   VINET   5   3   Vins et alcools Chevalier   59 rue de l'Abbaye  Reimsesddf  France
10249   VINET   5   3   Vins et alcools Chevalier   59 rue de l'Abbaye  Reimsesddf  France
10250   VINET   5   3   Vins et alcools Chevalier   59 rue de l'Abbaye  Reimsesddf  France

I have changed Reims to Reimsesddf for 10248 id, but it reflects in all rows and also other data will updated in all rows.

How can I fix this ?

Upvotes: 0

Views: 53

Answers (1)

Andrey Korneyev
Andrey Korneyev

Reputation: 26876

Your update statement missing where condition - so it updates all records in table:

update Orders Set 
  CustomerID = @CustomerID, EmployeeID= @EmployeeID, 
  ShipVia=@ShipVia, ShipName=@ShipName,
  ShipAddress=@ShipAddress,ShipCity=@ShipCity,
  ShipCountry= @ShipCountry

Add some condition in where (by ID of record or whatever) to prevent it:

update Orders Set 
  CustomerID = @CustomerID, EmployeeID= @EmployeeID, 
  ShipVia=@ShipVia, ShipName=@ShipName,
  ShipAddress=@ShipAddress,ShipCity=@ShipCity,
  ShipCountry= @ShipCountry
where ID = @ID

Upvotes: 6

Related Questions