How to update SQL Server database withi C# unique Row only?

Question

When I run my program and try to update it, it updates all the columns to the new data in textboxes I don't know how to tell it to do it for the certain username which is in a textbox (txtUsernameUser.Text) table name is UserData primary key is Username. I just want to say thanks in advance for your time

SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=E:\Graded unit Dev\BlackMarch\BlackMarch\bin\Debug\DataBaseBM.mdf;Integrated Security=True;Connect Timeout=30");

SqlCommand cmd = new SqlCommand("UPDATE UserData SET Username = @Username, Password = @Password, FirstName = @Firstname, Surname = @Surname, Age = @Age, Gender = @Gender, Mobile = @Mobile, Email = @Email", con);

con.Open();

cmd.Parameters.AddWithValue("@Username", txtUsernameUser.Text);
cmd.Parameters.AddWithValue("@Password", txtboxPass.Text);
cmd.Parameters.AddWithValue("@FirstName", txtboxFN.Text);
cmd.Parameters.AddWithValue("@Surname", txtboxSurname.Text);
cmd.Parameters.AddWithValue("@Age", txtboxAge.Text);
cmd.Parameters.AddWithValue("@Gender", txtboxGender.Text);
cmd.Parameters.AddWithValue("@Mobile", txtboxMobile.Text);
cmd.Parameters.AddWithValue("@Email", txtboxEmail.Text);

cmd.ExecuteNonQuery();

con.Close();

The only way that would work is if you had a WHERE statement. Which you don't. — durbnpoisn
– durbnpoisn, Commented May 25, 2016 at 19:27
Do you know the difference between MySql and Sql Server? Use the appropriate tags in your question — Steve
– Steve, Commented May 25, 2016 at 19:34
You should check out Can we stop using AddWithValue() already? and stop using .AddWithValue() - it can lead to unexpected and surprising results... — marc_s
– marc_s, Commented May 25, 2016 at 20:52

David · Accepted Answer · 2016-05-25 19:27:11Z

1

Add a WHERE clause to the UPDATE statement to identify the subset of records which are being identified. Additionally, you don't want to update the Primary Key (since it shouldn't change anyway). So you can use that parameter and just modify the query:

SqlCommand cmd = new SqlCommand("UPDATE UserData SET Password = @Password, FirstName = @Firstname, Surname = @Surname, Age = @Age, Gender = @Gender, Mobile = @Mobile, Email = @Email WHERE Username = @Username", con);

or, for readability in this post...

UPDATE
 UserData
SET
 Password = @Password,
 FirstName = @Firstname,
 Surname = @Surname,
 Age = @Age,
 Gender = @Gender,
 Mobile = @Mobile,
 Email = @Email
WHERE
 Username = @Username

answered May 25, 2016 at 19:27

David

221k42 gold badges247 silver badges338 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ScaisEdge · Accepted Answer · 2016-05-25 19:27:20Z

1

Add a proper where condition eg: if you can select the row with a YourID use it

"UPDATE UserData SET Username = @Username, 
  Password = @Password, 
 FirstName = @Firstname, 
 Surname = @Surname, 
 Age = @Age, 
 Gender = @Gender, 
Mobile = @Mobile, 
Email = @Email
WHERE YourID = @YourId"

answered May 25, 2016 at 19:27

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Collectives™ on Stack Overflow

How to update SQL Server database withi C# unique Row only?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related