Execute, Requery, and Clear Methods Example (VBScript)

This example demonstrates the Execute method when run from both a Command object and a Connection object. It also uses the Requery method to retrieve current data in a recordset, and the Clear method to clear the contents of the Errors collection. The ExecuteCommand and PrintOutput procedures are required for this procedure to run.

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 ExecuteVBS.asp. You can view the result in any client browser.

<!-- BeginExecuteVBS -->
<%@ 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 Execute 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 Execute Method</H3>
<HR>
<H4>Recordset Retrieved Using Connection Object</H4>
<!--- Recordsets retrieved using Execute method of Connection and Command Objects-->
<% 
' Remember to change the path to advworks.mdb if you
' installed it in other than the default location.
sDsrc="C:\Program Files\Microsoft Platform SDK\Samples\DataAccess\Rds\RDSTest\advworks.mdb"
sConnStr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDsrc

Set OBJdbConnection = Server.CreateObject("ADODB.Connection") 
OBJdbConnection.Open sConnStr 
SQLQuery = "SELECT * FROM Customers" 
'1st Recordset RSCustomerList using conn execute mthd
Set RSCustomerList = OBJdbConnection.Execute(SQLQuery) 

Set OBJdbCommand = Server.CreateObject("ADODB.Command")
OBJdbCommand.ActiveConnection = OBJdbConnection
SQLQuery2 = "SELECT * From Products"
OBJdbCommand.CommandText = SQLQuery2
'2nd Recordset RSProductList using conn execute mthd
Set RSProductList = OBJdbCommand.Execute
%>
<TABLE COLSPAN=8 CELLPADDING=5 BORDER=0 ALIGN=CENTER>
<!-- BEGIN column header row for Customer Table-->
<TR CLASS=thead>
  <TH>Company Name</TH>
  <TH>Contact Name</TH>
  <TH>E-mail address</TH>
  <TH>City</TH>
  <TH>State/Province</TH>
</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>
<H4>Recordset Retrieved Using Command Object</H4>

<TABLE CELLPADDING=5 BORDER=0 ALIGN=CENTER WIDTH="80%">

<!-- BEGIN column header row for Product List Table-->
<TR CLASS=thead2>
  <TH>Product Type</TH>
  <TH>Product Name</TH>
  <TH>Product Description</TH>
  <TH>Unit Price</TH>
</TR>

<!-- Display ADO Data Product List-->
<% Do While Not RsProductList.EOF %>
  <TR CLASS=tbody>
  <TD> 
  <%= RsProductList("ProductType")%> 
  </TD>
  <TD>
  <%= RsProductList("ProductName")%>  
  </TD>
  <TD align="left">
   <%= RsProductList("ProductDescription")%> 
  </TD>
  <TD> 
  <%= RsProductList("UnitPrice")%> 
  </TD>
  <!--  Next Row = Record -->
<% 
  RsProductList.MoveNext 
Loop 
'Remove objects from memory to free resources
RsProductList.Close
OBJdbConnection.Close
Set ObJdbCommand = Nothing
Set RsProductList = Nothing
Set OBJdbConnection = Nothing
%>
</TABLE>

</BODY>
</HTML>
<!-- EndExecuteVBS -->

Execute, Requery, and Clear Methods Example (VBScript)

See Also