SQLCommand.ExecuteNonQuery returns wrong number

[Harry F. Harrison]

I'm executing the .ExecuteNonQuery method of a SQLCommand object, with the
following SQL:

UPDATE BUDGET_ACCOUNT
SET Deleted = 1, DATE_DELETED = GetDate(), MODIFIED_BY_ID_FK = 1
WHERE ID = 951956

If I copy this SQL code to Query Analyzer, it returns 1 record affected.

However, if I run it in ADO.NET, it returns 2.

'ID' is the primary key, and is an auto numbered identity field...

I do not have a 'delete' trigger on the table.

Running VS 2003. SQL Server 2000 SP3. Win2K.

 

[David Browne]

I'm executing the .ExecuteNonQuery method of a SQLCommand object, with the
following SQL:

UPDATE BUDGET_ACCOUNT
SET Deleted = 1, DATE_DELETED = GetDate(), MODIFIED_BY_ID_FK = 1
WHERE ID = 951956

If I copy this SQL code to Query Analyzer, it returns 1 record affected.

However, if I run it in ADO.NET, it returns 2.

'ID' is the primary key, and is an auto numbered identity field...

I do not have a 'delete' trigger on the table.

Running VS 2003. SQL Server 2000 SP3. Win2K.

You could use this instead. Bind an output parameter to @ROWS.


UPDATE BUDGET_ACCOUNT
SET Deleted = 1, DATE_DELETED = GetDate(), MODIFIED_BY_ID_FK = 1
WHERE ID = 951956;
SET @ROWS = @@ROWCOUNT

David

 

[Joyjit Mukherjee]

Hi,

try with a SET NOCOUNT ON at the begining of the SP.

Regards
Joyjit

 

[Pablo Castro [MS]]

Hi Harry,

It's the first time I hear about something like this. Is this something you
can reproduce in an stand-alone program? If so, I'd be glad to take a look
if you could post it to the newgroup or email me directly.

Thanks,

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Harry F. Harrison]

I created a stand-alone sample, with code to create a new table, and was not
able to duplicate it.

After further investigation, I realized that I have an update trigger on the
table to update a field with the current date-time.

When I disabled the trigger, ExecuteNonQuery would return 1 - the same as
Query Analyzer.

So, I wonder why Query Analyzer is only returning 1,even when the trigger is
enabled.

Which result is 'correct'?

 

[Pablo Castro [MS]]

hm, interesting. I don't know why the Query Analyzer folks decided to show
side-effects they way you mention below, but I'll try to find out.

SqlClient simply returns the sum of all the affected rows as reported by the
server.

As to which one is correct, well, I guess that's the kind of question you
can argue for both ways, depending on what are your particular needs or
scenario. In the next release (Whidbey), the connection has an event that
let's you get notified of each completed statements along with the number of
rows it affected.

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.