[RESOLVED] Listview items/rows to access db

[JohnCloud]

I've been trying to import or save my listview items/rows/content to access db.. ive been playing around this code:

Set rs = New ADODB.Recordset
rs.Open "Select * from TblSearchMainAssets", cn, adOpenKeyset, adLockOptimistic
For Each itm In ListView1.ListItems
rs.AddNew
rs.Fields("Assettag") = ListView1.SelectedItem.Text
rs.Fields("AssetDescription") = ListView1.SelectedItem.SubItems(1)
Next
rs.Update
rs.Requery
MsgBox "done"
rs.Close
Set rs = Nothing

but it only save the item on the first row of my database. Any idea on what I should do with the code?
Thanks in advance

[dee-u]

The rs.Update should be inside the loop:

rs.AddNew
rs.Fields("Assettag") = ListView1.SelectedItem.Text
rs.Fields("AssetDescription") = ListView1.SelectedItem.SubItems(1)
rs.Update

[JohnCloud]

dee-u, I tried your suggestion but I have the same result.



Thanks!

[JohnCloud]

I think I'm missing a loop here

[jggtz]

IMO it would be better to use For-Next loop

        Dim X As Integer
        For X = 1 To ListView1.ListItems.Count
            rs.AddNew
              rs!Assettag = ListView1.ListItems(X).Text
              rs!AssetDescription = ListView1.ListItems(X).SubItems(1)
            rs.Update
        Next X

[JohnCloud]

Thanks JG, I will try your code and will get back to this thread.

[JohnCloud]

Many thanks to you both! problem is fixed now.
Here's the final code.

Private Sub Command1_Click()
Dim x As Integer
Set rs = New ADODB.Recordset
rs.Open "Select * from TblSearchMainAssets", cn, adOpenKeyset, adLockOptimistic
For x = 1 To ListView1.ListItems.Count
rs.AddNew
rs.Fields("Assettag") = ListView1.ListItems(x).Text
rs.Fields("AssetDescription") = ListView1.ListItems(x).SubItems(1)
rs.Update
Next x
rs.Requery
MsgBox "Done"
rs.Close
Set rs = Nothing
End Sub