Hi again, I'm having a trouble in updating an item from listview to database

I try this code , but it says syntax error in Update
nod=name of dish , qty = quantity
TBLORDERS - nod[text], qty[text], price[text]
lvorder - qty , [subitems] - name of dish, price

Dim sql = "SELECT * FROM TBLORDERS WHERE nod='" & lvorder.Items(0).SubItems(1).Text & "'"
        Dim cmd = New OleDbCommand(sql, con)
        Dim dr As OleDbDataReader = cmd.ExecuteReader
            If dr.Read = True Then
                For Each x As ListViewItem In lvorder.Items
                    Dim nod As String = x.SubItems(1).Text
                    Dim qty As Integer = Convert.ToDecimal(x.SubItems(0).Text)
                    Dim price As Integer = Convert.ToDecimal(x.SubItems(3).Text)

                    sql = "Update TBLORDERS 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)

                For Each x As ListViewItem In lvorder.Items
                    sql = "insert into TBLORDERS (tbl,nod,qty,price) values ('" _
                    & lbltable.Text & "','" _
                    & x.SubItems(1).Text & "','" _
                    & x.SubItems(0).Text & "','" _
                    & x.SubItems(3).Text & "')"

                    Dim ccmd = New OleDb.OleDbCommand(sql, con)

            End If

        Catch ex As Exception

        End Try

Edited by jez9

3 Years
Discussion Span
Last Post by jez9


sql = "Update TBLORDERS " & 
      "   set qty   = qty   + ?," & 
      "       price = price + ?" & 
      " where nod = ?"

You were missing a comma.


Dim sql = "SELECT * FROM TBLORDERS WHERE nod='" & lvorder.Items(0).SubItems(1).Text & "'"
Dim cmd = New OleDbCommand(sql, con)
Dim dr As OleDbDataReader = cmd.ExecuteReader

Why are you using these lines? And your thinkig is in wrong direction.
Go through the Items of the ListView. Every time check if it exists or not. If it exists update values else insert new one.
Please read the post and grow your conception.
In that post it was for only one Item, but here it is for every Item in the listview.

For Each x As ListViewItem In lvorder.Items
    'Do your jobs here

    'Don't forget to close the Command Object


'Hope, it must grow your conception properly.


@Reverend Jim, the item is duplicating
@shark 1, i used that code to see whether the item is exist or not, if ynk es then it needs to be updated. i think that's wrong


Perhaps if you explain what you are trying to do we can suggest how you might code it. You showed us the code but that only shows us what you coded, not what you are trying to accomplish. Your OP said only that you had a syntax error.

What I can suggest at the moment has nothing to do with your problem but will likely save you problems now and later. If your database field is numeric then make it numeric instead of string. That will likely save you storage space and a ton of conversion code which will help eliminate typos and make your code much clearer. Also, you will be able to embed calculations in the actual queries to do things like SUMS, etc.

Edited by Reverend Jim


@Reverend Jim,sorry about that, but i get it now, i tried to redo my code and it's working now. sorry for the trouble


Not a problem. When asked "do you know what time it is", it is easy to guess that the actual question is "what time is it." With code it is often easier to answer the question that was asked and forget the more lengthy "but what you should be doing is..." part. Especially, like now, when I have to type left handed because the cat wants attention ;-P

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.