p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/)
-   Pro VB 6 (http://p2p.wrox.com/pro-vb-6-73/)
-   -   problem writing listview data to mysql db (http://p2p.wrox.com/pro-vb-6/42669-problem-writing-listview-data-mysql-db.html)

method May 17th, 2006 07:15 AM

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

woodyz May 17th, 2006 10:51 AM

In your first sample it appears you have several problems:

1 - The syntax is incorrect for working with a listview
2 - Your use of quotes in building the string is incorrect

The code should look more like this:


Code:

Private Sub Command1_Click()

    Dim sSQL As String   
    Dim i As Long
    Dim item As ListItem    ' Here is the listitem you need to work with

    For i = 1 To ListView1.ListItems.Count
        Set item = ListView1.ListItems(i)
        sSQL = "INSERT INTO musictest (url,songname,artist,album) "
        sSQL = sSQL & " VALUES('" & item.Text & "', '"
        sSQL = sSQL & item.SubItems(1) & "', '"
        sSQL = sSQL & item.SubItems(2) & "', '"
        sSQL = sSQL & item.SubItems(3) & "')"

        Debug.Print sSQL
    Next

End Sub

Of course, you will have to put your data access code back in - I removed it for clarity.

I hope that helps

Woody Z http://www.learntoprogramnow.com

doug65536 April 10th, 2007 08:17 PM

Concatenating a sql statement as shown is a very bad idea.
What if one of the strings contain a quote?
It screws up. Use parameterized queries!!!


All times are GMT -4. The time now is 09:24 AM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
2013 John Wiley & Sons, Inc.