I have the following query:
create proc [dbo].[DeleteParts]
@TransNo nvarchar (6), @fpart nvarchar(25)
AS
DECLARE @Returns BIT
SET @Returns = 1
BEGIN
TRY
BEGIN TRANSACTION
DELETE FROM PARTABLE
WHERE TransNo = @TransNo and fpart = @fpart
COMMIT
END TRY
BEGIN CATCH
Print 'Delete failed'
SET @Returns = 0
-- Any Error Occurred during Transaction. Rollback
IF @@TRANCOUNT > 0
ROLLBACK -- Roll back
END CATCH
RETURN @Returns
This compiles perfectly fine.
In C#, I want to execute this query and get the return value.
My code is as below:
using(System.Data.SqlClient.SqlCommand deletecommand = this._connection.CreateCommand())
{
deletecommand.CommandText = "DeleteParts";
deletecommand.CommandType = System.Data.CommandType.StoredProcedure;
deletecommand.Parameters.AddWithValue("@TransNo", ItemSODBOM.SONO);
deletecommand.Parameters.AddWithValue("@fpart", ItemSODBOM.fbompart);
string ReturnValue = deletecommand.ExecuteNonQuery().ToString();
}
It does not give me any error but instead it is returning number of rows affected, I want to return 1 or 0.
Example: if delete operation success then return 1 and if it fails then return 0.
Any help with source code would be appreciated.
Thanks,
Pradeep
<< is the left shift operator. It is shifting the number 1 to the left 0 bits, which is equivalent to the number 1 .
The && (logical AND) operator indicates whether both operands are true. If both operands have nonzero values, the result has the value 1 . Otherwise, the result has the value 0 . The type of the result is int . Both operands must have an arithmetic or pointer type.
You need a parameter with Direction
set to ParameterDirection.ReturnValue
Something like:
SqlParameter returnParameter = deleteCommand.Parameters.Add("RetVal", SqlDbType.Int);
returnParameter.Direction = ParameterDirection.ReturnValue;
...
deleteCommand.ExecuteNonQuery();
...
int returnValue = (int) returnParameter.Value;
You Stored Procedure needs to return this return value of course:
create proc [dbo].[DeleteParts]
@TransNo nvarchar (6),
@fpart nvarchar(25)
AS
DECLARE @Returns BIT
SET @Returns = 1
...
RETURN @Returns
I don't see that you are returning the value. Please add the Return statement to return any value from the stored proc.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With