[RESOLVED] ODBC Connection to local MySQL DB throws "Datasource Name not Found"

Hi Everyone

I'm busy working on an ASP.NET Web Application (3.5) and can't seem to connect to a MySQL DB. I first created the entire application using a WinForms template and everything works 100%, but as soon as I move the exact same code to the Web Application, I can't connect anymore. Here's the code:

Code:

---

    public class DBC
    {
        OdbcConnection connection;
        public bool CreateConnection()
        {
            try
            {
                string connFormat = "Driver={MySQL ODBC 5.1 Driver};Server=[SERVER];Database=[DBNAME];User=[UID];Password=[PWD];";
                connFormat = connFormat.Replace("[SERVER]", Properties.Settings.Default.DBServer);
                connFormat = connFormat.Replace("[DBNAME]", Properties.Settings.Default.DBName);
                connFormat = connFormat.Replace("[UID]", Properties.Settings.Default.DBUser);
                connFormat = connFormat.Replace("[PWD]", Properties.Settings.Default.DBPassword);
                connection = new OdbcConnection(connFormat);
                connection.Open();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

---

As soon as I try to create the new OdbcConnection instance, I get the error:

Code:

---

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

---

As noted this works in a WinForms app, and even more strange is that I don't even specify a DSN name...

All I could find from reading through forums all over the internet is suggestions of downgrading the MySQL ODBC driver version, which I'd really rather not...

Any ideas?

Try this one

Firstly I would suggest that you use Connector/Net rather than ODBC. If you do that then issue may well go away because you're not using ODBC. Secondly, I would suggest using the appropriate connection string builder for your provider, e.g. OdbcConnectionStringBuilder, to set connection string fields.

@danasegarane:
I'm not using a DSN. I've also seen these articles everywhere where they explain that you need to create a DSN first, but I don't use one...

@jmcilhinney:
I'm hesitant to use the Connector/Net DLL as I'm going with a shared windows-hosting solution and I don't know if they'll have this installed or not. (Though I suppose the same could be said about the MySQL ODBC Driver...) I also think the hosting will be on MSSQL as opposed to MySQL. Irrespective, I believe that building with ODBC just makes it "so much easier" to connect to a variety of DB types. When you get a different DB, just change the connection string and there you go :) (I can already see hordes of developers grabbing at their hair about my "pro-ODBC" statement! :p)

Anyway, my app will be written in such a way that, should I use another DLL or way to connect to the DB, I just need to rewrite parts of a single DB-connection class, so I might just change to the more "MySQL Native" strategy once I can confirm what the hosting environment will look like.

I did manage to resolve this issue though. Turns out that for some reason, the local webserver built into VS when debugging was looking for the 32-bit ODBC driver and I only installed the 64-bit one. Might be useful for others who experience this problem in future. ;)

That sounds like a perfectly good reason to use ODBC to me. I think that you should be able to deploy the Connector/Net DLL with your app, so it would work even if the host hasn't installed it. Never tried myself though.

If you can upgrade to VS 2010 or later then you can also use IIS Express, which runs as a 64-bit process on 64-bit systems. The Cassini web server built into VS is fairly basic and, as VS is a 32-bit app, runs as a 32-bit process.

Early web server build on VS could be basic but the damn IIS Express with VS2012 is hanging on me right now for about 3 minutes....Ahh after 4 minutes i got the page to open. Suupeeer!!