CODEWITHSUNDEEP
c#asp.netexecutenonquery
Lali
Lali

Reputation: 2866

ExecuteNonQuery() returns -1 always

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?

Upvotes: 24

Views: 32573

Answers (2)

shravan sangishetti
shravan sangishetti

Reputation: 53

ExecuteNonQuery does not return any rows, any output parameters or return values mapped to parameters are populated with data.

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. For all other types of statements, the return value is -1.

  1. if you have SET NOCOUNT ON before executing insert/update/delete statements in your stored procedure.

    ExecuteNonQuery returns value -1

  2. In your entire stored procedure when there is no chances to execute Insert/Update/Delete statements due to any conditions like

Declare @x int = 5; If x > 5 Begin Performing Insert/Update/Delete operation. End

--other type of statements

In the above example, it will not reach to inside if condition so No chances of executing insert/update/delete statements in the entire stored procedure so in this case also

ExecuteNonQuery returns value -1

According to MS documentation Remarks:

You can use the ExecuteNonQuery to perform catalog operations (for example, querying the structure of a database or creating database objects such as tables), or to change the data in a database without using a DataSet by executing UPDATE, INSERT, or DELETE statements.

Although the ExecuteNonQuery returns no rows, any output parameters or return values mapped to parameters are populated with data.

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. For all other types of statements, the return value is -1. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. When SET NOCOUNT ON is set on the connection (before or as part of executing the command, or as part of a trigger initiated by the execution of the command) the rows affected by individual statements stop contributing to the count of rows affected that is returned by this method. If no statements are detected that contribute to the count, the return value is -1. If a rollback occurs, the return value is also -1.

Upvotes: -1

Stuart Manning
Stuart Manning

Reputation: 626

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.

Upvotes: 60

Related Questions