Execute, Requery, and Clear Methods Example

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.

Public Sub ExecuteX()

   Dim strSQLChange As String
   Dim strSQLRestore As String
   Dim strCnn As String
   Dim cnn1 As ADODB.Connection
   Dim cmdChange As ADODB.Command
   Dim rstTitles As ADODB.Recordset
   Dim errLoop As ADODB.Error

   ' Define two SQL statements to execute as command text.
   strSQLChange = "UPDATE Titles SET Type = " & _
      "'self_help' WHERE Type = 'psychology'"
   strSQLRestore = "UPDATE Titles SET Type = " & _
      "'psychology' WHERE Type = 'self_help'"

   ' Open connection.
      strCnn = "Provider=sqloledb;" & _
      "Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
   Set cnn1 = New ADODB.Connection
   cnn1.Open strCnn

   ' Create command object.
   Set cmdChange = New ADODB.Command
   Set cmdChange.ActiveConnection = cnn1
   cmdChange.CommandText = strSQLChange
   
   ' Open titles table.
   Set rstTitles = New ADODB.Recordset
   rstTitles.Open "titles", cnn1, , , adCmdTable

   ' Print report of original data.
   Debug.Print _
      "Data in Titles table before executing the query"
   PrintOutput rstTitles

   ' Clear extraneous errors from the Errors collection.
   cnn1.Errors.Clear

   ' Call the ExecuteCommand subroutine to execute cmdChange command.
   ExecuteCommand cmdChange, rstTitles
   
   ' Print report of new data.
   Debug.Print _
      "Data in Titles table after executing the query"
   PrintOutput rstTitles

   ' Use the Connection object's execute method to 
   ' execute SQL statement to restore data. Trap for 
   ' errors, checking the Errors collection if necessary.
   On Error GoTo Err_Execute
   cnn1.Execute strSQLRestore, , adExecuteNoRecords
   On Error GoTo 0

   ' Retrieve the current data by requerying the recordset.
   rstTitles.Requery

   ' Print report of restored data.
   Debug.Print "Data after executing the query " & _
      "to restore the original information"
   PrintOutput rstTitles

   rstTitles.Close
   cnn1.Close
   
   Exit Sub
   
Err_Execute:

   ' Notify user of any errors that result from
   ' executing the query.
   If Errors.Count > 0 Then
      For Each errLoop In Errors
         MsgBox "Error number: " & errLoop.Number & vbCr & _
            errLoop.Description
      Next errLoop
   End If
   
   Resume Next

End Sub

Public Sub ExecuteCommand(cmdTemp As ADODB.Command, _
   rstTemp As ADODB.Recordset)

   Dim errLoop As Error
   
   ' Run the specified Command object. Trap for 
   ' errors, checking the Errors collection if necessary.
   On Error GoTo Err_Execute
   cmdTemp.Execute
   On Error GoTo 0

   ' Retrieve the current data by requerying the recordset.
   rstTemp.Requery
   
   Exit Sub

Err_Execute:

   ' Notify user of any errors that result from
   ' executing the query.
   If Errors.Count > 0 Then
      For Each errLoop In Errors
         MsgBox "Error number: " & errLoop.Number & vbCr & _
            errLoop.Description
      Next errLoop
   End If
   
   Resume Next

End Sub

Public Sub PrintOutput(rstTemp As ADODB.Recordset)

   ' Enumerate Recordset.
   Do While Not rstTemp.EOF
      Debug.Print "  " & rstTemp!Title & _
         ", " & rstTemp!Type
      rstTemp.MoveNext
   Loop

End Sub

VBScript Version

Here is the same example written in VBScript to be used in an Active Server Page (ASP). To view this fully functional example, you need to create a system Data Source Name (DSN) called AdvWorks using the data source AdvWorks.mdb installed with IIS and located at C:\InetPub\ASPSamp\AdvWorks. 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 to Notepad or another text editor and save it as Execute.asp. You can view the result in any client browser.

<!-- #Include file="ADOVBS.INC" -->
<HTML><HEAD>
<TITLE>ADO Execute Method</TITLE></HEAD>

<BODY> 
<FONT FACE="MS SANS SERIF" SIZE=2>
<Center><H3>ADO Execute Method</H3><H4>Recordset Retrieved Using Connection Object</H4>
<TABLE WIDTH=600 BORDER=0>
<TD VALIGN=TOP ALIGN=LEFT COLSPAN=3><FONT SIZE=2>

<!--- Recordsets retrieved using Execute method of Connection and Command Objects-->
<% 
Set OBJdbConnection = Server.CreateObject("ADODB.Connection") 
OBJdbConnection.Open "AdvWorks" 
SQLQuery = "SELECT * FROM Customers" 
'First Recordset RSCustomerList
Set RSCustomerList = OBJdbConnection.Execute(SQLQuery) 

Set OBJdbCommand = Server.CreateObject("ADODB.Command")
OBJdbCommand.ActiveConnection = OBJdbConnection
SQLQuery2 = "SELECT * From Products"
OBJdbCommand.CommandText = SQLQuery2
Set RsProductList = OBJdbCommand.Execute

%>
<TABLE COLSPAN=8 CELLPADDING=5 BORDER=0>

<!-- BEGIN column header row for Customer Table-->

<TR><TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Company Name</FONT>
</TD>
<TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Contact Name</FONT>
</TD>
<TD ALIGN=CENTER WIDTH=150 BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>E-mail address</FONT>
</TD>
<TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>City</FONT>
</TD>
<TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>State/Province</FONT>
</TD></TR>

<!--Display ADO Data from Customer Table-->
<% Do While Not RScustomerList.EOF %>
  <TR>
  <TD BGCOLOR="f7efde" ALIGN=CENTER> 
  <FONT STYLE="ARIAL NARROW" SIZE=1> 
  <%= RSCustomerList("CompanyName")%> 
  </FONT></TD>
  <TD BGCOLOR="f7efde" ALIGN=CENTER>
  <FONT STYLE="ARIAL NARROW" SIZE=1> 
  <%= RScustomerList("ContactLastName") & ", " %> 
  <%= RScustomerList("ContactFirstName") %> 
  </FONT></TD>
  <TD BGCOLOR="f7efde" ALIGN=CENTER>
  <FONT STYLE="ARIAL NARROW" SIZE=1>
   
  <%= RScustomerList("ContactLastName")%> 
 </FONT></TD>
  <TD BGCOLOR="f7efde" ALIGN=CENTER>
  <FONT STYLE="ARIAL NARROW" SIZE=1> 
  <%= RScustomerList("City")%> 
  </FONT></TD>
  <TD BGCOLOR="f7efde" ALIGN=CENTER>
  <FONT STYLE="ARIAL NARROW" SIZE=1> 
  <%= RScustomerList("StateOrProvince")%> 
  </FONT></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 COLSPAN=8 CELLPADDING=5 BORDER=0>

<!-- BEGIN column header row for Product List Table-->

<TR><TD ALIGN=CENTER BGCOLOR="#800000">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Product Type</FONT>
</TD>
<TD ALIGN=CENTER BGCOLOR="#800000">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Product Name</FONT>
</TD>
<TD ALIGN=CENTER WIDTH=350 BGCOLOR="#800000">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Product Description</FONT>
</TD>
<TD ALIGN=CENTER BGCOLOR="#800000">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Unit Price</FONT>
</TD></TR>

<!-- Display ADO Data Product List-->
<% Do While Not RsProductList.EOF %>
  <TR>
  <TD BGCOLOR="f7efde" ALIGN=CENTER> 
  <FONT STYLE="ARIAL NARROW" SIZE=1> 
  <%= RsProductList("ProductType")%> 
  </FONT></TD>
  <TD BGCOLOR="f7efde" ALIGN=CENTER> 
  <FONT STYLE="ARIAL NARROW" SIZE=1> 
  <%= RsProductList("ProductName")%> 
  </FONT></TD>
  <TD BGCOLOR="f7efde" ALIGN=CENTER>
  <FONT STYLE="ARIAL NARROW" SIZE=1>
   <%= RsProductList("ProductDescription")%> 
 </FONT></TD>

  <TD BGCOLOR="f7efde" ALIGN=CENTER>
  <FONT STYLE="ARIAL NARROW" SIZE=1> 
  <%= RsProductList("UnitPrice")%> 
  </FONT></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></FONT></Center></BODY></HTML>