Referencing controls after enumerating them

[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?

[brittneyr]

[Topic moved by moderator to PowerShell GUIs forum]

[jvierra]

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

[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

[boyddt_co]

Thank you, I will give it a look.