Help using ADO to extract data from Oracle DB and store results in Array.

[gammaman]

Hello again,

I am trying to use ADO to connect to an Oracle DB, run a query, and then take the resultset and write the fields out to an array. This array will then be used to compare another array for a dependent pick list (where when the first picklist value is selected, the second only gets populated with choices based on the first). It is not working at all as the first picklist is not even getting populated with values. Here is the macro code I have so far. The initPickList routine is where the ADO code is. Note: DB connection parameters have intentionally been generalized.

Option Explicit
 
' ********************************************************************************************************************
' * Caution:
' Module level varialbes
Dim moCtlIndexing       As Object           ' ecIndex object
Dim moBatch              As Object           ' Batch object
 
' Constants
Private Const FIELD_MODEL = "Type"
Private Const FIELD_MAKE = "Workflow"
 
' Pick list arrays
Dim msTypes(0 To 0)  As Variant
 
Dim msWorkflows(0 To 0)   As Variant
 

Public Sub ecIndex_BatchOpen(Batch As Object, IndexingUI As Object, ctlIndexing As Object)
    On Error Resume Next
 
    ' Save batch object for later use
    Set moBatch = Batch
 
    ' Save indexing control reference for later use
    Set moCtlIndexing = ctlIndexing
 
 
 
    Call InitPickListArrays
End Sub

Private Sub LoadPickList(sMake As String)
    Dim nCount          As Integer
    Dim sCurrentItem    As String
    Dim sPickListVal    As String
    Dim sMakeCompare    As String
 
    For nCount = LBound(msWorkflows) To UBound(msWorkflows)
        ' Get current item
        sCurrentItem = msWorkflows(nCount)
 
        ' Get make from the array item
        sMakeCompare = Mid(sCurrentItem, 1, InStr(sCurrentItem, "=") - 1)
 
        ' If this model corresponds to the selected Make add it to the pick list.
        If UCase(sMake) = sMakeCompare Then
            ' Get the make pick list value
            sPickListVal = Mid(sCurrentItem, InStr(sCurrentItem, "=") + 1)
 
            ' Add the corresponding model to the model field pick list
            Call moCtlIndexing.AddPickListValue(FIELD_MODEL , sPickListVal)
        End If
    Next
 
    ' Refresh the indexing screen to have the new changes
    moCtlIndexing.Refresh 2
End Sub
 
Public Sub ecIndex_FieldLostFocus(ByVal FieldName As String, ByVal FieldValue As String, ByVal bLastField As Boolean, Continue As Boolean)
    ' Make sure we are on the Make field
    If FieldName = FIELD_MAKE Then
            Call ChangeDisplay(FieldValue)
    End If
End Sub
Private Sub ChangeDisplay(sMake As String)
    ' Clear the Document Type drop-down list
    moCtlIndexing.ClearPickList(FIELD_MODEL)
 
    ' Load the make pick list into the Make field.
    Call LoadPickList(sMake)
End Sub
Private Sub InitPickListArrays()
 
     'variables to store connection variables
     Dim dbConn As ADODB.Connection
     Dim ar         As ADODB.Recordset
 
    ' create a new connection and connect to the database
     Set dbConn = New ADODB.Connection
     dbConn.Open "Driver={Microsoft ODBC for Oracle};" & "Server=mydb;" & "Uid=myuserid;" & "Pwd=mypassword"
 
    'create a New Record Set
    Set ar = New ADODB.Recordset
 
    ' setup the query
    Dim mySQL As String
    mySQL =  "Select emplid from ps_job  where emplid=131774"
    'Execute the sql
 Set  ar = dbConn.Execute(mySQL)
 
   Dim numRecords As Integer
   Dim msRows As Variant
   Dim intString As String
    
    'trying to get the firstrow from query and store it into an array 
    msRows(0) = ar.GetRows(1)
   ' numRecords = UBound(msRows,2)+1
   'MsgBox (msRows)
   'assign each piece of data to picklist array
 
 
   'take the array from before and populate another array that will be used in the compare 
   msTypes(0)= msRows(0)
 

 

    ' Define models
   'parese out the stuff in quotations based on value of msTypes array 
   msWorkflows(0) = msTypes(0)="Trans-AP-CorrespondenceReqMgmt"

End Sub
 
Public Sub ecIndex_PageDisplay(PageNumber As Integer)
    Dim nCount As Integer
    Static bDone As Boolean
 
    ' If the Make pick list is already loaded, no need to do it again.
    If bDone = False Then
        ' Load Make pick list
        For nCount = LBound(msTypes) To UBound(msTypes)
            ' Add make from the Make array to the make field pick list
            Call moCtlIndexing.AddPickListValue(FIELD_MAKE, msTypes(nCount))
 
            ' Refresh the Indexing pane to show the newly loaded picklist
            moCtlIndexing.Refresh 2
        Next
 
        ' Set done flag
        bDone = True
    End If
End Sub

[dglienna]

Sure you want 0 to 0?

Dim msTypes(0 To 0)  As Variant
 
Dim msWorkflows(0 To 0)   As Variant

Press F8 to step thru line by line...

[DataMiser]

Why store the recordset in an array? You are aware that a recordset is an object array of rows already right?

I would just dim the recordset at the class level and work with that, should be easier and faster.