Referencing controls after enumerating them

Ask questions about creating Graphical User Interfaces (GUI) in PowerShell and using WinForms controls.
Forum rules
Do not post any licensing information in this forum.

Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
Locked
User avatar
boyddt_co
Posts: 78
Joined: Mon Dec 17, 2007 5:50 am

Referencing controls after enumerating them

Post by boyddt_co »

To help you better we need some information from you.

*** Please fill in the fields below. If you leave fields empty or specify 'latest' rather than the actual version your answer will be delayed as we will be forced to ask you for this information. ***

Powershell Studio, v7.6.138 :
Operating System: Windows 10
PowerShell version(s):5.1.17763.1490
32-bit version of software? No

I have a simple form with 3 listboxes that need to be populated from SQL queries.
first I enumerate the list boxes and get the table names from the listbox names e.g. lb_department gives me 'department' as the table name.
I pass the table name to the load-listbox function
$lb_items = $formIncident.controls | Where-Object { $_.name -like "lb*" }
foreach ($control in $lb_items)
{
$table = $control.Name
$table = $table.Substring(3, $table.Length - 3)
$control = $control
Write-Host $control.Name

# load-listbox -tbl_name $table -control $control

}


The load-listbox function setups the SQL statements and runs the queries

function load-listbox
{
param (
[parameter(Mandatory=$true)]
[string]$tbl_name,
[string]$control
)
Write-Host $tbl_name
$sql_server = ""
$sql_user = ""
$sql_password = ""
$sql_database = ""
$sql_column = $tbl_name
$sql_table = "tbl_" + $tbl_name
$sql_connectionstring = "server=$sql_server; database=$sql_database; persist security info=false; user id=`"$sql_user`"; password=`"$sql_password`""
Write-Host $sql_connectionstring
$sql_query = "select * from $sql_table order by $sql_column"
Write-Host $sql_query
$sql_connection = New-Object System.Data.SqlClient.SqlConnection
$sql_connection.ConnectionString = $sql_connectionstring
$sql_cmd = New-Object System.Data.SqlClient.SqlCommand
$sql_cmd.CommandText = $sql_query
$sql_cmd.Connection = $sql_connection

$sql_adapter = New-Object system.data.sqlclient.sqldataadapter
$sql_adapter.SelectCommand = $sql_cmd
$dataset = New-Object System.Data.DataSet
$sql_adapter.fill($dataset)

$items = $dataset.Tables[0]
foreach ($item in $items)
{
Write-Host $item.$sql_column
Update-ListBox $control $item.$sql_column -Append

# Update-ListBox $listBox1 "Red" -Append
}


}

I pass the item from the sql_colum to the update-listbox helper function and this is where things go south. How do I pass the name of the control to the update-listbox function. If I hard code it say Update-ListBox $lb_department $item.$sql_column -Append the process works and dumps all data into the lb_department listbox. I've tried adding `$$control to the command e.g. Update-ListBox `$$control $item.$sql_column -Append. What are my options here? Are there options?

User avatar
brittneyr
Site Admin
Posts: 637
Joined: Thu Jun 01, 2017 7:20 am
Answers: 8
Been upvoted: 6 times

Re: Referencing controls after enumerating them

Post by brittneyr »

[Topic moved by moderator to PowerShell GUIs forum]
Brittney Ryn
SAPIEN Technologies, Inc.

jvierra
Posts: 14666
Joined: Tue May 22, 2007 9:57 am
Answers: 5
Has voted: 1 time
Been upvoted: 5 times
Contact:

Re: Referencing controls after enumerating them

Post by jvierra »

"Control" needs to be an object and not a string. Just remove the "[string]" declaration.

jvierra
Posts: 14666
Joined: Tue May 22, 2007 9:57 am
Answers: 5
Has voted: 1 time
Been upvoted: 5 times
Contact:

Re: Referencing controls after enumerating them

Post by jvierra »

To begin with you need to simplify your code and use standard nomenclature and syntaxes.

The following shows how simple this can be when approached correctly.

Code: Select all

function Load-Listbox{
    param (
        [system.Windows.forms.listbox]$Listbox,
        [string]$TableName
    )
    
    $server = ""
    $userid = ""
    $password = ""
    $database = ""

    $connStr = "server=$server;database=$database;userid=$userid;password=$password"
    
    # create the connection and load teh query into a DataTable
    $conn = New-Object System.Data.SqlClient.SqlConnection($connStr)
    $cmd = $conn.CreateCommand()
    $cmd.CommandText = "select * from tbl_$TableName order by $TableName"
    $dt = New-Object System.Data.DataTable
    $rdr = $cmd.ExecuteReader()
    $dt.Load($rdr)
    
    # load the control from the table and display the correct column
    $Listbox.DataSource = $dt
    $Listbox.DisplayMember = $TableName
    
}
Learning how to correctly use quotes and when to not use them is critical to avoiding getting lost. There are many online documents that will explain this. Here is a place to start:

The PowerShell Best Practices and Style Guide

User avatar
boyddt_co
Posts: 78
Joined: Mon Dec 17, 2007 5:50 am

Re: Referencing controls after enumerating them

Post by boyddt_co »

Thank you, I will give it a look.

Locked