How to make MySQL work with ASP using the ODBC connection

When enabling classic mode under any .NET version (whether you are using .NET or not) if you enable classic mode your application runs in a worker application pool that is in a 32 bit environment. To fix this we need to disable 32 bit support.

How to fix the issue

Within your hosting control panel, click on the domain you are using, Web services, then the edit button next to ASPNET. You will then need to untick “enable 32-bit applications support” and submit. Please allow upto 15 minutes for this to apply.

You can then use the following code to check that your connection is working:

<%@ LANGUAGE="VBSCRIPT" %>

<HTML>
<HEAD>

<TITLE>Connecting to a MySQL database with a DSN in ASP</TITLE>
</HEAD>
<BODY>

<%
'Easy CGI Test script for connecting to a MySQL database in ASP with a DSN
'This script will connect to the database and output the results in an HTML table

'Some Variables
Dim objConn, rsRecord, strSQL, tableName, dsnName, dsnUser, dsnPass, database
Set objConn = Server.CreateObject("ADODB.Connection")

'Change these to point to your database
dsnName = "testdsn" 'The name of the DSN
dsnUser = "testuser" 'The username for the DSN
dsnPass = "testPass" 'The password for the DSN
database = "testdatabase" 'The database to use
tableName = "testtable" 'The table in the database to display

'Connect to database with DSN
objConn.Open "DSN="&dsnName&";UID="&dsnUser&";PWD="&dnsPass&";DATABASE="&database

'Check error
Err.clear
if (Err <> 0) then
Response.Write "Error connecting to DSN: " & dsnName
else
Response.Write "Connected to DSN: " & dsnName
end if

'Create a recordset for a query
strSQL = "SELECT * FROM " & tableName

Set rsRecord = objConn.Execute(strSQL)

'write out a table in html to display the table
Response.Write "<TABLE BORDER=1>"

'Write out Field Names
Response.Write "<TR>"
For i=0 to rsRecord.fields.count-1
Response.Write "<TH>"+rsRecord(i).Name+"</TH>"
Next
Response.Write "</TR>"

'Write out Data
Do while not rsRecord.eof
Response.Write "<TR>"
For i=0 to rsRecord.fields.count-1
Response.Write "<TD>"
Response.Write rsRecord(i)
Response.Write "</TD>"
Next
Response.Write "</TR>"
rsRecord.movenext
Loop

Response.Write "</TABLE>"

'Reset server objects
rsRecord.Close
objConn.Close
Set rsRecord = Nothing
Set objConn = Nothing

%>

</body>
</html>
</SPAN>

More information on how to create a ODBC DNS connection.

Classification: Public
Last saved: 2021/11/11 at 14:23 by Jamie

Legacy

How to fix the issue

How can we help?