Operation Not Allowed When the Object Is Closed When Running More Advanced Query

operation not allowed when the object is closed when running more advanced query

This is a common problem caused by row counts being interpreted as output from a Stored Procedure when using ADODB with SQL Server.

To avoid this remember to set

SET NOCOUNT ON;

in your Stored Procedure this will stop ADODB returning a closed recordset, or if for whatever reason you don't want to do this (not sure why as you can always use @@ROWCOUNT to pass the row count back), you can use

'Return the next recordset, which will be the result of the Stored Procedure, not 
'the row count generated when SET NOCOUNT OFF (default).
Set rs = rs.NextRecordset()

which returns the next ADODB.Recordset if ADODB has detected one being returned by the Stored Procedure (might be best to check rs.State <> adStateClosed when dealing with multiple ADODB.Recordset objects).

Operation is not allowed when the object is closed (Object is not closed)

OK, got it all figured out. below is the code that was used, with a few notes in the code, to show what I did to make it work.

Set getList = Server.CreateObject("ADODB.Command")
getList.ActiveConnection=EV_WikiConn
getList.Prepared = true
getList.commandtext = _
    "SET NOCOUNT ON " & _
    "declare @Lookup table(Id int identity(1, 1) , " & _
    "SongTitle nvarchar(512) ) " & _
    "insert into @Lookup(SongTitle)select * from " & _
    "( values ('Hotter_Than_Hell'), ('Firehouse'), ('She'), " & _
    "('Parasite'), ('Nothin''_To_Lose')) as x(a) " & _
    "select A.AlbumName, S.SongTitle , S.Writers , S.Vocals , " & _
    "S.SID , S.TheTime from Albums A inner join " & _
    "Songs S on A.AID = S.AID inner join " & _
    "@Lookup L on L.SongTitle = S.SongTitle order by L.Id"

' the SET NOCOUNT ON, was added, but did not resolve the issue, I just left it in.
' The next 3 lines is what fixed the issue.
While rsList.State <> adStateOpen
    Set rsList = rsList.NextRecordset
Wend
While Not rsList.EOF%>

<%=rsList("SongTitle")%>

<%rsList.movenext
wend
rsList.Close
set rsList = nothing

Related Topics