Updating SQL from C#, changing all the data

Benjamin Ashton

I am using the below code to try and update records within my SQL server but having problems, it is updating all the records to what I want it to change too. It would be great if someone could steer me in the right direction.

da.UpdateCommand = new SqlCommand("UPDATE userInfo SET Username = @USERNAME, Password = @PASSWORD", cs);

da.UpdateCommand.Parameters.Add("@USERNAME", SqlDbType.VarChar).Value = txtUsername.Text;

da.UpdateCommand.Parameters.Add("@PASSWORD", SqlDbType.VarChar).Value = txtPassword.Text;

cs.Open();

da.UpdateCommand.ExecuteNonQuery();

cs.Close();

dgUpdate();

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Benjamin Ashton14y ago
But surely that would only be the case if his name was Benjamin? I want it to change as I scroll down and select other records
Vulpes14y ago
If you're updating a single record in the userInfo table, then you need to specify a WHERE clause.

So, if the existing Username column contained 'Benjamin' say and this column is the primary key and hence uniquely defines the row, you could do:

da.UpdateCommand = new SqlCommand("UPDATE userInfo SET Username = @USERNAME, Password = @PASSWORD WHERE Username = 'Benjamin'", cs);

Or, alternatively, you could use a parameter for the existing username as well.
Benjamin Ashton14y ago
Yes dg is a grid and your code did not work, the same thing is happening.
Sarika14y ago
Modify your updateCommand with following:

    private void btnUpdate_Click(object sender, EventArgs e)
                {

                    da.UpdateCommand = new SqlCommand("UPDATE users SET Username = '"+txtUsername.Text+"' where Password = '"+ txtPassword.Text+"'", cs);

                    cs.Open();
                    da.UpdateCommand.ExecuteNonQuery();
                    cs.Close();

                }

hope this helps..

Sarika
Sarika14y ago
I don't see declaration for dg ? Is it a grid?

Sarika
Benjamin Ashton14y ago
It didn't work, I will put the whole code in a pastebin, as the problem may be with the updating of the data table.

http://pastebin.com/CJ8kbYJb
Satyapriya Nayak14y ago
Hi Benjamin,

Try

da.UpdateCommand = new SqlCommand("UPDATE userInfo SET Username = @USERNAME where Password = @PASSWORD", cs);
da.UpdateCommand.Parameters.Add("@USERNAME", SqlDbType.VarChar).Value = txtUsername.Text;
da.UpdateCommand.Parameters.Add("@PASSWORD", SqlDbType.VarChar).Value = txtPassword.Text;

cs.Open();
da.UpdateCommand.ExecuteNonQuery();
cs.Close();

dgUpdate();

Thanks