Hi all i have difficulty writing each row of listview to mysql db. I be happy if some one help me fix it.Thanks
Code:
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
error pic:
http://i5.photobucket.com/albums/y18...orlistview.jpg
and points to :CNN.Execute sSQL
i printed out the sql and the url field was used corectly but not the rest:
pic of sql statement:
[IMG]
http://i5.photobucket.com/albums/y18...7/sqlprint.jpg [/IMG]
I even tried this and got error:
Code:
sSQL = sSQL & " VALUES('" & ListView1.ListItems.Item(i).Text & "','" & ListView1.SubItems(1) & "','" & ListView1.SubItems(2) & "', '" & ListView1.SubItems(3) & "')"
Code:
Compile error:
Method or data member not found
pointing at :.
SubItems
could u help me fix this.thanks