newbie problem with stored procedure return value

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

microsoft - 09 Aug 2007 16:00 GMT

For the life of me I cannot get the following stored procedure to return a
value in a classic asp vbscript page. Can someone please help a poor soul?

Here is the SP that returns the next order number available for use:

CREATE PROCEDURE ap_GetOrderNumber2
AS
SET NOCOUNT ON
DECLARE @nextOrder integer

EXEC @nextOrder = [master].[dbo].[xp_NextOrderNum]

RETURN @nextOrder

GO

I just need to get this return value so that I can insert it into another
database. I've tried a combo of code to no avail.

Any help would be appreciated!

Reply to this Message

Bob Barrows [MVP] - 09 Aug 2007 17:23 GMT

> For the life of me I cannot get the following stored procedure to
> return a value in a classic asp vbscript page. Can someone please
[quoted text clipped - 17 lines]
>
> Any help would be appreciated!

You need to use an explicit Command object for this:

dim cn, cmd, nextorder
Const adExecuteNoRecords = &H00000080
Const adCmdStoredProc = &H0004
Const adParamReturnValue = &H0004
Const adInteger = 3
set cn=createobject("adodb.connection")
cn.open "<your connection string>"
set cmd=createobject("adodb.command")
with cmd
.commandtext="ap_GetOrderNumber2"
.CommandType=adCmdStoredProc
set .activeconnection = cn
.Parameters.Append .CreateParameter("RETURN_VALUE", _
adInteger,adParamReturnValue)
.execute
nextorder = .Parameters(0).value
end with

Signature

Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Reply to this Message

ASP Newman - 09 Aug 2007 19:03 GMT

Thanks so much!

>> For the life of me I cannot get the following stored procedure to
>> return a value in a classic asp vbscript page. Can someone please
[quoted text clipped - 36 lines]
> nextorder = .Parameters(0).value
> end with

Reply to this Message

Bob Barrows [MVP] - 09 Aug 2007 17:25 GMT

> For the life of me I cannot get the following stored procedure to
> return a value in a classic asp vbscript page.

Correction
You need to use an explicit Command object for this:

dim cn, cmd, nextorder
Const adExecuteNoRecords = &H00000080
Const adCmdStoredProc = &H0004
Const adParamReturnValue = &H0004
Const adInteger = 3
set cn=createobject("adodb.connection")
cn.open "<your connection string>"
set cmd=createobject("adodb.command")
with cmd
.commandtext="ap_GetOrderNumber2"
.CommandType=adCmdStoredProc
set .activeconnection = cn
.Parameters.Append .CreateParameter("RETURN_VALUE", _
adInteger,adParamReturnValue)
.execute ,,adExecuteNoRecords
nextorder = .Parameters(0).value
end with

Signature

Reply to this Message

Webmaster Forum / ASP / Database Access / August 2007

newbie problem with stored procedure return value