Use choice from one drop down box as variable in another

[Mark Dickinson Participant Topics: 2 Replies: 0 Points: 12Rank: Member]

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!

PowerShell

#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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

#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

 

[Rob Simmers Participant Topics: 17 Replies: 1949 Points: 3,975Rank: Community Hero]

Take a look at this example;

PowerShell

$DropDownBox.DropDownHeight = 200 $Form.Controls.Add($DropDownBox) #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) function Load-DrpDwn { $DropDownBox2.Items.Clear() $wksList2= Get-Process -Name $script:mDb foreach ($wks in $wksList2) { [void]$DropDownBox2.Items.Add($wks.name) } #end foreach #end second DROPDOWN box $DropDownBox2.SelectedIndex = 0 } #Load dropdown servers $wksList = Get-Process foreach ($wks in $wksList) { [void]$DropDownBox.Items.Add($wks.Name) } #Set first item in list $DropDownBox.SelectedIndex = 0 #Set script variable for selected item $script:mDB = $DropDownBox.SelectedItem #A better way to do this is https://info.sapien.com/index.php/guis/gui-controls/spotlight-on-the-combobox-control #But quick and dirty, we want to create a function to dynamically clear and load the second dd because it will get #called initially on load and again when something changes in dd1 Load-DrpDwn #Event for when something is selected to update dd2 $DropDownBox_SelectedIndexChanged = { $script:mDB = $DropDownBox.SelectedItem Load-DrpDwn } $DropDownBox.add_SelectedIndexChanged($DropDownBox_SelectedIndexChanged) $form.ShowDialog()

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53

$DropDownBox.DropDownHeight = 200 $Form.Controls.Add($DropDownBox)   #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)   function Load-DrpDwn {     $DropDownBox2.Items.Clear()       $wksList2= Get-Process -Name $script:mDb     foreach ($wks in $wksList2) {         [void]$DropDownBox2.Items.Add($wks.name)     } #end foreach     #end second DROPDOWN box       $DropDownBox2.SelectedIndex = 0 }   #Load dropdown servers $wksList = Get-Process foreach ($wks in $wksList) {     [void]$DropDownBox.Items.Add($wks.Name) }   #Set first item in list $DropDownBox.SelectedIndex = 0 #Set script variable for selected item $script:mDB = $DropDownBox.SelectedItem   #A better way to do this is https://info.sapien.com/index.php/guis/gui-controls/spotlight-on-the-combobox-control #But quick and dirty, we want to create a function to dynamically clear and load the second dd because it will get #called initially on load and again when something changes in dd1 Load-DrpDwn   #Event for when something is selected to update dd2 $DropDownBox_SelectedIndexChanged = {    $script:mDB = $DropDownBox.SelectedItem    Load-DrpDwn }   $DropDownBox.add_SelectedIndexChanged($DropDownBox_SelectedIndexChanged)   $form.ShowDialog()

[Author]

Posts