[RESOLVED] Save Multiple data from listview to MSaccess

[jovsjovel]

Hello Guys,,

I need Your Help I want to save my data in listview to my DB and This is My code to save data in listview

Private Sub save()
ListView1.ListItems.Add , , lblrp.Caption
ListView1.ListItems.Item(ListView1.ListItems.Count).ListSubItems.Add , , txtCode.Text
ListView1.ListItems.Item(ListView1.ListItems.Count).ListSubItems.Add , , txtproductname.Text
ListView1.ListItems.Item(ListView1.ListItems.Count).ListSubItems.Add , , txtqty.Text
ListView1.ListItems.Item(ListView1.ListItems.Count).ListSubItems.Add , , txtprice.Text
ListView1.ListItems.Item(ListView1.ListItems.Count).ListSubItems.Add , , lbltotal.Caption
ListView1.ListItems.Item(ListView1.ListItems.Count).ListSubItems.Add , , lbldate.Caption
End Sub

and this my code to Save Data in MSAccess Using Listview

Private Sub savetodailyrecord()
Dim lstSel As ListItem
Dim str As String

For Each lstSel In ListView1.ListItems

str = "Insert Into Dailyrecord (Receipt, BarCode, ProductName, Quantity, Price, TotalPrice, purchase_date) Values ('" & lstSel.Text & "', '" & lstSel.SubItems(1) & "','" & lstSel.SubItems(2) & "','" & lstSel.SubItems(3) & "','" & lstSel.SubItems(4) & "','" & lstSel.SubItems(5) & "'," & lstSel.SubItems(6) & ")"
acd.Execute str

Next

Call Purchase
ListView1.ListItems.clear

End Sub

But if my Data in Listview is only one.. My Program Run Correctly and Save the Data to DB, but in My listview had many data VB 6.0 Say: syntax error in the query expression

What shoul I Do?

Help Me guys..

[dglienna]

Use Debug.Print str, then paste the contents of that into Access (SQL Editor). It will highlight on the first position of the first error. When there are no more errors, change VB6 to match EXACTLY.

You can EXPORT a table to a text file.

[jggtz]

Use next way

Private Sub savetodailyrecord()

  Dim X   As Integer
  Dim str As String

  For X = 1 To ListView1.ListItems.Count
    str = "Insert Into Dailyrecord (Receipt, BarCode, ProductName, Quantity, Price, TotalPrice, purchase_date) Values ('" & ListView1.ListItems(X).Text & "', '" & ListView1.ListItems(X).SubItems(1) & "','" & ListView1.ListItems(X).SubItems(2) & "','" & ListView1.ListItems(X).SubItems(3) & "','" & ListView1.ListItems(X).SubItems(4) & "','" &  ListView1.ListItems(X).SubItems(5) & "'," & ListView1.ListItems(X).SubItems(6) & ")"
    acd.Execute str
  Next

  Call Purchase
  ListView1.ListItems.clear

End Sub

[jggtz]

Also
It would be better if You do not use the word STR as a variable name
Because it's a Reserved Word

[jovsjovel]

Thanks Guys.. Its Work..