UPDATE statement changes all the records in the DB

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

Guest

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...
 
G

Guest

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

UPDATE statement changes data for all the records in the DB 2
Edit records and UPDATE statements 3
System.NullReferenceException: Object reference not set to an instance of an object 2
Can't update field in datagrid 3
quick issue 8
ASP.NET/C# Help 1
BUG on BindingContext in framework V1.1 12

Top