|
 |
aspx_beginners thread: SP Return Value
Message #1 by Terrence Joubert <Terrence@v...> on Fri, 6 Jul 2001 11:36:39 +0400
|
|
Hi,
Is there any way I can catch the return value(an integer) from a stored
procedure.
Thanks
Terrence
Message #2 by "Alex Lowe" <alowe@s...> on Fri, 6 Jul 2001 07:35:22 -0400
|
|
Terrence,
Here is an example:
<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script language="VB" runat=server>
Sub Page_Load(Sender as Object, E as EventArgs)
Dim myConn As SqlConnection = new SqlConnection("Data Source=test;
uid=test pwd=test; Initial Catalog=Northwind")
Dim myCommand As SqlCommand = new SqlCommand ("Select
@CompanyName=CompanyName From Customers Where CustomerID='ALFKI'", myConn)
Dim workParam As SqlParameter = Nothing
workParam = myCommand.Parameters.Add(new SQLParameter("@CompanyName",
SqlDbType.NChar, 40))
workParam.Direction = ParameterDirection.Output
Try
myConn.Open()
myCommand.ExecuteNonQuery()
CompanyNameLabel.Text = myCommand.Parameters("@CompanyName").Value
myConn.Close()
Catch myError As Exception
Response.Write(myError.ToString())
End Try
End Sub
</script>
<asp:Label id="CompanyNameLabel" runat="server" />
hth,
Alex - Asplists.com Moderator
http://www.aspalliance.com/aldotnet
http://www.asp-grandrapids.net
----- Original Message -----
From: "Terrence Joubert" <Terrence@v...>
To: "aspx_beginners" <aspx_beginners@p...>
Sent: Friday, July 06, 2001 3:36 AM
Subject: [aspx_beginners] SP Return Value
Hi,
Is there any way I can catch the return value(an integer) from a stored
procedure.
Thanks
Terrence
Message #3 by Terrence Joubert <Terrence@v...> on Fri, 6 Jul 2001 15:43:24 +0400
|
|
Hi Alex,
Thanks. I understand it. The problem is this, the return value is not from a
field in the recordset, it a number that the Stored Procedure returns to
identify whether its actions succeeded or not. The stored procedure code
looks like this:
IF (thisActionWasSuccessful) THEN
RETURN @SUCCESS
ELSE
RETURN @ERROR
END IF
I want to somehow catch either @SUCCESS or @ERROR from within my ADO code.
The stored procedure does not return a recordset, just a single value, that
the ASP Page uses to display notification messages to the user.
Any more great ideas are welcomed. Many Thanks
Terrence
-----Original Message-----
From: Alex Lowe [mailto:alowe@s...]
Sent: Friday, 06 July, 2001 3:35 PM
To: aspx_beginners
Subject: [aspx_beginners] Re: SP Return Value
Terrence,
Here is an example:
<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script language="VB" runat=server>
Sub Page_Load(Sender as Object, E as EventArgs)
Dim myConn As SqlConnection = new SqlConnection("Data Source=test;
uid=test pwd=test; Initial Catalog=Northwind")
Dim myCommand As SqlCommand = new SqlCommand ("Select
@CompanyName=CompanyName From Customers Where CustomerID='ALFKI'", myConn)
Dim workParam As SqlParameter = Nothing
workParam = myCommand.Parameters.Add(new SQLParameter("@CompanyName",
SqlDbType.NChar, 40))
workParam.Direction = ParameterDirection.Output
Try
myConn.Open()
myCommand.ExecuteNonQuery()
CompanyNameLabel.Text = myCommand.Parameters("@CompanyName").Value
myConn.Close()
Catch myError As Exception
Response.Write(myError.ToString())
End Try
End Sub
</script>
<asp:Label id="CompanyNameLabel" runat="server" />
hth,
Alex - Asplists.com Moderator
http://www.aspalliance.com/aldotnet
http://www.asp-grandrapids.net
----- Original Message -----
From: "Terrence Joubert" <Terrence@v...>
To: "aspx_beginners" <aspx_beginners@p...>
Sent: Friday, July 06, 2001 3:36 AM
Subject: [aspx_beginners] SP Return Value
Hi,
Is there any way I can catch the return value(an integer) from a stored
procedure.
Thanks
Terrence
Message #4 by "Max" <max@b...> on Fri, 6 Jul 2001 09:02:38 -0600
|
|
You can use the following code in your stored procedure, after all sql
commands are executed, check for errors and return a status:
if @@error <> 0
begin
select @ReturnStatus =3D 1
end
else
begin
select @ReturnStatus =3D 0
end
Then, on the ASP/ADO side, you can check the ReturnStatus as you would
any other recordset field.
hope this helps.
Derek
BrooklineTechnologies.com
-----Original Message-----
From: Terrence Joubert [mailto:Terrence@v...]
Sent: Friday, July 06, 2001 5:43 AM
To: aspx_beginners
Subject: [aspx_beginners] Re: SP Return Value
Hi Alex,
Thanks. I understand it. The problem is this, the return value is not
from a
field in the recordset, it a number that the Stored Procedure returns to
identify whether its actions succeeded or not. The stored procedure code
looks like this:
IF (thisActionWasSuccessful) THEN
RETURN @SUCCESS
ELSE
RETURN @ERROR
END IF
I want to somehow catch either @SUCCESS or @ERROR from within my ADO
code.
The stored procedure does not return a recordset, just a single value,
that
the ASP Page uses to display notification messages to the user.
Any more great ideas are welcomed. Many Thanks
Terrence
-----Original Message-----
From: Alex Lowe [mailto:alowe@s...]
Sent: Friday, 06 July, 2001 3:35 PM
To: aspx_beginners
Subject: [aspx_beginners] Re: SP Return Value
Terrence,
Here is an example:
<%@ Page Language=3D"VB" %>
<%@ Import Namespace=3D"System.Data" %>
<%@ Import Namespace=3D"System.Data.SqlClient" %>
<script language=3D"VB" runat=3Dserver>
Sub Page_Load(Sender as Object, E as EventArgs)
Dim myConn As SqlConnection =3D new SqlConnection("Data
Source=3Dtest;
uid=3Dtest pwd=3Dtest; Initial Catalog=3DNorthwind")
Dim myCommand As SqlCommand =3D new SqlCommand ("Select
@CompanyName=3DCompanyName From Customers Where CustomerID=3D'ALFKI'",
myConn)
Dim workParam As SqlParameter =3D Nothing
workParam =3D myCommand.Parameters.Add(new
SQLParameter("@CompanyName",
SqlDbType.NChar, 40))
workParam.Direction =3D ParameterDirection.Output
Try
myConn.Open()
myCommand.ExecuteNonQuery()
CompanyNameLabel.Text =3D
myCommand.Parameters("@CompanyName").Value
myConn.Close()
Catch myError As Exception
Response.Write(myError.ToString())
End Try
End Sub
</script>
<asp:Label id=3D"CompanyNameLabel" runat=3D"server" />
hth,
Alex - Asplists.com Moderator
http://www.aspalliance.com/aldotnet
http://www.asp-grandrapids.net
----- Original Message -----
From: "Terrence Joubert" <Terrence@v...>
To: "aspx_beginners" <aspx_beginners@p...>
Sent: Friday, July 06, 2001 3:36 AM
Subject: [aspx_beginners] SP Return Value
Hi,
Is there any way I can catch the return value(an integer) from a stored
procedure.
Thanks
Terrence
Message #5 by Suddhasheel Ghosh <suddhasheel@y...> on Sat, 7 Jul 2001 02:18:28 -0700 (PDT)
|
|
Answer to the question
Hi,
Is there any way I can catch the return value(an
integer) from a stored
procedure.
Thanks
Terrence
DEAR MR TERRENCE,
IT MIGHT BE POSSIBLE THAT YOU MIGHT HAVE DECLARED YOUR
PROCEDURE AS
SUB PROCEDURENAME
-- PROCEDURE STATEMENTS
END SUB
IF YOU WANT TO CATCH A RETURN VALUE THEN THERE ARE TWO
METHODS
(1) DECLARE A GLOBAL VARIABLE LIKE
dim globalfoo
AND IN THE PROCEDURE ASSIGN
globalfoo = value
(2) DECLARE YOU PROCEDURE AS A FUNCTION
-----------------
function procedurename
--- PROCEDURE STATEMENTS
procedurename = thereturnvalue
end function
-----------------------
HERE YOU WILL HAVE TO CHANGE THE VARIABLES
ACCORDINGLY...
I THINK THE EARLIER ANSWERs YOU RECIEVED ARE TOO
TEDIOUS...
REGDS,
SUDDHASHEEL
suddhasheel@y...
Message #6 by "Alex Lowe" <alowe@s...> on Mon, 9 Jul 2001 10:12:54 -0400
|
|
Terrence,
Sorry I never got a chance to respond on Friday. My previous example
modified to pick up a return value:
<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script language="VB" runat=server>
Sub Page_Load(Sender as Object, E as EventArgs)
Dim myConn As SqlConnection = new SqlConnection("Data
Source=test;uid=test pwd=test; Initial Catalog=Northwind")
Dim myCommand As SqlCommand = new SqlCommand ("Select
@CompanyName=CompanyName From Customers Where CustomerID='ALFKI'", myConn)
Dim workParam As SqlParameter = Nothing
workParam = myCommand.Parameters.Add(new SQLParameter("RETURN_VALUE",
SqlDbType.Integer))
workParam.Direction = ParameterDirection.ReturnValue
Try
myConn.Open()
myCommand.ExecuteNonQuery()
CompanyNameLabel.Text = myCommand.Parameters("RETURN_VALUE").Value
myConn.Close()
Catch myError As Exception
Response.Write(myError.ToString())
End Try
End Sub
</script>
<asp:Label id="CompanyNameLabel" runat="server" />
hth,
Alex - Asplists.com Moderator
http://www.aspalliance.com/aldotnet
http://www.asp-grandrapids.net
----- Original Message -----
From: "Terrence Joubert" <Terrence@v...>
To: "aspx_beginners" <aspx_beginners@p...>
Sent: Friday, July 06, 2001 7:43 AM
Subject: [aspx_beginners] Re: SP Return Value
| Hi Alex,
|
| Thanks. I understand it. The problem is this, the return value is not from
a
| field in the recordset, it a number that the Stored Procedure returns to
| identify whether its actions succeeded or not. The stored procedure code
| looks like this:
|
| IF (thisActionWasSuccessful) THEN
| RETURN @SUCCESS
| ELSE
| RETURN @ERROR
| END IF
|
| I want to somehow catch either @SUCCESS or @ERROR from within my ADO code.
| The stored procedure does not return a recordset, just a single value,
that
| the ASP Page uses to display notification messages to the user.
|
| Any more great ideas are welcomed. Many Thanks
|
| Terrence
|
$subst('Email.Unsub')
|
|
|
|
View Cart
