View Single Post
  #1 (permalink)  
Old May 17th, 2006, 06:15 AM
method method is offline
Friend of Wrox
 
Join Date: Mar 2005
Location: , , .
Posts: 264
Thanks: 0
Thanked 0 Times in 0 Posts
Default problem writing listview data to mysql db

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
Reply With Quote