Getting an output parameter and a recordset from a proc 2

[NoCoolHandle]

Does anyone know how to return both an output parameter to a VB6 app using Ado?

I have been trying to do this from a single storedproc, but it seems that I can either get the recordset or the parameter. Not both.

Code looks something like...
[blue]
Dim cnlane As ADODB.Connection
Dim rs As Recordset
Dim cmd As ADODB.Command

Set cnlane = New ADODB.Connection
Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset

cnlane.Open "provider=sqloledb;server=(local);integrated security=sspi;database=lanemessages"

With cmd
.CommandText = "dbo.procGetMessages"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@plazahassome", adInteger, adParamOutput)
.ActiveConnection = cnlane
Set rs = .Execute
MsgBox .Parameters("@plazahassome").Value
End With

cnlane.Close
[/blue]

If I omit [blue]Set rs = .Execute[/blue] then the parameter is returned, if not, no parameter....

I have also tried
[blue]rs.ActiveCommand.parameters("@plazahassome").value[/blue] but this also fails..

Any help is appreciated.


Rob

 

[MarkSweetland]

The ADODB.Command object will return two recordsets when output parameters are used in conjuction with a recorset. The first returned recordset is the result of a SELECT in the sp, the second returned recordset contqins the value of the parameter:

With cmd
        .CommandText = "dbo.procGetMessages"
        .CommandType = adCmdStoredProc
        .Parameters.Append .CreateParameter("@plazahassome", adInteger, adParamOutput)
        .ActiveConnection = cnlane
         Set rs = .Execute
         [red]rs.NextRecordset[/red]
         MsgBox .Parameters("@plazahassome").Value
    End With

Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[NoCoolHandle]

Mark, this still doesn't seem to do it.

I am using mdac 2.7 and have tried both sqloledb and msdasql with a driver of sql server. Target system uses SQLServer 2K Developer.

What seems to happen is that the second recordset is being destroyed as after the call to rs.nextrecordset the recordset is comming back closed. Any call to the parameter comes back empty.

I have also been having problems returning more than one recordset. Any call to nextrecordset seems to work, but there isn't any recordset following.. and the recordset state comes back 0

Any ideas..
is this a 2.7 bug?
do I need to set nocount off?
limitation of SQL Developer or MSDE?


Thanks

Rob

 

[vidru]

Here's the way I've gotten both a recordset, and an OUPUT variable back from a procedure. I'm using a client-side cursor on the recordset:

    Dim cn As ADODB.Connection
    Dim rs As Recordset
    Dim cmd As ADODB.Command
    
    Set cn = New ADODB.Connection
    Set cmd = New ADODB.Command
    Set rs = New ADODB.Recordset
    
    cn.Open "Provider=SQLOLEDB;Server=IGNATZ;Integrated Security=SSPI;Database=Forms2"
    
    With cmd
        .CommandText = "CalcCurrentBillPeriods"
        .CommandType = adCmdStoredProc
        .Parameters.Append .CreateParameter("@BillPeriod", adInteger, adParamOutput)
        .ActiveConnection = cn
    End With

    With rs
        .CursorLocation = adUseClient
        .Open cmd
    End With

    Debug.Print cmd.Parameters("@BillPeriod").Value
    Debug.Print rs.RecordCount
    cn.Close

-dave

 

[NoCoolHandle]

That did the trick!!!!!

Thanks Dave