@ http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdconrecordsetopen.asp| ... Retrieving Multiple Recordsets You might occasionally need to execute a command that will return more than one result set. A common example is a stored procedure that runs against a SQL Server database, as in the following example. The stored procedure contains a COMPUTE clause to return the average price of all products in the table. The definition of the stored procedure is as follows:CREATE PROCEDURE ProductsWithAvgPrice AS SELECT ProductID, ProductName, UnitPrice FROM PRODUCTS COMPUTE AVG(UnitPrice) The Microsoft OLE DB Provider for SQL Server returns multiple result sets to ADO when the command contains a COMPUTE clause. Therefore, the ADO code must use the NextRecordset method to access the data in the second result set, as shown here:'BeginNextRs On Error GoTo ErrHandler:
Dim objConn As New ADODB.Connection Dim objCmd As New ADODB.Command Dim objRs As New ADODB.Recordset Set objConn = GetNewConnection objCmd.ActiveConnection = objConn
Do While Not objRs.EOF Debug.Print objRs(0) & vbTab & objRs(1) & vbTab & _ objRs(2) objRs.MoveNext Loop
Set objRs = objRs.NextRecordset
Debug.Print "AVG. PRICE = $ " & objRs(0) 'clean up objRs.Close objConn.Close Set objRs = Nothing Set objConn = Nothing Set objCmd = Nothing Exit Sub
ErrHandler: 'clean up If objRs.State = adStateOpen Then objRs.Close End If
If objConn.State = adStateOpen Then objConn.Close End If
Set objRs = Nothing Set objConn = Nothing Set objCmd = Nothing
If Err <> 0 Then MsgBox Err.Source & "-->" & Err.Description, , "Error" End If 'EndNextRs
@http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdconrecordsetopen.asp ....... Retrieving Multiple Recordsets You might occasionally need to execute a command that will return more than one result set. A common example is a stored procedure that runs against a SQL Server database, as in the following example. The stored procedure contains a COMPUTE clause to return the average price of all products in the table. The definition of the stored procedure is as follows:CREATE PROCEDURE ProductsWithAvgPrice AS SELECT ProductID, ProductName, UnitPrice FROM PRODUCTS COMPUTE AVG(UnitPrice) The Microsoft OLE DB Provider for SQL Server returns multiple result sets to ADO when the command contains a COMPUTE clause. Therefore, the ADO code must use the NextRecordset method to access the data in the second result set, as shown here:'BeginNextRs On Error GoTo ErrHandler:
Dim objConn As New ADODB.Connection Dim objCmd As New ADODB.Command Dim objRs As New ADODB.Recordset Set objConn = GetNewConnection objCmd.ActiveConnection = objConn
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdconrecordsetopen.asp|
...
Retrieving Multiple Recordsets
You might occasionally need to execute a command that will return more than one result set. A common example is a stored procedure that runs against a SQL Server database, as in the following example. The stored procedure contains a COMPUTE clause to return the average price of all products in the table. The definition of the stored procedure is as follows:CREATE PROCEDURE ProductsWithAvgPrice
AS
SELECT ProductID, ProductName, UnitPrice
FROM PRODUCTS
COMPUTE AVG(UnitPrice)
The Microsoft OLE DB Provider for SQL Server returns multiple result sets to ADO when the command contains a COMPUTE clause. Therefore, the ADO code must use the NextRecordset method to access the data in the second result set, as shown here:'BeginNextRs
On Error GoTo ErrHandler:
Dim objConn As New ADODB.Connection
Dim objCmd As New ADODB.Command
Dim objRs As New ADODB.Recordset Set objConn = GetNewConnection
objCmd.ActiveConnection = objConn
objCmd.CommandText = "ProductsWithAvgPrice"
objCmd.CommandType = adCmdStoredProc
Set objRs = objCmd.Execute
Do While Not objRs.EOF
Debug.Print objRs(0) & vbTab & objRs(1) & vbTab & _
objRs(2)
objRs.MoveNext
Loop
Set objRs = objRs.NextRecordset
Debug.Print "AVG. PRICE = $ " & objRs(0) 'clean up
objRs.Close
objConn.Close
Set objRs = Nothing
Set objConn = Nothing
Set objCmd = Nothing
Exit Sub
ErrHandler:
'clean up
If objRs.State = adStateOpen Then
objRs.Close
End If
If objConn.State = adStateOpen Then
objConn.Close
End If
Set objRs = Nothing
Set objConn = Nothing
Set objCmd = Nothing
If Err <> 0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Error"
End If
'EndNextRs
.......
Retrieving Multiple Recordsets
You might occasionally need to execute a command that will return more than one result set. A common example is a stored procedure that runs against a SQL Server database, as in the following example. The stored procedure contains a COMPUTE clause to return the average price of all products in the table. The definition of the stored procedure is as follows:CREATE PROCEDURE ProductsWithAvgPrice
AS
SELECT ProductID, ProductName, UnitPrice
FROM PRODUCTS
COMPUTE AVG(UnitPrice)
The Microsoft OLE DB Provider for SQL Server returns multiple result sets to ADO when the command contains a COMPUTE clause. Therefore, the ADO code must use the NextRecordset method to access the data in the second result set, as shown here:'BeginNextRs
On Error GoTo ErrHandler:
Dim objConn As New ADODB.Connection
Dim objCmd As New ADODB.Command
Dim objRs As New ADODB.Recordset Set objConn = GetNewConnection
objCmd.ActiveConnection = objConn
objCmd.CommandText = "ProductsWithAvgPrice"
objCmd.CommandType = adCmdStoredProc
Set objRs = objCmd.Execute
Do While Not objRs.EOF
Debug.Print objRs(0) & vbTab & objRs(1) & vbTab & _
objRs(2)
objRs.MoveNext
Loop
Set objRs = objRs.NextRecordset
Debug.Print "AVG. PRICE = $ " & objRs(0) 'clean up
objRs.Close
objConn.Close
Set objRs = Nothing
Set objConn = Nothing
Set objCmd = Nothing
Exit Sub
ErrHandler:
'clean up
If objRs.State = adStateOpen Then
objRs.Close
End If
If objConn.State = adStateOpen Then
objConn.Close
End If
Set objRs = Nothing
Set objConn = Nothing
Set objCmd = Nothing
If Err <> 0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Error"
End If
'EndNextRs