Page 1 of 1

Set ValueMember of ComboBox from SQL query

Posted: Mon Nov 05, 2018 12:37 pm
by ehebert1215
I'm trying to set a combo box for displaymember and valuemember. I have a function to get a list of collections from a SQL table. Each collection has a corresponding ID and I'm grabbing that as well. My data is retrievd properly and I can even use that data to popuate a datagriew with the proper columns (CollectionName, SiteID) and the values are correct.

Now, no matter how I try to update my combobox with this info, it doesn't worked as expected. If I were doing this with Visual Studio and VB or C# I wouldn't be having any problems, but in PSS it isn't working. My latest attempt (below) is using the Update-ComboBox helper function and is not working. The items displayed in the combobox are correct (i.e. the collection names); however, the ValueMember shows the same value for any item I choose from the list. In my below example, I'm trying to set the column name of SiteID from my SQL query as the ValueMember and what I'm getting as the value on any item in the list is literally SiteID.

Any thoughts on this?

Code: Select all

function Get-Collections
{
$sqlConn = New-Object System.Data.SqlClient.SqlConnection
$sqlConn.ConnectionString = $ConnectionString
$sqlConn.Open()
$sqlcmd = $sqlConn.CreateCommand()
$sqlcmd.Connection = $sqlConn
$sqlcmd.CommandText = "select CollectionName,SiteID from v_collections where CollectionComment = 'SoftwareDistribution' order by collectionname"
$adp = New-Object System.Data.SqlClient.SqlDataAdapter $sqlcmd
$data = New-Object System.Data.DataSet
$adp.Fill($data) | Out-Null
$sqlConn.Close()
return $data.Tables[0]
}

function Load-CollectionList
{
Update-ComboBox -ComboBox $CollectionList $(Get-Collections).CollectionName -DisplayMember "CollectionName" -ValueMember "SiteID" -Append
}

$formAddComputersToAColle_Load={
#TODO: Initialize Form Controls here
Load-CollectionList
}

FYI - I've also tried this weird one and get the same results (except this time the last SiteID retrieved from the query is being set as the value for every item in the combobox list:

Code: Select all

Get-Collections | %{ Update-ComboBox -ComboBox $CollectionList $_.collectionname -DisplayMember $_.CollectionName -ValueMember $_.SiteID -Append }

Re: Set ValueMember of ComboBox from SQL query

Posted: Mon Nov 05, 2018 1:07 pm
by ehebert1215
So, I figured this out...I guess I posted my question a lil too quickly.

You have to select the properties from the datatable when assigning the datasource to the combobox. I guess I understand that, but it's weird. Anyways,this is what I changed the Update-ComboBox parameters to in my code to get it working in case anyone else has this issue.

Code: Select all

Update-ComboBox -ComboBox $CollectionList (Get-Collections|select CollectionName,SiteID) -DisplayMember CollectionName -ValueMember SiteID