How To Process Multiple Recordsets and Messages in ADO (245179)

MORE INFORMATION

The SQL statements used to illustrate the function are for Microsoft SQL Server 6.5 or later. 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

Using the SQL Server Query Analyzer or equivalent tool, execute the preceding sample scripts.
In Visual Basic 5.0 or 6.0, from the Project menu, click References. Add a reference to the Microsoft ActiveX Data Objects 2.x Library.
Add four command buttons to the default form (Command1, Command2, Command3, Command4).

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

Add the HandleResults procedure to the form module.

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.