connecting to mysql

[man_luck]

hi ,

this is my first post on the group.I have just started working on vb.net and was able to connect to msaccess,with the following as connectionstring:

 

connectionCalenderEvent = New OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;data source=c:\CRMdatabase.mdb")

 

now i want to connect to MYSQL .i know for that i have to use oledbconnection class.now i have a few doubts,

 

1)what exactly we mean by ODBC driver and OLEDB driver?

2)which provider shall i use for connecting to MYSQL

3)What do we mean by a driver and a provider?

 

moreover i was able to connect to mysql server(which is on the local machine) as follows:

 

conPat_Details = New MySqlConnection("Host=localhost;Database=Appointments;User Id=manish;Password=manish99;Compress=True")

 

I have imported Corelab.Mysql class

 

Kindly make my doubts clear(assume i have a very elementary knowledge)

 

thanx

[HJB417]

1) Two different database connection technologies. Unfortunately, I don't know the difference between.

2) Mysql ships w/ a odbc provider, I believe, or it's at least available for download @ the website, but I use -> http://www.bytefx.com/DotData.aspx

because it supports named parameters, is open source, and the code is easy to understand for my needs.

3) I think they're interchangeable, but you should stick w/ the term 'provider'.

 

here's a quick read that will help u:

 

http://dev.mysql.com/tech-resources/articles/dotnet/

http://dev.mysql.com/doc/connector/odbc/en/faq_toc.html

[doubled]

This is what I did for a asp.net(vb.net) app with mysql so there shouldn't be to much different with straight vb.net

 

1) MyODBC - MyODBC is a 32-bit ODBC Driver, also known as MySQL ODBC 3.51 Driver. After you have downloaded and installed MyODBC, you will need to setup a new DSN (Program Files > Administrator Tools > DataSource ODBC. Go to "System DSN" and click on "Add" Scroll down to MySQL ODBC 3.51 Driver and click on "Finish" (your not done yet though!)

 

Next enter the information for your MySQL Database, note Data Source Name is what you wish to call the DSN, for instance "mysqldsn". When finished click on "Test Data Source", if it connects, then click on ok, if not check your entries and try again.

 

2) ODBC .NET Data Provider - The Open Database Connectivity (ODBC) .NET Data Provider is an add-on component to the .NET Framework. It provides access to native ODBC drivers the same way the OLE DB .NET Data Provider provides access to native OLE DB providers.

 

After you have downloaded the exe, simply install and your done... go to step 3...

 

3) MDAC 2.7 - This may or may not be needed with your system, if your running Windows XP, then your ok, everyone else needs to download the new version...

 

Before you install MDAC 2.7, be sure to stop IIS and reboot after the install is complete...

 

 

 

 

 

 

OK There are a couple of key things to notice and remember here, the first is on line one in the Page Declaration. Notice the CompilerOptions='/R:"C:\Program Files\Microsoft.NET\Odbc.Net\Microsoft.Data.Odbc.dll"', this is needed in order for your application to find the odbc.net dll. Of course you can set this in your webconfig file but that's beyond the scope of this post...

 

The second important line to notice is: Import Namespace="Microsoft.Data.ODBC". You must import the Microsoft.Data.ODBC namespace in order for your application to use the ODBC objects...

 

Finally, where we setup the dsn connection notice that the name "mysqldsn" is the same one I used as an example when setting up the dsn in step 2, be sure to change this to the name you entered when setting up your dsn...

[man_luck]

thanx 4 the reply.but a few of my doubts still remain unclear.I mean when we connect to access database we give a connection string as

 

connectionCalenderEvent = New OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;data source=c:\CRMdatabase.mdb")

 

do we need to mention a provider to connect to mysql database?

and if so which proider shall i list?

as i said i was able to connect to mysql database without mentioning the provider

 

 

 

 

 

 

This is what I did for a asp.net(vb.net) app with mysql so there shouldn't be to much different with straight vb.net

 

1) MyODBC - MyODBC is a 32-bit ODBC Driver, also known as MySQL ODBC 3.51 Driver. After you have downloaded and installed MyODBC, you will need to setup a new DSN (Program Files > Administrator Tools > DataSource ODBC. Go to "System DSN" and click on "Add" Scroll down to MySQL ODBC 3.51 Driver and click on "Finish" (your not done yet though!)

 

Next enter the information for your MySQL Database, note Data Source Name is what you wish to call the DSN, for instance "mysqldsn". When finished click on "Test Data Source", if it connects, then click on ok, if not check your entries and try again.

 

2) ODBC .NET Data Provider - The Open Database Connectivity (ODBC) .NET Data Provider is an add-on component to the .NET Framework. It provides access to native ODBC drivers the same way the OLE DB .NET Data Provider provides access to native OLE DB providers.

 

After you have downloaded the exe, simply install and your done... go to step 3...

 

3) MDAC 2.7 - This may or may not be needed with your system, if your running Windows XP, then your ok, everyone else needs to download the new version...

 

Before you install MDAC 2.7, be sure to stop IIS and reboot after the install is complete...

 

 

 

 

 

 

OK There are a couple of key things to notice and remember here, the first is on line one in the Page Declaration. Notice the CompilerOptions='/R:"C:\Program Files\Microsoft.NET\Odbc.Net\Microsoft.Data.Odbc.dll"', this is needed in order for your application to find the odbc.net dll. Of course you can set this in your webconfig file but that's beyond the scope of this post...

 

The second important line to notice is: Import Namespace="Microsoft.Data.ODBC". You must import the Microsoft.Data.ODBC namespace in order for your application to use the ODBC objects...

 

Finally, where we setup the dsn connection notice that the name "mysqldsn" is the same one I used as an example when setting up the dsn in step 2, be sure to change this to the name you entered when setting up your dsn...

[HJB417]

To use ole db means that the you're going to end up going through a com layer to access the database. What happens is, when u install an ole db database driver, it adds some entries into the system registry stating where the com object is. When you Open an ole db connection, it goes to the system registry and looks for an entry that matches the 'provider'. Do u kinda get what I'm saying? As an example run the following code:

 

System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();

conn.ConnectionString = @"provider=Does Not Exist;data source=c:\CRMdatabase.mdb";

conn.Open();

[Joe Mamma]

3) MDAC 2.7 - This may or may not be needed with your system, if your running Windows XP, then your ok, everyone else needs to download the new version...

Microsoft Data Access Components (MDAC) 2.8