Combobox Data Binding and Sorting Tip

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.