UPDATE statement changes data for all the records in the DB

  • Thread starter Thread starter Guest
  • Start date Start date
G

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();
}
 
Antonio said:
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();
}
Click to expand...

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