Populate Combo Box from Database

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.
User avatar
timstspry17
Posts: 26
Joined: Sun Sep 06, 2015 9:31 pm

Populate Combo Box from Database

Post by timstspry17 » Wed Sep 07, 2016 8:15 am

Product, version and build: PowerShell Studio 2016, build 5.2.127
32 or 64 bit version of product: 64 bit
Operating system: Windows 10 Enterprise
32 or 64 bit OS: 64 bit
PowerShell Version: 5

I am writing a form project and want to populate a combo box with the results of a T-SQL query from a database. I can't seem to find anything in the examples for this and would appreciate any help provided. I am using MS SQL Server.

User avatar
DevinL
Site Admin
Posts: 1098
Joined: Mon Aug 17, 2015 2:25 pm

Re: Populate Combo Box from Database

Post by DevinL » Wed Sep 07, 2016 10:09 am

[TOPIC MOVED TO POWERSHELL GUIS FORUM BY MODERATOR]
DevinL
SAPIEN Technologies, Inc.

User avatar
dan.potter
Posts: 709
Joined: Wed Oct 08, 2014 6:02 am

Re: Populate Combo Box from Database

Post by dan.potter » Wed Sep 07, 2016 10:49 am

You can use the load-combobox function or $combobox1.Items.add method or $combobox1.Items.addrange method.

You forgot to tell us what your data looks like.

User avatar
jvierra
Posts: 11735
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: Populate Combo Box from Database

Post by jvierra » Wed Sep 07, 2016 11:16 am

The data is from SQLServer. With SQLServer data we don't want to use Load-Combobox. Just assign the table to the combobox.
  1. $combobox1.DataSource = $dt
  2.  
  3. $combobox1.DisplayMember = 'ContactName'
  4.  
  5. $combobox1.ValueMember = 'Phone'
Here is a demo
Attachments
Demo-ComboSQLClientTable.psf
(29.02 KiB) Downloaded 93 times

User avatar
jvierra
Posts: 11735
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: Populate Combo Box from Database

Post by jvierra » Wed Sep 07, 2016 11:23 am

Load-ComboBox attempts to turn an object into a data table. When we query a SQL database we return a datatable. No conversion is needed. In fact trying to convert it will remove much of the functionality that may be included if we use a dataset with a custom view.

User avatar
timstspry17
Posts: 26
Joined: Sun Sep 06, 2015 9:31 pm

Re: Populate Combo Box from Database

Post by timstspry17 » Wed Sep 07, 2016 11:32 am

Thanks Dan. My data will be a single column with unique names of servers like below:

Server1
Server2
Server3

Etc...

I am actually looking for a sample project, or a more detailed explanation of querying a SQL Server table and populating a combo box with the result set from the T-SQL query. I am aware of the methods you mentioned in your response, and the existing samples do not contain a sample which does this. I would have thought that this was a fairly common thing to do and am surprised that there is not a sample project demonstrating how to do this.

User avatar
jvierra
Posts: 11735
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: Populate Combo Box from Database

Post by jvierra » Wed Sep 07, 2016 11:52 am

timstspry17 wrote:Thanks Dan. My data will be a single column with unique names of servers like below:

Server1
Server2
Server3

Etc...

I am actually looking for a sample project, or a more detailed explanation of querying a SQL Server table and populating a combo box with the result set from the T-SQL query. I am aware of the methods you mentioned in your response, and the existing samples do not contain a sample which does this. I would have thought that this was a fairly common thing to do and am surprised that there is not a sample project demonstrating how to do this.
The PSF I posted gives you all of that. I recommend either loading the Northwind sample database locally to allow you a target to test and understand samples. Leaning how to use Net with databases takes some time a testing to get used to. You can also just substitute you values in my example and test that assuming it is just a simple query.

User avatar
dan.potter
Posts: 709
Joined: Wed Oct 08, 2014 6:02 am

Re: Populate Combo Box from Database

Post by dan.potter » Wed Sep 07, 2016 12:10 pm

assumed just a flat list(not databound).

$combobox1.Items.AddRange(1..10)

User avatar
timstspry17
Posts: 26
Joined: Sun Sep 06, 2015 9:31 pm

Re: Populate Combo Box from Database

Post by timstspry17 » Wed Sep 07, 2016 12:14 pm

Thanks for the sample! That does help. The only issue I have is trying to modify the select * to a select columnname where columnname like '%0026%' I am unable to get anything except select * to return a result set. Any ideas or examples where the query is a little more complex?

Thanks in advance!

User avatar
dan.potter
Posts: 709
Joined: Wed Oct 08, 2014 6:02 am

Re: Populate Combo Box from Database

Post by dan.potter » Wed Sep 07, 2016 12:25 pm

might have to play with the qoutes

"select purchased from $table where dept like '%it%'"

Locked