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.
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.
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.
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;"
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")
Troubleshooting
When you use ADO in ASP, make sure that you:
- Always close recordsets and connections. For example:
rs.close
set rs=nothing
conn.close
set conn=nothing
176056
(http://support.microsoft.com/kb/176056/EN-US/
)
INFO: ADO/ASP Scalability FAQ
- Open late, and close early. Open ADO objects just before
they are needed, and close them right after you are done. This frees resources
while other logic is processing.
- Do not create ADO objects in session variables. This
effectively bypasses MTX connection and thread pooling. If threads are not
pooled, each object that is created for each user can tie up a thread. Also, if
the object is not specifically closed, a session object variable can live and
tie up a thread for the life of a session (the default is 20 minutes after last
click).
- Do not pass parameters to the Command object in the execute statement.
- Instantiate objects with the Server.CreateObject method if you are not marshalling data through a firewall. Server tells Microsoft Transaction Server (MTS) to create the object in
a transaction server package so that resources are pooled.
NOTE: This does not apply to Microsoft Internet Information Services
(IIS) 5.0 or Microsoft COM+. - Do not re-use Recordset or Command variables; create new ones.For
additional information, click the article number below to view the article in
the Microsoft Knowledge Base:
197449
(http://support.microsoft.com/kb/197449/EN-US/
)
PRB: Problems Reusing ADO Command Object on Multiple Recordsets
For ADO product information, see the following Microsoft
Web site:
For more information about Microsoft Data Access Components
(MDAC) 2.6 Software Development Kit (SDK), see the following Microsoft Web
site:
Back to the top
