Question Sort a DataGridView by values displayed in a combobox

[divjoy]

Hi, Im using vb.net 2010 express and sql server express.

I have a datagridview and want to sort it by a column that is a combobox. The combobox display member shows a persons name but that data member is their personnel no.

When I sort automatically this sorts by the hidden 'personnel no' and is not what the user wants.

I have trawled the internet but is seems it's something that cant be done.

Has anyone found a solution to this, I would imagine its quite a common problem at this stage.

kind regards

Ger.

 

[jmcilhinney]

Firstly, do you really mean "sort" or do you actually mean "filter"? I ask because you don't sort by a value but by a column. Either way, what you want to do is quite easy. If you're not already, populate a DataTable with the data, bind it to a BindingSource and bind that to the grid. When the user makes a selection in the ComboBox, you can get the displayed value from its Text property. You can then use that to set the Sort or Filter property of the BindingSource.

Write the code that you think is correct, first consulting the MSDN documentation for the Sort and/or Filter property of the BindingSource class if required, and post that code here with an explanation of what you expect and what happens if it doesn't work.

 

[divjoy]

I have a datagridview and want to sort it by a column that is a column of comboboxes.

I have no code because according to the www, it cant be done ! I have not attempted myself yet as I was hoping someone had already had it done and I want to promote code ruse !

 

[jmcilhinney]

I was hoping someone had already had it done and I want to promote code ruse !

Clearly that's a euphemism for copying someone else's code so that you don't have to write it yourself. I've told you exactly how it can be done so you simply have to follow my instructions. If you think that the web is telling you that it can't be done then you have been searching for, or at least finding, the wrong information. Sorting just about any column in a DataGridView bound to a DataTable is done the same way.

 

[JohnH]

jmcilhinney, I think you misunderstand the question. Although it could be more accurately described, as I understand OP has a DataGridViewComboBoxColumn and want to have that column sorted by its DisplayMember, rather than its ValueMember. As OP says, it doesn't appear to be easy, at least not if DataGridView is bound to a DataSource. It was discussed for example here: c# - Sorting Datagridview's Column by text and not value - Stack Overflow and one suggestion is to add a support column for the display values and use that for sorting.

 

[jmcilhinney]

jmcilhinney, I think you misunderstand the question. Although it could be more accurately described, as I understand OP has a DataGridViewComboBoxColumn and want to have that column sorted by its DisplayMember, rather than its ValueMember. As OP says, it doesn't appear to be easy, at least not if DataGridView is bound to a DataSource. It was discussed for example here: c# - Sorting Datagridview's Column by text and not value - Stack Overflow and one suggestion is to add a support column for the display values and use that for sorting.

I see what you mean, my mistake. @divjoy, my apologies. I was too hasty in reading your posts.

 

[divjoy]

Hi,

Thank you JohnH for taking the time to read my questions and reply.

I have looked again at all the links and the only suggestions that seems plausable is to create a hidden column, store the combobx value in this and sort be this column.

I'll give it a try.

kind regards


Can I advise Jmcihinney my comment about code reuse was not a euphemism, it's a joke and if you cant see that you need to chillax and take a holiday.

 

[divjoy]

Hi,

Here some test code that seems to work but...The last line produces an error about icomparer any suggestions?

Dim col As New DataGridViewTextBoxColumn
        col.HeaderText = "HH Name"
        col.Name = "HHName"
        DataGridView1.Columns.Add(col)
        Dim r As Integer = ds.Tables("StaffPaidAL").Rows.Count
        'Dim HHName As String
        'MsgBox(r)
        If r > 0 Then
            For x As Integer = 0 To r - 1
                'MsgBox(x)
                'HHName = Me.DataGridView1.Rows.Item(x).Cells(1).FormattedValue
                Me.DataGridView1.Rows.Item(x).Cells("HHName").Value = Me.DataGridView1.Rows.Item(x).Cells(1).FormattedValue
            Next x
        End If
        Me.DataGridView1.Columns.Item("HHName").SortMode = DataGridViewColumnSortMode.Automatic
        Me.DataGridView1.Sort(Me.DataGridView1.Columns.Item("HHName"))

 

[JohnH]

Hi,

Here some test code that seems to work but...The last line produces an error about icomparer any suggestions?

        Me.DataGridView1.Sort(Me.DataGridView1.Columns.Item("HHName"))

This should get you sorted out: DataGridView.Sort Method (System.Windows.Forms)

 

[divjoy]

Hi JohnH,

Thanks for that tried the methods but now throwing a different error
but still not sorting by added col HHName?

Err Msg "Databound DataGridView Can only be sorted by a data-bound column"

In other words my added column is not databound to the source the datatable and thererofr cannot be used to sort the datagridview, is their a work around?

Dim cmd As New SqlCommand
        '...Get Employees Annual Leave Record for DataGridView1...
               Dim sqlTxt As String
        sqlTxt = "SELECT * FROM ????? WHERE ?????"
        cmd = New SqlCommand(sqlTxt, conn)
        cmd.Parameters.AddWithValue("@HHAreaID", area)
        cmd.Parameters.AddWithValue("@PPEndDate", PPEndDate)
        Me.da1 = New SqlDataAdapter(cmd)
        da1.MissingSchemaAction = MissingSchemaAction.AddWithKey
        Me.da1.Fill(ds, "StaffPaidAL")
        '...Build DataGridView1 and link to BindingNavigator's bs1   
        Me.bs1.DataSource = ds.Tables("StaffPaidAL")
        Me.BindingNavigator1.BindingSource = bs1
        Me.DataGridView1.DataSource = Me.BindingNavigator1.BindingSource
        Me.DataGridView1.AutoResizeColumns()
        Me.DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells
        If Not Me.DataGridView1.Columns.Contains("HHName") Then
            Dim col As New DataGridViewTextBoxColumn
            col.HeaderText = "HH Name"
            col.Name = "HHName"
            DataGridView1.Columns.Add(col)
        End If
       Dim r As Integer = ds.Tables("StaffAL").Rows.Count
        If r > 0 Then
            For x As Integer = 0 To r - 1
                Me.DataGridView1.Rows.Item(x).Cells("HHName").Value = Me.DataGridView1.Rows.Item(x).Cells(1).FormattedValue
            Next x
        End If
        Me.DataGridView1.Columns.Item("HHName").SortMode = DataGridViewColumnSortMode.Automatic
        Me.DataGridView1.Sort(Me.DataGridView1.Columns.Item("HHName"), ComponentModel.ListSortDirection.Ascending)
        cmd.Dispose()
    End Sub

 

[JohnH]

In other words my added column is not databound to the source the datatable and thererofr cannot be used to sort the datagridview, is their a work around?

Add the column to hold "FormattedValue" to the DataTable, as was the idea in suggestion, then it will be included in binding (but DGV column can be hidden).

Note also that if you add a DataRelation to the DataSet you can set Expression property for this column like "Parent.ParentName" and it will be filled with the "FormattedValue" (ie DisplayValue) automatically, no need to loop to add these values manually. DataColumn.Expression Property (System.Data) (see Parent/Child Relation Referencing)

As for when and how to perform the sort, when you say automatically" that indicates user clicking a header cell. For that to happen (since you must sort on a different column than user clicks) you must set that DGV columns SortMode to Programmatic and handle DGV.ColumnHeaderMouseClick event, where you check if e.ColumnIndex is that column, then you can get the Column.HeaderCell.SortGlyphDirection and perform the DGV.Sort on the hidden helper column, then update SortGlyphDirection. You'd want to toggle sorting ascending/descending every time user clicks that header, in other words if SortGlyphDirection is SortOrder.Ascending when user clicks, then you sort Descending, and otherwise opposite.

 

[JohnH]

Since this in all is fairly complex I decided to write a sample project that shows what has been explained here, see attached project.

Updated sample, one form with a manual local dataset, and one form with a strongly typed dataset (SQL Server database file).

 

[divjoy]

Hi JOhnH,

TBH I had thought of adding the column to the underlying table, but was unsure as I am saving any changes additions or deletions to the dataset back to the database via a tableadapter and I don't know how the extra column will work with that?

Regards

 

[JohnH]

As an expression column it will not be included in data transfers of the tableadapter (ie not included in any SQL commands), it will be a readonly DataColumn in the strongly typed DataTable.
Compared to the "manual" sample, this expression column can be added in dataset designer, including adding relation (if not already there) and setting expression.

 

[divjoy]

Hi JOhnH,

OK, so what I'm thinking is..,,add another column to my datatable (held in my dataset) easily done. empdetails(personnelno, startdate, emphours)

But now I need to populate each cell of the new column with a text value representing the name of the employee, who personnelno is held in the first column.

Again no big deal a simple query will return me the names of each emp. But then I may have to run the query 30 times, over a slow network connection in some sites....

Thus my sort routine will take about 20 seconds not a long time, but not instant either !

OK another way is retrieve the emp names into a separate table (personnleNo, name) in the same order

Then populate the name column in my first table from the data already in the second table.

bind to my datagridview, hide my name column but sort by the hidden column !

Sounds like a plan what do you think ?

Seems very convoluted as I have 4 datagrids to do this too....