Can't connect to MySQL database using C#.NET

I just downloaded and installed MySQL, using default for everything.  The command line program seems to be working fine, but I can't get the connection to work from a C#.NET Windows application.  I have Visual Studio 2008, and I'm making my connection with an OdbcConnection.

The MySQL version I'm running is 5.0.45.  I have three databases; information-scheme, mysql and test.  When I query the current user, it says I'm root@localhost.

First I tried making the connection using a web service, which is the way I normally like to code.  Here is the code, pretty simple:
      OdbcConnection  conn = new OdbcConnection(pConnection);
      conn.Open();

These are the connection strings that I tried:

string connection = "Driver={MySQL ODBC 5.0 Driver};Server=localhost;Database=;User=root@localhost;Password=pwd;Option=3;";

string connection1 = "Driver={mySQL};Server=localhost;Option=16834;Database=;User=root@localhost;Password=pwd;";

string connection2 = "Driver={mySQL};Server=localhost;Option=16834;Database=mysql;User=root@localhost;Password=pwd;";

string connection3 = "Driver={MySQL ODBC 5.0 Driver};Server=localhost;Database=mysql;User=root@localhost;Password=pwd;Option=3;";

string connection4 = "Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=test;User=root@localhost;Password=pwd;Option=3;";

string connection5 = "Driver={MySQL ODBC 5.0 Driver};Server=localhost;Database=test;User=root;Password=sk1nner;Option=3;";

...but this is the error that I keep getting:

"ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified"

I installed both the 3.51 and 5.0 version of the MySQL ODBC drivers.  Nothing changed.


Then I tried opening the connection directly from the form.  Same error, with the single exception that connection4 gave me:

"ERROR [HY000] [MySQL][ODBC 3.51 Driver]Access denied for user 'root@localhost'@'localhost' (using password: YES)\r\nERROR [HY000] [MySQL][ODBC 3.51 Driver]Access denied for user 'root@localhost'@'localhost' (using password: YES)"

Any ideas as to what I need to do to get this working?
LVL 1
FrancineTaylorAsked:
Who is Participating?
 
oobaylyCommented:
First, I'd highly recomment using the MySql Connector for .Net, it's far more powerful & efficient than odbc (I've also just realised v.6 is out, I'll have to try it):
http://dev.mysql.com/downloads/connector/net/6.0.html

As for the connectionstring, #4 looks correct and the fact you get a MySql error rather than an ODBC error tells me it's working. Just that the user credentials are incorrect, the username should be just "root", not "root@localhost"
0
 
FrancineTaylorAuthor Commented:
Thanks!  I changed root@localhost to root on connector #4 and it worked.  I wondered why #5 didn't work, and I realized I should have specified 5.1 instead of 5.0 for the driver.  So here are the two connectors that worked for me:

"Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=test;User=root@localhost;Password=pwd;Option=3;";

"Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=test;User=root;Password=pwd;Option=3;";

I'll try it with the MySql connector next..thanks for helping me get this working!
0
 
FrancineTaylorAuthor Commented:
I appreciate the help!  Thanks...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.