Click here to Skip to main content
Click here to Skip to main content

Combobox Data Binding and Sorting Tip

, 2 Oct 2012
Rate this:
Please Sign up or sign in to vote.
How to sort a data bound combobox

Introduction

This article is a tip on how to sort a combobox that's databound to a data source and show the correct description in the text box. 

Background 

Being a C#/ADO.NET newbie I found it difficult to find articles which clearly explained how to bind a combobox to a data source with the combobox showing the data in a sorted manner. I found several articles, but they mostly only showed how to perform the binding, but not the sorting. 

The Good Ole Days... 

Having come from a VB6 background, I was used to populating and sorting comboboxes programmatically, i.e., setting the combobox's Sorted property to True, selecting all the rows of a table, looping through the results, and populating the combobox using the Add method with the description of the item. A hidden combobox was used to track the actual key values so when an item was selected in the "description" combobox, the key was readily available from the hidden combobox using the same index value. Phew!

The New Way: ADO.NET Data Binding 

Now that I've discovered ADO.NET and Data Binding, things are a lot easier. No more crazy coding. Just tell the combobox what the data source is, which column to display, and which column the key is, and voila! The values appear in the dropdownlist and textbox and the keys are also readily available. 

My BIG Mistake

Everything appeared to be working fine. I'd select a new record to look at on screen and the combobox would change accordingly. The only problem was, it displayed the wrong item! Huh? I thought that the binding was automatic and fail safe?!?  

The big mistake I made when implementing this wonderful new methodology was that I still set the combobox Sorted property to True (out of habit). Thus the combobox would sort itself after being loaded with all the data from the data source, screwing up the indexing, and every time I'd select a new record to look at from the master file, the combobox would display the wrong item from the support (code) file! What a mess! 

Solution

The (now) obvious solution is to leave the combobox unsorted, i.e., leave or set the Sorted property to False and perform the sorting as part of the SQL query.

It's all about the code 

Here's a brief example of my implementation. 

I have a maintenance form which uses the BindingNavigator to move through a dataset showing the data as I move forward and back through the dataset. The BindingNavigator.BindingSource is assigned at run-time using a DataAdapter to Fill a Dataset and use it as the source for a BindingSource.  

SqlConnection conn = new SqlConnection(sqlConnectionString);
SqlDataAdapter da = new SqlDataAdapter();
DataSet ds = new DataSet();
BindingSource bs = new BindingSource();

//Set the BindingNavigator source of the data to the Binding Source
uxRequestsBNav.BindingSource = bs;

//SQL Queries. NOTE the sorting of the resulting dataset using the ORDER BY clauses...
string sqlQuery = "SELECT * FROM Requests ORDER BY BriefDesc; " +
   "SELECT combined = Description + ' [' + " + 
   "convert(varchar,System_ID) + ']', System_ID FROM System ORDER BY combined";
da.SelectCommand = new SqlCommand(sqlQuery, conn);

//Due to multiple SELECTs, set up table mappings
//otherwise need to use Table, Table1, Table2 etc...
da.TableMappings.Add("Table", "Requests");
da.TableMappings.Add("Table1", "System");

//Go get the data
da.Fill(ds);

//Bind the dataset to the binding source
bsRequests.DataSource = ds.Tables["Requests"];

//Bind the ID Textbox's Text property to the Bindingsource's Requests_ID column
uxIDTxt.DataBindings.Add("Text", bsRequests, "Requests_ID");

//Set up data sources for combos
uxBriefDescCbo.DataSource = ds.Tables["Requests"]; //The Main Table
uxBriefDescCbo.DisplayMember = "BriefDesc";
uxBriefDescCbo.ValueMember = "Requests_ID";
uxBriefDescCbo.DataBindings.Add("SelectedValue", bsRequests, "Requests_ID");

//Set up the Codes Table
uxSystemCbo.DataSource = ds.Tables["System"];   //the source of the data for this cbo
uxSystemCbo.DisplayMember = "combined";         //the column name of the table to display
uxSystemCbo.ValueMember = "System_ID";          //the id for that item/record
//Bind the cbo to the Navigator's data source, i.e. the binding source (Main Table)
uxSystemCbo.DataBindings.Add("SelectedValue", bsRequests, "System");

Points of interest

Habits can be good, but in this case caused me hours of frustration and hair pulling. I've now formed a new habit of never setting a combobox's Sorted property to True when creating the control at design time, just leaving it at False.

History   

  • 27 Apr 2012 - Initial article/tip.
  • 02 Oct 2012 - Cosmetic changes.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

wkiess01
SA Police Super
Australia Australia
I have always been interested in electronics and computers. I built my own C/PM micro system as a teenager and progressed from there to the System80, C64 and finally the PC. I've been a programmer and analyst for most of my working life in private and government employment, using languages based on BASIC and C. Most of my career was spent as a Senior Systems Analyst using PICK, Universe, D3 and VB6. I am now working in C#, .NET4 and SQL Server 2008/2012, but still keeping in touch with D3 (PICK).
Although I spend most of my time in a Windows (7 & 2008) environment, I also have a bit of a Unix background and dabble in a bit of LINUX here and there.
I'm currently the Manager IT at SA Police Super where I look after all their IT needs. I'll probably be here until I retire. So much to do...

Comments and Discussions

 
QuestionData binding Pinmemberchaminda satanarachchi29-Sep-12 9:21 
thank you this helped me lot.... !

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140721.1 | Last Updated 2 Oct 2012
Article Copyright 2012 by wkiess01
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid