stored procedure always returns nonzero

I'm missing something really simple here...in testing a sql 2005 sp in asp.net 2.0 I whittled it down to this and "myresult" always returns -1, whereas I would expect 0 as success.   WHY?

objConn.Open()
Dim myresult As Integer = 0
Dim strSQL As String = "uspTEST"
Dim objCmd As New SqlCommand(strSQL, objConn)
objCmd.CommandType = CommandType.StoredProcedure
myresult = objCmd.ExecuteNonQuery()  *******************
===
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROC [dbo].[uspTEST]  
AS

DECLARE @saveerror int
SET @saveerror=0
select 'test'
set @saveerror=@@ERROR
return @saveerror
LVL 1
thefumblerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RiteshShahCommented:
Please see my following article which will explain your how to handle return value of stored procedure in asp.net with C#, you may get an idea from C# syntax and convert it in VB.NET code

http://www.sqlhub.com/2009/04/stored-procedure-return-value-handle.html
0
Anthony PerkinsCommented:
Since the ExecuteNonQuery method returns the Records Affected when there is a a SELECT, INSERT, UPDATE, DELETE query, in your case none of those are present, hence it is not supported and therefore -1.
0
Anthony PerkinsCommented:
Let's try that again (correction):
Since the ExecuteNonQuery method returns the Records Affected when there is a a INSERT, UPDATE, DELETE query, in your case none of those are present, hence it is not supported and therefore -1.
Here is what you would read in the Help for the ExecuteNonQuery method:
<quote>
For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.
</quote>
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

RiteshShahCommented:
all you need to do is, add parameter to your command which is ReturnValue parameter "System.Data.ParameterDirection.ReturnValue" and rather than ExecuteNonQuery, use ExecuteScalar, your return value will be stored in the parameter you have added. You can get more idea from my article.

0
Anthony PerkinsCommented:
>> use ExecuteScalar, your return value will be stored in the parameter you have added.<<
No, that is not the way the ExecuteScalar method works.  This is from the Help:
<quote>
Return Value
The first column of the first row in the result set, or a null reference if the result set is empty.
</quote
0
RiteshShahCommented:
>>No, that is not the way the ExecuteScalar method works.  <<

have you tried my solution practically? if not, than do it first and let me know whether you are getting value back in your "ReturnParameter" or not.
0
Anthony PerkinsCommented:
Why don't we focus on the question posed by the author? Here it is again:
"myresult" always returns -1, whereas I would expect 0 as success.   WHY?"

myresult is the return value for the .NET ExecuteNonQuery method.  But as I pointed out in my first comment the ExecuteNonQuery method returns the records affected when doing an INSERT, UPDATE or DELETE.  Since the author's Stored Procedure, does not appear to contain any of those 3 then the result will always be -1.

But don't take my word for it, here is the documentation on the ExecuteNonQuery Method:
SqlCommand..::.ExecuteNonQuery Method
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx

<quote>
You can use the ExecuteNonQuery to perform catalog operations (for example, querying the structure of a database or creating database objects such as tables), or to change the data in a database without using a DataSet by executing UPDATE, INSERT, or DELETE statements.

Although the ExecuteNonQuery returns no rows, any output parameters or return values mapped to parameters are populated with data.

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.
</quote>
0
RiteshShahCommented:
>>Why don't we focus on the question posed by the author? Here it is again:<<

author has used "Return" statement and to handle value which is "Return" in stored procedure, he should have "return parameter" from .NET code. he don't want to see what records are affected, only want to handle "return" value from SP which is NOT AT ALL possible with ExecuteNonQuery!!.
0
Anthony PerkinsCommented:
>>he don't want to see what records are affected, only want to handle "return" value<<
Of course they do not want the records affected. I never said they did.  They know perfectly well there are no records affected.

>>which is NOT AT ALL possible with ExecuteNonQuery!!.<<
Huh?  Of course, it is possible to get the return parameter using the .NET ExecuteNonQuery method. You really need to check your facts first.

But again, this is not about you.  I suggest we wait until the author gives us some feedback.
0
thefumblerAuthor Commented:
In my test example, changing the code from ExecuteNonQuery to ExecuteScalar and adding the lines
            objCmd.Parameters.Add("@ReturnValue", SqlDbType.Int)
            objCmd.Parameters("@ReturnValue").Direction = ParameterDirection.ReturnValue

results in an InvalidCastException : 'Conversion from string "test" to type 'Integer' is not valid.'

In my real code of course its not a simple select statement, there are an assortment of updates and deletes and a rollback of the entire procedure if any of them fail.   Its now clear as to WHY that occurs because its an unsupported SELECT statement in the stored procedure executed as a result of ExecuteNonQuery .... but can't I somehow to determine a success or failure of any stored procedure (regardless of whatever is inside such as SELECT, INSERT, DELETE or UPDATES in it) so that I can code accordingly.   Simply, I want to knowis my stored procedure is successful, True or False?  
0
Anthony PerkinsCommented:
In order to return OUTPUT parameters (a RETURN parameter is a special type of output parameter) you MUST use the ExecuteNonQuery method.  Period.

So all you have to do is declare the RETURN parameter as you have done and use the ExecuteNonQuery method.
0
Anthony PerkinsCommented:
>>Simply, I want to knowis my stored procedure is successful, True or False?<<
There are a couple of ways this can happen.  If for example, you violate a constraint then an automatic exception will be raised, which you can trap in your .NET app using Try ... Catch.  If on the other hand you want to set a return value then, as I stated previously, you have to use the ExecuteNonQuery method.  You can also trigger an exception from within your stored procedure by using RAISERROR.  But without seeing the Stored Procedure it is difficult to be more precise.

One other option you have with SQL Server 2005 & 2008 is that you can place your code in the Stored Procedure within a Try ... Catch blocks.  This way you can trap the error in your Stored Procedure and handle it, as opposed to an automtic exception been raised.
0
Anthony PerkinsCommented:
After re-reading your comments again.  I see to separate threads going on:
1. How do you return output parameters to .NET.  And in particular the RETURN parameter.
2. How do you execute a number of statements and ROLLBACK if any fail.

Is this accurately summarized and if so are you still having difficulty with either in particular?
0
thefumblerAuthor Commented:
Probably #1  because the stored procedure and rollback code work fine.   And I actually already have the Try /Catch in the .NET code.  Logically, with ExecuteNonQuery I thought I could return the 'saveerror' value to the .NET code from the stored procedure based on the return @saveerror line specifically when there is a rollback, but even if I set @saveerror to a static value 9999 it is -1 or 2 or whatever to the .NET code.  
line:
Here's a snippet of the stored Procedure:
....etc......
                IF @saveerror = 0
                begin                
                  UPDATE tblWrapUpComments SET tblWrapUpComments.UserID = @New_User WHERE UserID = @Old_User
                  Set @saveerror = @@ERROR
                end
 
              IF @saveerror = 0
              begin                
                        DELETE from tblUsers WHERE UserID = @Old_User
            Set @saveerror = @@ERROR
             end
 

        IF @saveerror = 0
                COMMIT
        ELSE
                ROLLBACK
END
return @saveerror

0
Anthony PerkinsCommented:
>>Probably #1<<
Try it this way:

Dim cmd As SqlCommand = New SqlCommand("us_Test", cn)
cmd.CommandType = CommandType.StoredProcedure

Dim ReturnParam As SqlParameter = cmd.Parameters.Add("RETURN_VALUE", SqlDbType.Int)
ReturnParam.Direction = ParameterDirection.ReturnValue

cmd.ExecuteNonQuery()

Dim myresult As Integer = ReturnParam.Value
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
>>because the stored procedure and rollback code work fine<<
You really need to look into using Try ... Catch in your Stored Procedure

BEGIN TRY
      UPDATE tblWrapUpComments SET tblWrapUpComments.UserID = @New_User WHERE UserID = @Old_User

      DELETE from tblUsers WHERE UserID = @Old_User
      
END TRY
BEGIN CATCH
      Set @saveerror = ERROR_NUMBER()
      ROLLBACK
    Return @saveerror
END CATCH

COMMIT

Return

Or at the very least do something like we had to do prior to SQL Server 2005:

UPDATE tblWrapUpComments SET tblWrapUpComments.UserID = @New_User WHERE UserID = @Old_User
Set @saveerror = @@ERROR
GoTo ErrHandler

DELETE from tblUsers WHERE UserID = @Old_User
Set @saveerror = @@ERROR
GoTo ErrHandler

COMMIT

Return

ErrHandler:

ROLLBACK
return @saveerror
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.