SUMMARY
This step-by-step procedure demonstrates how to hand code
an ActiveX Data Objects (ADO) data connection in an Active Server Pages (ASP)
page.
For the purposes of this article, the sample to follow uses the
Northwind sample database and establishes an ADO connection to both Microsoft
Access and Microsoft SQL Server 7.0 or 2000.
back to the top
The Connection String
The connection string is simply a set of string values that
contain your database connection information. For example, the following string
points to the Access database:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Mydatabase\Northwind.mdb
where "C:\Mydatabase\Northwind.mdb" represents the source path, which
you should modify to reflect the correct path information to the Northwind
database.
The following string points to SQL Server:
Provider=SQLOLEDB;Server=Your_Server_Name;Database=Northwind; & _
UID=your_user_account;PWD=your_password;
where you should modify the
Server,
UID, and
PWD parameters to reflect the correct server name, user name, and
password respectively for the database.
back to the top
Putting the Connection String to Work
This section presents sample code to get a better idea of how
this works.
- From the Windows Start menu, point to Programs, point to Accessories, and then click Notepad.
- Highlight the following code, right-click the code, and
then click Copy. In Notepad, click Paste on the Edit menu to paste the following code into Notepad:
<HTML>
<HEAD>
<title>ADO Open Method</title>
</HEAD>
<BODY>
<H3>ADO Open Method</H3>
<TABLE WIDTH=600 BORDER=0>
<TR>
<TD VALIGN=TOP COLSPAN=3>
<FONT SIZE=2>
<%
' Remember to change the path to Northwind.mdb
' to match the location on your system.
src = "C:\Database\Northwind.mdb"
sConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & src
' Uncomment the next two lines and comment the above line to switch between Access and SQL.
'sConnStr = "Provider=SQLOLEDB;Server=Your_Server_Name;Database=Northwind;" & _
'"UID=your_user_account;PWD=your_password;"
'Create and open the Connection object.
Set OBJdbConn = Server.CreateObject("ADODB.Connection")
OBJdbConn.Open sConnStr
SQLQuery = "SELECT * FROM Customers"
'First Recordset RsCustomerList
Set RsCustomerList = OBJdbConn.Execute(SQLQuery)
%>
<TABLE COLSPAN=8 CELLPADDING=5 BORDER=0>
<!-- BEGIN column header row for Customer Table-->
<TR CLASS=thead>
<TD>Company Name</TD>
<TD>Contact Name</TD>
<TD>City</TD>
<TD>State/Province</TD>
</TR>
<!--Display ADO Data from Customer Table-->
<% Do While Not RScustomerList.EOF %>
<TR CLASS=tbody>
<TD> <%= RSCustomerList("CompanyName")%> </TD>
<TD> <%= RScustomerList("ContactName")%></TD>
<TD> <%= RScustomerList("City")%> </TD>
<TD> <%= RScustomerList("Region")%> </TD>
</TR>
<!--Next Row = Record Loop and add to html table-->
<%
RScustomerList.MoveNext
Loop
RScustomerList.Close
Set RScustomerList = Nothing
OBJdbConn.Close
Set OBJdbConn = Nothing
%>
</TABLE>
</BODY>
</HTML>
- In Notepad, click Save on the File menu. Save the document with the file name Adopage.asp to the
default Web site on your server, which is typically located at
C:\InetPub\Wwwroot.
- To view the page, start your Web browser, and type the HTTP
location of the page in the Address bar. If you saved the file in the
above-mentioned location, type http://<servername>/Adopage.asp in the Address bar.
back to the top
Code Explanation
- In the preceding sample code, note the following code:
src = "C:\Database\Northwind.mdb"
sConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & src
' Uncomment the next two lines and comment the above line to switch between Access and SQL.
'sConnStr = "Provider=SQLOLEDB;Server=Your_Server_Name;Database=Northwind;" & _
'"UID=your_user_account;PWD=your_password;"
This code is currently set to connect to an Access database. To connect
to a SQL Server database, comment out the line for the Access provider and
uncomment the SQL Server lines. To comment out a line, insert an apostrophe (')
at the beginning of the line. To uncomment a line, remove the apostrophe from
the beginning of the line. The revised code appears as follows:
src = "C:\Database\Northwind.mdb"
'sConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & src
' Uncomment the next two lines and comment the above line to switch between Access and SQL.
sConnStr = "Provider=SQLOLEDB;Server=Your_Server_Name;Database=Northwind;" & _
"UID=your_user_account;PWD=your_password;"
- In the following line of code, you actually begin the
process of creating the ADO connection:
Set OBJdbConn = Server.CreateObject("ADODB.Connection")
Then, you execute the Open command and pass your connection string as follows:
OBJdbConn.Open sConnStr
back to the top
Troubleshooting
When you use ADO in ASP, make sure that you:
back to the top