Microsoft KB Archive/245179

= How To Process Multiple Recordsets and Messages in ADO =

Article ID: 245179

Article Last Modified on 7/15/2004

-

APPLIES TO


 * Microsoft ActiveX Data Objects 2.1
 * Microsoft ActiveX Data Objects 2.1 Service Pack 1
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7

-



This article was previously published under Q245179



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 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. </li></ol>

Keywords: kbhowto kbstoredproc kbclient KB245179

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.