Overview
To query a MySQL database using Classic ASP you connect through one of the MySQL ODBC drivers installed on our system. There is no DSN to set up; you name the driver directly in the connection string.
Before you start
Classic ASP is not enabled by default. If it has not been enabled for the site, an ASP page returns HTTP Error 404.3 - Not Found rather than running. See How to enable Classic ASP.
You will also need the connection details for your database. In the Everleap Control Panel, click Databases on the top menu and then Shared MySQL to find the server name, database name and user name.
Available drivers
Use one of the MySQL ODBC drivers installed on our system:
- MySQL ODBC 5.3 Unicode Driver
- MySQL ODBC 5.3 ANSI Driver
Use the Unicode driver unless you have a specific reason not to. It handles multi-byte character sets correctly, which the ANSI driver does not.
The driver name must be typed exactly as shown. If it is misspelled, or if you name a driver version that is not installed, the connection fails with "Data source name not found and no default driver specified", which reads like a database problem but is really a driver name problem.
Example connection string and query
Example connection string using Classic ASP to call a MySQL database:
<%
Dim cnnSimple ' ADO connection
Dim rstSimple ' ADO recordset
Set cnnSimple = Server.CreateObject("ADODB.Connection")
' DSN
cnnSimple.Open "DRIVER={MySQL ODBC 5.3 Unicode Driver};SERVER=[mySQL server];
DATABASE=[DBName];UID=[DBUser];PASSWORD=[DB Password];"
Set rstSimple = cnnSimple.Execute("SELECT * FROM tblTest")
%>
<P>Connecting to mySQL DB</P>
<table border="1">
<%
Do While Not rstSimple.EOF
%>
<tr>
<td><%= rstSimple.Fields("id").Value %></td>
<td><%= rstSimple.Fields("name").Value %></td>
</tr>
<%
rstSimple.MoveNext
Loop
%>
</table>
<%
' Close our recordset and connection and dispose of the objects
rstSimple.Close
Set rstSimple = Nothing
cnnSimple.Close
Set cnnSimple = Nothing
cnnSimple.close
%>Replace [mySQL server], [DBName], [DBUser] and [DB Password] with the values for your own database, and replace tblTest, id and name with a table and columns that exist in it.
Verify it worked
Upload the page and open it in a browser. You should see "Connecting to mySQL DB" followed by a table of rows. If you get a blank page or an error instead:
- HTTP Error 404.3 means Classic ASP is not enabled for the site.
- Data source name not found and no default driver specified means the driver name in the connection string does not match one of the two listed above.
- Any other ODBC error is usually the server name, database name, user name or password.
Related Articles