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

Posted on 2009-04-23
Last Modified: 2012-08-14
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);

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?
Question by:FrancineTaylor
    LVL 15

    Accepted Solution

    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):

    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"
    LVL 1

    Author Comment

    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!
    LVL 1

    Author Closing Comment

    I appreciate the help!  Thanks...

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now