[RESOLVED] LOST - VB6 and MySQL basics

[spazztic]

I am pretty familiar with SQL commands so I think I can figure this out once I get the basics but the basics are killing me and I was not able to find the information elsewhere in the forum.

I am a novice VB6 programmer and need to check an online customer database.


I need information on:
1. What Component do I need to add to my project?
2. Is there anything special I need to do with the component or references to make it work correctly?

Here is what I want to do (pseudo code):
1. set db connection
2. connect to db, tablename
3. "Select * from tablename where user=" & strUser
4. Get other variables placed from recordset (user, password, active (y/n)) into local variables and close connection to DB
5. If password=strPass AND active="Y" then
---->Do Code
-->Else
---->Do Code
-->End If


I have looked through the tutorial and a few other things but still seem to be lost. I *should* be able to figure out the remaining code once I can get a connection set up and working.

I have verified with my host that they allow remote connections and it is set up on my domain.

[Campion]

To connect to a mySQL database, you need to download a mySQL connector, available from the mySQL wwebsite. Preferably you would want the ODBC connector. Install that. It requires no setup.

Then you want to load the ADO reference into VB. (MSADO)

Once you have the ADO object referenced, you need to load a connection string. Check this page to see your options:

http://www.connectionstrings.com/?carrier=mysql

Then you open the connection, load a recordset, and run your query.

I would give more detailed info, but I don't have VB in front of me right now.

[spazztic]

I found and installed the MySQL ODBC connector.

Cannot find ADO or MSADO in references. I found several references for DAO - is it the same?

[Campion]

It'll be under Microsoft ActiveX Data Objects. You want to use the most current version you have available.

And FYI, DAO, while it eventually achieves the same goal, is older technology and is only for backward compatibility.

I'll have some example code for you in a while (assuming I have energy when I get home.) In the mean time, try playing with ADO.

[spazztic]

Thanks. I was actually coming here to post and ask if ActiveX Data Objects was what I was looking for. Your "pre"reply also included the next question of which version

I will try that, play with it some and come back shortly.

[spazztic]

Okay, I am starting to make some moves in the right direction but do not have a working connection yet. I am throwing an error on the open string:

        'dim the variables for connecting to DB
        Dim strServerName As String
        Dim strDatabaseName As String
        Dim strUserName As String
        Dim strPassWord As String
        Dim ConnectString As String
        Dim ADOCn As New ADODB.Connection
        Dim rsTempRecordSet As New ADODB.Recordset
       
        
        
        'build the DB connection

      strServerName = "ip.removed.because.notimportant"
      strDatabaseName = "dbname_iscorrect"
      strUserName = "thisis_correctaswell"
      strPassWord = "AlSocorreCt"
       
      ConnectString = "DRIVER={MySQL ODBC 3.51 Driver};" & _
                      "SERVER=" & strServerName & _
                      ";DATABASE=" & strDatabaseName & ";" & _
                      "USER=" & strUserName & _
                      ";PASSWORD=" & strPassWord & _
                      ";OPTION=3;"
                      
                      Set ADOCn = New ADODB.Connection
                        ADOCn.ConnectionString = ConnectString
                        ADOCn.Open '<--This throws error!!
                        
                         Set rsTempRecordSet = New ADODB.Recordset
                            rsTempRecordSet.Open "Select * customers where email='[email protected]'", ADOCn, adOpenForwardOnly, adLockReadOnly, adCmdText

I am using the Microsoft ActiveX Data Objects 2.8 Library (system\ado\msado15.dll) in my references.
The error is:
Data Source name not found and no default driver specified.
Run Time Error '-2147467259 (80004005)'

Thanks.

[rasinc]

MySQL connection strings require a server name and port also. Since the database is online it should be running on the same port all the time (default 3306). If the program is running from the server, then localhost should work for the computer name otherwise you need to know the name of the computer the database is running on. This should give you what you need.

[spazztic]

Shouldn't the server IP be sufficient for the connection string?
Also, I saw somewhere else that the port was not necessary in the connection string when the MySQL server was on the default port (3306).

Do I have to put the FQDN or is the IP sufficient?

Edit: That was a no go - tried switching to domain name and added port - same error.

[Campion]

Sorry, Spazz. I got drug into holiday things yesterday, so I couldn't get back to you.

As far as what you are seeing right now, check out your ODBC source. It's possible that setup did not set it up correctly, or at all.

Control Panel > Admin Tools > Data sources

Check under the System DSN tab and see if the mySQL connector is there. If not, then add it.

[spazztic]

You were correct - it did not setup at all.
I created a generic name for it but have a few questions:
1. DO I have to set up the server and all in the data source itself?
2. If so, when I compile the program, are the users of the software going to be able to see the data source? (I obviously do not want software users having access to my database username and password)

I will be out most of the day today (family reunion) but will check back this evening. Thanks again for your help

[Campion]

1) No, the driver name is all you need. There just needed to be a data source for it to plug into. The name of the data source is irrelevant to VB.

2) They can only see it if they go into the ODBC options.

[spazztic]

2) They can only see it if they go into the ODBC options.

So, someone that knew what they were doing could get access to my username and password to my MySQL DB if I coded this way? I assumed that since the connection string was hard-coded that they would not be able to see it....


Also, I am still getting the above referenced error...

[Campion]

So, someone that knew what they were doing could get access to my username and password to my MySQL DB if I coded this way? I assumed that since the connection string was hard-coded that they would not be able to see it....


Also, I am still getting the above referenced error...

If you explicitly put your username and password into the ODBC data source, then yes, they can get your information. If you use variables as place holders in your code, you can fill those in at run time, and it will do the job, because the connect string takes care of the data source information. All the connection string needs to know implicitly is what driver to use.

To combat the error, try commenting out the OPTION field in the connect string.

[spazztic]

To combat the error, try commenting out the OPTION field in the connect string.

That didn't work - same error.

[Campion]

Just on a whim, try putting a space between the { and M in the driver field in the connection string.

[spazztic]

Nice thought
Unfortunately that did not work either. I also tried adding a space after driver as well. - no go.

Same error

[spazztic]

Okay,
I did something else to try and get this to work. I downloaded the 5.1 connector, changed the driver syntax to reflect that.

With the 5.1 connector it seems that I am at least able to connect.

[Campion]

Hmm. This is a stumper. I'll take a closer look at my system and code at home later today and compare.

edit -

Good to see that 5.1 worked. Check your DSN settings to verify that things look okay there. I have both 3.51 and 5.1 on my system, and I can't get 5.1 to work properly.

[Campion]

Delete.

[spazztic]

Hmm. This is a stumper. I'll take a closer look at my system and code at home later today and compare.

edit -

Good to see that 5.1 worked. Check your DSN settings to verify that things look okay there. I have both 3.51 and 5.1 on my system, and I can't get 5.1 to work properly.

Yeah, this is weird. I am just glad it is connecting. Now I just need to get my code to pull the specific settings I need. I will repost my code afterwards for anyone else once I have it working so if anyone else is having this problem. Although I got it working by using a different driver, I do appreciate your help - you got my brain rolling in the right direction to get it figured out

[spazztic]

Okay, for anyone that needs help in this matter, take a look at this code:
It connects to a MySQL Database and inputs a few values from one record into variables that you can work with inside VB. It uses the MySQL 5.1 ODBC driver.

To get this to work, you have to download and install the 5.1 ODBC Connector from mysql.com, then install it. Then go to control panel -> Admin -> Data Sources (ODBC), then Add it to the list.

Open up your VB program, go to Project -> References, then add the Microsoft ActiveX Data Objects (newest version - I used 2.8 library as referenced above). You may have to save your project, exit, and re-enter. For some reason mine did not take the first time.

Then, you can connect and retrieve values like the code below:

        'dim the variables for connecting to DB
        Dim strServerName As String
        Dim strDatabaseName As String
        Dim strUserName As String
        Dim strPassWord As String
        Dim ConnectString As String
        Dim ADOCn As ADODB.Connection
        Dim rsTempRecordSet As New ADODB.Recordset
       
       'dim the variable for checking reg and unlock codes
       Dim lngReg As Long
       Dim lngUnlock As Long
       
        
        
        'build the DB connection

      strServerName = "ip.add.goes.here"
      strDatabaseName = "your_database"
      strUserName = "db_username"
      strPassWord = "db_password"
      
      ConnectString = "DRIVER={MySQL ODBC 5.1 Driver};" & _
                      "SERVER=" & strServerName & _
                      ";DATABASE=" & strDatabaseName & ";" & _
                      "USER=" & strUserName & _
                      ";PASSWORD=" & strPassWord & _
                       ";OPTION=;" '3;"
                      
                      Set ADOCn = New ADODB.Connection
                        ADOCn.ConnectionString = ConnectString
                        ADOCn.CursorLocation = adUseClient
                        ADOCn.Open

                         Set rsTempRecordSet = New ADODB.Recordset
                            rsTempRecordSet.Open "Select * From customers where email='" & gstrEmail & "'", ADOCn, adOpenForwardOnly, adLockReadOnly, adCmdText
                      gintActive = rsTempRecordSet("Active")
                      lngReg = rsTempRecordSet("RegKey")
                      lngUnlock = rsTempRecordSet("Unlock")
                      
                      ADOCn.Close

Hope this helps someone else not spend so much time trying to figure it out

[Campion]

Okay, Spazz, here's my string for 3.51, Notice the difference in variables.

objOptions.DBConnectString = "uid=" & objOptions.SQLUsername & ";" & _
                    "pwd=" & objOptions.SQLPassword & ";" & _
                    "server=" & "localhost;" & _
                    "driver={MySQL ODBC 3.51 Driver};" & _
                    "port=" & objOptions.SQLPort & ";" & _
                    "database=" & objOptions.DatabaseID & ";dsn=;"

[spazztic]

Yeah, I tried a very similar connection string elsewhere and tried it as well but it did not work for me. I think maybe I just had a bad install of the 3.51 ODBD driver - that is the only thing I can figure because everything "looked" like it should have worked under the old way.

I am just glad we got it to work, even if I did have to use a different driver