How to write a SqlCommand query that adds (numerical) input values to existing (numerical) column values

Question

I want update several columns in a table by adding a value to existing values in those columns. Here is my current code:

cmd = new SqlCommand("UPDATE Users SET Debit=@debit, 
                                       Score=@score 
                                 WHERE Phone=@phone", con);

con.Open();

cmd.Parameters.AddWithValue("@phone", textBox1.Text);
cmd.Parameters.AddWithValue("@debit", textBox2.Text);
cmd.Parameters.AddWithValue("@score", textBox3.Text);

cmd.ExecuteNonQuery();

MessageBox.Show("Амжилттай");
con.Close();

For example:

Table, Phone: 999 | Debit: 1500 | Score: 100 //current

When I add value from textBox1 = 999, textBox2 = 500, textBox3 = 50, the values should become:

Table, Phone: 999, Debit: 2000, Score: 150 //updating like that

I know how to do this via a SQL query, but I don't know how to write the code in a SqlCommand in .NET:

UPDATE Users 
SET Debit = Debit + [user input], Score = Score + [user input] 
WHERE = Phone

Any suggestions?

you SqlCommand seems fine, what is the problem?

styx
– styx

2017-10-17 06:45:55 +00:00
Commented Oct 17, 2017 at 6:45 — styx
– styx, Commented Oct 17, 2017 at 6:45

Dmitrii Bychenko · Accepted Answer · 2017-10-18 19:58:38Z

If you want to add, just add:

cmd = new SqlCommand(@"UPDATE Users 
                          SET Debit = Debit + @debit, 
                              Score = Score + @score 
                        WHERE Phone = @phone", con);

Please, notice verbatim string @"..." syntax. Please, do not forget about disposing (explicit Close is an antipattern):

string sql = 
  @"UPDATE Users 
       SET Debit = Debit + @debit, 
           Score = Score + @score 
     WHERE Phone = @phone";

//TODO: put the right connection string instead of "MyConnectionStringHere"
//DONE: IDisposable (SqlConnection) should be wrapped into using 
using (var con = new SqlConnection("MyConnectionStringHere")) {
  con.Open();

  //DONE: IDisposable (SqlCommand) should be wrapped into using
  using (var cmd = new SqlCommand(sql, con)) {
    //TODO: AddWithValue is often a bad choice; change to Add 
    cmd.Parameters.AddWithValue("@phone", textBox1.Text);
    cmd.Parameters.AddWithValue("@debit", textBox2.Text);
    cmd.Parameters.AddWithValue("@score", textBox3.Text);

    cmd.ExecuteNonQuery();
    //TODO: a better policy is to read localized strings from resources
    MessageBox.Show("Амжилттай");
  }
}

Saalim Bhoraniya · Accepted Answer · 2017-10-17 11:04:47Z

1

This will help you....just try in this way..

SqlCommand cmd = new SqlCommand("UPDATE Users SET Debit = Debit + " + textBox2.Text + ", Score = Score + " + textBox3.Text + " WHERE Phone = " + textBox1.Text + "", con);
                con.Open();
                cmd.ExecuteNonQuery();
                MessageBox.Show("Амжилттай");
                con.Close();

OR

SqlCommand cmd = new SqlCommand("UPDATE Users SET Debit = Debit + @debit, Score = Score + @score WHERE Phone = @phone", con);
                con.Open();
                cmd.Parameters.AddWithValue("@phone", textBox1.Text);
                cmd.Parameters.AddWithValue("@debit", textBox2.Text);
                cmd.Parameters.AddWithValue("@score", textBox3.Text);
                cmd.ExecuteNonQuery();
                MessageBox.Show("Амжилттай");
                con.Close();

edited Oct 17, 2017 at 11:04

answered Oct 17, 2017 at 7:24

Saalim Bhoraniya

3603 silver badges6 bronze badges

5 Comments

Dmitrii Bychenko Over a year ago

Please, do not hardcode sql, but use parameters (as in the question)

Saalim Bhoraniya Over a year ago

but output will be same Dmitry Bychenko.

Dmitrii Bychenko Over a year ago

Yes, the output will be the same, but: 1. (Almost) all the queries will be different (so the optimizer should have to generate plans for each query); 2. The query is prone to sql injection: imagine that textBox2.Text contains, say, 123; --. In this case you'll have the query: "UPDATE Users SET Debit = Debit + 123 -- ..." (please, notice **commenting** --). So input 123; --` will update the entire table, not just the required phone

Saalim Bhoraniya Over a year ago

ohhh..yes thanks a lot Dmitry Bychenko...my query is not secured.

Tamiraa Aquarius Over a year ago

I tried this way but it didn't solved my problem. I prefer to use Parameters

Abdulkadir Erkmen · Accepted Answer · 2017-10-17 06:50:12Z

0

You can use += operator for update. Change your sql command like this;

UPDATE Users SET Debit+=@debit, 
                 Score+=@score 
                                 WHERE Phone=@phone

answered Oct 17, 2017 at 6:50

Abdulkadir Erkmen

2021 silver badge4 bronze badges

Collectives™ on Stack Overflow

How to write a SqlCommand query that adds (numerical) input values to existing (numerical) column values

3 Answers 3

Comments

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related