Updating SQL from C# changing all the data

Question

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

Benjamin Ashton · Answer

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

Vulpes · Answer

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 Ashton · Answer

Yes dg is a grid and your code did not work the same thing is happening.

Sarika · Answer

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

Sarika · Answer

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

Benjamin Ashton · Answer

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 Nayak · Answer

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

Updating SQL from C#, changing all the data