Important alert: (current site time 10/23/2014 10:07:55 AM EDT)
 

article

Using MySQL with Visual Studio

Email
Submitted on: 6/14/2003 4:01:18 PM
By: Particle 
Level: Intermediate
User Rating: By 35 Users
Compatibility: VB.NET
Views: 350482
 
     If you're just wondering how to use MySQL with VS.NET or are having problems, give this a try.

 
 
Terms of Agreement:   
By using this article, you agree to the following terms...   
  1. You may use this article in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.
  2. You MAY NOT redistribute this article (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.   
  3. You may link to this article from another website, but ONLY if it is not wrapped in a frame. 
  4. You will abide by any additional copyright restrictions which the author may have placed in the article or article's description.
				Introduction
     Hello, my name's Geoff (aka Particle) and if you're here you're probably wondering how to get MySQL to work with Visual Studio.NET.  I had the same problem this morning and it took hours to find everything I needed.  Therefore, I thought I'd make a guide to save other people time.  If you're completely new to MySQL, please visit www.pcrpg.org/TRPGguides/mysqloledotnet.php for complete instructions on how to install and configure MySQL, useful tools such as phpMyAdmin, and everything needed to make phpMyAdmin work.  What I am posting here is just the last portion of the guide dealing with VS.NET itself.  I'll leave the "What You'll Need" table from the beginning of the guide in tact (except for the web components and installing MySQL, etc--for those please visit the link above).  Some of the links have been broken into two lines.  Sorry about the links--it appears that due to some immaturity, hyperlinks can no longer be used so I'll just bold them.
 
MyOLEDB Driver http://www.mysql.com/downloads/download.php?file=Downloads/
Win32/MyOLEDB3.exe&pick=mirror
Microsoft Visual Studio .NET http://msdn.microsoft.com/vstudio/
   
Other Stuff That's Nice to Have:  
.NET Framework 1.1 http://www.microsoft.com/downloads/details.aspx?FamilyID=
262d25e3-f589-4842-8157-034d1e7cf3a3&DisplayLang=en
.NET Framework SDK 1.1 http://www.microsoft.com/downloads/details.aspx?FamilyId=
9B3A2CA6-3647-4070-9F41-A333C6B9181D&displaylang=en
MDAC v2.7 http://www.microsoft.com/downloads/details.aspx?FamilyID=
9ad000f2-cae7-493d-b0f3-ae36c570ade8&DisplayLang=en

     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 were getting a message along the lines of "MySQLProv was not registered on localhost" then installing the MyOLEDB driver I linked to should fix that problem.

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.  (Information on using phpMyAdmin is available at www.pcrpg.org/TRPGguides/mysqloledotnet.php)

     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!


Other 3 submission(s) by this author

 


Report Bad Submission
Use this form to tell us if this entry should be deleted (i.e contains no code, is a virus, etc.).
This submission should be removed because:

Your Vote

What do you think of this article (in the Intermediate category)?
(The article with your highest vote will win this month's coding contest!)
Excellent  Good  Average  Below Average  Poor (See voting log ...)
 

Other User Comments

6/14/2003 9:33:59 PMBrian Clark

Well I have to say this is excellent! I use MySQL and PHP all the time and needed to make something in VB.NET similar to my PHP workings. Thanks and you get 5 globes from me :)
(If this comment was disrespectful, please report it.)

 
6/14/2003 10:13:59 PMParticle

Hey, thanks. Five hours on PSC and I already have 5 "excellent" marks and feedback! Thanks for the support guys!

(BTW, many say that OLE is faster than ODBC--even though it isn't supported officially by the guys at MySQL.)
(If this comment was disrespectful, please report it.)

 
6/15/2003 8:47:58 AM
Philip Pierce


great job. 5 globes
(If this comment was disrespectful, please report it.)

 
6/17/2003 6:13:05 PM

Exactly what i was looking for, excellent articl thanx!
(If this comment was disrespectful, please report it.)

 
6/17/2003 9:31:26 PM

you could have just posted the connection string that wouldve saved time for busy people reading your looooong article :)
(If this comment was disrespectful, please report it.)

 
6/17/2003 11:49:41 PMParticle

Heh, true! There are some people that might be new to OLEDB's altogether though. Also, if you thought this was long check out the full tutorial at pcrpg.org (link at the start of this article). =D
(If this comment was disrespectful, please report it.)

 
9/1/2003 4:05:05 AMSerkan GERAY

It's simple way. I think so . I am using this method also in VB 6.0
(If this comment was disrespectful, please report it.)

 
9/4/2003 1:17:22 PMJoseph Huntley

There is alsoa MySQL ADO.NEt provider which can be used just like Sql or OleDb, but is made specifically for MySql. Two providers are available for download at http://www.mysql.com/downloads/api-dotnet.html
(If this comment was disrespectful, please report it.)

 
9/12/2003 10:06:16 AM

You can also view MySQL tables in visual studio. Look at www.sevenobjects.com/mysql.aspx
(If this comment was disrespectful, please report it.)

 
10/11/2003 10:03:13 PM

An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll

Additional information: The 'MySQLProv' provider is not registered on the local machine.


highlighted line:

fdCom.Connection.Open()




What's wrong? Thanks

(If this comment was disrespectful, please report it.)

 
10/11/2003 11:26:39 PMParticle

You didn't install the MySQL Provider that I linked to. It is not optional--you have to install it for MySQLProv to work.
(If this comment was disrespectful, please report it.)

 
12/14/2003 8:53:53 PMaimtron

I get a DB_E_NOTABLE error when trying your code. The error occures at when you make the call to the OleDB.OleDBDataReader. I'm not really sure why this is, but any help would be appreciated.
(If this comment was disrespectful, please report it.)

 
12/30/2003 2:44:47 PM

SWEET!

Good code!
(If this comment was disrespectful, please report it.)

 
12/31/2003 10:09:19 AM

I had issues with the non-query commands for some reason. Anyways I started messing around with it and found that to execute a non-query all you really need is this chunk of code instead.
Dim fdCon As New OleDb.OleDbConnection("")

Dim fdCom As New OleDb.OleDbCommand("", fdCon)

fdCom.Connection.Open()

fdCom.CommandText = ""

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

fdCon.Close()

Anyways that's what worked for me.
(If this comment was disrespectful, please report it.)

 
12/31/2003 10:11:41 AM

Shoot. Sorry about that. Not thinking this morning.
I had issues with the non-query commands for some reason. Anyways I started messing around with it and found that to execute a non-query all you really need is this chunk of code instead.

Dim fdCon As New OleDb.OleDbConnection("")
Dim fdCom As New OleDb.OleDbCommand("", fdCon)
fdCom.Connection.Open()
fdC om.CommandText = ""
Dim fdRead As OleDb.OleDbDataReader = fdCom.ExecuteReader(CommandBehavior.Clos eConnection)
fdCon.Close()

Anyways that's what worked for me.
(If this comment was disrespectful, please report it.)

 
12/31/2003 10:13:28 AM

Dang it. Yes I'm a n00b at posting here.

Forgive my format.
(If this comment was disrespectful, please report it.)

 
1/30/2004 10:16:21 AM

I am using Myoledb 3.0 provider with MYSQL 4.0.17 (innodb tables). But I can not use transactions, I keep geeting error
(If this comment was disrespectful, please report it.)

 
1/30/2004 10:18:42 AM

I am using Myoledb 3.0 provider with MYSQL 4.0.17 (innodb tables). But I can not use transactions, I keep geeting error "The ITransactonLocal interface is not supported by the 'MySQLProv' provider. Local transactions are unavailable with the current provider." Please help
(If this comment was disrespectful, please report it.)

 
2/20/2004 6:50:55 PM

This article is very usuful.I have been looking or this for long time. Thank you
(If this comment was disrespectful, please report it.)

 
2/20/2004 6:52:36 PM

This article is very helpful. I have been looking for this for long time.Thank you
(If this comment was disrespectful, please report it.)

 
3/7/2004 12:57:37 PM

If you use Mysql and PHP, you might want to check out VS.Php. VS.Php is a plug in for Microsoft Visual Studio .Net 2003 that lets you build php applications within its environment.

Learn more here: http://www.jcxsoftware.com/vs.php

Jcx.Software Team
(If this comment was disrespectful, please report it.)

 
3/13/2004 6:24:37 AM

hi..i have a problem..i can't get the connection to work. the database is not on the local machine, so i've specified the location as the IP address of that machine in the pop-up box that comes regarding data source name and stuff. the error i get is
(If this comment was disrespectful, please report it.)

 
6/13/2004 7:55:44 PMBrian S. Smith

5 Globes from me, excellent tutorial.. saved me alot of time..

Thanks..
(If this comment was disrespectful, please report it.)

 
7/23/2004 11:43:44 AMEduardo Araujo

But How do I conect with a remote DB ?. I mean, where is it specified the server ??? Thnks, code is great.
(If this comment was disrespectful, please report it.)

 
7/24/2004 12:25:34 AMParticle

Public MySQLDatabase As String = "mydb"
Public MySQLServer As String = "xxx.xxx.xxx.xxx"
Public MySQLUser As String = "root"
Public MySQLPass As String = "pass"
Public MySQLConn As String = "Provider=MySQLProv;Location=" & MySQLServer & ";Data Source=" & MySQLDatabase & ";User Id=" & MySQLUser & ";Password=" & MySQLPass & ";"
(If this comment was disrespectful, please report it.)

 
8/5/2004 4:41:31 AM

Just like to ask this one.. Can i use a
(If this comment was disrespectful, please report it.)

 
8/5/2004 4:44:20 AM

Can i use a "server.mappath" thing with mySQL?? just like in access. I have a project which involves uploading only a mySQL file (e.g. upload products.mdb if new products are introduced and read the whole .mdb file), can i use this with mySQL?? help!! please email me at afrogeny@yahoo.com, a.s.a.p.. tnx..
(If this comment was disrespectful, please report it.)

 
10/15/2004 11:42:21 AM

Thank you! I've been trying to get OleDb support for my C# code for a while now. Your article is the first that has had all he pieces of the mystery in one place!
(If this comment was disrespectful, please report it.)

 
11/16/2004 2:01:14 AM

Provider=MySQLProv

- > Can this be something different on individual pc's ? i get an error cause of it../
(If this comment was disrespectful, please report it.)

 
11/16/2004 2:31:27 AM

opps.. Maybe i was using the wrong driver ;-)
Incase anyone else is as NEW as me i was using ODBC driver and not OLEDB Driver. Gulp
(If this comment was disrespectful, please report it.)

 
1/6/2005 4:55:39 AM

ive got a problem.. im currently using asp.net web matrix for my project and i need to connect mysql database to connect to it.. n i used your solution but i received this error: No error information available: DB_E_NOCOMMAND0x80040E0C. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.OleDb.OleDbException: No error information available: DB_E_NOCOMMAND0x80040E0C.
(If this comment was disrespectful, please report it.)

 
1/6/2005 4:57:41 AM

i've got a problem.. i'm currently using asp.net web matrix for my project and i need to connect mysql database to connect to it.. n i used your solution but i received this error:
No error information available: DB_E_NOCOMMAND(0x80040E0C).
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: No error information available: DB_E_NOCOMMAND(0x80040E0C).
(If this comment was disrespectful, please report it.)

 
1/6/2005 8:41:49 PM

The error line is: Dim fdRead As OleDb.OleDbDataReader = fdCom.ExecuteReader(CommandBehavior.CloseConnection)
(If this comment was disrespectful, please report it.)

 
1/10/2005 8:33:18 PM

i've managed to connect mysql to asp.net web matrix but i cant edit or delete information from the asp.net.. can help me plz..
(If this comment was disrespectful, please report it.)

 
1/17/2005 9:15:51 PM

hi! juz wanna letr u know tat i'm able to delete a record from the database but i cannot update it
(If this comment was disrespectful, please report it.)

 
1/21/2005 6:24:43 PM

its working !! thanks alot dude ...
(If this comment was disrespectful, please report it.)

 
1/23/2005 8:34:05 PM

commandbehaviour is not defined...

im fdRead As System.Data.OleDb.OleDbDataReader = fdCom.ExecuteReader(CommandBehavior.CloseConnection)

any ideas?

(If this comment was disrespectful, please report it.)

 
1/23/2005 8:36:38 PM

Errors at:
Dim fdRead As System.Data.OleDb.OleDbDataReader = fdCom.ExecuteReader(System.Data.CommandBehavior.CloseConnection)

Can you please help this is urgent!
(If this comment was disrespectful, please report it.)

 
1/24/2005 7:53:42 AMParticle

Can you please provide a current email address? I tried emailing you but it bounced.
(If this comment was disrespectful, please report it.)

 
2/1/2005 8:55:55 PM

Hi, i recently implement your solution, but i have a problem when the program gets to this point:
fdCom.Connection.Open()
reaching this point, a window from the OLEDB opens and ask for parameters... what should i put here???
(If this comment was disrespectful, please report it.)

 
2/1/2005 8:56:33 PM

Hi, i recently implement your solution, but i have a problem when the program gets to this point:
fdCom.Connection.Open()
reaching this point, a window from the OLEDB opens and ask for parameters... what should i put here???

thanks
(If this comment was disrespectful, please report it.)

 
4/19/2005 4:04:21 PM

I know this is a little old.. but I am having a problem that I am hoping you can help me with. I have everything up and running fine, but when I try and connect I get a prompt that says "MySQL Data Source Name Setup" I have your code pasted exactly as it is into my app, with the appropriate username, password, etc. the credentials work, I'm sure of it. What else causes this? Thanks.
(If this comment was disrespectful, please report it.)

 
4/20/2005 5:31:45 PMParticle

The new versions of MySQL use a different hash to store passwords. As the MySQLProv that I use is an older client and no longer updated, the only two real choices at the moment are to a) stick with an older MySQL server (v 4.something) or b) move to a non-oledb provider. The first option won't be viable soon, as updates are essential to a network connectable server of any sort. The second option is readily doable, moving to a pure connection through Connector/NET at http://dev.mysql.com/downloads/connector/net/1.0.html. It is faster and supports the latest connection method. However, coding with it (while similar) is a little different in respect of setting up a program to use it.
(If this comment was disrespectful, please report it.)

 
4/20/2005 5:33:07 PMParticle

Basically, all you need to do to run the above provider (faster, easy, and updated) is add a reference to the dll in your program and then use the MySql.Data.MySqlClient namespace to do your MySQL stuff.

Have fun and good luck with your programs.
(If this comment was disrespectful, please report it.)

 
7/26/2005 6:25:21 AMOLI

Help! I' using .NET 2005 and can connect to the database fine. But when I try and run ANY query, I get the following error:
'MySqlProv.3.0 failed with no error message available, result code: DB_E_ERRORSINCOMMAND(0x80040E14).'
After executing the SQL statement. What's going on???
(If this comment was disrespectful, please report it.)

 
8/30/2005 5:16:21 PMKen

Hi, when I tried your routing for accessing an mysql-database by VB.net, I got the following error message:
An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll
Additional information: No error information available: DB_E_ERRORSINCOMMAND(0x80040E14).
Can you see i what way I made an error?
Regards,
Ken
Kenneth.Lundkvist@comhem.se

(If this comment was disrespectful, please report it.)

 
9/26/2005 1:01:48 PMKevin R Hurst

I am using the .net data provider from MySQL with vb.net. On the MySQL server (Windows 2003) every connection that gets opened stays opened as a sleeping thread even though I am using the connection.close & connection.dispose commands in vb.net. This has lead to having a "too many connections" error on occasion. The threads eventually close but give me a "Aborted connection"/"(Got an error reading communication packets)
" error for each connection. How do I prevent the connections from persisting? There really is not anything specifically on this that I could find via Google or the MySQL site.
(If this comment was disrespectful, please report it.)

 
2/27/2006 3:53:13 PMAndrew

Nice article for connecting to MySQL, but I do not like to place my queries in the code. And since MySQL 4.0 does not support stored procedures, do you have a way to place these queries in a seperate file and execute them from there after adding parameters.
Thank you in advance.
(If this comment was disrespectful, please report it.)

 
2/28/2006 1:15:51 AMParticle

I wrote a way to do this for you at this page: http://www.pcrpg.org/TRPGguides/simulatestoredprocs.htm
(If this comment was disrespectful, please report it.)

 
8/4/2006 11:35:15 PMivy

the code for inserting data into the database really helps me a lot. thx. i also try to do the delete coding using the codes you posted but i keeping having error can you please help me with it? thx.
(If this comment was disrespectful, please report it.)

 
11/6/2006 3:49:48 PMFatos

Hi guys!!!
I have a problem i can not connect vb.net 2003 with MySQL i am trying to use ODBC but i cant figure it out how, please if you the code please post it or email it to me needed really urgent, tosi_uk@yahoo.co.uk. I also tried to connect with MyOleDB but i can not make it work i get some errors.

thanks

tosi
(If this comment was disrespectful, please report it.)

 
3/20/2007 4:40:57 AMok

ok
(If this comment was disrespectful, please report it.)

 
4/9/2007 3:27:51 PMDaniel

Hi,
Would this code work with MySQL version 5? I've tried the code but I get an error - result code: E_FAIL(0x80004005). Any ideas?
(If this comment was disrespectful, please report it.)

 
6/6/2007 1:22:36 AMM Sopian H

I have this problem DB_E_OBJECTCREATIONLIMITREACHED at queryComA.ExecuteReader(CommandBehavior.CloseConnection). Read on the net about the ERROR code, it means = "The maximum number of Sessions supported by the provider has already been created. The consumer must release one or more currently held Sessions before obtaining a new Session Object." Can anyone suggest to me how to solve this issue. I am suspecting the close() connection management, but, still looking around.

Thanks.
M Sopian H
(If this comment was disrespectful, please report it.)

 
10/16/2012 9:20:15 AMSayfuddin

Hi! Odbc is better to Mysql than Oledb. I have tested it many times when wanted to create my application to send SMS messages from Mysql or Mssql (with a gateway as engine). I used the following connection string (google: ozekisms.com/index.php?ow_page_number=168) This great article can help all of us to create great applications.

Tx

Boris
(If this comment was disrespectful, please report it.)

 

Add Your Feedback
Your feedback will be posted below and an email sent to the author. Please remember that the author was kind enough to share this with you, so any criticisms must be stated politely, or they will be deleted. (For feedback not related to this particular article, please click here instead.)
 

To post feedback, first please login.