Problem returning a Recordset AND an Output Parameter

[webuser]

I am returning a recordset and one output parameter from my Stored Procedure. When I run it from SQL Analyzer, it works fine. But when I call the Stored Procedure from ADO, it seems like the recordset is returned, but the output parameter is not. Is this a bug or by design? Are there any work arounds? Thanks in advance.

P.S I have many many Stored Procedures that I am using from essentially the same ADO code and they all work, but this happens to be the first Stored Procedure that we have used that returns BOTH a recordset AND an Output Parameter.

 

[ctarr]

The Recordset and Output Parameter are probably both coming back but you need to call RS.NextRecordset() to tell ADO to read the second part of the Recordset.

Hope that helps,

Craig

 

[Sameal]

I've found that you can't access the return parameters until you do 'rs.Close'. Which I think is very bad. If you find a work around to access any kind of return value before you loose the recordset's data by closing I'd love to hear.

 

[webuser]

Sameal,
I know I can access return or Output parameters WITHOUT closing the recordset. Are you saying that this is a problem specifically when returning recordsets too, b/c that is the only time I am having the problem - when returning a recordset AND an output parameter.

CTarr,
You said to use the NextRecordset method, but my problem is not that i can't access the recordset. It's that I can't access the output parameter. Will the NextRecordset method help me in returning the output parameter? Also, does the order in which the Stored Procedure runs matter (i.e Runs SQL that returns recordset or statement that sets output parameter?)

Thanks again.

 

[Sameal]

That is exactly what I'm saying...When you execute the stored proc, the recordset it returns fills your Recordset object. However before you can access that rs("Return&quot parameter you'll have to do an rs.Close. Try it in your code and your return value will be there.

This problem doesn't occur when you have just a recordset or just a return value. Because if you don't have a recordset the recordset is closed by default thus giving you access to that return value.

 

[Sameal]

I just found some info about this on the MS site.

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q256234

 

[webuser]

Thanks Sameal for the info, but for some reason I still can't get it to work!
I know the Sproc works b/c like I said earlier, it works from Query Analyzer. I get the recordset , the Return Value and the output parameter. I must be doing something wrong. I tried what was in the MS document (i.e using Client Side Cursor) and this did not work for me either. Here is some of the code I'm using...Maybe this will help.

Set cmd = New ADODB.Command
cmd.ActiveConnection = prvSprocConnection
cmd.CommandText = "SprocName"
cmd.CommandType = adCmdStoredProc

I then append a bunch of parameters, including Parameter(0) for the return value, parameters 1-5, which are input parameters and parameter(6) which is the output parameter.

I then run the Sproc using this line which is using a recordset object to run the command object configured previously:

rs.Open cmd, , adOpenForwardOnly, adLockReadOnly, adCmdStoredProc

I tried the above w/ Server-Side and Client-Side cursors.

After this statement, when I try to access the recordset, it works. But I can't access cmd.parameters(0) or cmd.Parameters(6) which are the Return Value and Output Parameters respectively. I CAN access parameters 1-5 which are my input parameters. they work fine.

Hope this is a clue...Thanks again!

 

[Sameal]

First of all I got mine fixed. It is returning recordsets, and return values now. So that being said I think it may be in your code somewhere. Can you post both your VB code and the Sproc that your using? I will run through them and see how they differ from mine. Right off I know I used the Execute Method of the Command object rather then the Open method of the Recordset, but I'm not to sure if that would cause it to not work. Below is the code i just did which worked...

---Beginning of VB Code---
Public Function EmployeeSelectByID(ByRef rs As ADODB.Recordset, intID As Integer) As Long
On Error GoTo Err_EmployeeSelectByID

'Use the global ADO command object.
Set g_objCmd = New ADODB.Command

'Setup the command object.
Set g_objCmd.ActiveConnection = g_objConn
g_objCmd.CommandText = "procEmployeeSelectByID"
g_objCmd.CommandType = adCmdStoredProc
g_objConn.CursorLocation = adUseClient

'Append the parameters into the command object.
g_objCmd.Parameters.Append g_objCmd.CreateParameter("Return", adInteger, _
adParamReturnValue, 0)
g_objCmd.Parameters.Append g_objCmd.CreateParameter("EmployeeID", adInteger, _
adParamInput, , intID)

'Now execute our command and return recordset result.
Set rs = g_objCmd.Execute

'Set our return value.
EmployeeSelectByID = g_objCmd.Parameters("Return&quot

Exit_EmployeeSelectByID:

Exit Function

Err_EmployeeSelectByID:

LogError "modRead.EmployeeSelectByID()"

End Function
---End Code---

---Begin Stored Procedure---
CREATE PROCEDURE procEmployeeSelectByID

@EmployeeID integer

AS

--Declare the output variable
DECLARE @Return int

--Get all the records
SELECT * FROM tblEmployee
WHERE EmployeeID = @EmployeeID

--Get the RecordCount so we can return it to the application
SET @Return = @@rowcount

--Look for errors
IF @@error > 0
SELECT @Return = -1
RETURN @Return
GO
---End Sproc---

 

[webuser]

Thanks! There was nothing specific that i found but as I looked through my code, I realized that after I started using CursorLocation of client, i checked the wrong parameter, so in fact the code is working now that I am using the Client-Side Cursor. But it appears like I don't have to close the recordset to get at the values now. Is this accurate? Also, I always have a problem determining when i should use Client-Side vs. Server-Side cursors. Why would I want to use Server-Side? What are the advantages?

 

[cmmrfrds]

It seems you only need to close the recordset object, when using server side cursors, to get at the output parameters.

One advantage of a client side cursor for web applications is you don't need to keep the recordset connection to the server open once the recordset is returned to the client. You can close the recordset connection and do any operation you want on the recordset even maintenance. If you are doing maintenance on the records, when finished the connection must be reestablished and a batch update done, which will update the database.

example
set rs.ActiveConnection = nothing
'update records in the app or ASP
rs.ActiveConnection = cn 'the connection object
rs.UpdateBatch

 

[webuser]

What about in a Windows App (Like Access or VB)...Why would I choose a Server-Side connection over a Client-Side?