stored proc RETURN value in ASP

[ben h]

I have a stored proc which checks for errors, and returns the error value as
a return code/value. how can i 'read' this from my asp code? Should I return
it as a recordset instead?

Here's how it currently is:-
SP snippet:
SET @Err = @@ERROR
RETURN @Err

ASP:
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open MM_risknet_STRING
sql = "EXEC proc @var=" & var
conn.Execute sql,,129

[Bob Barrows [MVP]]

ben h wrote:

> I have a stored proc which checks for errors, and returns the error
> value as a return code/value. how can i 'read' this from my asp code?
> Should I return it as a recordset instead?
>
> Here's how it currently is:-
> SP snippet:
> SET @Err = @@ERROR
> RETURN @Err
>
> ASP:
> Set conn = Server.CreateObject("ADODB.Connection")
> conn.Open MM_risknet_STRING
> sql = "EXEC proc @var=" & var
> conn.Execute sql,,129

You need to use an explicit Command object to retrieve this value (as well
as the values that would be returned if you used output
parameters.).Unfortunately, the most efficient way to utilize a Command
object's Parameters collection is also the trickiest to write and debug,
which has led to the creation of command object code generators such as the
one that i wrote that is available here:
[url]http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links.asp&c=&a=clear[/url]

The asp page will generate the code to create and append the Parameter
objects to the Command object's Parameters collection. It assumes a
previously opened connection object (the source code is of course included
so you can change the name I used for the connection and command object
variables if you wish). After the Execute statement, you would read the
return value by :

returnvalue = cmd.Parameters("RETURN_VALUE").value

The generator also assumes that you have included the definitions of all the
ADO constants, either by including the adovbs.inc file, or by using the
METADATA tag to reference the ADO type library as described here:


HTH,
Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Bob Barrows [MVP]]

Oops - accidently hit the Send button ...
ben h wrote:

> I have a stored proc which checks for errors, and returns the error
> value as a return code/value. how can i 'read' this from my asp code?
> Should I return it as a recordset instead?
>
> Here's how it currently is:-
> SP snippet:
> SET @Err = @@ERROR
> RETURN @Err
>
> ASP:
> Set conn = Server.CreateObject("ADODB.Connection")
> conn.Open MM_risknet_STRING
> sql = "EXEC proc @var=" & var
> conn.Execute sql,,129

You need to use an explicit Command object to retrieve this value (as well
as the values that would be returned if you used output
parameters). Unfortunately, the most efficient way to utilize a Command
object's Parameters collection is also the trickiest to write and debug,
which has led to the creation of command object code generators such as the
one that i wrote that is available here:
[url]http://www.thrasherwebdesign.com/index.asp?pi=links&hp=links.asp&c=&a=clear[/url]

The asp page will generate the code to create and append the Parameter
objects to the Command object's Parameters collection. It assumes a
previously opened connection object (the source code is of course included
so you can change the name I used for the connection and command object
variables if you wish). After the Execute statement, you would read the
return value by :

returnvalue = cmd.Parameters("RETURN_VALUE").value

The generator also assumes that you have included the definitions of all the
ADO constants, either by #including the adovbs.inc file, or by using the
METADATA tag to reference the ADO type library as described here:

[url]http://www.aspfaq.com/show.asp?id=2112[/url]

HTH,
Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"