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

Recommended Answers

All 15 Replies

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

ListView.FocusedItem Property

"...Use the SelectedItems or SelectedIndices properties to obtain the selected items in the ListView control, the FocusedItem property is not necessarily selected..."

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?

Yes.

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)          

@Reverend Jim , parameters didnt work on me. :(

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.

@Shark_1 got some error

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.

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()
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.