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