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 %>
<% ' use this meta tag instead of ADOVBS.inc%>
<!-- METADATA TYPE="typelib" uuid="00000205-0000-0010-8000-00AA006D2EA4"  -->
<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-->
<% 
    ' connection, command and recordset variables
   Dim Cnxn, strCnxn
   Dim rsCustomers, strSQLCustomers
   Dim Cmd 
   Dim rsProducts, strSQLProducts

    ' create and open connection
   Set Cnxn = Server.CreateObject("ADODB.Connection") 
   strCnxn="Provider=SQLOLEDB;Data Source=a-dgayne1;Initial Catalog=Northwind;User ID=sa;pwd=;"
   Cnxn.Open  strCnxn
    ' create and open recordset
   Set rsCustomers = Server.CreateObject("ADODB.Recordset")
   strSQLCustomers = "Customers"
   rsCustomers.Open strSQLCustomers, Cnxn, adOpenKeyset, adLockOptimistic, adCmdTable

   '1st Recordset using Connection - Execute
   Set rsCustomers = Cnxn.Execute(strSQLCustomers) 

   Set Cmd = Server.CreateObject("ADODB.Command")
   Cmd.ActiveConnection = Cnxn
   strSQLProducts = "SELECT * From Products"
   Cmd.CommandText = strSQLProducts

   '2nd Recordset Cmd - execute 
   Set rsProducts = Cmd.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>City</TH>
   </TR>

   <!--Display ADO Data from Customer Table-->
   <% 
   Do While Not rsCustomers.EOF %>
     <TR CLASS=tbody>
      <TD> 
      <%= rsCustomers("CompanyName")%> 
      </TD>
      <TD>
      <%= rsCustomers("ContactName") %> 
      </TD>
      <TD> 
      <%= rsCustomers("City")%> 
      </TD>
     </TR> 
     <% 
   rsCustomers.MoveNext 
   Loop 
   %>
</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 Name</TH>
  <TH>Unit Price</TH>
</TR>

<!-- Display ADO Data Product List-->
<% Do Until rsProducts.EOF %>
  <TR CLASS=tbody>
   <TD>
   <%= rsProducts("ProductName")%>  
   </TD>
   <TD> 
   <%= rsProducts("UnitPrice")%> 
   </TD>
<% 
  rsProducts.MoveNext 
Loop 
   
    ' clean up 
   
   rsCustomers.Close
   rsProducts.Close
   Cnxn.Close
   Set Cmd = Nothing
   Set rsProducts = Nothing
   Set Cnxn = Nothing
%>
</TABLE>

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

Execute, Requery, and Clear Methods Example (VBScript)

See Also