CODEWITHSUNDEEP
sqlsql-server-2008
The Furious Bear
The Furious Bear

Reputation: 572

ExecuteNonQuery() returning -1

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?

Upvotes: 1

Views: 323

Answers (2)

Shrikant-Divyanet Solution
Shrikant-Divyanet Solution

Reputation: 69

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.

Upvotes: 1

Brian Webster
Brian Webster

Reputation: 30875

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.

Upvotes: 1

Related Questions