Connecting to SQL Server 2005 from Classic ASP

Question

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

Answer 1

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()

Answer 2

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

-Dan