getting a return value from a SELECT sproc in ADO2.5

Leon,

cheers for this but there's a good reason for using return values & output parameters (for a discussion I had with angelIII about output params a while back, see here - also contains a link to a good resource on the subject: http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21495083.html).

Either way, the sproc uses a return value (I use the same db for an ASP.NET app) so I couldn't change it if I wanted to.

Suffice to say, I know I'm meant to close the recordset down before I can access the return value but I've tried looping through all records, closing and finally killing the recordset; nothing makes the return value come to life if the sproc returns records. Luckily, if the security check fails the sproc returns before it has a chance to get to the SELECT bit so at least I can detect this.

Cheers anyway,

s46.

>for a discussion I had with angelIII

Yes, you are creating a Command object instead of a Recordset object so to me it is a wash.

If you are returning a one or two values from a store procedure then the amount of meta data returned is minimal and from the VB point of view returning a recordset is a lot more vercital since your code will not depend on the type of data returned. You will also not need to recode if all of the sudden you need to return more than one value.

BTW, i think your issue has to do with you using adUseServer type cursor.  Try switching to client.

Leon

You cannot access your output parameters (that includes the Return parameter) until the recordset is closed.

Leon,

I'm not specifically using nay sort of cursor - I really should know this but where is that specified (been a while since I ;ve done this!)

acperkins...
worked that bit out (same as in .NET) but ...
set rst = command.execute
do until rst.eof
    ...
loop
rst.close
set rst = nothing
exit sub

I've tried getting the value of the param at every possible point here but no luck. Any thoughts?

cheers,
s46.

>I'm not specifically using nay sort of cursor - I really should know this but where is that specified (been a while since I ;ve done this!)

If you do not specify it defaults to adUseServer.  

rs.CursorLocation = adUseClient

Leon

it is like this:

set rst = command.execute
do until rst.eof
    ...
loop
rst.close
set rst = nothing
' Now you can get the Return value
mlngReturnValue = command.parameters("@ReturnValue").Value

acperkins,

that is exactly what I thought. Just tried a test proc to see what happens (the app uses a class I've built to combine the functions of the command and connection objects; I thought something in that might be interfering...). Paraphrased code follows...

Public Sub test()

Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim cnn As New ADODB.Connection
Dim prm As ADODB.Parameter

cnn.ConnectionString = "Provider=sqloledb;Data Source=****;Initial Catalog=****;User Id=****;Password=****"
cnn.Open

Set cmd = New ADODB.Command
cmd.ActiveConnection = cnn

cmd.CommandType = adCmdStoredProc
cmd.CommandText = "spom_Person_Search"


Set prm = cmd.CreateParameter("@RetVal", adInteger, adParamReturnValue)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@LoginID", adInteger, adParamInput, , 10)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@PersonID", adInteger, adParamInput, , 0)
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@CustomerCode", adVarChar, adParamInput, 20, "%")
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@LastName", adVarChar, adParamInput, 50, "%")
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@FirstName", adVarChar, adParamInput, 50, "James")
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@PostCode", adVarChar, adParamInput, 10, "%")
cmd.Parameters.Append prm
Set prm = cmd.CreateParameter("@ContactDetail", adVarChar, adParamInput, 100, "%")
cmd.Parameters.Append prm

Set rst = cmd.Execute

Do Until rst.EOF
    Debug.Print rst(0)
    rst.MoveNext
   
Loop

'tried the following section using cmd.Parameters(0) too - should be the same thing.
Debug.Print "RetVal1: " & cmd.Parameters("@RetVal") & "|"

rst.Close
Debug.Print "RetVal2: " & cmd.Parameters("@RetVal") & "|"

Set rst = Nothing
Debug.Print "RetVal3: " & cmd.Parameters("@RetVal") & "|"

cnn.Close
Debug.Print "RetVal4: " & cmd.Parameters("@RetVal") & "|"

Set cmd = Nothing
Set cnn = Nothing
Set prm = Nothing

End Sub


When I run it, this is what I get...
 3129              -> this is correct
RetVal1: |
RetVal2: |
RetVal3: |
RetVal4: |



For information, in Profiler this execution appears as...
exec spom_Person_Search 10, 0, '%', '%', 'James', '%', '%'

The sproc is a bit convoluted and there's little point showing the code; suffice to say that running it from QA with the following code...
DECLARE @RC int
exec @RC = spom_Person_Search 10, 0, '%', '%', 'James', '%', '%'
SELECT @RC

...gives two recordsets. This first is the row with field(0) = 3129 as expected; the second is the value of @RC (= 1 as it should be; I use positive return codes for success states; negative for errors and assume that 0 means I forgot to return anything!).


I do have a work-around now (if it fails for any reason, no recordset is generated and I get the negative return code back so I can at least detect errors) but this is really starting to bug me.

Cheers for the time,

s46

Leon,

in the above code, where would the rst.CursorLocation = acClient go?

Before the set rst = cms.execute, the rst doesn't exist so I can't set the property; after the .execute the connection is still open and if I close the connection to set it, the loop fails. Getting to the point where I'm willing to try anything so all suggestions appreciated;).

*** beginning to wish I'd never agreed to do this!!! ***

Cheers,

s46.

Ah....

Knew it would be something simple. Completely forgot about that though.

Now to work out how to get the return value BEFORE I try to loop through the recordset ;)

Many thanks,

s46.

>>Now to work out how to get the return value BEFORE I try to loop through the recordset<<
You could include it in your recordset.