connecting to mysql database

[phishbone]

Hello,
I need help connecting to a mysql database using a visual basic 5 program. I am able to do it through vbscript in an asp page, so I know I've got the connection string correct, I'm just not clear what activex dll I need, or how to include it. Can I still use the ADODB.connect?

Thanks,
Emily

 

[zemp]

You should still be able to use ADO. But I think that you will need to set the .connectionstring property and then use the .Open method.

You can double check your connection string at

http://www.connectionstrings.com

or

http://www.able-consulting.com/ADO_Conn.htm

zemp

 

[zemp]

Of Course, you will be using the ADO Connection object.

zemp

 

[mmilan]

Emily,

Make certain that you have selected the ADO stuff (Microsoft ActiveX Data Objects xxx Library) selected in your project references as well.

The referenences can be accessed through the project menu.

mmilan

 

[ZOR]

Have you done a search on planetsourcecode.com for MySql. You should find some examples in there to look at as well.

 

[phishbone]

Hello,
Thanks for all the help. Unfortunately I'm still having trouble connecting. I get this error when I try to connect "Data Source Name not found and no default driver specified". This is the code I'm using


Dim ConnectionSTR As String
Dim conn As ADODB.Connection
Dim Query As String
Dim rs As ADODB.Recordset

Text1.Text = "connecting to database..."
Set conn = New ADODB.Connection
conn.ConnectionString = "DRIVER ={MySQL ODBC 3.51 Driver}; SERVER = localhost; DATABASE = testunits; UID=root; PWD=; OPTION=3"
conn.Open
Text1.Text = "connection succeeded"

It looks the same as the examples on planetsourcecode.com.

Any ideas?

Emily

 

[ZOR]

I only just found out, there is a MySql Forum within Tek-Tips. I just entered MySql in the search for forums box at the top of this page, and hey presto theres a forum. Could try posting there as well?

 

[zemp]

Seems obvious but, does the target machine have the MySQL drivers installed?

zemp

 

[phishbone]

yes everything is installed. As I mentioned before, I was able to connect with an asp page written in vbscript on the same machine.

 

[zemp]

A couple of things that you can try.

1. Build a System DSN ODBC connection to ensure that you can connect and it is not some other issue.

2. Temporarily add and ADO data control and use it's wizard/properties to build a connection string and then compare it to yours.

zemp

 

[phishbone]

I tested the system DSN ODBC connection and it was successful. I also ran my old asp program just to be sure and it successfully connects and queries the db.

As for the ADO data control/wizard, I'm not sure where to find this. I'm not very experienced in Visual Basic. I didn't see it with the other controls. Is this a control that I have to add in? I'm still a little confused about which of the "Microsoft Access Data Object" libraries I need to include. There's a lot of them listed and it won't let me select them all.

Emily

 

[zemp]

You need to reference the ADO data control via the 'Project|components' menu. Scroll until you find 'Microsoft ADO Data Control 6.0'. Select it. It will then appear in your toolbox. You can then draw it on a form and access its properties as any other control.

A reference to ADO is found via the 'Project|References' menu. You should select 2.6 or 2.7. You only need one because they are suppose to be fully "backward compatible". Take that with a grain of salt.

zemp

 

[phishbone]

I can't seem to find that control in the list. Was it available for VB5?

 

[zemp]

I am not sure about it's availability in VB5. I was thinking VB6.

zemp

 

[Merlijn]

Emily,

I'm using a mySQL database too from within VB through ADO.

This is the connectionstring I'm using:

[tt]Provider=MSDASQL;Driver={MySQL ODBC 3.51 Driver};Server=localhost;Port=3306;Option=131072;Uid=MyID;Pwd=MyPWD;Database=myDB[/tt]

As you can see the option is quite different from what you are using. I got this optionn number through trial and error. At first it didn't work with the parameters I found in the mySQL manual. With this option-number it does!
I also added a provider to the string. ADO uses that to communicate with the odbc driver.


Merlin is the name, and logic is my game...

 

[phishbone]

Hi Merlin,
Thanks for your help. I tried adding in the provider and changing the option, but I still got the same error.
Emily