Programming Articles

Connecting to MySQL in ASP with a DSN

This script demonstrates connecting to a MySQL database in ASP with a DSN. The script will make the connection and then output all the rows of a specified table.

Dim objConn, rsRecord, strSQL, tableName, dsnName, dsnUser, dsnPass, database
Define the variables we will use in this script.

Set objConn = Server.CreateObject("ADODB.Connection")
Create the connection object. We will use ADO for the connection.

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
Sets the parameters to connect to the database, these should be changed to the values appropriate for your database.

objConn.Open "DSN="&dsnName&";UID="&dsnUser&";Password="&dsnPass&";DATABASE="&database
Opens the connection to the database.

The next lines check if an error occurred in the connection; if it did then the error is displayed, otherwise a success message is displayed.

strSQL = "SELECT * FROM " & tableName
Create the SQL string which will select all of the rows from the table that was specified.

Set rsRecord = objConn.Execute(strSQL)
Here we create a recordset from the results of the database query.

Response.Write "<TABLE BORDER=1>"
The results of the query will be put into an HTML table.

'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>"
This code will loop through each of the fields and displays the names in the first row of the table.

'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
This code will loop through each row in the table and print out the values in a row in the HTML table.

rsRecord.Close
objConn.Close
Set rsRecord = Nothing
Set objConn = Nothing
Closes the database connection and unassigns the variables.

--Here is the full script, the filename should be: mysql-dsn.asp--

<%@ 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&";Password="&dsnPass&";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>

--End Script--
1/5/2008 1:50:52 AM Category Database Connection Strings Comments 0

Back