Powershell: Click button on GUI to move to next item in array

Question

I have created a GUI with the purpose of deleting a row from a SQL table.

I am now stuck on having the results show one at a time as a button is pressed.

Screenshot of GUI:

If I have three results come back, it will show the first result in the text box, then when the Next button is pressed, it skips over the second result and shows the last one.

Debugging shows that it definitely finds three results, but its like I need to stop it between results until the button is pressed to move to the next one. I have tried many things, but have had no luck.

Here is the code (sorry there is quite a bit. The part I am stuck on it near the bottom):

<# 
.NAME
    Remove A File
#>

Start-Transcript -path "c:\path\RemoveScipt.txt"

$StaticData = "C:\path\Static Data.csv"
$SQLServer = "SERVERNAME"
$DB = "DATABASENAME"

Add-Type -AssemblyName System.Windows.Forms
[System.Windows.Forms.Application]::EnableVisualStyles()

$RemoveFile                      = New-Object system.Windows.Forms.Form
$RemoveFile.ClientSize           = New-Object System.Drawing.Point(530,510)
$RemoveFile.text                 = "Remove a script"
$RemoveFile.TopMost              = $false

$ScriptName                      = New-Object system.Windows.Forms.Label
$ScriptName.text                 = "Script Name"
$ScriptName.AutoSize             = $true
$ScriptName.width                = 25
$ScriptName.height               = 10
$ScriptName.location             = New-Object System.Drawing.Point(14,20)
$ScriptName.Font                 = New-Object System.Drawing.Font('Microsoft Sans Serif',10)

$ReportName                      = New-Object system.Windows.Forms.Label
$ReportName.text                 = "Report Name"
$ReportName.AutoSize             = $true
$ReportName.width                = 25
$ReportName.height               = 10
$ReportName.location             = New-Object System.Drawing.Point(14,50)
$ReportName.Font                 = New-Object System.Drawing.Font('Microsoft Sans Serif',10)

$Result                          = New-Object system.Windows.Forms.Label
$Result.text                     = "Is this the script you wish to remove?"
$Result.AutoSize                 = $true
$Result.width                    = 25
$Result.height                   = 10
$Result.location                 = New-Object System.Drawing.Point(14,110)
$Result.Font                     = New-Object System.Drawing.Font('Microsoft Sans Serif',10)

$ScriptNameCombo                 = New-Object system.Windows.Forms.ComboBox
$ScriptNameCombo.width           = 337
$ScriptNameCombo.height          = 20
$ScriptNameCombo.location        = New-Object System.Drawing.Point(120,19)
$ScriptNameCombo.Font            = New-Object System.Drawing.Font('Microsoft Sans Serif',10)
$ScriptNameSQL                   ="SELECT distinct Script_Name FROM TABLE"
$ScriptNameCall                  = Invoke-Sqlcmd -ServerInstance $SQLServer -Database $DB -Query $ScriptNameSQL
$ScriptNameCombo.Items.AddRange($ScriptNameCall.Script_Name) #ADDS ITEMS TO A DROPDOWN LIST

$ReportNameCombo                 = New-Object system.Windows.Forms.ComboBox
$ReportNameCombo.width           = 337
$ReportNameCombo.height          = 20
$ReportNameCombo.location        = New-Object System.Drawing.Point(120,49)
$ReportNameCombo.Font            = New-Object System.Drawing.Font('Microsoft Sans Serif',10)

$GoBut                           = New-Object system.Windows.Forms.Button
$GoBut.text                      = "Go!"
$GoBut.width                     = 45
$GoBut.height                    = 25
$GoBut.location                  = New-Object System.Drawing.Point(470,18)
$GoBut.Font                      = New-Object System.Drawing.Font('Microsoft Sans Serif',10)
$GoBut.BackColor                 =[System.Drawing.Color]::LightGreen

$Go2But                          = New-Object system.Windows.Forms.Button
$Go2But.text                     = "Go!"
$Go2But.width                    = 45
$Go2But.height                   = 25
$Go2But.location                 = New-Object System.Drawing.Point(470,48)
$Go2But.Font                     = New-Object System.Drawing.Font('Microsoft Sans Serif',10)
$Go2But.BackColor                =[System.Drawing.Color]::LightGreen

$NextBut                         = New-Object system.Windows.Forms.Button
$NextBut.text                    = "Next"
$NextBut.width                   = 70
$NextBut.height                  = 30
$NextBut.location                = New-Object System.Drawing.Point(242,103)
$NextBut.Font                    = New-Object System.Drawing.Font('Microsoft Sans Serif',10)
$NextBut.BackColor               =[System.Drawing.Color]::FloralWhite

$ResultText                      = New-Object system.Windows.Forms.TextBox
$ResultText.multiline            = $true
$ResultText.width                = 500
$ResultText.height               = 325
$ResultText.scrollbars           = "Both"
$ResultText.WordWrap             = $false
$ResultText.location             = New-Object System.Drawing.Point(14,135)
$ResultText.Font                 = New-Object System.Drawing.Font('Microsoft Sans Serif',10)

$RemoveScript                    = New-Object system.Windows.Forms.Button
$RemoveScript.text               = "Remove the script"
$RemoveScript.width              = 150
$RemoveScript.height             = 30
$RemoveScript.location           = New-Object System.Drawing.Point(14,470)
$RemoveScript.Font               = New-Object System.Drawing.Font('Microsoft Sans Serif',10)

$CancelBut                       = New-Object system.Windows.Forms.Button
$CancelBut.text                  = "Cancel"
$CancelBut.width                 = 150
$CancelBut.height                = 30
$CancelBut.location              = New-Object System.Drawing.Point(365,470)
$CancelBut.Font                  = New-Object System.Drawing.Font('Microsoft Sans Serif',10)
$CancelBut.BackColor             =[System.Drawing.Color]::IndianRed

$RemoveFile.controls.AddRange(@($ScriptName,$ReportName,$RemoveScript,$ScriptNameCombo,$ReportNameCombo,$GoBut,$ResultText,$Result,$Go2But,$CancelBut,$NextBut))

$GoBut.Add_Click({ 
    
    #USES WHAT WAS CHOSEN FROM THE FIRST DROPDOWN LIST TO FIND ANY ENTRIES WITH THAT ITEM IN THE FIRST COLUMN TO CREATE A SECOND DROPDOWN LIST. THIS IS TO SHORTEN THE RESULTS
    
    $ReportNameLinkedSQL="SELECT distinct Report_Name FROM TABLE where Script_Name = '$($ScriptNameCombo.text)'"
    $ReportNameCallLinked= Invoke-Sqlcmd -ServerInstance $SQLServer -Database $DB -Query $ReportNameLinkedSQL
    
    if ($ScriptNameCombo.text.length -eq 0){[System.Windows.Forms.MessageBox]::Show('Ya gotta select a Script, buddy!','Uh oh','OK','Error')}else{
    $ReportNameCombo.Items.AddRange($ReportNameCallLinked.Report_Name)}

})

$Go2But.Add_Click({ 

    #THIS USES THE PREVIOUS TWO SELECTIONS TO FIND ALL MATCHING ROWS IN THE DATABASE WITH THOSE TWO ITEMS IN THE FIRST TWO COLUMNS. IT WILL SHOW THE FIRST RESULT IN THE TEXT BOX
 
   $FullScriptSQL="SELECT * FROM TABLE where Script_Name = '$($ScriptNameCombo.text)' and Report_Name = '$($ReportNameCombo.text)'"
   $global:FullScriptCallLinked= @(Invoke-Sqlcmd -ServerInstance $SQLServer -Database $DB -Query $FullScriptSQL)
   $count=$FullScriptCallLinked.count
   $nl = "`r`n"

   if ($ScriptNameCombo.text.length -eq 0){[System.Windows.Forms.MessageBox]::Show('Ya gotta select a Script, buddy!','Uh oh','OK','Error')}
       elseif($ReportNameCombo.text.length -eq 0){[System.Windows.Forms.MessageBox]::Show('Ya gotta select a Report, buddy!','Uh oh','OK','Error')}
           elseif($count -eq $null -and $FullScriptCallLinked.Email_or_file -eq 'E'){$ResultText.text = ($nl),"Script Name = ",$FullScriptCallLinked.script_name,($nl),($nl),"Report Name = ",$FullScriptCallLinked.Report_name,($nl),($nl),"View Name = ",$FullScriptCallLinked.View_name,($nl),($nl),"Module = ",$FullScriptCallLinked.Module,($nl),($nl),"Email Address = ",$FullScriptCallLinked.email,($nl),($nl),"Email Subject = ",$FullScriptCallLinked.email_subject,($nl),($nl),"Email Body = ",$FullScriptCallLinked.email_message,($nl),($nl),"File Name = ",$FullScriptCallLinked.filename,($nl),($nl),"File Type = ",$FullScriptCallLinked.file_type}
               elseif($count -eq $null -and $FullScriptCallLinked.Email_or_file -eq 'F'){$ResultText.text = ($nl),"Script Name = ",$FullScriptCallLinked.script_name,($nl),($nl),"Report Name = ",$FullScriptCallLinked.Report_name,($nl),($nl),"View Name = ",$FullScriptCallLinked.View_name,($nl),($nl),"Module = ",$FullScriptCallLinked.Module,($nl),($nl),"File Name = ",$FullScriptCallLinked.filename,($nl),($nl),"File Path = ",$FullScriptCallLinked.file_path,($nl),($nl),"Todays Date = ",$FullScriptCallLinked.today_date,($nl),($nl),$FullScriptCallLinked.yesterday_date,($nl),($nl),"File Type = ",$FullScriptCallLinked.file_type}
                    elseif($FullScriptCallLinked[0].Email_or_file -eq 'E'){$ResultText.text = ($nl),"Script Name = ",$FullScriptCallLinked[0].script_name,($nl),($nl),"Report Name = ",$FullScriptCallLinked[0].Report_name,($nl),($nl),"View Name = ",$FullScriptCallLinked[0].View_name,($nl),($nl),"Module = ",$FullScriptCallLinked[0].Module,($nl),($nl),"Email Address = ",$FullScriptCallLinked[0].email,($nl),($nl),"Email Subject = ",$FullScriptCallLinked[0].email_subject,($nl),($nl),"Email Body = ",$FullScriptCallLinked[0].email_message,($nl),($nl),"File Name = ",$FullScriptCallLinked[0].filename,($nl),($nl),"File Type = ",$FullScriptCallLinked[0].file_type}
                        elseif($FullScriptCallLinked[0].Email_or_file -eq 'F'){$ResultText.text = ($nl),"Script Name = ",$FullScriptCallLinked[0].script_name,($nl),($nl),"Report Name = ",$FullScriptCallLinked[0].Report_name,($nl),($nl),"View Name = ",$FullScriptCallLinked[0].View_name,($nl),($nl),"Module = ",$FullScriptCallLinked[0].Module,($nl),($nl),"File Name = ",$FullScriptCallLinked[0].filename,($nl),($nl),"File Path = ",$FullScriptCallLinked[0].file_path,($nl),($nl),"Todays Date = ",$FullScriptCallLinked[0].today_date,($nl),($nl),$FullScriptCallLinked[0].yesterday_date,($nl),($nl),"File Type = ",$FullScriptCallLinked[0].file_type}

})

$Nextbut.Add_Click({ 

    #HERE IS WHERE I AM STUCK. MY INTENT IS TO SCROLL THROUGH EACH RESULT WHEN THE BUTTON IS CLICKED

    function onClick {
                    foreach($i in $FullScriptCallLinked){
                    $ResultText.text = ($nl),"Script Name = ",$i.script_name,($nl),($nl),"Report Name = ",$i.Report_name,($nl),($nl),"View Name = ",$i.View_name,($nl),($nl),"Module = ",$i.Module,($nl),($nl),"Email Address = ",$i.email,($nl),($nl),"Email Subject = ",$i.email_subject,($nl),($nl),"Email Body = ",$i.email_message,($nl),($nl),"File Name = ",$i.filename,($nl),($nl),"File Type = ",$i.file_type
                    }
   }
   onClick

            
})

$RemoveScript.Add_Click({ 
    
    #NOT COMPLETE
    
    $deleteSQL = "DELETE FROM TABLE WHERE Script_Name = '$($FullScriptCallLinked.script_name)' and Report_Name = '$($FullScriptCallLinked.Report_name)' and View_Name = '$($FullScriptCallLinked.View_name)' and FILENAME = '$($FullScriptCallLinked.filename)' and File_type = '$($FullScriptCallLinked.file_type)'"
    write-host $deleteSQL
    #[environment]::exit(0) 
    })

$CancelBut.Add_Click({ 
    
    #CLEARS ALL FIELDS IN THE FORM
    
    $ScriptNameCombo.text=$null
    $ReportNameCombo.items.clear()
    $ReportNameCombo.resettext()
    $ResultText.clear()

    })

[void]$RemoveFile.ShowDialog()

Any help with this would be amazing! Thanks in advance

Answer 1

Here is the problem:

foreach($i in $FullScriptCallLinked){
    $ResultText.text = ....
}

The foreach statement iterates through all the items, one by one. So if you have three items in $FullScriptCallLinked, the above code will set $ResultText.text three times. The first two values will be overwritten by the last one. So what you see in the UI is just the last item.

You can do something like this:

$currentResultIndex = 0

$Nextbut.Add_Click({ 

    function onClick {
        $currentResultIndex = $currentResultIndex+1
        if($currentResultIndex -ge $FullScriptCallLinked.Length) {
            $currentResultIndex = 0;
        }

        $i = $FullScriptCallLinked[$currentResultIndex]
        $ResultText.text = ($nl),"Script Name = ",$i.script_name,($nl),($nl),"Report Name = ",$i.Report_name,($nl),($nl),"View Name = ",$i.View_name,($nl),($nl),"Module = ",$i.Module,($nl),($nl),"Email Address = ",$i.email,($nl),($nl),"Email Subject = ",$i.email_subject,($nl),($nl),"Email Body = ",$i.email_message,($nl),($nl),"File Name = ",$i.filename,($nl),($nl),"File Type = ",$i.file_type
    }
    onClick
})

Please read more about PowerShell collections and the foreach statement.

Answer 2

Oops! I forgot about this one. I did figure out how to both scroll through each result, then cycle back to the first result when it reaches the end.

I made some changes to the GUI to make it easier for me.

I have completed the script. I updated the 'Go' button and finished the 'Next' button:

$GoBut.Add_Click({ 

   #Establish SQL statements which include a search and a count to verify it completed successfully at the end 
   $FullScriptSQLEmail = "SELECT * FROM TABLE where email = '$($EmailAddressCombo.text)' and Email_or_file = 'E'"
   $FullScriptSQLFile = "SELECT * FROM TABLE where Filename = '$($fileNameCombo.text)' and Email_or_file = 'F'"
   $CountSelectE = "SELECT count (*) FROM TABLE where email = '$($EmailAddressCombo.text)' and Email_or_file = 'E'"
   $CountSelectF = "SELECT count (*) FROM TABLE where Filename = '$($fileNameCombo.text)' and Email_or_file = 'F'"
   $FullScriptCallEmail = @(Invoke-Sqlcmd -ServerInstance $SQLServer -Database $DB -Query $FullScriptSQLEmail)
   $FullScriptCallFile = @(Invoke-Sqlcmd -ServerInstance $SQLServer -Database $DB -Query $FullScriptSQLFile)
   $CountResultE = Invoke-Sqlcmd -ServerInstance $SQLServer -Database $DB -Query $CountSelectE   
   $CountResultF = Invoke-Sqlcmd -ServerInstance $SQLServer -Database $DB -Query $CountSelectF
   
   $nl = "`r`n"

   #Get a count. This works for a 'sanity check', but counts one as zero if you attempt to output it
   $countEmail = $FullScriptCallEmail.count
   $countFile = $FullScriptCallFile.count
   
   #Bit of a cleanup to prevent errors
   $IfExist = Test-Path "./artifacts/*.txt"
   if ($IfExist -eq $true){[System.Windows.Forms.MessageBox]::Show('Whoops! Just gonna clear out some stuff to give you a fresh start Bucko.','Oh dear','OK','Error')

        $EmailAddressCombo.text=$null
        $fileNameCombo.text=$null
        $ScriptCount.text=$null
        $ResultText.clear()
        Remove-Item ".\Artifacts\*.txt"
            
        return} 
   
   #Only allow one combobox to be chosen, but must be one
   Function Clear_fields{
         $EmailAddressCombo.text=$null
         $fileNameCombo.text=$null
   }

   if ($EmailAddressCombo.text.length -ne 0 -and $fileNameCombo.text.length -ne 0){[System.Windows.Forms.MessageBox]::Show('THERE CAN ONLY BE ONE! You can only choose an Email OR a File Path, champ!','Uh oh','OK','Error'); Clear_fields; return}
       elseif($EmailAddressCombo.text.length -eq 0 -and $fileNameCombo.text.length -eq 0){[System.Windows.Forms.MessageBox]::Show('Ya gotta select a Report, buddy!','Uh oh','OK','Error'); return}

   #Using the Count SQL statment to get a real count and then putting the number of results into a box so you know how many you got
   if ($CountResultE.Column1 -gt 0){$ScriptCount.text = $CountResultE.Column1}else
        {$ScriptCount.text = $CountResultF.Column1}
   
   #Put all the data into a text file and format it at the same time    
   if($countEmail -gt 0)
       {foreach($i in $FullScriptCallEmail){
        add-content -path "./Artifacts/results.txt" -value "$($nl) Script Name = $($i.script_name)$($nl)$($nl) Report Name = $($i.Report_name)$($nl)$($nl) View Name = $($i.View_name)$($nl)$($nl) Module = $($i.Module)$($nl)$($nl) Email Address = $($i.email)$($nl)$($nl) Email Subject = $($i.email_subject)$($nl)$($nl) Email Body = $($i.email_message)$($nl)$($nl) File Name = $($i.filename)$($nl)$($nl) File Type = $($i.file_type)"}
        }          
           
   if($countFile -gt 0)
       {foreach($i in $FullScriptCallFile){
        add-content -path "./Artifacts/results.txt" -value "$($nl) Script Name = $($i.script_name)$($nl)$($nl) Report Name = $($i.Report_name)$($nl)$($nl) View Name = $($i.View_name)$($nl)$($nl) Module = $($i.Module)$($nl)$($nl) File Name = $($i.filename)$($nl)$($nl) File Path = $($i.file_path)$($nl)$($nl) Todays Date = $($i.today_date)$($nl)$($nl) Yesterdays Date = $($i.yesterday_date)$($nl)$($nl) File Type = $($i.file_type)"}
        }

   #Establish the results file and create a copy. Show the first 18 lines in the result box
   $resultfile = "./Artifacts/results.txt"
   copy-item "./Artifacts/results.txt" -destination "./Artifacts/result_copy.txt"
   @($content = get-content $resultfile)
   $ResultText.Lines = $content[0..17]
             
})


$Nextbut.Add_Click({ 

        $resultfile = "./Artifacts/results.txt"
    
    #Function to delete the top 18 lines of the result file so the next 18 are grabbed. If there is only one set left (counts how many times Script Name appears), then the data in the copy is copied and pasted into the results file, starting it all over again
        function del-line {

            $Check1 = get-content $resultfile
            $Check2 = $Check1 -match "Script Name"
            $check2.count

            if ($check2.count -eq 1){

               $copy = "./Artifacts/result_copy.txt"
               get-content $copy | set-content $resultfile
               
               $ResultText.clear()

            }else{
    
               $ResultText.clear()
                
                (Get-Content $resultfile | Select-Object -Skip 18) | Set-Content $resultfile
                }

            }

    #Funciton to get the first 18 lines of the result file and display it after using the del-line function
        function onClick{

            del-line
            $LineCount = get-content $resultfile
            if (($LineCount.count) -eq 1){
   
            $content = get-content $resultfile
            $ResultText.text = $content}
            else{
            $content = get-content $resultfile
            $ResultText.Lines = $content[0..17]
                }
                       
            }
        
    #Start the loop    
        onClick

})

If anyone wants the whole script let me know.

I have written 5 GUI scripts to make life easier for my team to:

Add a SQL entry

Remove a SQL entry

Amend a SQL entry

Search for and export a SQL entry or multiple entries

I am no expert and I know it is a little clunky, but works for us. I would think large DB's might cause some strain on resources