Fix for ASP Pages That Will Not Display an ADO Recordset based on a #Temp Table
Here’s the problem: your asp page is designed to display the data contained in an SQL generated #temp table. When you run the page, you get an error that states that the operation is not allowed when the object is closed. If you execute the SQL command in a query window though, the data is displayed as expected.
Trying to run an ASP page that pulls from #temp table but you get a nasty message that looks something like this:
ADODB.Recordset error '800a0e78'
Operation is not allowed when the object is closed.
This happens because each SQL statement within your stored procedure returns a result. This is a built-in behavior of the SQL Server OLEDB provider (the SQL Server ODBC provider doesn’t do this). The result may be a resultset, but if your ASP page is barfing on displaying the resultset, then the result being returned is a count of rows affected.
To stop the SQL Server OLEDB provider from returning the count of rows affected, add the following line to the beginning of your stored procedure:
SET NOCOUNT ON
After you have altered the stored procedure, the ASP page in question should display the desire resultset with no further issues.
Note: Another error that requires the SET NOCOUNT ON line is:
ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name or ordinal.
Follow the reactions below and share your own thoughts.