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 either have the data source AdvWorks.mdb (installed with the SDK) located at C:\Program Files\Microsoft Platform SDK\Samples\DataAccess\Rds\RDSTest\advworks.mdb or edit the path in the example code to reflect the actual location of this file. 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.

<!-- BeginOpenVBS -->
<%@ Language=VBScript %>
<!--#include file="adovbs.inc"-->
<HTML>
<HEAD>
<META name="VI60_DefaultClientScript"  content=VBScript>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<title>ADO Open Method</title>
<STYLE>
<!--
BODY {
   font-family: 'Verdana','Arial','Helvetica',sans-serif;
   BACKGROUND-COLOR:white;
   COLOR:black;
    }
.thead {
   background-color: #008080; 
   font-family: 'Verdana','Arial','Helvetica',sans-serif; 
   font-size: x-small;
   color: white;
   }
.thead2 {
   background-color: #800000; 
   font-family: 'Verdana','Arial','Helvetica',sans-serif; 
   font-size: x-small;
   color: white;
   }
.tbody { 
   text-align: center;
   background-color: #f7efde;
   font-family: 'Verdana','Arial','Helvetica',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-->
<%
' Remember to change the path to advworks.mdb if you
' installed it in other than the default location.
src = "C:\Program Files\Microsoft Platform SDK\Samples\DataAccess\Rds\RDSTest\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>
<!-- EndOpenVBS -->

Open and Close Methods Example (VBScript)

See Also