POWERSHELL: Can't get 2nd DropDownBox choice passed as variable

Question

This is an automated SQL backup routine...

This process is supposed to list SQL server names (based on query to a database)... you choose one in the 1st box...

then based on that choice it shows only the databases on that SQL server in the 2nd box...

There are a few TEXT boxes, which are working as desired.

then it passes those values down to the bottom of the code where the backup routine is, executes the routine, and sends an email.

I choose a server from the first drop down, click the button to 'set' that choice as a variable, and a query to a database uses the value from the 1st box to show only the databases on that server...and the correct list of databases is populated into the second drop down.

My problem is I cannot figure out how to properly add a 2nd button to set the choice in that second drop down box as a variable to be used later in the backup routine. So, no value is passed to the $DBName variable at the bottom of the code, so it doesnt know what database to back uip, so FAIL.

Any suggestions would be appreciated.

Add-Type -AssemblyName System.Windows.Forms
Add-Type -AssemblyName System.Drawing
Add-Type -AssemblyName Microsoft.VisualBasic
 
$form = New-Object System.Windows.Forms.Form
 
$image = [System.Drawing.Image]::Fromfile('Path to image')    
$pictureBox = new-object Windows.Forms.PictureBox  #--instantiates a PictureBox
$pictureBox.width=420
$pictureBox.height=420
$pictureBox.top=20
$pictureBox.left=350
$pictureBox.Image=$image
 
#$form.Font = New-Object System.Drawing.Font("Lucida Console",11,[System.Drawing.FontStyle]::Regular)
 
$form = New-Object System.Windows.Forms.Form
$form.Text = 'MSSQL DATABASE BACKUP UTILITY'
$form.Size = New-Object System.Drawing.Size(800,500)
$form.Font = New-Object System.Drawing.Font("Times New Roman",11,[System.Drawing.FontStyle]::Regular)
$form.StartPosition = 'CenterScreen'
$form.Controls.add($pictureBox)
 
$okButton = New-Object System.Windows.Forms.Button
$okButton.Location = New-Object System.Drawing.Point(10,400)
$okButton.Size = New-Object System.Drawing.Size(75,23)
$okButton.Text = 'OK'
$okButton.DialogResult = [System.Windows.Forms.DialogResult]::OK
$form.AcceptButton = $okButton
$form.Controls.Add($okButton)
 
#first DROPDOWN Box
$label = New-Object System.Windows.Forms.Label
$label.Location = New-Object System.Drawing.Point(10,20)
$label.Size = New-Object System.Drawing.Size(280,20)
$label.Text = 'SQL Instance Name'
$form.Controls.Add($label)

$DropDownBox = New-Object System.Windows.Forms.ComboBox
$DropDownBox.Location = New-Object System.Drawing.Size(10,40)
$DropDownBox.Size = New-Object System.Drawing.Size(260,20)
$DropDownBox.DropDownHeight = 200
$Form.Controls.Add($DropDownBox)

$wksList= invoke-sqlcmd -query "select * from VIEW NAME
    order by instance_name" -database DBA -serverinstance INSTANCE_NAME

 
foreach ($wks in $wksList)
    { $DropDownBox.Items.Add($wks.Instance_Name) }
 
$Button = New-Object System.Windows.Forms.Button
$Button.Location = New-Object System.Drawing.Size(10,80)
$Button.Size = New-Object System.Drawing.Size(120,23)
$Button.Text = "Set Instance"

$Form.Controls.Add($Button)

#Add Button event
 $Button.Add_Click(
{   
    #second DROPDOWN Box
    $InstanceName = $DropDownBox.SelectedItem
    $label2 = New-Object System.Windows.Forms.Label
    $label2.Location = New-Object System.Drawing.Point(10,120)
   $label2.Size = New-Object System.Drawing.Size(280,20)
    $label2.Text = 'Database Name'
    $form.Controls.Add($label2)

    $DropDownBox2 = New-Object System.Windows.Forms.ComboBox
    $DropDownBox2.Location = New-Object System.Drawing.Size(10,140)
    $DropDownBox2.Size = New-Object System.Drawing.Size(260,20)
    $DropDownBox2.DropDownHeight = 200
    $Form.Controls.Add($DropDownBox2)

    $wksList2= invoke-sqlcmd -query "select name from sys.databases
    where database_id>4
    order by name" -database MASTER -serverinstance $InstanceName

    foreach ($wks in $wksList2)
        { $DropDownBox2.Items.Add($wks.name) }

})

#end second DROPDOWN box
 
# third text box
$label3 = New-Object System.Windows.Forms.Label
$label3.Location = New-Object System.Drawing.Point(10,220)
$label3.Size = New-Object System.Drawing.Size(280,20)
$label3.Text = 'Desired Backup Location'
$form.Controls.Add($label3)
 
$textBox3 = New-Object System.Windows.Forms.TextBox
$textBox3.Location = New-Object System.Drawing.Point(10,240)
$textBox3.Size = New-Object System.Drawing.Size(260,20)
$form.Controls.Add($textBox3)
#end third text box
 
# fourth text box
$label4 = New-Object System.Windows.Forms.Label
$label4.Location = New-Object System.Drawing.Point(10,280)
$label4.Size = New-Object System.Drawing.Size(280,20)
$label4.Text = 'Desired Backup Name'
$form.Controls.Add($label4)
 
$textBox4 = New-Object System.Windows.Forms.TextBox
$textBox4.Location = New-Object System.Drawing.Point(10,300)
$textBox4.Size = New-Object System.Drawing.Size(260,20)
$form.Controls.Add($textBox4)
#end fourth text box
 
# fifth text box
$label5 = New-Object System.Windows.Forms.Label
$label5.Location = New-Object System.Drawing.Point(10,340)
$label5.Size = New-Object System.Drawing.Size(280,20)
$label5.Text = 'Your Email Address'
$form.Controls.Add($label5)
 
$textBox5 = New-Object System.Windows.Forms.TextBox
$textBox5.Location = New-Object System.Drawing.Point(10,360)
$textBox5.Size = New-Object System.Drawing.Size(260,20)
$form.Controls.Add($textBox5)
#end fifth text box
 
$form.Topmost = $true
 
$form.Add_Shown({$DropDownBox.Select()})
$result = $form.ShowDialog()
 
if ($result -eq [System.Windows.Forms.DialogResult]::OK)
{
$Server = $DropDownBox.text
$DBName = $DropDownBox2.text
$SharedFolder = $textBox3.text
$BUName = $textBox4.text
$mail = $textBox5.text
 
Get-Variable
 
   <#$Date = Get-Date -format yyyyMMdd#>
Backup-SqlDatabase  -ServerInstance $server `
                    -Database $DBName `
                    -CopyOnly `
                    -CompressionOption on `
                    -BackupFile "$($SharedFolder)\$DBName-$BUName.bak" `
                    -BackupAction Database `
                    -checksum
 
#SMPT Section
 
$From = "SUPPORT EMAIL"
$To = $mail
$Subject = "RECENT MSSQL BACKUP REQUEST"
$Body = "Your MSSQL database backup from $server, $DBName, has been backed up to $SharedFolder. Please check the directory path you specified for your backup. If you have any problems, fwd this email to SUPPORT EMAIL for assistance."
$PSEmailServer = "SMTP SERVER"
 
Send-MailMessage -From $From -To $To -Subject $Subject -Body $Body -SmtpServer $PSEmailserver

Answer 1

You might want to have a C# or VB version of this for reference. You'll not find many examples of using WinForms from PowerShell. In particular you might want to learn about layout panels instead of using fixed positioning and sizing for all your controls.

Anyway your problem here is that you're dynamically adding the combobox in an OnClick event, and you don't really have a reference to it after the dialog is dismissed. You can always find a control by its name, but that shouldn't normally be necessary if you have variables referencing each control (which should have better names than ComboBox2, etc).

Add-Type -AssemblyName System.Windows.Forms
Add-Type -AssemblyName System.Drawing
Add-Type -AssemblyName Microsoft.VisualBasic
 
$form = New-Object System.Windows.Forms.Form
 
#$image = [System.Drawing.Image]::Fromfile('Path to image')    
$pictureBox = new-object Windows.Forms.PictureBox  #--instantiates a PictureBox
$pictureBox.width=420
$pictureBox.height=420
$pictureBox.top=20
$pictureBox.left=350
#$pictureBox.Image=$image
 
#$form.Font = New-Object System.Drawing.Font("Lucida Console",11,[System.Drawing.FontStyle]::Regular)
 
$form = New-Object System.Windows.Forms.Form
$form.Text = 'MSSQL DATABASE BACKUP UTILITY'
$form.Size = New-Object System.Drawing.Size(800,500)
$form.Font = New-Object System.Drawing.Font("Times New Roman",11,[System.Drawing.FontStyle]::Regular)
$form.StartPosition = 'CenterScreen'
$form.Controls.add($pictureBox)
 
$okButton = New-Object System.Windows.Forms.Button
$okButton.Location = New-Object System.Drawing.Point(10,400)
$okButton.Size = New-Object System.Drawing.Size(75,23)
$okButton.Text = 'OK'
$okButton.DialogResult = [System.Windows.Forms.DialogResult]::OK
$form.AcceptButton = $okButton
$form.Controls.Add($okButton)
 
#first DROPDOWN Box
$label = New-Object System.Windows.Forms.Label
$label.Location = New-Object System.Drawing.Point(10,20)
$label.Size = New-Object System.Drawing.Size(280,20)
$label.Text = 'SQL Instance Name'
$form.Controls.Add($label)

$DropDownBox = New-Object System.Windows.Forms.ComboBox
$DropDownBox.Location = New-Object System.Drawing.Size(10,40)
$DropDownBox.Size = New-Object System.Drawing.Size(260,20)
$DropDownBox.DropDownHeight = 200
$Form.Controls.Add($DropDownBox)

$Button = New-Object System.Windows.Forms.Button
$Button.Location = New-Object System.Drawing.Size(10,80)
$Button.Size = New-Object System.Drawing.Size(120,23)
$Button.Text = "Set Instance"

$Form.Controls.Add($Button)

$DropDownBox2 = New-Object System.Windows.Forms.ComboBox
$DropDownBox2.Location = New-Object System.Drawing.Size(10,140)
$DropDownBox2.Size = New-Object System.Drawing.Size(260,20)
$DropDownBox2.DropDownHeight = 200
$Form.Controls.Add($DropDownBox2)

#end second DROPDOWN box
 
# third text box
$label3 = New-Object System.Windows.Forms.Label
$label3.Location = New-Object System.Drawing.Point(10,220)
$label3.Size = New-Object System.Drawing.Size(280,20)
$label3.Text = 'Desired Backup Location'
$form.Controls.Add($label3)
 
$textBox3 = New-Object System.Windows.Forms.TextBox
$textBox3.Location = New-Object System.Drawing.Point(10,240)
$textBox3.Size = New-Object System.Drawing.Size(260,20)
$form.Controls.Add($textBox3)
#end third text box
 
# fourth text box
$label4 = New-Object System.Windows.Forms.Label
$label4.Location = New-Object System.Drawing.Point(10,280)
$label4.Size = New-Object System.Drawing.Size(280,20)
$label4.Text = 'Desired Backup Name'
$form.Controls.Add($label4)
 
$textBox4 = New-Object System.Windows.Forms.TextBox
$textBox4.Location = New-Object System.Drawing.Point(10,300)
$textBox4.Size = New-Object System.Drawing.Size(260,20)
$form.Controls.Add($textBox4)
#end fourth text box
 
# fifth text box
$label5 = New-Object System.Windows.Forms.Label
$label5.Location = New-Object System.Drawing.Point(10,340)
$label5.Size = New-Object System.Drawing.Size(280,20)
$label5.Text = 'Your Email Address'
$form.Controls.Add($label5)
 
$textBox5 = New-Object System.Windows.Forms.TextBox
$textBox5.Location = New-Object System.Drawing.Point(10,360)
$textBox5.Size = New-Object System.Drawing.Size(260,20)
$form.Controls.Add($textBox5)
#end fifth text box


#event handlers
#Add Button event
 $Button.Add_Click(
{   
    #second DROPDOWN Box
    $InstanceName = $DropDownBox.SelectedItem
    $label2 = New-Object System.Windows.Forms.Label
    $label2.Location = New-Object System.Drawing.Point(10,120)
   $label2.Size = New-Object System.Drawing.Size(280,20)
    $label2.Text = 'Database Name'
    $form.Controls.Add($label2)


    $wksList2= invoke-sqlcmd -query "select name from sys.databases
    where database_id>4
    order by name" -database MASTER -serverinstance $InstanceName

    foreach ($wks in $wksList2)
        { $DropDownBox2.Items.Add($wks.name) }

})


#Initialize
$wksList= invoke-sqlcmd -query "select * from VIEW NAME
    order by instance_name" -database DBA -serverinstance INSTANCE_NAME

 
foreach ($wks in $wksList)
    { $DropDownBox.Items.Add($wks.Instance_Name) }

 
$form.Topmost = $true
 
$form.Add_Shown({$DropDownBox.Select()})

#show form and wait for input
$result = $form.ShowDialog()
 
if ($result -eq [System.Windows.Forms.DialogResult]::OK)
{
    $Server = $DropDownBox.text
    $DBName = $DropDownBox2.text
    $SharedFolder = $textBox3.text
    $BUName = $textBox4.text
    $mail = $textBox5.text
 
    write-output $DropDownBox2
    Write-Output "Db $DbName"

 }