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 by pritaeas: Moved to VB.NET

3 Years
Discussion Span
Last Post by jez9

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()
    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.
instead use selecteditems or selectedindicies as cgeier pointed out to you before.

Edited 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


            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)

        Catch ex As Exception
        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.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.