Link to home
Start Free TrialLog in
Avatar of Richard Gardner
Richard GardnerFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Powershell GUI pull downs populated by MS SQL table

I am generating a Powershell GUI with pull down menus. I am trying to populate these pull down menu's from a table in a SQL database. Within the pull down I want to be the value to be set by a table called 'AD_USERNAME' and displayname to be set by a table called 'DISPLAY_NAME'. I know how to display the contents on the screen but I'm not sure how to populate the pull downs?

I know I need to use Foreach to loop through the table but I can't work out how. I included the $name variables which I can use to print the table information on screen.

Here is the code I have so far -

$SQLServer = 'SQL1'
$SQLDBName = 'test'
$SqlQuery = 'select * from dbo.SMS_CONTACT'

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd

$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet) | Out-Null
 
$SqlConnection.Close()

[void] [System.Reflection.Assembly]::LoadWithPartialName("System.Drawing")
[void] [System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms")

$Form = New-Object System.Windows.Forms.Form    
$Form.Size = New-Object System.Drawing.Size(600,400)

$DropDownBox = New-Object System.Windows.Forms.ComboBox
$DropDownBox.Location = New-Object System.Drawing.Size(20,50)
$DropDownBox.Size = New-Object System.Drawing.Size(180,20)
$DropDownBox.DropDownHeight = 200
$DropDownBox.DropDownStyle = 2
$DropDownBox.Items.Add("$table")

$Form.Controls.Add($DropDownBox)

foreach ($Row in $DataSet.tables)
{
  $DropDownBox.Items.Add($Row)
  #$name = $Row.Item('AD_USERNAME'), $Row.Item('DISPLAY_NAME')
  #$name
}

$Form.Add_Shown({$Form.Activate()})
[void] $Form.ShowDialog()

ASKER CERTIFIED SOLUTION
Avatar of oBdA
oBdA

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Richard Gardner

ASKER

Thank you! Works perfectly