|
 |
asp_databases thread: Accessing Return Values from SQL Server 2000 Stored Procedures
Message #1 by "Tom Garrison" <tom.garrison@e...> on Thu, 27 Sep 2001 21:46:23
|
|
I have been having a fair amount of problems with this and have tried
several examples but can not get it to work. I am running a SQL Server
2000 DB, with the following stored procedure:
Create Procedure get_user
As
BEGIN
select * from user
return @@ERROR
END
I want to get the @@ERROR code and print it to the screen (SQL Server will
return 0 if no error, or a number if an error occurred). I wrote the
following ASP Code:
set objComm = Server.CreateObject ("ADODB.Command")
With objComm
.ActiveConnection = db
.CommandText = "get_user"
.CommandType = adCmdStoredProc
set p = .Parameters
p.Append .CreateParameter("returnStatus",adInteger,adParamReturnValue)
Set rs = .Execute
End With
status = p("returnStatus")
Response.Write "Status is " & status & "<br>"
When I run this page I always get status as empty. How do I get the
Return Value? I have even tried to force the return value to 0, still
nothing. Any ideas?
Tom Garrison
Message #2 by "Ken Schaefer" <ken@a...> on Fri, 28 Sep 2001 12:11:31 +1000
|
|
You need to close the recordset before you can access the parameters
collection if you are using server-side cursors.
<%
Set objRS = objCommand.Execute
If not objRS.EOF then arrUsers = objRS.GetRows
ObjDispose(objRS, True, True)
intReturnValue = objCommand.Parameters("@ReturnValue").Value
%>
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Tom Garrison" <tom.garrison@e...>
To: "ASP Databases" <asp_databases@p...>
Sent: Thursday, September 27, 2001 9:46 PM
Subject: [asp_databases] Accessing Return Values from SQL Server 2000 Stored
Procedures
: I have been having a fair amount of problems with this and have tried
: several examples but can not get it to work. I am running a SQL Server
: 2000 DB, with the following stored procedure:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Message #3 by "Tomm Matthis" <matthis@b...> on Fri, 28 Sep 2001 10:10:28 -0400
|
|
Try
status = p("returnStatus").value
or
status= p(0).value
The RETURN var is always under index 0 regardless of how you name it.
-- Tomm
> -----Original Message-----
> From: Tom Garrison [mailto:tom.garrison@e...]
> Sent: Thursday, September 27, 2001 9:46 PM
> To: ASP Databases
> Subject: [asp_databases] Accessing Return Values from SQL Server 2000
> Stored Procedures
>
>
> I have been having a fair amount of problems with this and have tried
> several examples but can not get it to work. I am running a SQL Server
> 2000 DB, with the following stored procedure:
>
> Create Procedure get_user
> As
> BEGIN
> select * from user
> return @@ERROR
> END
>
> I want to get the @@ERROR code and print it to the screen (SQL
> Server will
> return 0 if no error, or a number if an error occurred). I wrote the
> following ASP Code:
>
> set objComm = Server.CreateObject ("ADODB.Command")
> With objComm
> .ActiveConnection = db
> .CommandText = "get_user"
> .CommandType = adCmdStoredProc
> set p = .Parameters
> p.Append .CreateParameter("returnStatus",adInteger,adParamReturnValue)
> Set rs = .Execute
> End With
> status = p("returnStatus")
> Response.Write "Status is " & status & "<br>"
>
> When I run this page I always get status as empty. How do I get the
> Return Value? I have even tried to force the return value to 0, still
> nothing. Any ideas?
>
> Tom Garrison
>
> $subst('Email.Unsub')
>
Message #4 by "Garrison, Tom" <tom.garrison@e...> on Fri, 28 Sep 2001 09:38:49 -0500
|
|
Ahh, I did not know that!! Now it works. Thanks for the help Ken. This
has been driving me crazy.
Tom
-----Original Message-----
From: Ken Schaefer [mailto:ken@a...]
Sent: Thursday, September 27, 2001 9:12 PM
To: ASP Databases
Subject: [asp_databases] Re: Accessing Return Values from SQL Server
2000 Stored Procedures
You need to close the recordset before you can access the parameters
collection if you are using server-side cursors.
<%
Set objRS = objCommand.Execute
If not objRS.EOF then arrUsers = objRS.GetRows
ObjDispose(objRS, True, True)
intReturnValue = objCommand.Parameters("@ReturnValue").Value
%>
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Tom Garrison" <tom.garrison@e...>
To: "ASP Databases" <asp_databases@p...>
Sent: Thursday, September 27, 2001 9:46 PM
Subject: [asp_databases] Accessing Return Values from SQL Server 2000 Stored
Procedures
: I have been having a fair amount of problems with this and have tried
: several examples but can not get it to work. I am running a SQL Server
: 2000 DB, with the following stored procedure:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
$subst('Email.Unsub')
Message #5 by "Garrison, Tom" <tom.garrison@e...> on Fri, 28 Sep 2001 12:21:50 -0500
|
|
Still having a problem with this. I can only access a return value if no
error occurrs. If an error occurs I don't get anything back from the stored
procedure. Take this update for example:
CREATE PROCEDURE add_user
@in_User_name varchar(10),
@in_User_Lname varchar(30),
@in_User_Fname varchar(30)
AS
BEGIN
insert into user
(user_name,
user_lname,
user_fname)
values
(@in_User_name,
@in_User_Lname,
@in_User_Fname)
RETURN @@ERROR
END
I have username as a unique index so it can't accept duplicates. If I enter
a non-duplicate value for username @@ERROR returns a 0 (which means the add
worked), however if I add a duplicate value (which causes and SQL Error),
@@ERROR does not return anything. It supposed to return an integer error
code. I can't figure out what I am doing wrong. Here is my ASP Code.
<%
On Error Resume Next
dim objComm, rs, status, p
!Code that gets values
!
!
set objComm = Server.CreateObject ("ADODB.Command")
With objComm
.CommandText = "add_user"
.CommandType = adCmdStoredProc
Set p = .Parameters
p.Append
.CreateParameter("@ReturnValue",adInteger,adParamReturnValue,5)
p.Append .CreateParameter("@in_User_name",
adVarChar, adParamInput, 10)
p("@in_User_name").Value = uname
p.Append .CreateParameter("@in_User_Lname",
adVarChar, adParamInput, 30)
p("@in_User_Lname").Value = lname
p.Append .CreateParameter("@in_User_Fname",
adVarChar, adParamInput, 30)
p("@in_User_Fname").Value = fname
.ActiveConnection = db
End With
objComm.Execute
status = p(0).value
Response.Write "Status is " & status & "<br>"
Set objComm = Nothing
%>
Anyone have any ideas?
Tom Garrison
-----Original Message-----
From: Ken Schaefer [mailto:ken@a...]
Sent: Thursday, September 27, 2001 9:12 PM
To: ASP Databases
Subject: [asp_databases] Re: Accessing Return Values from SQL Server
2000 Stored Procedures
You need to close the recordset before you can access the parameters
collection if you are using server-side cursors.
<%
Set objRS = objCommand.Execute
If not objRS.EOF then arrUsers = objRS.GetRows
ObjDispose(objRS, True, True)
intReturnValue = objCommand.Parameters("@ReturnValue").Value
%>
Cheers
Ken
|
|
|
View Cart
