Set ValueMember of ComboBox from SQL query

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
ehebert1215
Posts: 2
Joined: Thu Dec 07, 2017 9:05 am

Set ValueMember of ComboBox from SQL query

Post by ehebert1215 » Mon Nov 05, 2018 12:37 pm

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?
  1. function Get-Collections
  2.  
  3. {
  4.  
  5.     $sqlConn = New-Object System.Data.SqlClient.SqlConnection
  6.  
  7.     $sqlConn.ConnectionString = $ConnectionString
  8.  
  9.     $sqlConn.Open()
  10.  
  11.     $sqlcmd = $sqlConn.CreateCommand()
  12.  
  13.     $sqlcmd.Connection = $sqlConn
  14.  
  15.     $sqlcmd.CommandText = "select CollectionName,SiteID from v_collections where CollectionComment = 'SoftwareDistribution' order by collectionname"
  16.  
  17.     $adp = New-Object System.Data.SqlClient.SqlDataAdapter $sqlcmd
  18.  
  19.     $data = New-Object System.Data.DataSet
  20.  
  21.     $adp.Fill($data) | Out-Null
  22.  
  23.     $sqlConn.Close()
  24.  
  25.     return $data.Tables[0]
  26.  
  27. }
  28.  
  29.  
  30.  
  31. function Load-CollectionList
  32.  
  33. {
  34.  
  35.     Update-ComboBox -ComboBox $CollectionList $(Get-Collections).CollectionName -DisplayMember "CollectionName" -ValueMember "SiteID" -Append
  36.  
  37. }
  38.  
  39.  
  40.  
  41. $formAddComputersToAColle_Load={
  42.  
  43.     #TODO: Initialize Form Controls here
  44.  
  45.     Load-CollectionList
  46.  
  47. }

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:
  1. Get-Collections | %{ Update-ComboBox -ComboBox $CollectionList $_.collectionname -DisplayMember $_.CollectionName -ValueMember $_.SiteID -Append }

User avatar
ehebert1215
Posts: 2
Joined: Thu Dec 07, 2017 9:05 am

Re: Set ValueMember of ComboBox from SQL query

Post by ehebert1215 » Mon Nov 05, 2018 1:07 pm

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.
  1. Update-ComboBox -ComboBox $CollectionList (Get-Collections|select CollectionName,SiteID) -DisplayMember CollectionName -ValueMember SiteID

Locked