Connecting to SQL Server 2005 from Classic ASP

I am trying to return multiple recordsets to a classic ASP web page from SQL Server 2005 and then use GetRows() in ASP to fill 2 arrays with the data. I'm using a command object to run a stored procedure which performs 2 simple selects in SQL Server. The stored procedure works fine in SQL Server Management Studio so I'm guessing it's an ADO issue.

The ASP code looks like this:

strConnect = "DRIVER={SQL Native Client};SERVER=MyServer.IsAtMyIsp.com;DATABASE=MyDb;UID=Me;PWD=MyPwd; MARS Connection=True;"

Set conn = Server.CreateObject("ADODB.Connection")

conn.ConnectionString = strConnect

conn.Open

Set objCommand = Server.CreateObject("ADODB.Command")

Set objRecordset = Server.CreateObject("ADODB.Recordset")

Set objRs = Server.CreateObject("ADODB.Recordset")

With objCommand

.ActiveConnection = conn

.CommandText = "sp_GetShowList"

.CommandType = adCmdStoredProc

If Len(strBeginDate) And IsDate(strBeginDate) Then

.Parameters.Append .CreateParameter("@.i_DateStart",adDate,adParamInput,,strBeginDate)

If Len(strEndDate) And IsDate(strEndDate) Then

.Parameters.Append .CreateParameter("@.i_DateEnd",adDate,adParamInput,,strEndDate)

End If

End If

End With

Set objRs = objCommand.Execute

arrEvents = objRs.GetRows()

objRs.NextRecordset

arrSched = objRs.GetRows()

The error message below occurs at the 2nd GetRows() command.

Operation is not allowed when the object is closed.

Can anyone tell me what I might be doing wrong?

-Dan

You don't assign the second recordset returned by NextRecordset to the recordset variable

Try

Set objRs = objCommand.Execute
arrEvents = objRs.GetRows()
objRs = objRs.NextRecordset
arrSched = objRs.GetRows()

|||

That was so obvious I feel like an idiot. Thanks for the response!

-Dan