inserting items from listview to database(ms. access)

[jambok]

hi,anyone can help me on my error, im just new in vb.net, i got some problems on my query, i get errors("Syntax Error in INSERT INTO Statement") can you check which is wrong on this code...

Dim li as ListViewItem
Dim query as String = ""
Dim com as OleDbCommand

Try

With li

query = "Insert into tab1(Names, Price, Quantity, Total) Values" query += "(" & ListView1.Items(0).Text & ", " & .SubItems(1).Text & ", " & .SubItems(2).Text & ", " & .SubItems(3).Text & ")" com = New OleDbCommand(query, scon) com.ExecuteNonQuery() MsgBox("Success!", MsgBoxStyle.Information, "Success")

End With

Catch ex As Exception

MsgBox(ex.Message)

End Try

 

[IanRyder]

Hi,

A couple of things to mention for you here:-

1) Your connection "scon" does not exist in this context so I assume that you have declared this elsewhere?

2) You have defined a ListViewItem in this context, being Li, but you have not added any information to the object therefore any attempt to access indices within this object will cause an error. To add any information to this object, other than an existing ListViewItem object, you need to declare the object with the New keyword.

3) You use a "With Li", being a ListViewItem, statement to encase your query definition but you then seem to access two different Object types with ListView1.Items(0).Text & ", " & .SubItems(1).Text - Have you defined a ListView object on the form called ListView1? - You need to decide which object you are using.

4) You start your query definition with "Insert into tab1(Names, Price, Quantity, Total)". Notice that you have missed a space in "tab1(Names". It should be "Insert into tab1 (Names, Price, Quantity, Total) etc".

5) With being new to .NET I suggest you look into Parameters when using the OleDbCommand object. This will make your code a lot easier to read and write. See Here:-

OleDbCommand.Parameters Property (System.Data.OleDb)

Your code could then become something like:-

Const strQuery As String = "Insert into tab1 (Names, Price, Quantity, Total) Values (@Names, @Price, @Quantity, @Total)"
With li
  com = New OleDbCommand(strQuery, scon)
  com.Parameters.AddWithValue("@Names", .Text)
  com.Parameters.AddWithValue("@Price", .SubItems(1).Text)
  'etc etc etc
  com.ExecuteNonQuery()
End With

Hope this helps.

Cheers,

Ian

 

[jambok]

thanks IanRyder

it helps me a lot! somewhat i solved the problem with your help! tnx!