title: Scope/persistence of data issue
body: <p>I'm not sure what to search for to solve this one.  I have a scope/persistence of data issue, where a variable used to populate my data structure is over-writing other items in the data structure (ImageFileName).  This is my code:</p>
  <pre><code>###Functions...first two are just for info
  
  #method to get messages for mdb files 
  #dependency-Need to download accessDatabaseEngine(64 bit) redestributable: https://www.microsoft.com/en-us/download/details.aspx?id=54920
  Function ProcessHelpMDB{
  [cmdletbinding()]
    Param ([string]$mdbLookupError, [string]$mdbFilePath, [string]$mdbDeviceSeries) #$mdbLookupError = error number, like 701. $mdbDeviceSeries is 1000
    Process
    {
  
      #Write-Host &quot;mdbLookupString: $mdbLookupString&quot; -ForegroundColor Cyan
      $adOpenStatic = 3
      $adLockOptimistic = 3
      #Write-Host &quot;In ProcessMDB&quot; -ForegroundColor darkRed
      #$pathViewBase = 'C:\me\EndToEnd_view\' #View dir. Maybe param later
  
      #$pathToMdb = Join-Path -Path $pathViewBase -ChildPath $mdbFileName
  
      $deviceTable = $mdbDeviceSeries + &quot;PP&quot;
      $mdbLookupError -Match '[0-9]*-([0-9]*)'
      $errLookup = $Matches[1]
  
      #Write-Host &quot;dps&quot; -ForegroundColor DarkMagenta
  
      $selectQuery = “SELECT [$($deviceTable)].[HelpCode],
      [$($deviceTable)].[ScreenNumber],
      [$($deviceTable)].[TextID],
      [$($deviceTable)].[PictureID]
          FROM [$($deviceTable)]
          WHERE 
              [$($deviceTable)].[HelpCode] = $($errLookup)&quot;
  
  
      $cn = new-object -comobject ADODB.Connection
      $rs = new-object -comobject ADODB.Recordset
  
      $cn.Open(&quot;Provider = Microsoft.ACE.OLEDB.16.0;Data Source = $mdbFilePath&quot;) 
      $rs.Open($selectQuery,
          $cn, $adOpenStatic, $adLockOptimistic)
  
      $i=0
      $ret = [System.Collections.Generic.List[psobject]]::new() 
  
      if($rs.EOF)
      {
          Write-Host &quot;$mdbLookupString not in $mdbFileName...record set returned emtpy for query&quot;
      }#if
      else
      {  
          while($rs.EOF -ne $True)
          {
              $result = [ordered]@{}
              #$hash = @{}
              foreach ($field in $rs.Fields)
              {
                  $result[$field.name] = $field.Value
              }#result
              $newObject = [PSCustomObject]$result
              $ret.Add($newObject) #
              $rs.MoveNext()
          } #while
      }#else
      Write-Host &quot;retArr[0] $($ret[0])&quot; #prints retArr[0] @{PictureID=HELP_...; TextID=HELP_...; HelpCode=9; ScreenNumber=1}
      $i=0
      foreach($row in $ret) #goes thru all rows
      {
          Write-Host &quot;retArr[0] $($ret[$i,&quot;TextID&quot;])&quot; #prints retArr[0] @{HelpCode=9; ScreenNumber=1; TextID=HELP_...; PictureID=HELP_...}
          ####
          Write-Host &quot;retArr[] $($row)&quot; #prints retArr[] @{HelpCode=9; ScreenNumber=1; TextID=HELP_...; PictureID=HELP_7000_POWER_COVER_RIBBON}
          Write-Host &quot;retArr[0] $($row[$i])&quot; #prints retArr[0] @{HelpCode=9; ScreenNumber=1; TextID=HELP_...; PictureID=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_...
      }
  
      $ret | ForEach-Object {
          Write-Host TextID= $($_.TextID) #prints TextID= HELP_...
          Write-Host TextID= $($_.'TextID') #prints TextID= HELP_...
      } #
         
      $cn.Close()
      return $ret #Items queried from db ################################################# need to put them in excel file in order next
    } #end Process
  }# End of Function process mdb's
  
      
  #This function gets mdb info out and returns English-US message found
  Function ProcessK_MDB{
      [cmdletbinding()]
        Param ([string]$mdbLookupstring) #$mdbLookupString like HELP_...
        Process
        {
          $adOpenStatic = 3
          $adLockOptimistic = 3
  
          $pathViewBase = 'C:\me\\EndToEnd_view\' #View dir. Maybe param later
          $mdbFileNamePath = 'KAppText.mdb'
      
          $pathToMdb = Join-Path -Path $pathViewBase -ChildPath $mdbFileNamePath
  
          if(Test-Path $pathToMdb)
          {
              $selectQuery = “SELECT [Messages].[Message Description],
              [Messages].[English - Us]
                  FROM [Messages]
                  WHERE [Messages].[Message Description] = '$($mdbLookupString)'”
     
              $cn = new-object -comobject ADODB.Connection
              $rs = new-object -comobject ADODB.Recordset
              $cn.Open(&quot;Provider = Microsoft.ACE.OLEDB.16.0;Data Source = $pathToMdb&quot;)
             
              $rs.Open($selectQuery,
                  $cn, $adOpenStatic, $adLockOptimistic)
          
              if($rs.EOF) #empty
              {
                  Write-Host &quot;$mdbLookupString not in $mdbFileName...record set returned emtpy for query&quot;
                  $ret = &quot;&quot;
              }#if
              else #got results
              {  
                  $returnArr = $rs.GetRows()
                  #$ret = $returnArr[0,1]
                  #$ret2 = $returnArr[1,1]
                  $ret = $returnArr[1,0] #has long text
                  #$ret4 = $returnArr[0,0] #has short text
                  #Write-Host $ret
  
              }#else got results    
              
              $cn.Close()
          } #testPath
          else {
              Write-Host &quot;$pathToMdb does not exist&quot;
          }
          return $ret #Message English-US for parameter/Message Description given
        } #end Process
      }# End of Function process mdb's
  
  Function Get-ImageName{
      [cmdletbinding()]
        Param ([string]$imageName, [string]$fileNamePath) 
        Process
        {
          #find image file name to look for
          [System.String] $pictureName = &quot;&quot;
          if($imageName -Match &quot;HELP&quot;)
          {
              #remove &quot;HELP&quot; part of file name
              $($row.PictureID) -Match &quot;HELP_(.*)&quot; #get the part after HELP_
              Write-Host $Matches[0]
              Write-Host $Matches[1]
              $pictureName = $Matches[1]
          }
          else {
              $pictureName = $imageName
          }
  
          $imageFile2 = Get-ChildItem -Recurse -Path $ImageFullBasePath -Include @(&quot;*.bmp&quot;,&quot;*.jpg&quot;,&quot;*.png&quot;) | Where-Object {$_.Name -match &quot;$($pictureName)&quot;}  #$imageFile | Select-String -Pattern '$($pictureName)' -AllMatches
          Write-Host &quot;ImageFile2: $($imageFile2)&quot;
          $imgFile = &quot;&quot;
          foreach($imgFile in $imageFile2) #there may be more than one...just get last one...there are multiple telephone images
          {
              if($imgFile.Exists) #if($imageFile2.Exists)
              {
                  #$image = [System.Drawing.Image]::FromFile($imgFile) #may not need this step
                  #need to figure out which is correct if there's multiple images
                  return $imgFile
              }
              else {
                  Write-Host &quot;$($imgFile) does not exist&quot;
                  return $null
              }
          } #foreach imageFile2
          return $null
        } #end Process
      }# End of Function process mdb's
  </code></pre>
  <p>###main####</p>
  <pre><code>          $resultHelp = ProcessHelpMDB -mdbLookupError $errCode -mdbFilePath $basePathFull -mdbDeviceSeries $deviceVer #######
            $result = foreach($row in $resultHelp.GetEnumerator()) #this is working for each row
            {
                if($row -ne $True) #not sure why it adds true at the top
                {
                  Write-Host $row.TextID #prints HELP_...
                  #lookup value from kapptext.mdb
                  Write-Host $($row.TextID) #prints nothing but looks ok in next function
                  $longText = ProcessK_MDB -mdbLookupstring $($row.TextID) #gives English-US message from device for parameter given
                  #insert images######################
                  #I can see that these are assigned correctly and returned from Function:
                  [System.Drawing.Image] $image = New-Object System.Drawing.Image  #error...see Update2
                  [System.String] $imageNamePath = New-Object System.String  #error...see Update2
                  [System.String] $imageNamePath = Get-ImageName -imageName $($row.PictureID) -fileNamePath $ImageFullBasePath
                  if($null -ne $imageNamePath)
                  {
                      $image = Get-Image -imageFileName $imageNamePath
                  }
                  else 
                  {
                      Write-Host &quot;Did not find an image file for $($row.PictureID) in $ImageFullBasePath&quot;
                  }
                  #get the data ready to put in spreadsheet
                   New-Object psobject -Property $([ordered]@{
                      ScreenNumber = $($row.ScreenNumber)
                      KPMKey = $($row.TextID)
                      EnglishUS = $($longText)
                      PictureID = $($row.PictureID)
                      ImageFound = &quot;&quot;
                      ImageFileName = $($imageNamePath) ###???this is over-written...all same in $result after done with loop
                  })
                } #if not true
            } #foreach row $resultHelp
  </code></pre>
  <p>##note: $image gets inserted in the spreadsheet later, but since the names aren't showing correctly when use $results, I'm not sharing that part.</p>
  <p>I can see after this runs for the 9 rows, that each ImageFileName is the same in $result.  How do I do this so it maintains what is returned from the method? Right now I am trying New-Object, but it didn't fix the issue.</p>
  <p>This is what $resultHelp looks like:</p>
  <blockquote>
  <p>[0] $true (I'm not sure why it puts true at the top) [1]
  @(HelpCode=9;ScreenNumber=1;TextID=HELP_...JA;PictureID=HELP_...RIB)
  [2]
  @(HelpCode=9;ScreenNumber=2;TextID=HELP_...ROLL;PictureID=HELP_...ROLL)
  [3]
  @(HelpCode=9;ScreenNumber=3;TextID=HELP_...EDGE;PictureID=HELP_...UT)
  ...</p>
  </blockquote>
  <p>I am using Powershell 5.1 and VSCode.</p>
  <p>Update:</p>
  <p>I checked again, and in Get-ImageName, it's returning a file name like &quot;Contact_Service&quot;...bmp and it looks like it's getting put in the psObject ImageFileName...($imageNamePath), and the same thing for the other images found. When I look in the end, $result has all the same values for that ImageFileName. and the one that used to say &quot;Contact_Service&quot;..bmp is something else now, matching the other ones in $result.</p>
  <p>Update2:</p>
  <p>There are a couple errors:</p>
  <blockquote>
  <p>New-Object : A constructor was not found. Cannot find an appropriate
  constructor for type System.Drawing.Image</p>
  </blockquote>
  <p>and</p>
  <blockquote>
  <p>New-Object : A constructor was not found. Cannot find an appropriate
  constructor for type System.String.</p>
  </blockquote>
  <p>for where I tried dealing with the object over-write issue in the main code</p>
  <p>Update3:</p>
  <p>After I get the psobject in good shape, I put it in a spreadsheet with Export-Excel.  I just wanted to give this info so you know why I'm putting it in the psobject like that:</p>
  <pre><code>$xlsx = $result | Export-Excel -Path $outFilePath -WorksheetName $errCode -Autosize -AutoFilter -FreezeTopRow -BoldTopRow  -PassThru # -ClearSheet can't ClearSheet every time or it clears previous data  
    
  </code></pre>