ASP: stored procedure return value and output params

Question

ASP: stored procedure return value and output params

Russell Shilling

SSCarpal Tunnel

Points: 4399
More actions
November 20, 2005 at 1:38 pm

#88562

I have an odd problem calling a stored procedure using ASP. Here's the code:
    objCmd.CommandText = "usp_query"
    objCmd.CommandType = adCmdStoredProc
    objCmd.ActiveConnection = cnnDB
    objCmd.Parameters.Append objCmd.CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue)
    objCmd.Parameters.Append objCmd.CreateParameter("@param1", adVarChar, adParamInput, 511, strParam1)
    Set rstClaims = objCmd.Execute
    intReturn = objCmd.Parameters("RETURN_VALUE").Value
I am not getting anything back into intReturn. As far as I can tell, it is blank. Not even a number. I had output parameters in the code, but I wasn't getting anything returned in those, either.
Does anyone see the obvious error in the code? It must be something simple, but I just don't see what is wrong.
So long, and thanks for all the fish,
Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

What is @param1? Did you mean param1?

Also, what happens if you execute the stored procedure in T-SQL with an OUTPUT parameter say through QA? Do you get a value back?

K. Brian Kelley
@kbriankelley

Russell Shilling SSCarpal Tunnel Points: 4399 More actions · Answer 2

The stored procedure works fine in QA. @param1 is the name of the parameter for the sp. The value gets sent to the sp correctly.

So long, and thanks for all the fish,

Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 3

Well, @param1 should probably be param1 without the @, but other than that, unless the database has case sensitivity, that follows the basic values. I think I used adParamOutput but you've already tried that. If you do a profiler trace (toggling all the error events), what do you see when this query executes from code?

K. Brian Kelley
@kbriankelley

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

I think you need to close the recordset before accessing the return value (or is that the connection, I can never remember that one).

Russell Shilling SSCarpal Tunnel Points: 4399 More actions · Answer 5

I have no idea why, but I started getting output parameters back this morning. I think my stored procedure was generating an error, which may have caused the parameters to NOT be returned.

Does this sound plausible? I'll have to check the ADO error in my code, I'd guess?

So long, and thanks for all the fish,

Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Make sure the so is working correctly before debugging in the vb code... that way it's easier to find the real cause of the problem.

Russell Shilling SSCarpal Tunnel Points: 4399 More actions · Answer 7

This is going to drive me nuts. As soon as I added a second output parameter, the values again failed to come across. I thought that, perhaps, there was something being cached on my computer, in PWS or somewhere, so I restarted IIS, then I rebooted and tried again. No joy. I'm still getting nothing back.

As before, everything works fine with QA. No errors, and the output params get the correct data from the procedure.

Next, I think I'll try moving the procedure and DSN to point to a different server, and see what happens.

So long, and thanks for all the fish,

Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3

Russell Shilling SSCarpal Tunnel Points: 4399 More actions · Answer 8

I found the solution in an article about VisualInterDev or something odd. The stored procedure would return a recordset when there was no error, but none if there was an error. I had to add:

While (Not rstClaims Is Nothing)

Set rstClaims = rstClaims.NextRecordset()

Wend

This would, apparently, get the return values recordset, if there was one preceding it. I knew it was something trivial and stupid that wasn't really documented anywhere.

So long, and thanks for all the fish,

Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3