Figure 1   Storing Data in Arrays


 <SCRIPT RunAt=Server Language=VBScript>
 Function GetInfo(InfoId)
     ' Populates array and returns through function
     Dim oCmd, oConn, oRs
 
     Set oConn = Server.CreateObject("ADODB.Connection")
     Set oCmd = Server.CreateObject("ADODB.Command")    
     oConn.Open Application("ConnectionString ")
     Set oCmd.ActiveConnection = oConn
     oCmd.CommandText = "sp_GetInfo"
     oCmd.CommandType = adCmdStoredProc
     oCmd.Parameters.Append _
         oCmd.CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue)
     oCmd.Parameters.Append _
         oCmd.CreateParameter("@InfoID", adInteger, adParamInput, , InfoId)
     Set oRs = oCmd.Execute 
     If Not oRs.EOF Then GetInfo = oRs.GetRows()
     oRs.Close
     oConn.Close 
     Set oRs = Nothing
     Set oCmd = Nothing
     Set oConn = Nothing
 End Function
 </SCRIPT>

Figure 2   Initializing the Array


 Function GetInfoText(Id)
     CONST ARRAY_INITIAL_SIZE = 50
     CONST ARRAY_INCREMENT = 10      ' Size to increment array by 
                                     ' when recordset exceeds array size
     CONST RECORDSET_COLUMN_COUNT = 2
     Dim oCmd, oConn, oRs, intRowCounter, intColCounter
     Dim aryTemp
     Redim aryTemp(RECORDSET_COLUMN_COUNT, ARRAY_INITIAL_SIZE)
 
     Set oConn = Server.CreateObject("ADODB.Connection")
     oConn.Open Application("ConnectionString")
     Set oCmd = Server.CreateObject("ADODB.Command")
     Set oCmd.ActiveConnection = oConn
     oCmd.CommandText = "sp_GetInfoText"
     oCmd.CommandType = adCmdStoredProc
     oCmd.Parameters.Append _
         oCmd.CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue)
     oCmd.Parameters.Append _
         oCmd.CreateParameter("@ID", adInteger, adParamInput, , Id)
     Set oRs = oCmd.Execute 
 
     intRowCounter = 0
     Do While Not oRs.EOF
         If (intRowCounter > UBound(aryTemp,2)) Then 
             Redim Preserve aryTemp(2, UBound(aryTemp,2) + ARRAY_INCREMENT)
         End If
         For intColCounter = 0 To RECORDSET_COLUMN_COUNT
             aryTemp(intColCounter,intRowCounter) = oRs.Fields(intColCounter).Value
         Next
         oRs.Movenext
         intRowCounter = intRowCounter + 1
     Loop
     Redim Preserve aryTemp(RECORDSET_COLUMN_COUNT, intRowCounter - 1)
     GetInfoText = aryTemp
 End Function

Figure 3   Using Disconnected Recordset


 Function GetInfoText(Id)
     Dim oCmd, oConn, oRs
 
     Set oConn = Server.CreateObject("ADODB.Connection")
     oConn.Open Application("ConnectionString")
     Set oCmd = Server.CreateObject("ADODB.Command")
     Set oCmd.ActiveConnection = oConn
     oCmd.CommandText = "{ ? = call sp_GetInfoText(?) }"
     oCmd.CommandType = adCmdText
     oCmd.Parameters.Append _
         oCmd.CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue)
     oCmd.Parameters.Append _
         oCmd.CreateParameter("@ID", adInteger, adParamInput, , Id)
     Set oRs = oCmd.Execute 
     Set oConn = Nothing
     Set oCmd = Nothing    
 
     Set GetInfoText = oRs
 End Function