Introduction - What's in This Guide
This guide is completely obsolete as it was written before the introduction of MySQL Server 4.1 and the official MySQL Connector/Net ADO.NET connector.
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
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
Installing a MySQL
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
3) Open a command prompt. Going to
Start>Run and typing cmd (then enter) will bring one
4) Navigate to your MySQL\bin directory.
5) Type and run without quotes: "mysqld-max-nt.exe
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
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')
for clarification, without quotes you would enter:
UPDATE user SET Password=PASSWORD('MyOwnPassword')
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
Installing the MyOLEDB
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
Optional #1 - Installing
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
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
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
||$cfg['PmaAbsoluteUri'] = '';
(There are three of these next ones.
Change all three.)
*(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
AuthName "Restricted Files"
AuthUserFile "C:/Program Files/Apache
Require user WhateverUserNameYouWant
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
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
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
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:
If you have problems and cannot find the answer
anywhere, email me at:
email@example.com 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
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
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.
OleDb.OleDbCommand("SELECT * FROM TABLENAME
ORDER BY DESIREDFIELD ASC", fdCon)
'Do what you want here.
Below is code that will pop up a message box for
'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))
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
or for an explained easy-to-learn course of basic
SQL commands such as INSERT, SELECT, UPDATE, and
DELETE go to:
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.
fdCom.CommandText = "INSERT INTO TABLENAME
(FIELDNAME1, FIELDNAME2) VALUES (""lookatme"", ""newline!"")"
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:
fdCom.CommandText = "INSERT INTO TABLENAME
(FIELDNAME1, FIELDNAME2) VALUES (""" & ImaVar1 &
""", """ & ImaVar2 & """)"
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
fdCom.CommandText = "INSERT INTO TABLENAME
(FIELDNAME1, FIELDNAME2) VALUES ('" & ImaVar1 & "',
'" & ImaVar2 & "')"
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
firstname.lastname@example.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!