database table : tblsale
fields : id [autonumber], name[text], TOTALQTY [text], TOTALPRICE[text]
lvlist column: QTY, NAME, PRICE
this is my code

sql = "UPDATE tblsale set TOTALQTY = TOTALQTY + lvlist.FocusedItem.getTOTALQTY AND TOTALPRICE = TOTALPRICE + lvlist.FocusedItem.SubItems(2).getTOTALPRICE where name = '" & lvlist.FocusedItem.SubItems(1) 

Dim acscmd = New OleDb.OleDbCommand(sql, Con)
acsdr = acscmd.ExecuteReader()

[SYNTAX ERROR in string in query expression 'name = 'Focaccia Bread'.]

i'm trying to to update the record in the tblsale from lvlist record, also i want to add the previous QTY from new one, the same goes to PRICE

Edited 1 Year Ago by pritaeas: Moved to VB.NET

i post it there. thank you :)

by the way, i don't know how to use parameter, i thought using parameter only works at MySql not on MS Access.?

i try this codes, but i've error in [ Dim qty = Convert.ToDecimal(lvorders.FocusedItem.SubItems(0))] [ it says that Unable to cast object of type 'ListViewSubItem' to type 'System.IConvertible]

If lvorders.FocusedItem IsNot Nothing Then

        Dim qty = Convert.ToDecimal(lvorders.FocusedItem.SubItems(0))
        Dim price = Convert.ToDecimal(lvorders.FocusedItem.SubItems(2))
        Dim name = lvorders.FocusedItem.SubItems(1).ToString()

        sql = "UPDATE tbdishlist set qty = qty + @qty, " & _
               "price = price + @price " & _
               "where nod = @name"

        Dim acscmd = New OleDb.OleDbCommand(sql, con)
        acscmd.Parameters.AddWithValue("@qty", qty)
        acscmd.Parameters.AddWithValue("@price", price)
        acscmd.Parameters.AddWithValue("@name", name)
        Dim rowsChanged = acscmd.ExecuteNonQuery()
        MsgBox("upate")
    End If

i've got 2 or more records that i wanted to save, i want to save them at once so i did not use selectedItems. do you think that's possible?

For OleDB you use ? for the parameters as follows:

sql = "UPDATE tbdishlist SET " & _
          "qty = qty + ?,    " & _
          "price = price + ? " & _
      "WHERE nod = ?"

acscmd.Parameters.AddWithValue("@qty", qty)
acscmd.Parameters.AddWithValue("@price", price)
acscmd.Parameters.AddWithValue("@name", name)          

Unable to cast object of type 'ListViewSubItem' to type 'System.IConvertible]

An object never convert to Decimal type.
The codes should be

Dim qty = Convert.ToDecimal(lvorders.FocusedItem.SubItems(0).Text)
Dim price = Convert.ToDecimal(lvorders.FocusedItem.SubItems(2).Text)
Dim name = lvorders.FocusedItem.SubItems(1).Text.ToString()

Then do the same as Reverend Jim describes.

Using focused.item doesn't mean it is also selected.
"lvorders.FocusedItem.SubItems(2).Text"
instead use selecteditems or selectedindicies as cgeier pointed out to you before.

Edited 1 Year Ago by Minimalist

This is must to declare the variable types.

Dim qty As Decimal= Convert.ToDecimal(lvorders.FocusedItem.SubItems(0).Text)
Dim price As Decimal = Convert.ToDecimal(lvorders.FocusedItem.SubItems(2).Text)
Dim name As String= lvorders.FocusedItem.SubItems(1).Text.ToString()

Hope it can help you.

[@Shark_1 @Minimalist,@Reverend Jim,@cgeier],thank you guys so much for helping :)
I replace the focuseditem with an item only, so i can update in the same field now but the problem now is in QTY and PRICE , instead 5+5 = 10, it became 5+5 = 55

Try

            For Each item As ListViewItem In lvorder.Items

                Dim qty As Decimal = Convert.ToDecimal((item.SubItems(1).Text))
                Dim price As Decimal = Convert.ToDecimal(item.SubItems(2).Text)
                Dim nod As String = item.Text.ToString()


                Dim sql As String = "UPDATE table1 SET " & _
                                     "qty = qty + ?, " & _
                                      "price = price + ? " & _
                                      "WHERE nod = ?"
                cmd = New OleDbCommand(sql.ToString, con)

                cmd.Parameters.AddWithValue("@qty", qty)
                cmd.Parameters.AddWithValue("@price", price)
                cmd.Parameters.AddWithValue("@nod", nod)


                cmd.ExecuteNonQuery()
            Next
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try

The field type is Text type so the "+" symbol concatinates those two text type variables, without performing the addition.
Declare the field types as Numerical type like Integer, Double,...etc., what is the appropriate one.

@Shark_1 ,thank you very very much, now i think my system is working 50%. :D

Dim qty As Integer = Convert.ToDecimal((item.SubItems(1).Text))
Dim price As Integer = Convert.ToDecimal(item.SubItems(2).Text)
Dim nod As String = item.Text.ToString()
This question has already been answered. Start a new discussion instead.