Fix for ASP Pages That Will Not Display an ADO Recordset based on a #Temp Table

Posted January 22, 2008 by Rob Rogers in Web application programming

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.


About Rob Rogers

Once a prolific author here on Tech-Recipes, Rob has moved on to greener pastures.
View more articles by Rob Rogers

The Conversation

Follow the reactions below and share your own thoughts.

Leave a Reply