ExecuteNonQuery() returns -1 always

Lali picture Lali · Oct 17, 2011 · Viewed 23.1k times · Source

I am using a stored procedure to insert some value in table.

CREATE PROCEDURE [dbo].[Sp_InsertValue]
@Val1 as nvarchar(50)
@Val2 as nvarchar(50)
as
BEGIN
    IF NOT EXISTS(SELECT * FROM @mytable WHERE ID=@Val1)
    INSERT INTO @mytable VALUES(@VAL2)
END

I am using ExecuteNonQuery() to call this stored procedure in ASP.NET using C#. It works fine, no issues, it inserts values if they don't exist. The issue is that cmd.ExecuteNonQuery() always return -1. I expect if a record is inserted, it should return 1, and 0 if not, right?

Answer

Stuart Manning picture Stuart Manning · Apr 10, 2013

Check that you don't have "SET NOCOUNT ON" in your stored procedure. This will stop the number of affect rows be returned. Literally 'NoCount' is ON.

Default response will always be '-1' in this situation.