Open and Close Methods Example (VBScript)

This example uses the Open and Close methods on both Recordset and Connection objects that have been opened.

Use the following example in an Active Server Page (ASP). To view this fully functional example, you must have the data source AdvWorks.mdb (installed with the SDK) located at C:\mssdk\samples\dataaccess\rds. This is a Microsoft Access database file.

Use Find to locate the file Adovbs.inc and place it in the directory you plan to use. Cut and paste the following code into Notepad or another text editor, and save it as MoveOne.asp. You can view the result in any browser.

<%@ Language=VBScript %>
<!-- #Include file="ADOVBS.INC" -->
<HTML>
<HEAD>
<TITLE>ADO Open Method</TITLE>
<STYLE>
<!--
BODY {
   font-family: "MS SANS SERIF",sans-serif;
    }
.thead {
   background-color: #008080; 
   font-family: 'Arial Narrow','Arial',sans-serif; 
   font-size: x-small;
   color: white;
   }
.thead2 {
   background-color: #800000; 
   font-family: 'Arial Narrow','Arial',sans-serif; 
   font-size: x-small;
   color: white;
   }
.tbody { 
   text-align: center;
   background-color: #f7efde;
   font-family: 'Arial Narrow','Arial',sans-serif; 
   font-size: x-small;
    }
-->
</STYLE>
</HEAD>
<BODY> 
<H3>ADO Open Method</H3>

<TABLE WIDTH=600 BORDER=0>
<TR>
<TD VALIGN=TOP COLSPAN=3>
<FONT SIZE=2>
<!--- ADO Connection used to create 2 recordsets-->
<%
src = "C:\mssdk\samples\dataaccess\rds\advworks.mdb"
sConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & src

'Create and Open Connection Object
Set OBJdbConn = Server.CreateObject("ADODB.Connection") 
OBJdbConn.Open  sConnStr
SQLQuery = "SELECT * FROM Customers" 

'First Recordset RsCustomerList
Set RsCustomerList = OBJdbConn.Execute(SQLQuery) 

'Second Recordset RsProductist
Set RsProductList = Server.CreateObject("ADODB.Recordset")
RsProductList.CursorType = adOpenDynamic
RsProductList.LockType = adLockOptimistic
RsProductList.Open "Products", OBJdbConn 
%>

<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>E-mail address</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("ContactLastName") & ", " %> 
       <%= RScustomerList("ContactFirstName") %> </TD>
  <TD> <%= RScustomerList("ContactLastName")%> </TD>
  <TD> <%= RScustomerList("City")%> </TD>
  <TD> <%= RScustomerList("StateOrProvince")%> </TD>
</TR> 
<!-Next Row = Record Loop and add to html table-->
<% 
   RScustomerList.MoveNext 
Loop 
RScustomerList.Close
%>
</TABLE>

<HR>

<TABLE COLSPAN=8 CELLPADDING=5 BORDER=0>
<!-- BEGIN column header row for Product List Table-->

<TR CLASS=thead2>
   <TD>Product Type</TD>
   <TD>Product Name</TD>
   <TD>Product Description</TD>
   <TD>Unit Price</TD>
</TR>
<!-- Display ADO Data Product List-->
<% Do While Not RsProductList.EOF %>
  <TR CLASS=tbody>  
  <TD> <%= RsProductList("ProductType")%> </TD>
  <TD> <%= RsProductList("ProductName")%> </TD>
  <TD> <%= RsProductList("ProductDescription")%> </TD>
  <TD> <%= RsProductList("UnitPrice")%> </TD>
  </TR>
  <!--  Next Row = Record -->
<% 
  RsProductList.MoveNext 
Loop 
OBJdbConn.Close

'Remove Objects from Memory Freeing  
Set RsProductList = Nothing
Set OBJdbConn = Nothing
%>
</TABLE>
</BODY>
</HTML>