get sum of a column and a value inputted in textbox in vb.net

[Kristiani]

i have a textbox named AddU.text, i also have a msaccess table field named Quantity, what I want is that when i input a value in AddU.Text and click the add button, the value i entered will be automatically add to the existing value in Quantity. I keep searching for solution but can't find the right one. Can anyone help me? Heres my current codes:

Dim cmd As New OleDb.OleDbCommand
    If Not conn.State = ConnectionState.Open Then
        conn.Open()
    End If
    Try
        cmd.Connection = conn
        cmd.CommandText = "UPDATE BC_Inventory SET [Addition]='" + AddU.Text + "'," + _
        "[Date_Updated]='" + DateU.Text + "',[Time_Updated]='" + TimeU.Text + "',[Updated_By]='" + UpdatedBy.Text + "'" + _
        "WHERE [Item]='" + com_ItemU.Text + "'"
        cmd.ExecuteNonQuery()




    Catch ex As Exception
        MessageBox.Show(ex.Message, "Error")
    Finally
        conn.Close()
    End Try

actually i still have no codes for it, i keep trying the codes i found in researching but nothing helps thats why i deleted it.

[jez9]

'search whether the ItemOrder is already exist or not
dim sql = "select * from BC_Inventory where Item='" & com_ItemU.text & "'"
dim cmd = new oledbcommand (sql, con)
dim dr as oledbdatareader = cmd.ExecuteReader
try
  if dr.read = true then
     dim quantity as integer = Convert.ToDecimal(AddU.text)
     sql = "Update BC_Inventory set quantity = quantity + ?" & _
                                         "where Item = ?"
     cmd = new Oledbcommand(sql.Tostring, con)
     cmd.Parameters.AddWithValue("@quantity" , quantity)
     cmd.Parameters.AddWithValue("@Item" , com_ItemU.text)
     cmd.ExecuteNonQuery()
     MsgBox ("Updated")

     else
     sql = "Insert into BC_Inventory (Item, quantity) values (?, ?)
     cmd = new Oledbcommand(sql, con)
     cmd.Parameters.AddWithValue("@Item" , com_ItemU.Text)
     cmd.Parameters.AddWithValue("@quantity" , AddU.text)
     cmd.ExecuteNonQuery()
     MsgBox ("Save")

end if

I hope it works on you, it works on me :)

[Santanu.Das]

@jez9:
Lot of thanks for your try to help others. But made some mistake at the bottom, where you tried to insert a new row into the table. You could never open same Command object twice for different work.
You need some modification in your codes.
Here you have shown that if condition will satisfy then update the value else insert a new one. But this not the way to do that, though your thougt is perfectly correct.
Solution :
First check if there is a value then update it else insert new one.
The codes should be

'Check if there has a row in table as per condition
dim sql = "select Count(*) from BC_Inventory where Item= ?"
dim cmd = new oledbcommand (sql, con)
cmd.Parameters.AddWithValue("@Item" , com_ItemU.text)

Dim result As Integer = Convert.ToInt32(cmd.ExecuteScalar())
cmd.Parameters.Clear()
cmd.Dispose()

If result > 0 then
     sql = "Update BC_Inventory set [Addition] = [Addition] + ? where Item = ?"
     cmd = new Oledbcommand(sql, con)
     cmd.Parameters.AddWithValue("@quantity" , Val(AddU.text))
     cmd.Parameters.AddWithValue("@Item" , com_ItemU.text)
     cmd.ExecuteNonQuery()
     MsgBox ("Updated")
else
     sql = "Insert into BC_Inventory (Item, [Addition]) values (?, ?)"
     cmd = new Oledbcommand(sql, con)
     cmd.Parameters.AddWithValue("@Item" , com_ItemU.Text)
     cmd.Parameters.AddWithValue("@quantity" , Val(AddU.text))
     cmd.ExecuteNonQuery()
     MsgBox ("Save")
end if
cmd.Parameters.Clear()
cmd.Dispose()

Hope, it can help you.

[jez9]

@shark 1,i forgot that, but thank you again :)