Exec stored procedure to return ADODB.Recordset [Resolved]

[RobDog888]

I created a stored procedure in SQL that outputs a record. I need
to have a function in vb exec the sp and return a recordset to use
in my program.
Stored procedure...

IF EXISTS(SELECT Name FROM SysObjects
      WHERE Name = 'sp_RRGetTask' AND Type = 'P')
   DROP PROCEDURE sp_RRGetTask
GO

CREATE PROCEDURE sp_RRGetTask 
   @Task_No nvarchar(8) OUTPUT
AS 
SELECT *
   FROM Tasks 
   WHERE Task_No = @Task_No
GO

Function in vb to execute it.

Public Function Exec_SPrs(ByRef oCnnS As ADODB.Connection, ByVal sp_Name As String) As ADODB.Recordset
'<RR 09/16/2003 - VB/OUTLOOK GURU>
    On Error GoTo No_Bugs
    
    Dim oCM As ADODB.Command
    Dim lRecs As Long
    
    Set oCM = New ADODB.Command
    oCM.ActiveConnection = oCnnS
    oCM.CommandType = adCmdStoredProc
    oCM.CommandText = sp_Name
    
    'NEED TO CHANGE FROM HERE???
    oCM.Execute lRecs
    If lRecs > 0 Then
        gbExec_SPrs = True
    Else
        gbExec_SPrs = False
    End If
    Exit Function
    
No_Bugs:
    
    MsgBox Err.Number & " - " & Err.Description, vbOKOnly + vbInformation, App.ProductName
    
End Function

This is my code for executing stored procedures that don't return anything.
How can I complete it to return an ADODB.Recordset?

[RobDog888]

Updated function almost working. It is giving an "hstmt" error -
"Connection is busy with results from another hstmt."
Any ideas?
I searched books on line and only came up with -
The SQL Server ODBC driver allows only one active hstmt.
Not much help.

Public Function Exec_SPrs(ByRef oCnnS As ADODB.Connection, ByVal sp_Name As String, Optional ByVal sParam As String) As ADODB.Recordset
'<RR 09/16/2003 - VB/OUTLOOK GURU>
    On Error GoTo No_Bugs
    
    Dim oRs As ADODB.Recordset
    Dim oCM As ADODB.Command
    Dim oParam As ADODB.Parameter
    Dim lRecs As Long
    
    Set oRs = New ADODB.Recordset
    Set oCM = New ADODB.Command
    oCM.ActiveConnection = oCnnS
    oCM.CommandType = adCmdStoredProc
    oCM.CommandText = sp_Name
    If sParam = "" Then
        Set oParam = oCM.CreateParameter("Task_No", adChar, adParamInput, 8, sParam)
        oCM.Parameters.Append oParam
    End If
    Set oRs = oCM.Execute(lRecs, sParam)
    Set Exec_SPrs = oRs
    Exit Function
    
No_Bugs:
    
    MsgBox Err.Number & " - " & Err.Description, vbOKOnly + vbInformation, App.ProductName
    
End Function

[techgnome]

change Set oRs = oCM.Execute(lRecs, sParam) to Set oRs = oCM.Execute
Also since you are passing back the recordset, you may want to disconnect it....

Set oRs = oCM.Execute
Set oRS.ActiveConnection = Nothing
Set Exec_SPrs = oRs
Exit Function

One last thing... you need to include the @ in the parameter name....

Set oParam = oCM.CreateParameter("@Task_No", adChar, adParamInput, 8, sParam)

[RobDog888]

If I do this - Set oRs = oCM.Execute - I get an error that its requires a parameter.
I think I might of confused using a Command Object with
executing a stored procedure. I get the original error when I try
to perform any kind of operation on the same connection. So I will
test your other option of .ActiveConnection. I will let you know
how it goes.

[techgnome]

I missed it the first time, but now I see why it might be complaining about parameters...
the ActiveConnection on the Command object needs to be SET.....
oCM.ActiveConnection = oCnnS
should be
Set oCM.ActiveConnection = oCnnS

As for the command object... that's what it's for, executing stored procedures....

[RobDog888]

Ok. We got a little farther. The sp executes and no error
requesting a parameter. I do get error stating that "Operation
not allowed when object is open." It is refering to the line - Set
oRs.ActiveConnection = Nothing.

Also, I hade the logic backwards for the parameter - = instead of <>.
I could create another connection just for executing the sp's.
This is what is working.

Public Function Exec_SPrs(ByRef oCnnS As ADODB.Connection, ByVal sp_Name As String, Optional ByVal sParam As String) As ADODB.Recordset
'<RR 09/16/2003 - VB/OUTLOOK GURU>
    On Error GoTo No_Bugs
    
    Dim oRs As ADODB.Recordset
    Dim oCM As ADODB.Command
    Dim oParam As ADODB.Parameter
    Dim lRecs As Long
    
    Set oRs = New ADODB.Recordset
    Set oCM = New ADODB.Command
    Set oCM.ActiveConnection = oCnnS
    oCM.CommandType = adCmdStoredProc
    oCM.CommandText = sp_Name
    If sParam <> "" Then
        Set oParam = oCM.CreateParameter("@Task_No", adChar, adParamInput, 8, sParam)
        oCM.Parameters.Append oParam
    End If
'    Set oRs = oCM.Execute(lRecs, sParam)
    Set oRs = oCM.Execute 'Works this way now
    Set Exec_SPrs = oRs
    Set oRs.ActiveConnection = Nothing 'Error here
    Set oParam = Nothing
    Set oCM = Nothing
    Exit Function
    
No_Bugs:
    
    MsgBox Err.Number & " - " & Err.Description, vbOKOnly + vbInformation, App.ProductName
'    Resume
End Function

[RobDog888]

Final version is working now. I had to create another connection
and set it to be a server side cursor. The only thing is that it is
not all that fast. I had converted it from a query to a sp.

Any ideas on how to speed it up?

Public Function Exec_SPrs(ByRef oCnnS As ADODB.Connection, ByVal sp_Name As String, Optional ByVal sParam As String) As ADODB.Recordset
'<RR 09/16/2003 - VB/OUTLOOK GURU>
    On Error GoTo No_Bugs
    
    Dim oRs As ADODB.Recordset
    Dim oCM As ADODB.Command
    Dim oParam As ADODB.Parameter
    Dim lRecs As Long
    
    Set oRs = New ADODB.Recordset
    Set oCM = New ADODB.Command
    Set oCM.ActiveConnection = oCnnS
    oCM.CommandType = adCmdStoredProc
    oCM.CommandText = sp_Name
    If sParam <> "" Then
        Set oParam = oCM.CreateParameter("@Task_No", adChar, adParamInput, 8, sParam)
        oCM.Parameters.Append oParam
    End If
    Set oRs = oCM.Execute
    Set Exec_SPrs = oRs
    Set oCM.ActiveConnection = Nothing
    Set oParam = Nothing
    Set oCM = Nothing
    Exit Function
    
No_Bugs:
    
    MsgBox Err.Number & " - " & Err.Description, vbOKOnly + vbInformation, App.ProductName
'    Resume
End Function

[brucevde]

Just some comments.

1) Server side cursors bascially block any further activity on a connection. You can only have one server side recordset per connection.

2) To disconnect a recordset you must use a client side cursor.

3) You do not need to use the @ when defining a parameter name (unless you have NamedParameters option set).

4) There is very little speed gain b/w executing a sql query vs a SP via the command object. Speed is gained when you execute the command more than once, say in a loop or something.

5) Calling Set oCM.ActiveConnection = Nothing does not close the connection on the recordset.

6) Using the syntax Set oRs = oCM.Execute there is no need to create an instance of the recordset beforehand.

7) Also, when using the syntax Set oRs = oCM.Execute, a Read-Only Forward Only recordset is always created. Use the following syntax to control the cursor and lock types. Note in this case you need to create the recordset instance first.

Set oRS = New ADODB.Recordset
oRS.Open oCM, , adOpenStatic, adLockBatchOptimistic

8) When executing a command that does not return a recordset use the following syntax, it is more efficient.

oCM.Execute lRecs, , adExecuteNoRecords

9) It probably does not matter in this case but generally you should define your command parameters to match the stored procedure parameters. The stored procedure you posted accepts an nvarchar(8) variable. Not a Char(8). Use the adVarWChar constant instead.

10) HTH