Catching return values of an SP

[cesark]

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:
[code:1:6ec0af7d1c]
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
[/code:1:6ec0af7d1c]

And to check if the right value is returned I use:
[code:1:6ec0af7d1c]
strConnection.open()
cmdInsert.ExecuteNonQuery
'Set the value of a textbox
ident.text = nID
strConnection.close()
[/code:1:6ec0af7d1c]


But no value appears in the textbox, How can I achieve it? What is
wrong?
[/code]

 

[Michael Ramey]

Have you declared your parameter in your stored procedure as output?

create proc myprocname(
@myReturnValue int output
) as

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:
[code:1:6ec0af7d1c]
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
[/code:1:6ec0af7d1c]

And to check if the right value is returned I use:
[code:1:6ec0af7d1c]
strConnection.open()
cmdInsert.ExecuteNonQuery
'Set the value of a textbox
ident.text = nID
strConnection.close()
[/code:1:6ec0af7d1c]


But no value appears in the textbox, How can I achieve it? What is
wrong?
[/code]

 

[Jon]

Hi,
you'd probably want something like this
In your sp
Create Procedure Whatever
@id int OUTPUT
as
.....
select @id = scope_identity()

and then to get the value
Dim id As SqlParameter = New SqlParameter("@id", SqlDbType.Int, 4)
id.Direction = ParameterDirection.Output
cmdInsert.Parameters.Add(id)
cmdInsert.executenonquery()
response.write("new id is " & cmdInsert.Parameters("@ID").Value)

Jon

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:
[code:1:6ec0af7d1c]
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
[/code:1:6ec0af7d1c]

And to check if the right value is returned I use:
[code:1:6ec0af7d1c]
strConnection.open()
cmdInsert.ExecuteNonQuery
'Set the value of a textbox
ident.text = nID
strConnection.close()
[/code:1:6ec0af7d1c]


But no value appears in the textbox, How can I achieve it? What is
wrong?
[/code]