I've spent many weekends looking into this problem and thought I'd best blog it so some one else at least gets the benefit of it.
The issue I am talking about centres around the following error message.
"The connection cannot be used to perform this operation. It is either closed or invalid in this context."
The above error message has been a true bane to me. It was an issue on a classic ASP site that was quite happily ticking away for many years. I spent ages looking through the code ensuring that the SQL Connection was properly closed after each use and that ADODB.Recordsets were being used correctly. The error didn't make sense to me because the problem only happened occasionally and I was convinced it was either an issue with MDAC or the version of IIS (we had moved to a new server a few months ago) .
To cut a long story short, the solution I discovered was in SQL Server 2005! Looking through the SQL server logs I discovered that after the last request SQL Server would "Auto Close" the connection and release resources. When the website made another request SQL would be busy spinning up which would then return the error above!
To stop this happening right click on your database in SQL Server Management Studio, select properties, then select Options and set "Auto Close" to false. I believe this option is now removed in newer versions of SQL Server.