HOWTO: Process Multiple Recordsets and Messages in ADO

ID: Q245179

The information in this article applies to:
  • ActiveX Data Objects (ADO), versions 2.1, 2.1 SP1, 2.1 SP2, 2.5
  • Microsoft Visual Basic Professional and Enterprise Editions for Windows, versions 5.0, 6.0

SUMMARY

This article contains a generic function for processing multiple recordsets and other messages that can be returned from stored procedures or the execution of batch SQL statements.


MORE INFORMATION

The SQL statements used to illustrate the function are for Microsoft SQL Server 6.5 or 7.0. If you have a different server, you should use SQL statements appropriate to your system. The client ActiveX Data Objects (ADO) code will not need to be modified.

ActiveX Data Objects can receive five different types of data from the server:

  • A Recordset.


  • The number of records modified by an action query (INSERT, UPDATE, DELETE, SELECT INTO).


  • An informational message or warning.


  • An error message.


  • Stored procedure return values and output parameters.


When dealing with multiple return results, you must use a server-side, forward-only, read-only cursor. Each piece of information will have an associated Recordset. For all but the first item, the associated Recordset object will be closed.

Note Regarding Return Values and Output Parameters

Stored procedure return values and output parameters are only available if the CommandType is adCmdStoredProc and the command consists of only a single stored procedure call.

If you are submitting a batch of commands along with the stored procedure call, or submitting multiple stored procedure calls, then the return value(s) and output parameters are not available directly. You can get them indirectly by declaring some SQL Server variables and returning them as a Recordset. In the example below, the batch calls two stored procedures, both of which have a return value. The ? parameter markers can only be used for input parameters. You will receive an error if you try to use them with the OUTPUT keyword. 

DECLARE @RetVal1 INT
DECLARE @RetVal2 INT
DECLARE @Output2 VARCHAR(10)
EXECUTE @RetVal1 = sp_MyProc1 ?, ?
EXECUTE @RetVal2 = sp_MyProc2 ?, @Output2 OUTPUT
SELECT @RetVal1 AS RetVal1, @RetVal2 AS RetVal2, @Output2 AS Output2

Preparing the Server

The sample code uses the Authors table of the pubs sample database. It also uses a sample table and stored procedure. The script is given below. You can cut and paste the statements directly into the SQL Query Analyser tool or ISQL or OSQL. The stored procedure contains a variety of statements and conditional execution in order to illustrate the sample code. If you are using an OUTPUT text parameter, you will need to define the parameter size in the stored procedure. 

USE pubs
go

CREATE TABLE dbo.ParamTest (
	ID int NULL ,
	FirstName varchar (255) NULL ,
	LastName varchar (255) NULL 
)
go

CREATE PROCEDURE sp_ParamTest
@id int output,
@fname varchar(255) output,
@lname varchar(255)
AS
  raiserror('First Error', 10, 1)
  SELECT * FROM Authors
  raiserror('Second Error', 10,1)
  SET NOCOUNT OFF
  INSERT INTO ParamTest VALUES(@id, @fname, @lname)
  raiserror('Third Error',10, 1)
  IF @id = 5
    BEGIN
      SET NOCOUNT ON
      INSERT INTO ParamTest VALUES(@id, @fname, @lname)
      raiserror('Fourth Error', 11, 1)
      SELECT @fname = 'Jack'
      RETURN 13
    END
  ELSE
    BEGIN
      INSERT INTO ParamTest VALUES(6, 'John', 'Doe')
      SELECT id FROM ParamTest WHERE id = @id
      PRINT 'This is a message that sp_ParamTest prints.'
      SELECT @id = @id + 5
      SELECT @fname = 'Sherlock'
      RETURN 6
    END

Sample Procedure

The procedure below accepts a Recordset object and a variable used in the RecordsAffected parameter of the command used to create it. The main loop consists of checking whether the Recordset is open. If it is, the routine prints a count of the fields and records. If it is closed, then the routine checks the Errors collection of the Connection object for warnings. If there are no warnings, the number of records affected are printed.

Errors and stored procedure return values and output parameters are handled outside the loop. The error handler handles errors that may be encountered when retrieving the next Recordset. Using the Transact-SQL RAISERROR statement with an error code of 11 or greater will cause an error. Using RAISERROR with an error code of 10 or less or using the PRINT statement generates a warning that is handled in the loop.

Return values are handled after the loop exits normally or after the error handler has handled the error. As noted earlier, only stored procedures executed with the adCmdStoredProc CommandType can return output parameters and return values directly.

The variables are: 


        rs - The Recordset created in the calling routine.
  Affected - A variable holding the Records Affected value from when the Recordset was created.
        cn - The ActiveConnection of the Recordset.
       cmd - The ActiveCommand of the Recordset.
         E - Used to get warning information.
         P - Used to check for Output parameters and return values.
ItemNumber - Incremented to provide a number for each piece of return data.
  RecCount - Incremented to count the number of records in the Recordset. 

Public Sub HandleResults(rs As ADODB.Recordset, Affected As Long)
Dim cn As ADODB.Connection, cmd As ADODB.Command
Dim E As ADODB.Error, P As ADODB.Parameter
Dim ItemNumber As Long, RecCount As Long
  Set cn = rs.ActiveConnection
  Set cmd = rs.ActiveCommand
  ItemNumber = 0
  Do While Not (rs Is Nothing)
    ItemNumber = ItemNumber + 1
    If rs.State <> adStateClosed Then
      RecCount = 0
      Do While Not rs.EOF
        ' handle record here
        RecCount = RecCount + 1
        rs.MoveNext
      Loop
      Debug.Print "Item " & ItemNumber & ": Recordset has " & RecCount & " records and " & rs.Fields.Count & " fields."
    ElseIf cn.Errors.Count > 0 Then
      For Each E In cn.Errors
        Debug.Print "Item " & ItemNumber & ": Error " & E.Number & " " & E.Description
      Next E
    Else
      Debug.Print "Item " & ItemNumber & ": " & Affected & " records affected."
    End If
    On Error GoTo Param_Error
    Set rs = rs.NextRecordset(Affected)
    On Error GoTo 0
  Loop
  
Param_Enum:
  On Error GoTo 0
  For Each P In cmd.Parameters
    Select Case P.Direction
      Case adParamReturnValue
        Debug.Print "Return value: " & P.Value
      Case adParamOutput
        Debug.Print "Output: " & P.Value
      Case adParamInputOutput
        Debug.Print "Changed: " & P.Value
    End Select
  Next P
  Exit Sub
  
Param_Error:
  ItemNumber = ItemNumber + 1
  Debug.Print "Item " & ItemNumber & ": Fatal Error " & Err.Number & " " & Err.Description
  Resume Param_Enum
End Sub
You need to modify the sample routine to suit the specific needs of your application, but it should provide a framework to build upon.

The Test Harness

  1. Using the SQL Server Query Analyzer or equivalent tool, execute the preceding sample scripts.


  2. In Visual Basic 5.0 or 6.0, using the Project, References menu, add a reference to the Microsoft ActiveX Data Objects 2.1 Library.


  3. Add four command buttons to the default form (Command1, Command2, Command3, Command4).


  4. Add the following code to the form Module (edit the "server=" and "pwd=" arguments in the strConn variable below so they will refer to the name of your SQL Server and include the password for the sa login account): 
    
Option Explicit

Private Const strConn = "Provider=SQLOLEDB;server=MyServer;uid=sa;pwd=;database=pubs"

Private Sub Command1_Click()
Dim cn As ADODB.Connection, rs As ADODB.Recordset, SQL As String
Dim cmd As ADODB.Command, Affected As Long, P As ADODB.Parameter
  Set cn = New ADODB.Connection
  Set cmd = New ADODB.Command
  Set rs = New ADODB.Recordset
  cn.Open strConn
  SQL = "SELECT * FROM Authors WHERE au_lname LIKE ?" & vbCrLf & _
        "SELECT au_id, au_lname, au_fname FROM Authors" & vbCrLf & _
        "INSERT INTO ParamTest VALUES (?,?,?)" & vbCrLf & _
        "DECLARE @Ret int" & vbCrLf & _
        "EXECUTE @Ret = sp_ParamTest ?, ?, ?" & vbCrLf & _
        "SELECT @Ret AS SProcReturn" & vbCrLf & _
        "INSERT INTO ParamTest VALUES (5,'Sally','Smith')"
  Set cmd.ActiveConnection = cn
  cmd.CommandText = SQL
  cmd.CommandType = adCmdText
  With cmd
    .Parameters.Append .CreateParameter("LNAME", adVarChar, adParamInput, 2, "S%")
    .Parameters.Append .CreateParameter("ID1", adInteger, adParamInput, , 7)
    .Parameters.Append .CreateParameter("fname1", adVarChar, adParamInput, 255, "Barney")
    .Parameters.Append .CreateParameter("lname1", adVarChar, adParamInput, 255, "Williams")
    .Parameters.Append .CreateParameter("ID2", adInteger, adParamInput, 4, 5)
    .Parameters.Append .CreateParameter("fname2", adVarChar, adParamInput, 255, "Jane")
    .Parameters.Append .CreateParameter("lname2", adVarChar, adParamInput, 255, "Doe")
  End With
  Set rs = cmd.Execute(Affected)
  HandleResults rs, Affected
End Sub

Private Sub Command2_Click()
Dim cn As ADODB.Connection, rs As ADODB.Recordset, SQL As String
Dim cmd As ADODB.Command, Affected As Long, P As ADODB.Parameter
  Set cn = New ADODB.Connection
  Set cmd = New ADODB.Command
  Set rs = New ADODB.Recordset
  cn.Open strConn
  SQL = "SELECT * FROM Authors WHERE au_lname LIKE ?" & vbCrLf & _
        "SELECT au_id, au_lname, au_fname FROM Authors" & vbCrLf & _
        "INSERT INTO ParamTest VALUES (?,?,?)" & vbCrLf & _
        "DECLARE @Ret int" & vbCrLf & _
        "EXECUTE @Ret = sp_ParamTest ?, ?, ?" & vbCrLf & _
        "SELECT @Ret AS SProcReturn" & vbCrLf & _
        "INSERT INTO ParamTest VALUES (5,'Sally','Smith')"
  Set cmd.ActiveConnection = cn
  cmd.CommandText = SQL
  cmd.CommandType = adCmdText
  With cmd
    .Parameters.Append .CreateParameter("LNAME", adVarChar, adParamInput, 2, "S%")
    .Parameters.Append .CreateParameter("ID1", adInteger, adParamInput, , 7)
    .Parameters.Append .CreateParameter("fname1", adVarChar, adParamInput, 255, "Barney")
    .Parameters.Append .CreateParameter("lname1", adVarChar, adParamInput, 255, "Williams")
    .Parameters.Append .CreateParameter("ID2", adInteger, adParamInput, 4, 4)
    .Parameters.Append .CreateParameter("fname2", adVarChar, adParamInput, 255, "Jane")
    .Parameters.Append .CreateParameter("lname2", adVarChar, adParamInput, 255, "Doe")
  End With
  Set rs = cmd.Execute(Affected)
  HandleResults rs, Affected
End Sub

Private Sub Command3_Click()
Dim cn As ADODB.Connection, rs As ADODB.Recordset, SQL As String
Dim cmd As ADODB.Command, Affected As Long, P As ADODB.Parameter
  Set cn = New ADODB.Connection
  Set cmd = New ADODB.Command
  Set rs = New ADODB.Recordset
  cn.Open strConn
  SQL = "{? = CALL sp_ParamTest (?, ?, ?)}"
  Set cmd.ActiveConnection = cn
  cmd.CommandText = SQL
  cmd.CommandType = adCmdText
  With cmd
    .Parameters.Append .CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue, 4)
    .Parameters.Append .CreateParameter("ID2", adInteger, adParamInputOutput, 4, 4)
    .Parameters.Append .CreateParameter("fname2", adVarChar, adParamInputOutput, 255, "Jane")
    .Parameters.Append .CreateParameter("lname2", adVarChar, adParamInput, 255, "Doe")
  End With
  Set rs = cmd.Execute(Affected)
  HandleResults rs, Affected
End Sub

Private Sub Command4_Click()
Dim cn As ADODB.Connection, rs As ADODB.Recordset, SQL As String
Dim cmd As ADODB.Command, Affected As Long, P As ADODB.Parameter
  Set cn = New ADODB.Connection
  Set cmd = New ADODB.Command
  Set rs = New ADODB.Recordset
  cn.Open strConn
  SQL = "sp_ParamTest"
  Set cmd.ActiveConnection = cn
  cmd.CommandText = SQL
  cmd.CommandType = adCmdStoredProc
  With cmd
    .Parameters.Append .CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue, 4)
    .Parameters.Append .CreateParameter("ID2", adInteger, adParamInputOutput, 4, 5)
    .Parameters.Append .CreateParameter("fname2", adVarChar, adParamInputOutput, 255, "Jane")
    .Parameters.Append .CreateParameter("lname2", adVarChar, adParamInput, 255, "Doe")
  End With
  Set rs = cmd.Execute(Affected)
  HandleResults rs, Affected
End Sub


  5. Add the HandleResults procedure to the form module.


  6. Run the application and click the various command buttons. The Immediate window displays the output generated by the SQL statements in each of the four routines. Here are the statements (numbered) executed as a batch by Command2_Click: 
    
1   SELECT * FROM Authors WHERE au_lname LIKE ?
2   SELECT au_id, au_lname, au_fname FROM Authors
3   INSERT INTO ParamTest VALUES (?,?,?)
4   DECLARE @Ret int
5   EXECUTE @Ret = sp_ParamTest ?, ?, ?
6   SELECT @Ret AS SProcReturn
7   INSERT INTO ParamTest VALUES (5,'Sally','Smith')
    Here is sample output from Command2_Click: 
    
Item 1: Recordset has 3 records and 9 fields.
Item 2: Recordset has 23 records and 3 fields.
Item 3: 1 records affected.
Item 4: Error 0 First Error
Item 5: Recordset has 23 records and 9 fields.
Item 6: Error 0 Second Error
Item 7: 1 records affected.
Item 8: Error 0 Third Error
Item 9: 1 records affected.
Item 10: Recordset has 7 records and 1 fields.
Item 11: Error 0 This is a message that sp_ParamTest prints.
Item 12: Recordset has 1 records and 1 fields.
Item 13: 1 records affected.
    Statement 1 generates Item 1.
    Statement 2 generates Item 2.
    Statement 3 generates Item 3.
    Statement 4 does not generate any return data.
    Statement 5, the stored procedure, generates Items 4 to 11 due to having multiple statements.
    Statement 6 generates Item 12, the stored procedure return value, returned as a Recordset.
    Statement 7 generates Item 13.



REFERENCES

(c) Microsoft Corporation 1999, All Rights Reserved. Contributions by Malcolm Stewart, Microsoft Corporation.

Additional query words:

Keywords : kbADO kbADO210 kbClient kbMDAC kbSQLServ kbStoredProc kbGrpVBDB kbGrpMDAC kbDSupport
Version : WINDOWS:2.1,2.1 SP1,2.1 SP2,2.5,5.0,6.0
Platform : WINDOWS
Issue type : kbhowto


Last Reviewed: January 12, 2000
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.