Capturing Stored Procedure return value

[Mike P]

I have a SQL Server stored procedure that is returning a 0 if the
execution was OK and 1 if not.

e.g. :

commit trans
return 0

on_error:
rollback tran
return 1

I am trying to capture this return value in my C# :

objConnection.Open();
int intResult = objCommand.ExecuteNonQuery();
objConnection.Close();


What am I doing wrong?


Any assistance would be really appreciated.


Cheers,

Mike

 

[Matthias S.]

Hi,

ExecuteNonQuery() will return you the rows affected if you do an update,
delete or insert.

you can use the ExecuteScalar() instead which returns the value of the
first colum and field.

hope that helps,

matthias

 

[Nicholas Paldino [.NET/C# MVP]]

Matthias and Mike,

This isn't really correct. If you use ExecuteScalar, and the stored
procedure returns a result set, then you will not get the result that you
want.

Rather, what you should do is create a parameterized command (which I
assume you have already) and attach a parameter where the Direction property
is ParameterDirection.ReturnValue. This parameter will then get the return
value result.

Hope this helps