Number of rows affected for OleDbCommand.ExecuteNonQuery

  • Thread starter Thread starter Reece Laurie
  • Start date Start date
R

Reece Laurie

Hello,

A customer claims that when executing against SQL Server, the use of
OleDbCommand.ExecuteNonQuery will return the number of rows affected when
executing a Stored Procedure. The customer also claims that the
ExecuteNonQuery will return the number of rows affected by a select into
temp table statement.

To me, such behavior would seem acceptable...

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...
http://msdn.microsoft.com/library/d...ledboledbcommandclassexecutenonquerytopic.asp

Concern is that the documentation would indicate that executing a Stored
Procedure or Select statement should provide a return value of -1 instead of
the number of rows affected...

Can someone confirm that the above customer claim is accurate and expected
information? If it is, is there any other documentation that can be used to
more accurately define the expected return values?

Thanks,
Reece
 
Reece said:
A customer claims that when executing against SQL Server, the use of
OleDbCommand.ExecuteNonQuery will return the number of rows affected when
executing a Stored Procedure. The customer also claims that the
ExecuteNonQuery will return the number of rows affected by a select into
temp table statement.
nope.

To me, such behavior would seem acceptable...

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...
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfsystemdataoledboledbcommandclassexecutenonquerytopic.asp

Concern is that the documentation would indicate that executing a Stored
Procedure or Select statement should provide a return value of -1 instead of
the number of rows affected...

Can someone confirm that the above customer claim is accurate and expected
information? If it is, is there any other documentation that can be used to
more accurately define the expected return values?
Click to expand...

If the procedure disables rowcounting, -1 is returned. This is often done to
save performance. Also, you can disable rowcounting in the connection
parameters of the SqlServer instance. If that's the case, -1 is the only
result you'll receive.

Only INSERT, DELETE and UPDATE statements will set the amount of rows
affected. Select won't. So even if the proc selects rows from a temp table,
that select will not affect the amount of rows affected value and if the proc
disables rowcounting altogether, you'll see -1 no matter what.

FB
 
Back
Top