Powershell: Use choice from one drop down box as variable in another

user_13902714 0
Viewed 1221

I need to get the results of the first DROPDOWN box stored as a variable, and used in place of INSTANCE_NAME in the second DROPDOWN box.

Essentially, the SELECT query from the second box needs to be run against the MASTER database in the SLQ_INSTANCE that the first DROPDOWN box results in once a choice has been made.

I’ve tried a few variations and can’t seem to get it right. Any suggestions?

Thanks in advance!

#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 Server 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 DATABASE_NAME -serverinstance INSTANCE_NAME
foreach ($wks in $wksList) {
                      $DropDownBox.Items.Add($wks.Instance_Name)
                              } #end foreach
#end first DROPDOWN box
#second DROPDOWN Box
$label2 = New-Object System.Windows.Forms.Label
$label2.Location = New-Object System.Drawing.Point(10,90)
$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,110)
$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 INSTANCE_NAME
foreach ($wks in $wksList2) {
                     $DropDownBox2.Items.Add($wks.name)
                            } #end foreach
#end second DROPDOWN box

Trying to post current code below....screwed the edit up :-(, including the changes suggested by Jonathan... will have to start new post.....sorry!!!!!!!!!!!!

As mentioned in my comments to Jonathan, it seems right, but whatever value is passed to -serverinstance doesnt seem to work. It 'tries' to connect, but powershell generates an error stating it cannot connect to the server.

Additional note - By adding a Get-Variable command, I determined that the value being passed for InstanceName is actually blank.

99.9% there... any ideas? Thanks again in advance.

By moving the $InstanceName = $DropDownBox.SelectedItem command under the #Second DROPDOWN box, that aprt of the process seems to work correctly now. I choose a server from the first drop down, click the button, and the correct list of databases is populated into the second drop down.

HOWEVER, now the $DBName = $DropDownBox2.text statement at the bottom of the process fails, giving a blank value for the db name..so, no backup.

Still playing with variations, but if anyone sees anything I missed on that, let me know.

I am assuming I need to add a second button and click action, to set the results of Box 2 as the db variable, but i'm having trouble with the placement.

YAY.,...got the code in correctly....

This is the version that seems to work, all except for passing the value in the second Combo Box down to the $DBName at the bottom.....

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
dropdown powershell sql variables
2 Answers

This example uses a button to control the workflow. In this case, users will select the instance in the first dropdown box. When they click the button, the selected name will be stored in $InstanceName which can be used in the query for the second dropdown

$form = New-Object System.Windows.Forms.Form

#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 Server 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 DATABASE_NAME -serverinstance INSTANCE_NAME


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


$InstanceName = $DropDownBox.SelectedItem

$Button = New-Object System.Windows.Forms.Button
$Button.Location = New-Object System.Drawing.Size(10,60)
$Button.Size = New-Object System.Drawing.Size(120,23)
$Button.Text = "Select DB"

$Form.Controls.Add($Button)

#Add Button event 
$Button.Add_Click(
{    
    #second DROPDOWN Box
    $label2 = New-Object System.Windows.Forms.Label
    $label2.Location = New-Object System.Drawing.Point(10,90)
    $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,110)
    $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) }

})

$form.ShowDialog()
user_32905070

Replace $InstanceName = $DropDownBox.SelectedItem; with $DropDownBox.Add_SelectedIndexChanged({ $InstanceName = $DropDownBox.SelectedItem; }) and you're gtg.

user_137899310
Related