Reputation: 37
I have created a stored procedure for deleting record. In this stored procedure I am first checking for the usage of data which I am going to delete. If it is being used, then the stored procedure will return -2 otherwise it deletes the record.
But the problem is that even the record exists its return -1 instead of -2. I have also set the NOCOUNT OFF
but don't know where is the problem.
I know this question is already answered by setting NOCOUNT OFF
but its not working for me
ALTER PROCEDURE [dbo].[spDeletePIDNumber]
@Id int
AS
BEGIN
SET NOCOUNT OFF;
-- Insert statements for procedure here
if(exists(select * from tblBills where PID = @Id))
begin
return -2
end
else
begin
Delete from HelperPIDNumber
where Id = @Id
end
END
public int DeletePIDNumber(int Id)
{
try
{
int result = 0;
using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.connection))
{
var cmd = new SqlCommand("spDeletePIDNumber", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Id", Id);
conn.Open();
result = cmd.ExecuteNonQuery();
}
return result;
}
catch
{
throw;
}
}
Upvotes: 1
Views: 322
Reputation: 50
use cmd.ExecuteScalar()
instead of cmd.ExecuteNonQuery()
ascmd.ExecuteNonQuery()
return only the number of affected rows and not the value you are selecting.
Upvotes: 0
Reputation: 12022
Generally ExecuteNonQuery
will return number of affected records. It will return -1
in two cases:
When SET NOCOUNT ON
has been set. From your code, its clear, you have SET NOCOUNT OFF
and so this is not an issue at your case.
If number of affected rows is nothing, it will return -1
. In your case, it looks like you are checking the data exists from one table tblBills
and delete from another table HelperPIDNumber
. So there is more chance there will be no matching record and nothing deleted.
Please check the point # 2 above.
if( exists(select * from tblBills where PID = @Id))
begin
return -2
end
else
begin
Delete from HelperPIDNumber where Id = @Id
end
Upvotes: 2
Reputation: 16146
From the ExecuteNonQuery
documentation:
Executes a Transact-SQL statement against the connection and returns the number of rows affected.
Having SET NOCOUNT ON;
in your procedure explicitely tells to SQL Server not to return a row count. In that case the return of the ExecuteNonQuery
function is -1.
Also if the procedure does not affect any rows, it will not return a row count either even if NOCOUNT
is OFF
. In that case the return will also be -1.
What you appear to want to do is get the return value
of the stored procedure. You will not get that from the result of ExecuteNonQuery
. Please refer to this question on StackOverflow: Getting return value from stored procedure in ADO.NET
Upvotes: 3