8

I am using ExecuteNonQuery to run an insert proc, it returns 2, but in actual I am inserting only 1 record. I am getting 1 extra due to trigger. Is there anyway that I get only actual number of rows affected. I do not want the rows affected by trigger.

Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
Girish Gupta
  • 1,241
  • 13
  • 27
  • 1
    possible duplicate of [ExecuteNonQuery() Returns unexpected number of rows affected C#](http://stackoverflow.com/questions/15702131/executenonquery-returns-unexpected-number-of-rows-affected-c-sharp) – Dimitris Batsougiannis Aug 25 '15 at 10:49
  • 3
    You do realize you are complaining about the fact that `ExecuteNonQuery` returns the correct number, right? – Zohar Peled Aug 25 '15 at 10:50
  • ExecuteNonQurey return number of row affected. It doesn't return inserted rows. http://stackoverflow.com/questions/2974154/what-is-the-difference-between-executescalar-executereader-and-executenonquery – Stoyan Petkov Aug 25 '15 at 10:53
  • try dividing the result by 2 :) – Helio Aug 25 '15 at 11:01

2 Answers2

9

If you don't have it already, disable counting rows in your trigger:

SET NOCOUNT ON

For example:

CREATE TRIGGER [dbo].[triggerName] ON [dbo].[TableName]
AFTER INSERT
AS
SET NOCOUNT ON;
......

MSDN

Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • Tim i want to clear one thing `SET NOCOUNT ON` will not return any count right ?? – wiretext Aug 25 '15 at 11:01
  • 1
    @tinka: if the trigger has `SET NOCOUNT ON` it's affected rows are not counted. But if you insert three rows `ExecuteNoQuery` still returns 3. – Tim Schmelter Aug 25 '15 at 11:02
  • I am creating my own query builder. It can run any type of query and in my database there are thousands of table and many triggers. So do it need to change all triggers to ignore count? – Girish Gupta Aug 25 '15 at 12:09
  • 1
    @girishgupta211: that would be a good approach. It is recommended to SET NOCOUNT ON in triggers, it also increases the performance (as also Reza [has mentioned](http://stackoverflow.com/a/32202397/284240)). Otherwise it's not possible to ignore trigger's affected row-count. Of course you could change your query to somehwhat like this: `insert into dbo.TableName Values('B'); select @@ROWCOUNT";` and use `(int)ExecuteScalar`. But then it could be incorrect because it only returns the very last statement's row-count. This would ignore trigger. – Tim Schmelter Aug 25 '15 at 12:20
  • Thanks Tim Schmelter. Let me try this approach. – Girish Gupta Aug 26 '15 at 05:56
1

please refer below mention Image

enter image description here

However, the MSDN article on this function states: 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. If a rollback occurs, the return value is also -1.

See... enter link description here

Tanmay Nehete
  • 2,138
  • 4
  • 31
  • 42