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();
uxRequestsBNav.BindingSource = bs;
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);
da.TableMappings.Add("Table", "Requests");
da.TableMappings.Add("Table1", "System");
da.Fill(ds);
bsRequests.DataSource = ds.Tables["Requests"];
uxIDTxt.DataBindings.Add("Text", bsRequests, "Requests_ID");
uxBriefDescCbo.DataSource = ds.Tables["Requests"];
uxBriefDescCbo.DisplayMember = "BriefDesc";
uxBriefDescCbo.ValueMember = "Requests_ID";
uxBriefDescCbo.DataBindings.Add("SelectedValue", bsRequests, "Requests_ID");
uxSystemCbo.DataSource = ds.Tables["System"];
uxSystemCbo.DisplayMember = "combined";
uxSystemCbo.ValueMember = "System_ID";
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.