SQL Update Column By Adding A Number To Current Int

Question

Simple enough, I can't figure out how to add (that's +) an integer from a textbox to the integer in the SQL Field.

So for example, the SQL Field may have '10' in it and the textbox may have '5' in it. I want to add these numbers together to store '15' without having to download the SQL Table.

The textbox that contains the integer to be added to the SQL integer is tranamount.Text and the SQL Column in the SQL Table is @ugpoints. Please note, without the '+' - which is in the below code and is admittedly wrong- the value of tranamount.Text is added to the Table without an issue, but it simply replaces the original value; meaning the end result would be '5' in the SQL Field.

What would be the proper way to structure this? I've tried the below code, but that clearly doesn't work.

cmd = New SqlCommand("UPDATE PersonsA SET U_G_Studio=@ugpoints WHERE Members_ID=@recevierID", con)

cmd.Parameters.AddWithValue("@recevierID", tranmemberID.Text)
cmd.Parameters.AddWithValue("@ugpoints", + tranamount.Text) '<--- Value to add.
cmd.ExecuteNonQuery()

Newbies question I know, I'm new to SQL in vb.

score 1 · Answer 1 · answered Jan 22 '14 at 19:38

You have to use the correct sql:

Dim sql = "UPDATE PersonsA SET U_G_Studio=U_G_Studio + @ugpoints WHERE Members_ID=@recevierID"

Also use the correct type with AddWithValue:

Using cmd = New SqlCommand(sql, con)
    ' use the using-statement to dispose everything that implements IDisposable, so also the connection '
        cmd.Parameters.AddWithValue("@ugpoints", Int32.Parse(tranamount.Text)) 
    ' .... '
End Using

score 0 · Answer 2 · answered Jan 22 '14 at 19:38

0

The SQL you want is:

"UPDATE PersonsA SET U_G_Studio= (U_G_Studio + @ugpoints) " & _
"WHERE Members_ID=@recevierID"

answered Jan 22 '14 at 19:38

D Stanley

149,601
11
178
240

score 0 · Accepted Answer · answered Jan 22 '14 at 19:39

Take the current value of the U_G_Studio field, add the value of the parameter and reassign to U_G_Studio, but keep in mind that you need to pass the value as an integer because otherwise the AddWithValue will pass a string and you get conversion errors coming from the db.

    cmd = New SqlCommand("UPDATE PersonsA SET U_G_Studio=U_G_Studio + @ugpoints " & 
                         "WHERE Members_ID=@recevierID", con)
    cmd.Parameters.AddWithValue("@recevierID", tranmemberID.Text)
    cmd.Parameters.AddWithValue("@ugpoints", Convert.ToInt32(tranamount.Text))
    cmd.ExecuteNonQuery()

Thank you very much my friend. You've helped me a lot! – user3224987 Jan 22 '14 at 20:06 — user3224987, Jan 22 '14 at 20:06

score 0 · Answer 4 · answered Jan 22 '14 at 19:40

0

what about

cmd.Parameters.AddWithValue("@ugpoints",  (int)tranamount.Text) 
....
cmd = New SqlCommand("UPDATE PersonsA SET U_G_Studio= SET U_G_Studio + @ugpoints WHERE Members_ID=@recevierID", con)

edit1: STEVE WAS FASTER!

answered Jan 22 '14 at 19:40

CodeHacker

2,127
1
22
35

Thanks for your solution, though Steve was faster I appreciate your response as well. – user3224987 Jan 22 '14 at 20:07

SQL Update Column By Adding A Number To Current Int

4 Answers4