UPDATE statement changes data for all the records in the DB

[Guest]

Can someone tell me why the following procedure updates ALL the records in
the database with the field being updated for one record?

private void updateRow(object source,
System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
SqlConnection conn = new SqlConnection
(ConfigurationSettings.AppSettings["SqlConnectionString"]);

SqlCommand updCommand = new SqlCommand();
updCommand.Connection = conn;
updCommand.CommandText = "UPDATE GEM.customers SET "
+ "name = '" + ((TextBox)e.Item.Cells[5].Controls[0]).Text
+ "', address_1 = '" + ((TextBox)e.Item.Cells[9].Controls[0]).Text
+ "', city = '" + ((TextBox)e.Item.Cells[10].Controls[0]).Text
+ "', state = '" + ((TextBox)e.Item.Cells[11].Controls[0]).Text
+ "', zip_code = '" + ((TextBox)e.Item.Cells[12].Controls[0]).Text
+ "', postal_code = '" + ((TextBox)e.Item.Cells[13].Controls[0]).Text
+ "', country = '" + ((TextBox)e.Item.Cells[14].Controls[0]).Text
+ "', phone = '" + ((TextBox)e.Item.Cells[15].Controls[0]).Text + "'"
+ " FROM GEM.customers INNER JOIN GEM.config_usernames ON "
+ "GEM.customers.cust_id = GEM.config_usernames.cust_id INNER JOIN "
+ "GEM.contacts ON GEM.config_usernames.cust_id =
GEM.contacts.contact_id";

//updates contacts information
// updCommand.CommandText = "UPDATE GEM.contacts SET "
// + " name_first = '" + ((TextBox)e.Item.Cells[6].Controls[0]).Text
// + "', name_last = '" + ((TextBox)e.Item.Cells[7].Controls[0]).Text
// + "', email = '" + ((TextBox)e.Item.Cells[8].Controls[0]).Text + "'"
// + " FROM GEM.config_usernames INNER JOIN"
// + " GEM.contacts ON GEM.config_usernames.contact_id =
GEM.contacts.contact_id INNER JOIN"
// + " GEM.customers ON GEM.config_usernames.cust_id =
GEM.customers.cust_id";


SqlDataAdapter adapter = new SqlDataAdapter(updCommand);

DataSet ds = new DataSet();

updCommand.CommandType = CommandType.Text;
conn.Open();
updCommand.ExecuteNonQuery();
adapter.Fill(ds);
dgCustInfo.EditItemIndex = -1;
conn.Close();
bindData();
}

 

[pvdg42]

Can someone tell me why the following procedure updates ALL the records in
the database with the field being updated for one record?

private void updateRow(object source,
System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
SqlConnection conn = new SqlConnection
(ConfigurationSettings.AppSettings["SqlConnectionString"]);

SqlCommand updCommand = new SqlCommand();
updCommand.Connection = conn;
updCommand.CommandText = "UPDATE GEM.customers SET "
+ "name = '" + ((TextBox)e.Item.Cells[5].Controls[0]).Text
+ "', address_1 = '" + ((TextBox)e.Item.Cells[9].Controls[0]).Text
+ "', city = '" + ((TextBox)e.Item.Cells[10].Controls[0]).Text
+ "', state = '" + ((TextBox)e.Item.Cells[11].Controls[0]).Text
+ "', zip_code = '" + ((TextBox)e.Item.Cells[12].Controls[0]).Text
+ "', postal_code = '" + ((TextBox)e.Item.Cells[13].Controls[0]).Text
+ "', country = '" + ((TextBox)e.Item.Cells[14].Controls[0]).Text
+ "', phone = '" + ((TextBox)e.Item.Cells[15].Controls[0]).Text + "'"
+ " FROM GEM.customers INNER JOIN GEM.config_usernames ON "
+ "GEM.customers.cust_id = GEM.config_usernames.cust_id INNER JOIN "
+ "GEM.contacts ON GEM.config_usernames.cust_id =
GEM.contacts.contact_id";

//updates contacts information
// updCommand.CommandText = "UPDATE GEM.contacts SET "
// + " name_first = '" + ((TextBox)e.Item.Cells[6].Controls[0]).Text
// + "', name_last = '" + ((TextBox)e.Item.Cells[7].Controls[0]).Text
// + "', email = '" + ((TextBox)e.Item.Cells[8].Controls[0]).Text + "'"
// + " FROM GEM.config_usernames INNER JOIN"
// + " GEM.contacts ON GEM.config_usernames.contact_id =
GEM.contacts.contact_id INNER JOIN"
// + " GEM.customers ON GEM.config_usernames.cust_id =
GEM.customers.cust_id";


SqlDataAdapter adapter = new SqlDataAdapter(updCommand);

DataSet ds = new DataSet();

updCommand.CommandType = CommandType.Text;
conn.Open();
updCommand.ExecuteNonQuery();
adapter.Fill(ds);
dgCustInfo.EditItemIndex = -1;
conn.Close();
bindData();
}

Have you tested your update query outside your code? Does it update only one
row? Normally, when the goal is to update a single row, a specific primary
key value is passed to the query as a parameter.
As a check, you could use your INNER JOIN clauses with a simple SELECT query
on the table you are updating to see the number of rows returned.

 

[Robert Bravery]

HI,

You need to tell it what to update. Leaving this out, sql assumes everythimg
Add a where clause to you sql statement, something like
update customers set name = "Robert" where custid = 123

Robert