Update Records from ListVIEW TO Database

Question

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()

The error I received was

[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

http://stackoverflow.com/questions/542510/how-do-i-create-a-parameterized-sql-query-why-should-i — Ňɏssa Pøngjǣrdenlarp, Jan 15 '15 at 23:22
Do you think that your database engine has any idea of what _lvlist.FocusedItem.getTOTALQTY_ is? — Steve, Jan 15 '15 at 23:24
resolve that awful, lengthy, convoluted expression on its own and then pass it as a parameter. `SQL = "UPDATE tblsale set TOTALQTY = TOTALQTY + @quan WHERE Name = @p2` — Ňɏssa Pøngjǣrdenlarp, Jan 15 '15 at 23:33

score 1 · Accepted Answer · edited Jan 16 '15 at 01:00

There are a lot of problems in your command text.

First you need to look at the correct syntax for an UPDATE command. The AND between the two fields is wrong, you need a comma.

Second you can't put the lvList.FocusedItem.... inside the text of your command. In that way it becomes a literal string and doesn't represent the value to be inserted.

Finally, a parameterized query is always the way to go, Access is more difficult to exploit with Sql Injection because it doesn't support batch statements like SQL Server or MySQL, nevertheless, the parameterized approach has other advantages. For example you don't need to quote strings or numbers and avoid to forget to close a quote like you do at the end of your command.

if lvList.FocusedItem IsNot Nothing Then

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

    sql = "UPDATE tblsale set TOTALQTY = TOTALQTY + @qty, " & _
           "TOTALPRICE = TOTALPRICE + @price " & _  
           "where name = @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

By the way, FocusedItem doesn't mean SelectedItem. A ListView could have a FocusedItem when it is shown on your form but there is no SelectedItem until your user choose a specific element (at that point FocusedItem and SelectedItem are the same)

I'm a beginner in vb.net,that's why i just experiment and mess up the codes, sorry :) but i can understand the codes and how it works. i will try the code that you gave me. Thank you so much for helping me sir. I really appreciate it. Thank you — jez, Jan 16 '15 at 00:03
i've this error sir, Unable to cast object of type 'ListViewSubItem' to type 'System.IConvertible'. in line Dim qty = Convert.ToDecimal(lvList.FocusedItem.SubItems(0)) — jez, Jan 16 '15 at 00:25

Update Records from ListVIEW TO Database

1 Answers1