UPDATE statement changes data for all the records in the DB

[Antonio]

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(objec t source,
System.Web.UI.W ebControls.Data GridCommandEven tArgs e)
{
SqlConnection conn = new SqlConnection
(ConfigurationS ettings.AppSett ings["SqlConnectionS tring"]);

SqlCommand updCommand = new SqlCommand();
updCommand.Conn ection = conn;
updCommand.Comm andText = "UPDATE GEM.customers SET "
+ "name = '" + ((TextBox)e.Ite m.Cells[5].Controls[0]).Text
+ "', address_1 = '" + ((TextBox)e.Ite m.Cells[9].Controls[0]).Text
+ "', city = '" + ((TextBox)e.Ite m.Cells[10].Controls[0]).Text
+ "', state = '" + ((TextBox)e.Ite m.Cells[11].Controls[0]).Text
+ "', zip_code = '" + ((TextBox)e.Ite m.Cells[12].Controls[0]).Text
+ "', postal_code = '" + ((TextBox)e.Ite m.Cells[13].Controls[0]).Text
+ "', country = '" + ((TextBox)e.Ite m.Cells[14].Controls[0]).Text
+ "', phone = '" + ((TextBox)e.Ite m.Cells[15].Controls[0]).Text + "'"
+ " FROM GEM.customers INNER JOIN GEM.config_user names ON "
+ "GEM.customers. cust_id = GEM.config_user names.cust_id INNER JOIN "
+ "GEM.contac ts ON GEM.config_user names.cust_id =
GEM.contacts.co ntact_id";

//updates contacts information
// updCommand.Comm andText = "UPDATE GEM.contacts SET "
// + " name_first = '" + ((TextBox)e.Ite m.Cells[6].Controls[0]).Text
// + "', name_last = '" + ((TextBox)e.Ite m.Cells[7].Controls[0]).Text
// + "', email = '" + ((TextBox)e.Ite m.Cells[8].Controls[0]).Text + "'"
// + " FROM GEM.config_user names INNER JOIN"
// + " GEM.contacts ON GEM.config_user names.contact_i d =
GEM.contacts.co ntact_id INNER JOIN"
// + " GEM.customers ON GEM.config_user names.cust_id =
GEM.customers.c ust_id";


SqlDataAdapter adapter = new SqlDataAdapter( updCommand);

DataSet ds = new DataSet();

updCommand.Comm andType = CommandType.Tex t;
conn.Open();
updCommand.Exec uteNonQuery();
adapter.Fill(ds );
dgCustInfo.Edit ItemIndex = -1;
conn.Close();
bindData();
}

[pvdg42]

Re: UPDATE statement changes data for all the records in the DB


"Antonio" <Antonio@discus sions.microsoft .com> wrote in message
news:887158E5-7894-4ED2-A13A-72775172A1F7@mi crosoft.com...[color=blue]
> 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(objec t source,
> System.Web.UI.W ebControls.Data GridCommandEven tArgs e)
> {
> SqlConnection conn = new SqlConnection
> (ConfigurationS ettings.AppSett ings["SqlConnectionS tring"]);
>
> SqlCommand updCommand = new SqlCommand();
> updCommand.Conn ection = conn;
> updCommand.Comm andText = "UPDATE GEM.customers SET "
> + "name = '" + ((TextBox)e.Ite m.Cells[5].Controls[0]).Text
> + "', address_1 = '" + ((TextBox)e.Ite m.Cells[9].Controls[0]).Text
> + "', city = '" + ((TextBox)e.Ite m.Cells[10].Controls[0]).Text
> + "', state = '" + ((TextBox)e.Ite m.Cells[11].Controls[0]).Text
> + "', zip_code = '" + ((TextBox)e.Ite m.Cells[12].Controls[0]).Text
> + "', postal_code = '" + ((TextBox)e.Ite m.Cells[13].Controls[0]).Text
> + "', country = '" + ((TextBox)e.Ite m.Cells[14].Controls[0]).Text
> + "', phone = '" + ((TextBox)e.Ite m.Cells[15].Controls[0]).Text + "'"
> + " FROM GEM.customers INNER JOIN GEM.config_user names ON "
> + "GEM.customers. cust_id = GEM.config_user names.cust_id INNER JOIN "
> + "GEM.contac ts ON GEM.config_user names.cust_id =
> GEM.contacts.co ntact_id";
>
> //updates contacts information
> // updCommand.Comm andText = "UPDATE GEM.contacts SET "
> // + " name_first = '" + ((TextBox)e.Ite m.Cells[6].Controls[0]).Text
> // + "', name_last = '" + ((TextBox)e.Ite m.Cells[7].Controls[0]).Text
> // + "', email = '" + ((TextBox)e.Ite m.Cells[8].Controls[0]).Text + "'"
> // + " FROM GEM.config_user names INNER JOIN"
> // + " GEM.contacts ON GEM.config_user names.contact_i d =
> GEM.contacts.co ntact_id INNER JOIN"
> // + " GEM.customers ON GEM.config_user names.cust_id =
> GEM.customers.c ust_id";
>
>
> SqlDataAdapter adapter = new SqlDataAdapter( updCommand);
>
> DataSet ds = new DataSet();
>
> updCommand.Comm andType = CommandType.Tex t;
> conn.Open();
> updCommand.Exec uteNonQuery();
> adapter.Fill(ds );
> dgCustInfo.Edit ItemIndex = -1;
> conn.Close();
> bindData();
> }[/color]

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]

Re: UPDATE statement changes data for all the records in the DB

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

"Antonio" <Antonio@discus sions.microsoft .com> wrote in message
news:887158E5-7894-4ED2-A13A-72775172A1F7@mi crosoft.com...[color=blue]
> 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(objec t source,
> System.Web.UI.W ebControls.Data GridCommandEven tArgs e)
> {
> SqlConnection conn = new SqlConnection
> (ConfigurationS ettings.AppSett ings["SqlConnectionS tring"]);
>
> SqlCommand updCommand = new SqlCommand();
> updCommand.Conn ection = conn;
> updCommand.Comm andText = "UPDATE GEM.customers SET "
> + "name = '" + ((TextBox)e.Ite m.Cells[5].Controls[0]).Text
> + "', address_1 = '" + ((TextBox)e.Ite m.Cells[9].Controls[0]).Text
> + "', city = '" + ((TextBox)e.Ite m.Cells[10].Controls[0]).Text
> + "', state = '" + ((TextBox)e.Ite m.Cells[11].Controls[0]).Text
> + "', zip_code = '" + ((TextBox)e.Ite m.Cells[12].Controls[0]).Text
> + "', postal_code = '" + ((TextBox)e.Ite m.Cells[13].Controls[0]).Text
> + "', country = '" + ((TextBox)e.Ite m.Cells[14].Controls[0]).Text
> + "', phone = '" + ((TextBox)e.Ite m.Cells[15].Controls[0]).Text + "'"
> + " FROM GEM.customers INNER JOIN GEM.config_user names ON "
> + "GEM.customers. cust_id = GEM.config_user names.cust_id INNER JOIN "
> + "GEM.contac ts ON GEM.config_user names.cust_id =
> GEM.contacts.co ntact_id";
>
> //updates contacts information
> // updCommand.Comm andText = "UPDATE GEM.contacts SET "
> // + " name_first = '" + ((TextBox)e.Ite m.Cells[6].Controls[0]).Text
> // + "', name_last = '" + ((TextBox)e.Ite m.Cells[7].Controls[0]).Text
> // + "', email = '" + ((TextBox)e.Ite m.Cells[8].Controls[0]).Text + "'"
> // + " FROM GEM.config_user names INNER JOIN"
> // + " GEM.contacts ON GEM.config_user names.contact_i d =
> GEM.contacts.co ntact_id INNER JOIN"
> // + " GEM.customers ON GEM.config_user names.cust_id =
> GEM.customers.c ust_id";
>
>
> SqlDataAdapter adapter = new SqlDataAdapter( updCommand);
>
> DataSet ds = new DataSet();
>
> updCommand.Comm andType = CommandType.Tex t;
> conn.Open();
> updCommand.Exec uteNonQuery();
> adapter.Fill(ds );
> dgCustInfo.Edit ItemIndex = -1;
> conn.Close();
> bindData();
> }[/color]