Can't get data out of ordered dictionary

Question

I'm trying to get data out of my ordered dictionary, and for some reason it's not printing the data in it. I can get to the first item in the data structure, but I'm having trouble iterating over all the rows. I'm using PowerShell 5.1 and visual studio code.

The code queries an mdb file, and returns multiple rows.

Function ProcessHelpMDB{
[cmdletbinding()]
  Param ([string]$mdbLookupError, [string]$mdbFilePath, [string]$mdbPrinterSeries) 
  Process
  {
   $adOpenStatic = 3
   $adLockOptimistic = 3
   $deviceTable = $mdbPrinterSeries + "PP"
   $mdbLookupError -Match '[0-9]*-([0-9]*)'
   $errLookup = $Matches[1]

    $selectQuery = “SELECT [$($deviceTable)].[HelpCode],
    [$($deviceTable)].[ScreenNumber],
    [$($deviceTable)].[TextID],
    [$($deviceTable)].[PictureID]
        FROM [$($deviceTable)]
        WHERE
            [$($deviceTable)].[HelpCode] = $($errLookup)"

        $cn = new-object -comobject ADODB.Connection
        $rs = new-object -comobject ADODB.Recordset
        $cn.Open("Provider = Microsoft.ACE.OLEDB.16.0;Data Source = $mdbFilePath") 

        $rs.Open($selectQuery,
        $cn, $adOpenStatic, $adLockOptimistic)
        $i=0
        $ret = [ordered]@{}
        if($rs.EOF)
        {
            Write-Host "$mdbLookupString not in $mdbFileName...record set returned emtpy for query"
        }#if
        else
        {  
            while($rs.EOF -ne $True)
            {

                $result = [ordered]@{}
                foreach ($field in $rs.Fields)
                {
                    $result[$field.name] = $field.Value
                }#result
                $newObject = [PSCustomObject]$result
                $ret.Add($i,$newObject) ###ordered dictionary needs key to add values
                $i++ 
                $rs.MoveNext()
            } #while
 
            Write-Host "retArr[] $($ret)" #prints retArr[] System.Collections.Specialized.OrderedDictionary
            Write-Host "retArr[0] $($ret[0,"TextID"])" #prints retArr[0]
            Write-Host "retArr[0] $($ret[0])" #prints retArr[0] @{PictureID=HELP_BLAH; TextID=HELP_INFO; HelpCode=9; ScreenNumber=1}
            foreach($row in $ret)
            {
                foreach($item in $row.value) #it's skipping these
                {
                    Write-Host $item #
                    Write-Host $item[0] #
                    Write-Host $item[0].'TextID' #
                    Write-Host $item.'TextID' #
                }#foreach
            }

            $cn.Close()
            return $ret
      } #end Process
    }# End of Function process mdb's

The data looks like this in the mdb table:

HelpCode   ScreenNumber    TextID                PictureID
1000           1           HELP_INFO             HELP_BLAH
1000           2           HELP_INFO2            HELP_BLAH2
...

My question is, how do I print out the data in the table, from the data structure? I'll need to access it later to re-piece it into a string with other data. My problem is that I can't figure out how to access each row. in the data structure, and each item individually in each row. I seem to know how to access the first row, but not other rows.

I've been looking at this most recently: looping over hash table

Update: I got the TextID to print for each row of ret as follows per comments below..thank you!!:

foreach($row in $ret.GetEnumerator()) #this is working for each row, using 
{
    Write-Host $row.TextID #prints nothing
    Write-Host $row.'TextID' #prints nothing
    Write-Host $($row.TextID) #prints nothing
    Write-Host $($row.'TextID') #prints nothing
    Write-Host $($row[0].TextID) #prints nothing
    Write-Host $($row[0].'TextID') #prints nothing
    Write-Host $($row[0].Value.TextID) #prints TextID value of current row
    Write-Host $($row[0].Value.'TextID') #prints TextID value of current row
}

Update2: I'm trying to data structure improvement suggested in the answer below. Thank you!! It's a great simplification.

$ret = [System.Collections.Generic.List[psobject]]::new() #different data struc
if($rs.EOF)
{
    Write-Host "$mdbLookupString not in $mdbFileName...record set returned emtpy for query"
}#if
else
{  
    while($rs.EOF -ne $True)
    {
        $result = [ordered]@{}
        foreach ($field in $rs.Fields)
        {
            $result[$field.name] = $field.Value
        }#result
        $newObject = [PSCustomObject]$result
        $ret.Add($newObject) ###
        $rs.MoveNext()
    } #while
}#else
Write-Host "retArr[] $($ret)" #prints retArr[] 
Write-Host "retArr[0] $($ret[0,"TextID"])" #prints retArr[0]
Write-Host "retArr[0] $($ret[0])" #prints retArr[0] @{PictureID=...; TextID=...; HelpCode=9; ScreenNumber=1}
$i=0
foreach($row in $ret) #goes thru all rows
{
    Write-Host "retArr[] $($ret)" #prints retArr[] 
    Write-Host "retArr[0] $($ret[$i,"TextID"])" #prints retArr[0] @{HelpCode=9; ScreenNumber=1; TextID=...; PictureID=...}
    Write-Host "retArr[0] $($ret[$i].TextID)" #prints retArr[0] HELP_...
    Write-Host "retArr[0] $($ret[$i].'TextID')" #retArr[0] HELP_...
    ####
    Write-Host "retArr[] $($row)" #prints retArr[] @{HelpCode=9; ScreenNumber=1; TextID=...; PictureID=HELP_...}
    Write-Host "retArr[0] $($row[$i,"TextID"])" #prints retArr[0]
    Write-Host "retArr[0] $($row[$i])" #prints retArr[0] @{HelpCode=9; ScreenNumber=1; TextID=HELP_...; PictureID=HELP_...}
    Write-Host "retArr[0] $($row[$i].TextID)" #prints retArr[0] HELP_...
    Write-Host "retArr[0] $($row[$i].'TextID')" #retArr[0] HELP_...
    $i++
}

foreach($row in $ret.GetEnumerator()) #this is working for each row
{
    Write-Host $row.TextID #prints HELP_...
    Write-Host $row.'TextID' #prints HELP_...
    Write-Host $($row.TextID) #prints HELP_...
    Write-Host $($row.'TextID') #prints HELP_...
    Write-Host $($row[0].TextID) #HELP_...
    Write-Host $($row[0].'TextID') #HELP_...
    Write-Host $($row[0].Value.TextID) #prints nothing
    Write-Host $($row[0].Value.'TextID') #prints nothing
}

This works great. Thanks for the help @mathias!

Update3: I'm trying the pipeline idea from @iRon comment, with update2 (optimized data structure):

$ret | ForEach-Object {
        Write-Host TextID= $($_.TextID) #prints TextID= HELP_...
        Write-Host TextID= $($_.'TextID') #prints TextID= HELP_...
        Write-Host TextID= $($_.Value.TextID) #prints TextID=
        Write-Host TextID= $($_.Value.'TextID') #prints TextID=
    }

Thanks iRon! That's a good way to access the data in the data structure. :)

Answer 1

You can loop over each key-value entry in a dictionary by explicitly calling GetEnumerator() on it and passing the resulting enumerator to foreach or a pipeline-enabled cmdlet:

foreach($kvp in $ret.GetEnumerator())
{
  Write-Host "Key: $($kvp.Key) - Value: $($kvp.Value)"
}

That being said, as long as you're using successive integers as the keys, you might as well use a list instead of a dictionary:

# create a list instead of a dictionary
$ret = [System.Collections.Generic.List[psobject]]::new()

# ...
while($rs.EOF -ne $True)
{
    $result = [ordered]@{}
    foreach ($field in $rs.Fields)
    {
        $result[$field.name] = $field.Value
    }

    $newObject = [PSCustomObject]$result
    $ret.Add($newObject) # add object to list, no need to keep track of `$i` here.
    $rs.MoveNext()
} 

# ...

Lists are indexable the same as arrays, so you can do:

$ret[4] # this will get you the 5th row