return a value from SQL server SP back to .net

I have a SP code:
select 'nothing' from tableA where userID = '123'
if @.@.rowcount = 0
return 0
else
return 1

.net code:
Dim myConnection As New SqlConnection("server=(local);database=pubs;Trusted_Connection=yes")
Dim myCommand As New SqlCommand("StoreP", myConnection)

myCommand.Connection.Open()
Returnvalue = myCommand.ExecuteNonQuery()
myCommand.Connection.Close()

Returnvalue shows -1, it doesnt show the return value from SP. how do I fix this problem? thanksHi,

The return value from the ExecuteNonQuery returns the number of rows affected by the query, not the return value from the stored procedure. For a SELECT statement like you're using, it always returns -1.

To get the return value, you have to add a parameter to the ADO.NET command object, and set its Direction property to ReturnValue. Something like this:

myCommand.Parameters.Add("@.RetVal", SqlType.Integer).Direction = _
 ParameterDirection.ReturnValue

Then you can read the value after you run the query:

Dim i as Int32 = myCommand.Parameters("@.RetVal").Value

I've typed the code from memory, so it may need some tweaking.

Don|||I still have two questions
1. If I want to return 2 value from SP to asp.net, how do I do?
2. when I want to insert same value into PK twice in SP, it will give me a error message 2627, and the code break. How do I do to let SP return me an error message WITHOUT hanging the code. In other word, I am trying to let SP return the error message, but I dont want the asp.net web page to stop.

Thank you|||Hi,

1. If I want to return 2 value from SP to asp.net, how do I do?

Then use output parameters. You can define as many of those as you want. For them, use ParameterDirection.Output for the Direction property.

2. when I want to insert same value into PK twice in SP, it will give me a error message 2627, and the code break. How do I do to let SP return me an error message WITHOUT hanging the code. In other word, I am trying to let SP return the error message, but I dont want the asp.net web page to stop.

Probably the best way is to raise an error from the SP using the RAISERROR statement. That will generate a SqlException that you can catch and handle in your page.

Another way is to return one or more output parameters from the SP, one for an error number and another for a message. I don't like this option because it's more work and doesn't hook into the natural exception infrastructure of .NET.

Don|||Can you tell me how exactly you do it? I try parameterdirection.output it gives me an error "too many argument specified." how do I code in SP to return 2 values? thank you|||It sounds like you haven't added the output parameters to the stored procedure, right? You have to do that as well as add the ADO.NET code.

Post the complete sp definition and we'll help you make the changes.

Don|||the SP code is

CREATE PROCEDURE test @.aaa as varchar(10) output, @.bbb as varchar(10) output AS
select @.aaa = '111'
select @.bbb = '222'
return
GO

asp.net code is

Dim objCOmmand As New SqlCommand(strSQL, objConnection)
objConnection.Open()
objCOmmand.CommandType = CommandType.StoredProcedure
objCOmmand.Parameters.Add(New SqlParameter("@.aaa", SqlDbType.VarChar))
objCOmmand.Parameters.Add("@.aaa", SqlDbType.VarChar).Direction = ParameterDirection.ReturnValue
objCOmmand.Parameters("@.aaa").Value = "aaa"
objCOmmand.Parameters.Add(New SqlParameter("@.bbb", SqlDbType.VarChar))
objCOmmand.Parameters.Add("@.bbb", SqlDbType.VarChar).Direction = ParameterDirection.ReturnValue
objCOmmand.Parameters("@.bbb").Value = "bbb"
objCOmmand.ExecuteNonQuery()
Label1.Text = objCOmmand.Parameters("@.aaa").Value
Label2.Text = objCOmmand.Parameters("@.bbb").Value
objConnection.Close()

after SP I should get 111 in stead of aaa in @.aaa and 222 instead of bbb in @.bbb, but I get aaa and bbb in the result. how do I get the value return from SP?|||Your are not using the correct ParameterDirection. ReturnValue is solely to return the value that appears after the RETURN keyword in your stored procedure. Valid ParameterDirection values for stored procedure parameters are:
Input
Output
InputOutput

In your case, you should be using InputOutput for @.aaa and @.bbb since you are supplying data to the stored procedure (input) and are new receiving data back from the stored procedure (output).

Terri|||I try:

objCOmmand.Parameters.Add("@.bbb", SqlDbType.VarChar).Direction = ParameterDirection.InputOutput

but it gives me an error:

Parameter 1: '@.aaa' of type: String, the property Size has an invalid size: 0

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: Parameter 1: '@.aaa' of type: String, the property Size has an invalid size: 0|||Since you are using a VarChar datatype, you need to specify the length. And I don't know if it matters, but I usually take 2 lines to add a parameter and set the direction:

objCommand.Parameters.Add("@.aaa", SqlDbType.VarChar, 10)
objCommand.Parameters("@.aaa").Direction=ParameterDirection.InputOutput
objCommand.Parameters.Add("@.bbb", SqlDbType.VarChar, 10)
objCommand.Parameters("@.bbb").Direction=ParameterDirection.InputOutput

Terri