Getting MySQL and Visual Studio.NET Working Together
Via OLEDB

Introduction - What's in This Guide
     I really doubt that you're here to waste time, so I'll skip the usual introduction and get right to how to make this damn thing work.  If you're here, you're either just now wondering how to get MySQL and VS.NET to work with each other or having problems getting OLEDB to properly function with MySQL.  My reason for writing this page is for the people having trouble with MySQLProv not being registered on your machine.  If this isn't your problem, you can still read this page to setup everything--the guide is complete and will allow you to successfully start using OLEDB for a MySQL database in .NET.  Since this is a long guide, I'll change the section headers to a shade of light blue instead of the normal bold white.

Getting Started - Downloading What You'll Need
     This section, aptly titled "Downloading What You'll Need" will do just that.  Below is a list of links that WORK as of today-6/14/2003.

     Alright.  As you can see, we'll be using MySQL 4.0, MDAC 2.7, and one of the many listed MyOLEDB drivers listed on MySQL's website.  I'm not using VS.NET 2003, so if you have that product I cannot ensure that this guide will work (or is even necessary).  If you are going to use phpMyAdmin for setting up and working with the database, you'll also need a web server (such as Apache), and obviously PHP so you can run phpMyAdmin.  You don't have to have phpMyAdmin as you can do everything with a command-line tool provided with MySQL, but it makes the process a lot nicer.  If you've already got all of what you need installed (MyOLEDB Driver, MySQL server, and a database/know how to edit it) then just skip all of this following information and go to the "Ok, Now I've Done All of That Crap--What About Visual Studio?" section, seven section below.

Installing a MySQL Server
     Installing MySQL for the first time can be a long and daunting task.  As much as I don't want to cover it, I will just for the fact that nobody else seems to have made a short and simple guide.

1)  Install the Server.
2)  Run the winmysqladmin.exe program in your MySQL\bin directory.  It will setup your my.ini file so when you go to install the service things don't die.  If it asks you for a username and password, please note you can enter anything you want.  This is for the admin utility in the future.
3)  Open a command prompt.  Going to Start>Run and typing cmd (then enter) will bring one up.
4)  Navigate to your MySQL\bin directory.
5)  Type and run without quotes:  "mysqld-max-nt.exe --remove"
6)  It should say that the service has been removed.  If it gives an error about there being no service in the first place, proceed anyway.  If you cannot get rid of the error, make sure that the service is not running in Start>Control Panel>Administrative Tools>Services.  The service will be named "MySQL".
7)  Type and run without quotes:  "mysqld-max-nt.exe --install"
8)  Once again, if you have done everything right so far then it shouldn't display an error.
9)  Now type and run without quotes:  "mysql.exe -u root mysql"
10)  You should now be running the mysql.exe program.  If you are, the prompt will be labeled "MySQL>" or something similar.
11)  Where you see MyOwnPassword please substitute in your own real-life password that you intend to use for authenticating with MySQL.  Run this command without double quotes--leave the single quotes in: 
"UPDATE user SET Password=PASSWORD('MyOwnPassword') WHERE user='root'"
for clarification, without quotes you would enter:
UPDATE user SET Password=PASSWORD('MyOwnPassword') WHERE user='root'
12)  Now you have setup a password for you MySQL server.  This is vital if the database is to be accessable over the internet.  If this is just for your local machine, then the password really isn't needed except to keep out anybody that might have access to your computer.  What I guess I'm trying to say is:  passwords are always a good idea, but if the database is accessable over the Internet then make sure you have a DAMN GOOD password.  You need to stop and restart the service by going to your Start Menu>Control Panel>Administrative Tools>Services and selecting the MySQL service.  Right-click it and select the "Restart" option.  This will shutdown and restart the MySQL service so your password change on the root user takes place.  You can also use the "FLUSH PRIVELEGES;" command after you update the password for the root user in mysql.exe, but I prefer restarting the service entirely.

Installing the MyOLEDB Driver
     Installing this driver is very straight forward.  Just follow the instructions on screen after executing the setup program and this will be correctly setup, barring any error messages.  Once you've installed the MySQL server and this driver, you've got everything you must have in order to get running in VS.NET.  I strongly recommend that you install the latest .NET framework, SDK, and MDAC components.

Optional #1 - Installing Apache 2.0
     If you're already Run the installer and fill in the information that it asks for.  If you don't have a domain or you don't wish to install Apache on that domain, use "localhost" without the quotes as your domain/website.  I suggest you make it an automatic service.

Optional #2 - Installing PHP 4.3.2
     Assuming your Apache install went correctly (which you can test by opening your favorite browser and going to http://localhost - it should pop up with a page.  Install PHP to any directory of your choosing.  Afterward, go to your Apache2\conf\ directory and open up httpd.conf with notepad or any other text editing program.  At the end of that file, add these lines:

ScriptAlias /php/ "C:/Program Files/PHP/"
AddType application/x-httpd-php .php
Action application/x-httpd-php "/php/php.exe"

Be sure to change "C:/Program Files/PHP" to whatever directory you installed PHP to.  The rest can stay the same.  Save the file and restart the Apache service.  It's in the same location as mentioned in Step 12 of installing MySQL.  This time, restart the Apache2 service.  After it restarts, you're good to go with phpMyAdmin and you can install it.

Optional #3 - Installing phpMyAdmin
     Now you'll need to open up the directory used to store all of your web files.  By default, this is your Apache2\htdocs\ directory.  Unzip phpMyAdmin into this directory (the directory is already provided in the zip).  You may wish to rename the directory containing phpMyAdmin.  I suggest renaming it (once agian, very aptly) phpmyadmin.  Now you will need to open this directory and open up config.inc.php with a text editor, preferable notepad.  The following lines are in order from the top of the file to the bottom.  Change the following lines:

*(Use the password that you used for your MySQL server.  If you skipped that part, then you might also need to change the username accordingly.  The purpose is to tell it what username and password to use to establish a connection to the database.)

Now open up the httpd.conf file again and add this information:

<Directory C:/Web/phpadmin2>
AuthType Basic
AuthName "Restricted Files"
AuthUserFile "C:/Program Files/Apache Group/Apache2/passes/passwords"
Require user WhateverUserNameYouWant
</Directory>

Once again, you'll need to change the C:/Web/phpadmin2 directory to where you put phpMyAdmin.  Yes, use forward slashes.  Use them anywhere I use forward slashes.  Some people are just backward... go figure.  If the AuthUserFile path is incorrect (to your Apache directory) please change it accordingly.  Leave the /passes/passwords after it though.  For example if you installed Apache to W:\Apache instead, you might change it to W:/Apache/Apache2/passes/passwords.  Also be sure to change WhateverUserNameYouWant to a username of your choice.  This doesn't have to be the same username as your MySQL username--in fact I discourage it.

Now open up a command prompt (Start>Run; type cmd and press enter).  Navigate to your Apache2\bin\ directory and type the following command without quotes:
"htpasswd.exe -c YourApacheDirectory/Apache2/passes/passwords TheUserNameYouUsedAbove"

Make sure to change YourApacheDirectory to your installation directory and TheUserNameYouUsedAbove to the same (including case) username you supplied for WhateverUserNameYouWant.  If it errors, make sure the Apache2\passes\ directory exists.  If not, then create it.  If it didn't error, you should be all setup.  It will ask you for a password and to confirm that password.  After that, it should be all green lights.  Make sure to restart the Apache2 service once again.  Now your phpMyAdmin directory should be password protected against intruders.  Open up a browser window and go to http://localhost/phpmyadmin (or whatever you named it).  It should prompt you for a username and a password (which Windows can save for you while still keeping prying eyes out).  I suggest you bookmark it.

I've Had Problems With One of Those Steps (If you had no problems, skip to the next section)
     If you had trouble with Apache, check the documentation available under the Apache HTTP Server folder in your start menu.  It can provide answers to most of your questions.
     If you had trouble with PHP, check the documentation at:  http://www.php.net/docs.php  and you'll most likely figure it out.  It's definitely more in-depth than my tutorial.
     If you had trouble with phpMyAdmin, then be sure to check the documents available from the website:  http://www.phpmyadmin.net/documentation/
     If you have problems and cannot find the answer anywhere, email me at:  mysqlhelp@pcrpg.org and I'll see what we can get going.  Please note that I'm not just a help desk--only email me as a last resort.  If you're demanding or sound in other ways arrogant/pissed then your email is likely to be deleted with no reply.  Please only send intelligent messages.

Ok, Now I've Done All of That Crap--What About Visual Studio?
     If you have a database setup, you can proceed to work in Visual Studio.  If not, use phpMyAdmin to create a database and a simple table.  Usage of phpMyAdmin is rather straight-forward.  If you need help, try the documentation for it above.  Also, there are links named "Documentation" everywhere throughout phpMyAdmin that can point you to help.

     Now, assuming you've installed the MyOLEDB driver and your MySQL server is up and running, let's begin.  I only cover VB.NET code, but the methodology is similar for C# as well.  If you're programming and have come this far using C++, then you should be able to adapt this to your language.

Dim fdCon As New OleDb.OleDbConnection("Provider=MySQLProv;Data Source=DATABASE;User Id=YOURSQLUSERNAME;Password=YOURSQLPASSWORD;")
Dim fdCom As New OleDb.OleDbCommand("SELECT * FROM TABLENAME ORDER BY DESIREDFIELD ASC", fdCon)

fdCom.Connection.Open()

Dim fdRead As OleDb.OleDbDataReader = fdCom.ExecuteReader(CommandBehavior.CloseConnection)

While fdRead.Read
     'Do what you want here.  Below is code that will pop up a message box for every record.
     'This code would work if your table had three fields.
     'This database's first field is an auto-incrementing ID medium integer, second field is a
     'VarChar, and the third is also a VarChar.  This code displays each field on its own line.
     MsgBox(fdRead.GetValue(0) & vbCrLf & fdRead.GetValue(1) & vbCrLf & fdRead.GetValue(2))
End While

fdCon.Close()

     You will need to change the bold parts to the appropriate information.  For example, DATABASE should be the name of your MySQL database, YOURSQLUSERNAME is the username (probably root) for the database, and the same concept for YOURSQLPASSWORD.  Change TABLENAME to the name of your table.  It's a good idea to stay away from all capital names in SQL.  You don't have to use the ORDER BY DESIRED FIELD ASC statement, but you can to sort the data.  If you want to use it, change the DESIREDFIELD to the name of one of your fields.  Sorting by an ID field if you've got one is always a good idea.  ASC = ascending; DESC = descending.  For more information on SQL commands, please visit a site such as:
http://www.phpfreaks.com/postgresqlmanual/page/sql-commands.html
or for an explained easy-to-learn course of basic SQL commands such as INSERT, SELECT, UPDATE, and DELETE go to:
http://www.developerfusion.com/show/48/1/
Once you've learned the stuff at Developer Fusion, the PHP Freaks page will come in handy as you'll understand it better.

     Now that you've learned basic data retrieval, let's go over a non-query.

Dim fdCon As New OleDb.OleDbConnection("Provider=MySQLProv;Data Source=DATABASE;User Id=YOURSQLUSERNAME;Password=YOURSQLPASSWORD;")
Dim fdCom As New OleDb.OleDbCommand("", fdCon)

fdCom.Connection.Open()

Dim fdRead As OleDb.OleDbDataReader = fdCom.ExecuteReader(CommandBehavior.CloseConnection)

fdCom.CommandText = "INSERT INTO TABLENAME (FIELDNAME1, FIELDNAME2) VALUES (""lookatme"", ""newline!"")"
fdCom.ExecuteNonQuery()

fdCon.Close()

Once again, replace TABLENAME and the FIELDNAMEx to appropriate values.  You can have more or less fields (depending on what's in your table) or select one the fields you want.  However, take notice of the order in which you list them as that's the order in which the VALUES will be placed.  If you take notice, "lookatme" would be inserted as FIELDNAME1 and "newline!" would be inserted for FIELDNAME2.  Using a double double-quote in VB acts as an escape character and actually inserts a real double-quote.  It's always a good practice to either use that or a solitary single-quote around your variable names.  If you were going to use the variables ImaVar1 and ImaVar2 with double-quotes, you could do it like this:

Dim fdCon As New OleDb.OleDbConnection("Provider=MySQLProv;Data Source=DATABASE;User Id=YOURSQLUSERNAME;Password=YOURSQLPASSWORD;")
Dim fdCom As New OleDb.OleDbCommand("", fdCon)

fdCom.Connection.Open()

Dim fdRead As OleDb.OleDbDataReader = fdCom.ExecuteReader(CommandBehavior.CloseConnection)

fdCom.CommandText = "INSERT INTO TABLENAME (FIELDNAME1, FIELDNAME2) VALUES (""" & ImaVar1 & """, """ & ImaVar2 & """)"
fdCom.ExecuteNonQuery()

fdCon.Close()

Yes, that's three double-quotes together.  That's the double double-quote inside of a quoted string.  It stores an actual quote there.  Now, to use single quotes you might do it like below:

Dim fdCon As New OleDb.OleDbConnection("Provider=MySQLProv;Data Source=DATABASE;User Id=YOURSQLUSERNAME;Password=YOURSQLPASSWORD;")
Dim fdCom As New OleDb.OleDbCommand("", fdCon)

fdCom.Connection.Open()

Dim fdRead As OleDb.OleDbDataReader = fdCom.ExecuteReader(CommandBehavior.CloseConnection)

fdCom.CommandText = "INSERT INTO TABLENAME (FIELDNAME1, FIELDNAME2) VALUES ('" & ImaVar1 & "', '" & ImaVar2 & "')"
fdCom.ExecuteNonQuery()

fdCon.Close()

That's a single-quote on the inside of the string declaration quotes.  ' " and " ' accordingly--no spaces between them.  I guess that about wraps it up.  If you need help on something, fire off an email to mysqlhelp@pcrpg.org and I'll get back to you as soon as I can.  Thanks for reading this tutorial--it's pretty long, I know.  I could have spent a Saturday better ways, trust me!