Update Database Records From Listview Items

[jez9]

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
        Try
            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)
                    cmd.ExecuteNonQuery()
                    cmd.dispose
                    MsgBox("update")
                Next

            Else
                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)
                    ccmd.ExecuteNonQuery()
                    ccmd.Dispose()
                Next
                MsgBox("save")
                Me.Refresh()


            End If

        Catch ex As Exception

            MsgBox(ex.Message)
        End Try

[Reverend Jim]

Try

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

You were missing a comma.

[Santanu.Das]

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

Next

'Hope, it must grow your conception properly.

[jez9]

@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

[Reverend Jim]

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.

[jez9]

@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

[Reverend Jim]

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

[jez9]

@Reverend Jim. haha, yeah thanks :)