Problem of Output messages in Classic ASP applications

SQL Server produce output messages by default, for every query executed. Read on to find the potential problems due to this in the ASP applications

The "SET NOCOUNT ON" statement in Stored Procedures  is absolutely necessary to avoid some ADO errors, which are very difficult to figure out in programming especially for ASP users.

With the 'SET NOCOUNT OFF'  (default) produces messages like "1 row updated", or "3 rows deleted" which fills up the result set (See below for example). So , while working with result sets, especially in ASP , you will encounter lot of problems running through the result set.

(1 row(s) affected)

Value

---------------------------------------

1310

The first line (1 row(s) affected) can mess up the result set.  We need to get rid of those comments telling us how many rows were affected.  The SET NOCOUNT  ON will prevent SQL Server from telling how many rows each statement affected.

SET NOCOUNT OFF will return SQL Server back to its default setting.

In case you need to know the rows that were affected, use @@ROWCOUNT that will return the number of rows that were affected by the SQL statement , regardless of the setting of NOCOUNT.

By [)ia6l0 iii   Popularity  (646 Views)