ExecuteNonQuery() returning -1

Question

I am calling a SQL Server stored procedure from C#, consisting of a straightforward INSERT statement. The ExecuteNonQuery() returns the number of rows affected by the statement OK unless there is no update, in which case I'm getting -1.

This doesn't make much sense to me; how can -1 rows be affected?

Perhaps this can help: http://stackoverflow.com/questions/7794097/executenonquery-returns-1-always — sgeddes, Feb 13 '13 at 01:12
I'd read about the NOCOUNT and had tried it with no change; if one record added, returns 1 correctly but if 0 rows added, returns -1. — The Furious Bear, Feb 13 '13 at 01:22

score 1 · Accepted Answer · answered Feb 13 '13 at 02:33

The @@ROWCOUNT function is updated even when SET NOCOUNT is ON.

Source

So, since you are at a dead-end with turning NOCOUNT off, try this:

Use @@ROWCOUNT with ExecuteNonQuery

At the end of your SP, execute SELECT @@ROWCOUNT and use ExecuteScalar instead of ExecuteNonQuery. This will return the result that you desire.

score 1 · Answer 2 · answered Jul 11 '16 at 11:29

You can get -1 after calling ExecuteNonQuery() with stored procedure having insert/delete/update query. This happens if stored procedure do not call any query(Insert/Update/Delete). Here is one example which will help you to understand this.

StoredProcedure- MySQL

DELIMITER $$ DROP PROCEDURE IF EXISTS sp_DML $$ CREATE PROCEDURE sp_DML
(
P_Operation varchar(50)
) BEGIN

  if(P_Operation='Insert')
    /*  Insert Statment*/
  end if;

  if(P_Operation='Update')
    /*  Update Statment*/
    end if;
  if(P_Operation='Delete')
    /*Delete Statment*/
    end if;

END$$

If u call this SP using ExecuteNonQuery(); and passing parameter P_Operation as 'NoOpeartion' which does not satisfy any condition written into Stored procedure then it will always return -1.

ExecuteNonQuery() returning -1

2 Answers2