Sorting ComboBox Items from bound DataSource

Question

I've been trying over and over again to get my ComboBox items to sort correctly, but nothing I do seems to work. I've looked at a number of SO topics with various suggestions for making it work but no matter what I try, every time I load my form, the items in my ComboBox will not be sorted. Here's the basic code I'm using to populate the ComboBox:

'ClaimCodes is a class-level variable
Private ClaimCodes As DataTable

Private Sub LoadCodeComboBoxes()
    Dim CodeTable As DataTable
    Dim DescriptionTable As DataTable
    Dim DescriptionBinding As New BindingSource
    Dim CodeBinding As New BindingSource

    Using MyDB As New DbConnection("my connection string")
        ClaimCodes = MyDB.ExecuteStatement("SELECT code, descr FROM code_table WHERE code > 0")
    End Using

    CodeTable = ClaimCodes.Copy
    CodeTable.DefaultView.Sort = "code ASC"
    CodeTable = CodeTable.DefaultView.ToTable

    DescriptionTable = ClaimCodes.Copy
    DescriptionTable.DefaultView.Sort = "descr ASC"
    DescriptionTable = DescriptionTable.DefaultView.ToTable

    CodeBinding.DataSource = CodeTable
    CodeBinding.Sort = "code"

    DescriptionBinding.DataSource = DescriptionTable
    DescriptionBinding.Sort = "descr"

    With Me.cboClaimCode
        .DataSource = Nothing
        .Items.Clear()
        .DataSource = CodeBinding
        .ValueMember = "code"
        .DisplayMember = "code"
        .AutoCompleteSource = AutoCompleteSource.ListItems
        .AutoCompleteMode = AutoCompleteMode.SuggestAppend
        .SelectedIndex = -1
    End With

    With Me.cboClaimCodeDescription
        .DataSource = Nothing
        .Items.Clear()
        .DataSource = DescriptionBinding
        .ValueMember = "code"
        .DisplayMember = "descr"
        .AutoCompleteSource = AutoCompleteSource.ListItems
        .AutoCompleteMode = AutoCompleteMode.SuggestAppend
        .SelectedIndex = -1
    End With
End Sub

I've tried various iterations of the above, including:

• Removed the BindingSource objects completely and directly used the XXXTable.DefaultView
• Apply the Sort at the DataTable level, the BindingSource level, and both
• Changing the SelectedIndex property of the ComboBox to display an item instead of setting it to -1 (unselected)
• Changing the order of the ComboBox property setting so that the DataSource is set before/after the ValueMember and DisplayMember
• Setting the .Sorted property of the ComboBox to true (of course, that throws an exception because the object is bound to the DataSource)

The ClaimCodes DataTable (before any sorting has been applied) has 17 rows:

+---------------------+
| code |    descr     |
+---------------------+
| 10   | <code desc>  |
| 11   | <code desc>  |
| 13   | <code desc>  |
| 14   | <code desc>  |
| 15   | <code desc>  |
| 30   | <code desc>  |
| 35   | <code desc>  |
| 99   | <code desc>  |
| 1    | <code desc>  |
| 36   | <code desc>  |
| 54   | <code desc>  |
| 60   | <code desc>  |
| 29   | <code desc>  |
| 61   | <code desc>  |
| 50   | <code desc>  |
| 71   | <code desc>  |
| 70   | <code desc>  |
+---------------------+

After I apply sorting, I can check the object (DataTable and/or BindingSource) and look at the results in the Watch window. From there, they show sorted as expected (1, 10, 11, etc.). However, when the form is displayed, the items in the ComboBox are not sorted and show up in the same order as the original, unsorted DataTable (10, 11, 13, etc.).

Just a few examples of SO questions I've looked at for inspiration:

• Sort string items in a datatable as int using c# (doesn't really apply b/c I've verified that the values are stored in the DataTable as System.Int32)
• Sort the items in the combobox in vb net
• Sort combobox items from datasource (GetScheme("Tables")
• how to sort data in my combobox
• Sort combobox alphabetically
• how to sorting item in combo box by Alphabet

I feel like I'm probably overlooking something really simple/stupid, but I've gone over and over this with no progress and can't figure out why. What is it that I'm doing wrong here?

One other thing that I just thought of which might have something to do with this: These ComboBoxes have the following properties:

• DrawMode: OwnerDrawFixed
• DropDownStyle: DropDownList
• FlatStyle: System
• FormattingEnabled: True

Not sure if OwnerDrawFixed might have something to do with it, but I'm using that to be able to implement a custom .DrawItem event handler to highlight the ComboBox when it's selected.

---

QUICK UPDATE:

I switched the DrawMode on one of the ComboBoxes back to Normal and tried again, but the items were still "unsorted". It doesn't appear that that is the cause of the issue.

---

ADDITIONAL CODE:

As mentioned, I'm using a custom DrawItem handler for the ComboBoxes for visual styling when it's selected. Here's the code for that event handler, which is basically reused on each of the ComboBoxes:

Private Sub cboClaimCode_DrawItem(ByVal sender As Object, ByVal e As System.Windows.Forms.DrawItemEventArgs) Handles cboClaimCode.DrawItem
    Dim ComboBox As ComboBox = TryCast(sender, ComboBox)
    Dim ComboBoxFont As Font = ComboBox.Font
    Dim ComboBoxColor As Color
    Dim TextColor As Color
    Dim ComboBoxBounds As Rectangle = e.Bounds
    Dim TextBrush As SolidBrush
    Dim ComboBoxBrush As SolidBrush

    If (e.State And DrawItemState.Selected) = DrawItemState.Selected Then
        TextColor = SystemColors.WindowText
        ComboBoxColor = Colors.PaleBlue
    Else
        TextColor = SystemColors.WindowText
        ComboBoxColor = SystemColors.Window
    End If

    TextBrush = New SolidBrush(TextColor)
    ComboBoxBrush = New SolidBrush(ComboBoxColor)

    e.Graphics.FillRectangle(ComboBoxBrush, ComboBoxBounds)

    If e.Index >= 0 Then
        If Not IsCellEmpty(ClaimCodes(e.Index)("code")) Then
            e.Graphics.DrawString(ClaimCodes(e.Index)("code").ToString, ComboBoxFont, TextBrush, New RectangleF(e.Bounds.X, e.Bounds.Y, e.Bounds.Width, e.Bounds.Height))
        End If
    End If
End Sub

Answer 1

"FINAL" UPDATED CODE TO INCORPORATE SUGGESTIONS FROM ORIGINAL COMMENTS

Now that I was able to find the cause of the unexpected behavior, I've gone back and done some code clean-up to incorporate the suggestions made by @Jimi. I've removed the class-level DataTable objects entirely and gotten rid of all the DefaultView "clutter" in the LoadCodeComboBoxes() method (NOTE: I've fully qualified most of the object types, just for the sake of absolute clarity. Feel free to remove the "extraneous" namespaces as needed.):

Private Sub LoadCodeComboBoxes()
    Dim CodeTable As System.Data.DataTable
    Dim DescriptionBinding As BindingSource
    Dim CodeBinding As BindingSource

    Using MyDB As New DbConnection("my connection string")
        CodeTable = MyDB.ExecuteStatement("SELECT code, descr FROM code_table WHERE code > 0")
    End Using

    CodeBinding = New BindingSource(CodeTable.Copy, Nothing) With {.Sort = "code ASC"}
    DescriptionBinding = New BindingSource(CodeTable.Copy, Nothing) With {.Sort = "descr ASC"}

    With Me.cboClaimCode
        .DataSource = Nothing
        .DisplayMember = "code"
        .ValueMember = "code"
        .DataSource = CodeBinding
        .AutoCompleteSource = AutoCompleteSource.ListItems
        .AutoCompleteMode = AutoCompleteMode.SuggestAppend
        .SelectedIndex = -1
    End With

    With Me.cboClaimCodeDescription
        .DataSource = Nothing
        .DisplayMember = "descr"
        .ValueMember = "code"
        .DataSource = DescriptionBinding
        .AutoCompleteSource = AutoCompleteSource.ListItems
        .AutoCompleteMode = AutoCompleteMode.SuggestAppend
        .SelectedIndex = -1
    End With
End Sub

Also, by eliminating the class-level DataTables and taking the suggestion to use the event argument's properties, I was able to create a more "generic" event handler that I can assign to any ComboBox:

Public Sub DrawComboBoxItem(ByVal sender As Object, ByVal e As System.Windows.Forms.DrawItemEventArgs)
    If TypeOf (sender) Is System.Windows.Forms.ComboBox Then
        Dim ComboBox As System.Windows.Forms.ComboBox = DirectCast(sender, System.Windows.Forms.ComboBox)
        Dim ComboBoxColor As System.Drawing.Color
        Dim TextColor As System.Drawing.Color

        If (e.State And System.Windows.Forms.DrawItemState.Selected) = System.Windows.Forms.DrawItemState.Selected Then
            TextColor = System.Drawing.SystemColors.WindowText
            ComboBoxColor = System.Drawing.Color.Blue
        Else
            TextColor = System.Drawing.SystemColors.WindowText
            ComboBoxColor = System.Drawing.SystemColors.Window
        End If

        Using TextBrush As New System.Drawing.SolidBrush(TextColor)
            Using ComboBoxBrush As New System.Drawing.SolidBrush(ComboBoxColor)
                e.Graphics.FillRectangle(ComboBoxBrush, e.Bounds)

                If e.Index >= 0 Then
                    e.Graphics.DrawString(ComboBox.GetItemText(ComboBox.Items(e.Index)),
                                      e.Font,
                                      TextBrush,
                                      New RectangleF(e.Bounds.X, e.Bounds.Y, e.Bounds.Width, e.Bounds.Height))
                End If
            End Using
        End Using
    End If
End Sub

This also incorporates the Using statement for the SolidBrush objects so that they are disposed of properly within the method.

With that in place, I removed all of the individual in-form event handlers (cboClaimCode_DrawItem, etc.) and just assigned them in a method that applies other event handlers:

Private Sub AddFormControlHandlers()
    AddHandler Me.cboClaimCode.DrawItem, AddressOf DrawComboBoxItem
    AddHandler Me.cboClaimCodeDescription.DrawItem, AddressOf DrawComboBoxItem
    [...]
End Sub

I hope that helps anyone else looking for how to populate their ComboBoxes. Thank you again, @Jimi and @LarsTech for your help and suggestions.

Answer 2

Thanks to @LarsTech and @Jimi for their comments which helped lead me to find what the problem was. As I was testing, I also noticed that the selections weren't working exactly as expected either (I hadn't noticed that before), so that and their comments pointed me in a different direction altogether. This is basically a "rough draft" of the solution I threw together to get it working. Take a look at my other, accepted answer for a "cleaned-up" version of the code that incorporates suggestions from the comments.

As mentioned in the question, I've got a custom DrawItem event handler for these ComboBoxes. That event handler was referencing the "original", unsorted DataTable to add some UX "prettification" for the users to be able to more easily identify when the ComboBox is selected (without it, the users were having difficulty seeing when the box was in focus).

To address this, I moved the DataTables that were used by the ComboBoxes up to the class-level instead of within the method:

Public Class MyForm
    Private ClaimCodes As DataTable
    Private ClaimCodeDescriptions As DataTable
    [...]
End Class

(yes, I reused the ClaimCodes object name from before)

Then I changed my LoadCodeComboBoxes() method to fill those class-level DataTable objects instead of method-local objects:

Private Sub LoadCodeComboBoxes()
    Dim CodeTable As DataTable
    Dim DescriptionBinding As New BindingSource
    Dim CodeBinding As New BindingSource

    Using MyDB As New DbConnection("my connection string")
        CodeTable = MyDB.ExecuteStatement("SELECT code, descr FROM code_table WHERE code > 0")
    End Using

    ClaimCodes = CodeTable.Copy
    ClaimCodes.DefaultView.Sort = "code ASC"
    ClaimCodes = ClaimCodes.DefaultView.ToTable

    ClaimCodeDescriptions = CodeTable.Copy
    ClaimCodeDescriptions.DefaultView.Sort = "descr ASC"
    ClaimCodeDescriptions = ClaimCodeDescriptions.DefaultView.ToTable

    CodeBinding.DataSource = ClaimCodes
    CodeBinding.Sort = "code"

    DescriptionBinding.DataSource = ClaimCodeDescriptions
    DescriptionBinding.Sort = "descr"

    With Me.cboClaimCode
        .DataSource = Nothing
        .DisplayMember = "code"
        .ValueMember = "code"
        .DataSource = CodeBinding
        .AutoCompleteSource = AutoCompleteSource.ListItems
        .AutoCompleteMode = AutoCompleteMode.SuggestAppend
        .SelectedIndex = -1
    End With

    With Me.cboClaimCodeDescription
        .DataSource = Nothing
        .DisplayMember = "descr"
        .ValueMember = "code"
        .DataSource = DescriptionBinding
        .AutoCompleteSource = AutoCompleteSource.ListItems
        .AutoCompleteMode = AutoCompleteMode.SuggestAppend
        .SelectedIndex = -1
    End With
End Sub

Finally, I updated my DrawItem event handlers to use the individual class-level DataTables:

Private Sub cboClaimCodeDescription_DrawItem(ByVal sender As Object, ByVal e As System.Windows.Forms.DrawItemEventArgs) Handles cboClaimCodeDescription.DrawItem
    Dim ComboBox As ComboBox = TryCast(sender, ComboBox)
    Dim ComboBoxFont As Font = ComboBox.Font
    Dim ComboBoxColor As Color
    Dim TextColor As Color
    Dim ComboBoxBounds As Rectangle = e.Bounds
    Dim TextBrush As SolidBrush
    Dim ComboBoxBrush As SolidBrush

    If (e.State And DrawItemState.Selected) = DrawItemState.Selected Then
        TextColor = SystemColors.WindowText
        ComboBoxColor = Colors.Blue
    Else
        TextColor = SystemColors.WindowText
        ComboBoxColor = SystemColors.Window
    End If

    TextBrush = New SolidBrush(TextColor)
    ComboBoxBrush = New SolidBrush(ComboBoxColor)

    e.Graphics.FillRectangle(ComboBoxBrush, ComboBoxBounds)

    If e.Index >= 0 Then
        If Not IsCellEmpty(ClaimCodeDescriptions(e.Index)("descr")) Then
            e.Graphics.DrawString(ClaimCodeDescriptions(e.Index)("descr").ToString, ComboBoxFont, TextBrush, New RectangleF(e.Bounds.X, e.Bounds.Y, e.Bounds.Width, e.Bounds.Height))
        End If
    End If
End Sub

Now that each is using its own, sorted DataTable, everything seems to be working exactly as expected.