UPDATE statement changes all the records in the DB

[Antonio]

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();
}

[Göran Andersson]

Because you didn't specify what record to update.

Antonio wrote:

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();
}

[Antonio]

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" wrote:

Because you didn't specify what record to update.

Antonio wrote:

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();
}

[Göran Andersson]

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.
Antonio wrote:

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" wrote:

Because you didn't specify what record to update.

Antonio wrote:

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();
}