Capture Return Value from Stored Procedure, Use Same in Code Behind Page

My stored procedure works and codes is working except I need to capture the return value from the stored procedure and use that value in my code behind page to indicate that a duplicate record entry was attempted. In my code behind file (VB) how would I capture the value "@.myERROR" then display in the label I have that a duplicate entry was attempted.

Stored Procedure
CREATE PROCEDURE dbo.usp_InsertNew
@.IDNumber nvarchar(25),
@.ID nvarchar(50),
@.LName varchar(50),
@.FName varchar(50)

AS

DECLARE @.myERROR int -- local @.@.ERROR
, @.myRowCount int --local @.@.rowcount
BEGIN
-- See if a contact with the same name and zip code exists
IF EXISTS (Select * FROM Info
WHERE ID = @.ID)

BEGIN
RETURN 1
END
ELSE
BEGIN TRAN

INSERT INTO Info(IDNumber, ID, LName,
FName) VALUES (@.IDNumber, @.ID, @.LName,
@.FName)
SELECT @.myERROR = @.@.ERROR, @.myRowCount = @.@.ROWCOUNT
If @.myERROR !=0 GOTO HANDLE_ERROR

COMMIT TRAN
RETURN 0

HANDLE_ERROR:
ROLLBACK TRAN
RETURN @.myERROR

END
GO

asp.net page
<asp:SqlDataSource ID="ContactDetailDS" runat="server" ConnectionString="<%$ ConnectionStrings:EssPerLisCS %>"
SelectCommand="SELECT * FROM TABLE_One"

UpdateCommand="UPDATE TABLE_One WHERE ID = @.ID"

InsertCommand="usp_InsertNew" InsertCommandType="StoredProcedure">

<SelectParameters>
<asp:ControlParameter ControlID="GridView1" Name="ID" PropertyName="SelectedValue" />
</SelectParameters>

</asp:SqlDataSource>

You have to declare it as output parameter in your stored procedure

Stored Procedure
CREATE PROCEDURE dbo.usp_InsertNew
@.IDNumber nvarchar(25),
@.ID nvarchar(50),
@.LName varchar(50),
@.FName varchar(50),
@.myERROR int OUTPUT

and just read value of this parameter after you close connection in which you call you SP.

remember to set parameter type as INPUTOUTPUT or OUTPUT when you define parameter in you VB code.

|||

Hi,

I included "@.myERROR" in the stored procedure, but have no idea where or how to read its value. As far as closing the connection in which I call the sp; my sp is called within the SqlDataSource, so how would I read/write the Return Value.

Thank you.

Ayomide