UPDATE statement changes all the records in the DB

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

Guest

Can somebody tell my why the following procedure changes the data in the
fields being updated to all the records in the database?

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();
}
 
Ok...could you tell me more? I thought I was telling to update the fields
with the data I entered into those fields in the update statement.

Göran Andersson said:
Because you didn't specify what record to update.
Can somebody tell my why the following procedure changes the data in the
fields being updated to all the records in the database?

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...
Click to expand...
 
Yes, you do. But as you don't specify what record to update, it will
update all records.

Here is an example of a regular update query:

UPDATE TheTable SET this='something', that='something else' WHERE id=42

It's the WHERE statement that does the trick. It specifies what records
should be updated.

I suppose that you just threw in the FROM and INNER JOINs because you
don't know what you are doing... so lose them.

Ok...could you tell me more? I thought I was telling to update the fields
with the data I entered into those fields in the update statement.

Göran Andersson said:
Because you didn't specify what record to update.
Can somebody tell my why the following procedure changes the data in the
fields being updated to all the records in the database?

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...
Click to expand...
Click to expand...
 
Back
Top