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

Home -> Programming -> Web

From the computer of: shamanstears (737 recipes)
Created: Jan 22, 2008     Updated: Jan 24, 2008

Add to:

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:

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:

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: