problem writing listview data to mysql db

[tony007]

Hi all i have difficulty writing each row of listview to mysql db. I be happy if some one help me fix it.Thanks

Private Sub Command11_Click()
 Dim CNN As ADODB.Connection
Set CNN = New ADODB.Connection
CNN.Open "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=visualbasic6;USER=root;PASSWORD=;OPTION=3;"
 
 
 Dim sSQL As String
Dim i As Long
For i = 1 To ListView1.ListItems.Count
    sSQL = "INSERT INTO musictest (url,songname,artist,album) "
    sSQL = sSQL & " VALUES('" & ListView1.ListItems.Item(i).Text & "', '"
    sSQL = sSQL & " " & ListView1.Subitems(1) &  ', '"
    sSQL = sSQL & " & ListView1.Subitems(2) &  ', '"
    sSQL = sSQL & " & ListView1.Subitems(3) &  ', '"
    
    MsgBox sSQL
    'etc
    CNN.Execute sSQL
Next
      
MsgBox " The songs are inserted to database successfully"
End Sub

and points to :CNN.Execute sSQL

i printed out the sql and the url field was used corectly but not the rest:




I even tried this and got error:

sSQL = sSQL & " VALUES('" & ListView1.ListItems.Item(i).Text & "','" & ListView1.SubItems(1) & "','" & ListView1.SubItems(2) & "', '" & ListView1.SubItems(3) & "')"

Compile error:
 
Method or data member not found

pointing at :.SubItems

could u help me fix this.thanks

[si_the_geek]

Your last version is the way it should be - the only problem is that you are refering to items that don't exist.

I don't use the Listview much, but would this be more like it?

With ListView1.ListItems.Item(i)
  sSQL = sSQL & " VALUES('" & .Text & "','" & .SubItems(1) & "','" & .SubItems(2) & "', '" & .SubItems(3) & "')"
End With