The DropDownList control does not expose a sort method to sort items by either
the value or the displayed text. Usually this is not a problem because the DropDownList
will display the items in the order they were loaded into the control. Typically
it's easy enough to ensure the data is loaded in the desired order by simply
using an ORDER BY clause in the SQL SELECT
statement or the stored procedure. Likewise, if you’re not pulling the values
from the database, you simply use Items.Add in the
desired order.
What if you don’t have the ability to modify the stored procedure to fit your
sorting needs? Or, what if you want to dynamically add items to the control
without re-querying the database? How can you sort the DropDownList? The solution
is to load your data into a SortedList, and then bind the SortedList to the
DropDownList.
There's a Hitch
This solution works great except for one hitch: SortedList sorts by Key only;
you cannot sort by Value. This will work for you sometimes, but not always.
Say, for example, you have a list of customer IDs and names. You load your SortedList
with “2003” and “Miesner, Trent”, “2326” and “Gates, Bill”, and “2200” and “Bush,
George.” The SortedList will store the data ordered by Key, which in this case,
is customer ID. Consequently, when the data is bound to the DropDownList, it
is in the following order:
| 2003 |
Miesner, Trent |
| 2200 |
Bush, George |
| 2326 |
Gates, Bill |
What you really wanted, of course, was to display the list alphabetically. To accomplish that, we simply intentionally use two wrongs to make a right.
Loading the SortedList Backward
Our first wrong will be to load the SortedList backward. We will add our Keys
(the customer IDs) as the SortedList’s Values and our Values (the customer names)
as the SortedList’s Keys. So, now our SortedList looks like this:
| Bush, George |
2200 |
| Gates, Bill |
2326 |
| Miesner, Trent |
2003 |
Here’s the code that loads the SortedList backward:
DIM slCustomers as SortedList = New SortedList
' fill SortedList backwards…normal syntax is Add(Key, Value); we load
' our Values first and Keys second
slCustomers.Add("Miesner, Trent", "2003")
slCustomers.Add("Bush, George", "2200")
slCustomers.Add("Gates, Bill", "2326")
|
The idea is the same if we were loading data from a stored procedure call instead.
In this case, we’d simply loop through the DataReader and call .Add
like this:
sdrSQLDataReader = scSQLCommand.ExecuteReader()
DO WHILE sdrSQLDataReader.READ()
slCustomers.Add(sdrSQLDataReader("CustomerName"), sdrSQLDataReader("CustomerID"))
LOOP
sdrSQLDataReader.Close()
|
Binding the Data Backward
If we now bind the SortedList to the DropDownList in the usual way, our DropDownList
will display the customer IDs instead of the customer names. So, our second
wrong is to bind the SortedList to the DropDownList backward:
' this may not look backward but it is…DataValueField is usually
' bound to "Key" and DataTextField is usually bound to "Value"
ddlCustomers.DataSource = slCustomers
ddlCustomers.DataValueField = "Value"
ddlCustomers.DataTextField = "Key"
ddlCustomers.DataBind()
|
Of course, we need a DropDownList on our page that has an ID of ddlCustomers:
|
<ASP:DropDownList AutoPostBack id="ddlCustomers" runat="server" />
|
Our complete page would look like this:
<%@ Page Language="VB" Debug="true" %>
<%@ Import Namespace="System" %>
<%@ Import Namespace="System.Collections" %>
<script language="VB" runat=server>
Sub Page_Load(source As Object, E As EventArgs)
DIM slCustomers as SortedList = New SortedList
' fill sorted list backwards…normal syntax is Add(Key, Value); we load our Values first and Keys second
slCustomers.Add("Miesner, Trent", "2003")
slCustomers.Add("Bush, George", "2200")
slCustomers.Add("Gates, Bill", "2326")
' bind the data backwards
ddlCustomers.DataSource = slCustomers
ddlCustomers.DataValueField = "Value"
ddlCustomers.DataTextField = "Key"
ddlCustomers.DataBind()
End Sub
</script>
<html>
<head>
</head>
<body>
<form runat=server>
<asp:DropDownList id="ddlCustomers" runat="server" />
</form>
</body>
</html>
|
Adding an Item
If you store your SortedList in Session State, you can use the SortedList throughout
your application without going back to the database on each page you want to
show the DropDownList. If you add a new item, you can simply use the .Add
method to update the SortedList rather than pulling the data from the database
again. (Of course, you still need to insert the data into the database; you
just don't need to read the data back out.)
Conclusion
Loading SortedLists “backward” gives you the ability to sort by Value rather
than Key. You can use this trick, along with binding your data “backward” to
achieve your desired result of having a DropDownList that is sorted by Value.