I spent a frustrating couple of hours yesterday trying to get output from a database into a web page – something that I have done many times before. However, on this occasion as the query was complex I decided that it needed to go into a stored procedure. I quickly got that working and tested via query analyser and then plugged it into my asp page. No matter what I tried I could not get the query to run and I was getting pretty frustrated. Finally I got a message that I could Google, as follows:
ADODB.Recordset error ‘800a0e78’
Operation is not allowed when the object is closed
It seems that when you return data from a query to a record set via a stored procedure not only does the data get returned but also the number of records as a string at the end. This is the text that you would usually see only in query analyser:
1 row(s) affected
Suppressing this message in the stored procedure using SET NOCOUNT ON means that only the data is returned and the asp knows what to do. I eventually found the answers at the links below but I will not get back those two hours of my life!
http://tutorials.aspfaq.com/8000xxxxx-errors/why-do-i-get-800a0cc1-errors.html
http://tutorials.aspfaq.com/8000xxxxx-errors/why-do-i-get-800a0e78-errors.html