How To Process Multiple Recordsets and Messages in ADO

Exclusion de responsabilité du contenu obsolète de la base de connaissances

Cet article a été rédigé sur les produits pour lesquels Microsoft n’offre plus aucune prise en charge. Par conséquent, cet article est proposé « en l’état » et ne sera plus mis à jour.

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

  1. Using the SQL Server Query Analyzer or equivalent tool, execute the preceding sample scripts.
  2. 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.
  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.
Propriétés

ID d'article : 245179 - Dernière mise à jour : 15 juil. 2004 - Révision : 1

Commentaires