Getting a Count of the Rows Affected by a SQL Statement

Getting a Count of the Rows Affected by a SQL Statement

SQL Server does not return a result set for action queries (SQL insert,update, or delete statements). If you were to use the SQLExecute methodand execute the statement directly, RDO would catch the returning row countand make it available to you. However, since the statement is executedin a stored procedure, the row count information is not returned to ODBC.Hence, the Remote Data Object (RDO) can”t return this information.

If you run a stored procedure that contains a mix of select and actionstatements, and an action statement fails, an error is returned to RDO.The error comes back to VB in the form of a trappable data error, whichyou can handle with the On Error syntax. The only thing that is not availableis the actual number of rows affected by the action statement because theSQL Server does not return this information to RDO. However, if your system must to know the actual number of rows affected,you can put:

 Select @@ROWCOUNT

after any action statements in the stored procedure. This produces aone-column, one-row result set containing the number of rows affected bythe action statement. DBLib and ODBC API require the same procedure asRDO.


Share the Post: