Number of rows affected for OleDbCommand.ExecuteNonQuery

[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

 

[William Ryan eMVP]

It should return the number of rows affected, at least I know the the
SqlClient Library will for sure and I think the OleDb will as well.

--

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/

 

[William Ryan eMVP]

Just confirmed it with OleDb and it works as expected too, so if two records
were inserted in the proc, it'd return 2 as the records affected.

--

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/

 

[Frans Bouma [C# MVP]]

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?

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