Programming Articles

Connecting to MySQL in ASP without a DSN

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

Dim objConn, rsRecord, strSQL, tableName, address, username, password, 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.

address = "mysql1.easycgi.com" 'The server address
username = "testuser" 'The username for the server
password = "testpass" 'The password for the server
database = "testdatabase" 'The name of the database
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 "driver={MySQL ODBC 3.51 Driver};server="&address&";uid="&username&";pwd="&password&";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-dsnless.asp--

<%@ LANGUAGE="VBSCRIPT" %>

<HTML>
<HEAD>

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

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

'Some Variables
Dim objConn, rsRecord, strSQL, tableName, address, username, password, database
Set objConn = Server.CreateObject("ADODB.Connection")

'Change these to point to your database
address = "mysql1.easycgi.com" 'The server address
username = "testuser" 'The username for the server
password = "testpass" 'The password for the server
database = "testdatabase" 'The name of the database
tableName = "testtable" 'The table in the database to display

'Connect to database with DSN
objConn.Open "driver={MySQL ODBC 3.51 Driver};server="&address&";uid="&username&";pwd="&password&";database="&database

'Check error
Err.clear
if (Err <> 0) then
     Response.Write "Error connecting to server: " & address
else
     Response.Write "Connected to server: " & address
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>

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

Back