none
Connecting to SQL Server 2005 from Classic ASP RRS feed

  • 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

    Thursday, August 3, 2006 9:04 PM

Answers

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

    Friday, August 4, 2006 1:49 AM

All replies

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

    Friday, August 4, 2006 1:49 AM
  • That was so obvious I feel like an idiot. Thanks for the response! 

    -Dan

    Monday, August 7, 2006 3:49 PM