MySQL ODBC connections (again)

0
votes

I know this question has been asked many times but my variation is slightly different and I'm pulling my hair out after spending several hours trying to get one line of code to work. I have a MySQL database and am trying to connect using ODBC. I'm on Windows 10 version 1909. 

Line 13: OpenString ="DRIVER={MySQL ODBC 8.0 ANSI Driver};SERVER=localhost;DATABASE=collections;UID=edited_out;PWD=edited_out;"
Line 14: Conn = Server.Createobject("ADODB.Connection")
Line 15: Conn.open = OpenString

The code quoted works fine on my main PC with Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.8.4075.0 and DRIVER={MySQL ODBC 5.3 ANSI Driver}. On the test PC with an almost identical configuration, the exact same code with the exception of the driver name fails. (Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.8.4075.0 and DRIVER={MySQL ODBC 8.0 ANSI Driver} or, for that matter, DRIVER={MySQL ODBC 8.0 Unicode Driver} - both installed.) Please see the attached image for evidence. I get the well-known and depressing "Data source name not found and no default driver specified". Code snippet and drivers

I realise that this error is almost always due to getting the driver name wrong. However, I've checked and rechecked my syntax and the driver installation. I know there's nothing obviously wrong with my ODBC installation since I installed LibreOffice purely for testing and was able to connect to the same database via ODBC within seconds on the same PC using the same driver shown here. Is there something different or particularly troublesome about the ODBC version 8.0 drivers? Do I need to try to downgrade this one to 5.3?

Thanks for any help.

1
mysqlasp.netvb.netodbc
Why using ODBC? There's a MySQL client for .NET available. – mason
Thanks Mason. I guess I'll give that a try if I can't find a fix for this problem, but I've written quite a lot of code and would prefer to transfer it rather than start again. – TyneBridges
It wouldn't be "start again". It's still ADO.NET. For the most part, it's as easy as updating a new namespaces and switching from OdbcConnection to MySqlConnection or whatever the names are. Probably take you less than a minute to verify it works, and another 10 minutes to update your project. – mason
Thanks again. Will have a look. I've been hand coding individual pages rather than using Visual Studio and it's probably time I learned to use that. – TyneBridges
So you're saying that code containing OpenString ="DRIVER={MySQL ODBC 8.0 ANSI Driver} works fine on a PC with driver 5.3 installed, but not on a PC with driver 8.0 installed? – Caius Jard

1 Answers

0
votes

FIX: Disable 32 bit applications for your site if working with 64 bit!

(Answering my own question in the hope that it helps somebody.) I figured it out. The connection string was a red herring. Further detective work led me to a post that advised checking application pools in IIS. The advice was to enable 32 bit applications under Advanced Settings for your site if you have any 32 bit applications, which is fairly obvious.

However, my application is 64 bit and I noticed that this option was DISABLED on my working PC. Disabling it on the other PC restored the connection: evidently Windows was defaulting to a nonexistent 32 bit driver and showing an ambiguous error message when this failed. Software writers seem to dig a lot of these holes for users to fall into...