Operation is not allowed when the object is closed

Question

The following code, is generating that error.

Set getList = Server.CreateObject("ADODB.Command")
getList.ActiveConnection=EV_WikiConn
getList.Prepared = true
getList.commandtext= "declare @Lookup table(Id int identity(1, 1) , SongTitle nvarchar(512) ) 
insert into @Lookup(SongTitle)select * from ( values ('Deuce')) 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"
set rsList = getList.execute
while not rsList.eof ' Error is on this line here.

I added this code here

Set getList = Server.CreateObject("ADODB.Command")
getList.ActiveConnection=EV_WikiConn
getList.Prepared = true
getList.commandtext= "declare @Lookup table(Id int identity(1, 1) , SongTitle nvarchar(512) ) 
insert into @Lookup(SongTitle)select * from ( values ('Deuce'),('Strutter'),('Parasite')) 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"
set rsList = getList.execute
If rsList.State <> adStateOpen Then
While rsList.State <> adStateOpen
Set rsList = rsList.NextRecordset

rsList.movenext
wend
end if

This makes it run, however, I only get one record, instead of the 10 that is in the actual form. So, this is not going to work, but wanted to show what I have tried so far.

Answer 1

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

Answer 2

Regardless of what the OP believes this is a duplicate of

ADODB.Recordset error '800a0e78' Operation is not allowed when the object is closed

If we analyse the original code revision (ignoring the hard wrap which will cause an error in VBScript)

Set getList = Server.CreateObject("ADODB.Command")
getList.ActiveConnection=EV_WikiConn
getList.Prepared = true
getList.commandtext= "declare @Lookup table(Id int identity(1, 1) , SongTitle nvarchar(512) ) 
insert into @Lookup(SongTitle)select * from ( values ('Deuce')) 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"
set rsList = getList.execute
while not rsList.eof ' Error is on this line here.

You can see that the original issue is the lack of

SET NOCOUNT ON;

to stop the closed ADODB.Recordset object being returned for the initial INSERT operation.

The subsequent edits the OP makes confuses the issue, before this adding SET NOCOUNT ON; would have fix the problem without needing further changes.

The next revision is where the confusion begins (again, ignoring the hard wrap which will cause an error in VBScript)

Set getList = Server.CreateObject("ADODB.Command")
getList.ActiveConnection=EV_WikiConn
getList.Prepared = true
getList.commandtext= "declare @Lookup table(Id int identity(1, 1) , SongTitle nvarchar(512) ) 
insert into @Lookup(SongTitle)select * from ( values ('Deuce'),('Strutter'),('Parasite')) 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"
set rsList = getList.execute
If rsList.State <> adStateOpen Then
While rsList.State <> adStateOpen
Set rsList = rsList.NextRecordset

rsList.movenext
wend
end if

Let's just dissect this part

If rsList.State <> adStateOpen Then
While rsList.State <> adStateOpen
Set rsList = rsList.NextRecordset

rsList.movenext
wend
end if

First the If and While loop do the same thing accept the While does it repeatedly until the condition is met, making the If completely irrelevant. The While loop checks for the current state of the ADODB.Recordset which without SET NOCOUNT ON; is returned as adStateClosed (due to the INSERT operation). This is fine but by changing the While loop from

while not rsList.eof

to

While rsList.State <> adStateOpen

the condition has been changed and the While is no longer checking for rsList.EOF which would be the case when we are enumerating the ADODB.Recordset object. However, now it is only checking for the .State of rsList which once .NextRecordset is called will exit the loop as a open ADODB.Recordset object has been returned. Because the condition has been met the loop will only run once calling .MoveNext once and only returning one record.

So to summaries using

SET NOCOUNT ON;

initially would have negated this whole scenario but hey "I don't understand the code" apparently.