Catching return values of a SP

I have calling a stored procedure that returns two values, and I want to catch these values and to store them into a variable.

Here is a piece of my SP inside SQL Server that shows the returned values:

…
SELECT @.Id = SCOPE_IDENTITY()
SELECT @.Id AS user_id
SELECT 1 AS Value
END
GO

In my aspx page I am trying to call the first value like this:

Dim nID
CmdInsert.Parameters.Add(New SqlParameter("@.RETURN_VALUE", SqlDbType.bigint, 8, "user_id"))
 CmdInsert.Parameters("@.RETURN_VALUE").Direction = ParameterDirection.ReturnValue
CmdInsert.Parameters("@.RETURN_VALUE").Value = nID

And to check if the right value is returned I use:

strConnection.open()
 cmdInsert.ExecuteNonQuery
 'Set the value of a textbox
 ident.text = nID
 strConnection.close()

But now no value appears in the textbox, How can I achieve it? What is wrong?You are sort of combining a few different approaches to solving this problem. Since only one ReturnValue can be returned from a stored procedure and you need 2 values, that approach won't work. And since you only have 2 values, I think that you should use OUTPUT parameters.

The stored procedure would look like this:

CREATE PROCEDURE
 myProcedure
AS
 @.myInput1 varchar(50),
 @.myInput2 varchar(50),
 @.myOutput1 bigint OUTPUT,
 @.myOutput2 bigint OUTPUT
INSERT <etc etc
SET @.myOutput1 = SCOPE_IDENTITY
SET @.myOutput2 = 2

Your aspx page code would look like this:

CmdInsert.Parameters.Add("@.myOutput1", SqlDbType.bigint)
CmdInsert.Parameters("@.myOutput1").Direction = ParameterDirection.Output
CmdInsert.Parameters.Add("@.myOutput2", SqlDbType.bigint)
CmdInsert.Parameters("@.myOutput2").Direction = ParameterDirection.Output
strConnection.open()
cmdInsert.ExecuteNonQuery
'Set the value of a textbox
ident.text = CmdInsert("@.myOutput1")
strConnection.close()

Terri|||I have followed all your steps, and now this error message appears:

BC30367: Class 'System.Data.SqlClient.SqlCommand' cannot be indexed because it has no default property.

What does it mean?|||Sorry, the line afected is this:

Line 105: ident.text = CmdInsert("@.Id")|||I'm the one who's sorry. The correct syntax for that line is:

ident.text = CmdInsert.Parameters("@.Id").Value

Terri|||i think its something like :

ident.text=convert.toint32(CmdInsert.Parameters("@.Id").Value)

hth|||Good! now runs fine

Thank you very much,
Cesar