How To Hand Code an ADO Data Connection in ASP

Article translations Article translations
Article ID: 299980 - View products that this article applies to.
This article was previously published under Q299980
Expand all | Collapse all

On This Page

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.

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.

  1. From the Windows Start menu, point to Programs, point to Accessories, and then click Notepad.
  2. 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>
    					
  3. 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.
  4. 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;"
    						
    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
    					

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
    					
    For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
    176056 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 PRB: Problems Reusing ADO Command Object on Multiple Recordsets

References

  For more information about Microsoft Data Access Components (MDAC) 2.6 Software Development Kit (SDK), see the following Microsoft Web site:
http://msdn.microsoft.com/library/default.asp?URL=/library/psdk/dasdk/mdac3sc7.htm



Properties

Article ID: 299980 - Last Review: October 12, 2012 - Revision: 4.0
Applies to
  • Microsoft Active Server Pages 4.0
Keywords: 
kbaspobj kbcodesnippet kbdatabase kbhowto kbhowtomaster kbscript KB299980
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com