newbie problem with stored procedure return value

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!

Re: newbie problem with stored procedure return value

microsoft wrote:
> 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!
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 ""
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

--
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.

Re: newbie problem with stored procedure return value

microsoft wrote:
> 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 ""
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
--
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.

Re: newbie problem with stored procedure return value

Thanks so much!

"Bob Barrows [MVP]" wrote in message
news:%23NVEkGq2HHA.748@TK2MSFTNGP04.phx.gbl...
> microsoft wrote:
>> 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!
> 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 ""
> 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
>
> --
> 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.
>
>