Results 1 to 13 of 13

Thread: RESOLVED:Problem writing data to mysql db

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2005
    Posts
    1,907

    Resolved RESOLVED:Problem writing data to mysql db

    Hi all i got this code that supposed to write the content of each record from immedite window to mysql but for some reason i get errors. The insert worked in my other project but does not here!!. Currently the song path and title is printed in immedite window but i want to write each row in to mysql table.
    I be happy if some one tell me what i am doing wrong here.Thanks
    Code:
    Compile Error:
    
    User-defined type not defined

    and points the red line "

    CNN As New Connection

    Code:
    Private Sub Command3_Click()
    
    Dim CNN As New Connection == points to here
    CNN.Open "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=visualbasic6;USER=root;PASSWORD=;OPTION=3;"
    
    
     Dim Parser As New clsXMLParser
      Dim Node As clsXMLNode
      Dim Child As clsXMLNode
      Dim fn As Long
      Dim i As Long
       Dim path As String
      Dim title As String
    
      fn = FreeFile
      Open "C:\temp.dat" For Output As #fn
        Print #fn, RichTextBox1.Text
      Close #fn
    
      Call Parser.Parse("C:\temp.dat")
      Call Kill("C:\temp.dat")
      
      Set Node = Parser.ParentNode
       
       For i = 1 To Node.ChildrenCount
          Set Child = Node.enumChild(i)
          If LCase$(Child.Name) = "song" Then
            path = Child.getAttribute("path")
            title = Child.getAttribute("title")
            'now you have song's path and title loaded into path and title variables
            Debug.Print path, title
            
            ' inserting it to mysql database
             'If CNN.State <> 0 Then
          
            CNN.Execute "INSERT INTO users VALUES(0,'" & path & "','" & title & "')"
            CNN.Close
          End If
       Next
    
    
    End Sub
    Last edited by tony007; Apr 27th, 2006 at 07:29 AM.

  2. #2
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,335

    Re: Problem writing data to mysql db

    Try
    VB Code:
    1. Dim CNN As ADODB.Connection
    2. Set CNN = New ADODB.Connection
    3. CNN.Open "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=visualbasic6;USER=root;PASSWORD=;OPTION=3;"

  3. #3
    Giants World Champs!!!! Mark Gambo's Avatar
    Join Date
    Sep 2003
    Location
    Colorado
    Posts
    2,965

    Re: Problem writing data to mysql db

    Try this

    VB Code:
    1. Dim CNN As ADODB.Connection
    2. Set CNN = New ADODB.Connection
    Regards,

    Mark

    Please remember to rate posts! Rate any post you find helpful. Use the link to the left - "Rate this Post". Please use [highlight='vb'] your code goes in here [/highlight] tags when posting code. When a question you asked has been resolved, please go to the top of the original post and click "Thread Tools" then select "Mark Thread Resolved."


  4. #4
    Giants World Champs!!!! Mark Gambo's Avatar
    Join Date
    Sep 2003
    Location
    Colorado
    Posts
    2,965

    Re: Problem writing data to mysql db

    Quote Originally Posted by Hack
    Try

    VB Code:
    1. Dim CNN As ADODB.Connection
    2. Set CNN = New ADODB.Connection
    3. CNN.Open "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=visualbasic6;USER=root;PASSWORD=;OPTION=3;"
    Rats, Hack beat me again
    Regards,

    Mark

    Please remember to rate posts! Rate any post you find helpful. Use the link to the left - "Rate this Post". Please use [highlight='vb'] your code goes in here [/highlight] tags when posting code. When a question you asked has been resolved, please go to the top of the original post and click "Thread Tools" then select "Mark Thread Resolved."


  5. #5

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2005
    Posts
    1,907

    Arrow Re: Problem writing data to mysql db

    Quote Originally Posted by Hack
    Try
    VB Code:
    1. Dim CNN As ADODB.Connection
    2. Set CNN = New ADODB.Connection
    3. CNN.Open "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=visualbasic6;USER=root;PASSWORD=;OPTION=3;"
    Thank u for u reply. I tried and same error :-(( Is there any refrences do i need to make ? I hope i get this fixed with your help.Thanks

  6. #6
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,335

    Re: Problem writing data to mysql db

    Quote Originally Posted by tony007
    Thank u for u reply. I tried and same error :-(( Is there any refrences do i need to make ? I hope i get this fixed with your help.Thanks
    You can't use either ADO or DAO without referencing its object library.

  7. #7
    Giants World Champs!!!! Mark Gambo's Avatar
    Join Date
    Sep 2003
    Location
    Colorado
    Posts
    2,965

    Re: Problem writing data to mysql db

    You have to add a Reference to the Microsoft Active X Data Object 2.X Library
    Attached Images Attached Images  
    Regards,

    Mark

    Please remember to rate posts! Rate any post you find helpful. Use the link to the left - "Rate this Post". Please use [highlight='vb'] your code goes in here [/highlight] tags when posting code. When a question you asked has been resolved, please go to the top of the original post and click "Thread Tools" then select "Mark Thread Resolved."


  8. #8
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,335

    Re: Problem writing data to mysql db

    Don't let all of those object librarys scare you. Pick the newest version (for me that is 2.7 but it appears as though Mark has 2.8 )

  9. #9

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2005
    Posts
    1,907

    Re: Problem writing data to mysql db

    many thanks that refrence solved the problem. Could any tell me if i want insert data in 2 feilds in table that has like 20 fields how i can do it? I tried this and gave me syntax error:

    Code:
    INSERT INTO songs filename,title  VALUES(0,'" & path & "','" & title & "')
    I want the remaining feields to be empty . Note: filname and title are not after
    one another in table structure.

  10. #10
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,335

    Re: Problem writing data to mysql db

    Quote Originally Posted by tony007
    many thanks that refrence solved the problem. Could any tell me if i want insert data in 2 feilds in table that has like 20 fields how i can do it? I tried this and gave me syntax error:

    Code:
    INSERT INTO songs filename,title  VALUES(0,'" & path & "','" & title & "')
    I want the remaining feields to be empty . Note: filname and title are not after
    one another in table structure.
    Your syntax looks Ok except for the VALUES clause. You have two fields listed but three values in your VALUES clause. What is the 0 for?

  11. #11

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2005
    Posts
    1,907

    Arrow Re: Problem writing data to mysql db

    Quote Originally Posted by Hack
    Your syntax looks Ok except for the VALUES clause. You have two fields listed but three values in your VALUES clause. What is the 0 for?
    Thank u for u reply. Well in my table the first table column is called :

    `ID` mediumint(8) unsigned NOT NULL auto_increment,

    i need to increment it for each input so i do not know if that 0 will do the thing for me? do u think i need to mention the other feilds name although i want them to be empty at this point?Thanks


    Code:
    CREATE TABLE `songs` (
      `ID` mediumint(8) unsigned NOT NULL auto_increment,
      `filename` text NOT NULL,
      `LastModified` text NOT NULL,
      `md5_file` varchar(32) NOT NULL default '',
      `md5_data` varchar(32) NOT NULL default '',
      `md5_data_source` varchar(32) NOT NULL default '',
      `filesize` int(10) unsigned NOT NULL default '0',
      `fileformat` varchar(255) NOT NULL default '',
      `audio_dataformat` varchar(255) NOT NULL default '',
      `video_dataformat` varchar(255) NOT NULL default '',
      `audio_bitrate` float NOT NULL default '0',
      `video_bitrate` float NOT NULL default '0',
      `playtime_seconds` varchar(255) NOT NULL default '',
      `tags` varchar(255) NOT NULL default '',
      `artist` varchar(255) NOT NULL default '',
      `title` varchar(255) NOT NULL default '',
      `remix` varchar(255) NOT NULL default '',
      `album` varchar(255) NOT NULL default '',
      `genre` varchar(255) NOT NULL default '',
      `comment` varchar(255) NOT NULL default '',
      `track` varchar(7) NOT NULL default '',
      `comments_all` text NOT NULL,
      `comments_id3v2` text NOT NULL,
      `comments_ape` text NOT NULL,
      `comments_lyrics3` text NOT NULL,
      `comments_id3v1` text NOT NULL,
      `warning` text NOT NULL,
      `error` text NOT NULL,
      `track_volume` float NOT NULL default '0',
      `encoder_options` varchar(255) NOT NULL default '',
      `vbr_method` varchar(255) NOT NULL default '',
      PRIMARY KEY  (`ID`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=586 ;

  12. #12
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,335

    Re: Problem writing data to mysql db

    If it is auto increment then you should not need to stipulate a value for it. Just write the INSERT naming the two fields, and the VALUES clause with the values for those two fields and you should be fine.

  13. #13

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2005
    Posts
    1,907

    Re: Problem writing data to mysql db

    Quote Originally Posted by Hack
    If it is auto increment then you should not need to stipulate a value for it. Just write the INSERT naming the two fields, and the VALUES clause with the values for those two fields and you should be fine.
    resolved lol . It had to be

    CNN.Execute "INSERT INTO songs (filename,title) VALUES('" & path & "','" & title & "')"
    Last edited by tony007; Apr 27th, 2006 at 07:27 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width