zeWEBHOST Knowledge Base - MySQL Databases Connection Strings in ASP

zeWEBHOSTAffordable Web Hosting Services Beyond Your Expectations

Home Web Hosting Guide Compare Hosting Plans Basic Hosting Plan Professional Hosting Plan Extreme Hosting Plan Email Only Hosting Web Forwarding Advanced Features Hosting Control Panel SSL Certificates Hosting Order Page

WISDOM

The Essence of Knowledge is Having it to Apply it.

Confocious

Knowledge Base IP Location Contact Us Login Order Free Toolbar
KNOWLEDGE BASE / CONNECTION STRINGS / CONNECTING TO DATABASES IN ASP

The Full Script

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 = "mysqlX.hcpanel.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>
<%
'zeWEBHOST 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 = "mysqlX.hcpanel.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--

To Top