DataGridView Sort on columns combobox 'Display Value', how to do? 1

[Kieran777]

I am developing an application and using the VS2008 DataGridView control.

I have a bound DataGridView using a binding source etc.
I need to provide sorting within the DataGridView control on a column using the ‘Display Value’ of the cells rather than the value from the bound datasource.

The column uses a ComboBox that references a lookup table to get the ‘Display value’, it is this Display Value I need the user to be able to sort the datagrid on.

With AutoSort turned on it sorts on the cell value data rather than the 'Display Value' which is meaningless to the user.

Eg., the cell value (combobox) is the 'employee_id' from the bound datasource whilst the display value being the 'employee name' from a lookup table i.e., the employee table.

This seems like a fairly standard requirement for many applications. Can anyone advise if this is possible and how this can be achieved.

Regards Kieran

 

[RiverGuy]

One similar application I have comes to mind. Instead of a sort, the user picks a column from a combobox to use as a filter criteria. To set this up, instead of binding my DataGridView directly to the DataTable, I bind it to a DataView based off of the DataTable. On the SelectedIndexChanged event of your ComboBox, you can get the sort column, and change the .Sort property of your DataView to update the sorting.

 

[Kieran777]

Hello RiverGuy,

Thanks for your response.

The DataGridView in question is bound via a binding source to a datatable in a dataset which gets its data from an SQL Database Table.

The data that the SORT is required on is the datagrid column 'Display Value' of a 'ComboBox', not the value in the bound datatable. This display value (Employee Name) is not in the underlying bound datatable as it only contains the Employee_Id which is used in the DataGridView 'Employee Name' Cells to lookup the employee table to get the display value i.e. the employee name.

Hence as it stands if the user clicks to sort the 'Employee Name' column then the rows are sorted on the 'Employee_ID' which is not what is required and meaningless to the user. The displayed data i.e., 'Employee Names' are in no meaningful sequence.

As per your suggestion -

1) How could I use a DataView to incorporate the 'Employee Name' when it is not part of the underlying datatable upon which the dataview would be made and

2) This Datagridview allows the user to Update, Add and delete Data, if a DataView can resolve the sort on display value requirement then will it also allow for Updating Adding and Deleting Data in the DataGridView?

3) Have you any example code depicting similar solution.

Cheers
Kieran

 

[RiverGuy]

I don't ever use a binding source. However, here is an example all done manually that you can adapt for your application. Just add an empty DataGridView to a test form.

    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Set Up DataGridView
        Me.DataGridView1.AutoGenerateColumns = False
        Me.DataGridView1.Columns.Add(New DataGridViewComboBoxColumn())
        Me.DataGridView1.Columns(0).DataPropertyName = "Col1"

        'DataTable for ComboBox source
        Dim dt2 As New DataTable
        dt2.TableName = "Lookup"
        dt2.Columns.Add("Val", System.Type.GetType("System.Int32"))
        dt2.Columns.Add("Des", System.Type.GetType("System.String"))

        dt2.Rows.Add(New Object() {43, "smith"})
        dt2.Rows.Add(New Object() {1, "adams"})
        dt2.Rows.Add(New Object() {11, "baker"})

        'DataView to sort the items
        Dim dv As New DataView
        dv.Table = dt2
        dv.Sort = "Des"
        Dim cb As DataGridViewComboBoxColumn = Me.DataGridView1.Columns(0)
        cb.DataSource = dv
        cb.DisplayMember = "Des"
        cb.ValueMember = "Val"

        'DataTable to bind to DataGridView
        Dim dt1 As New DataTable
        dt1.TableName = "Values"
        dt1.Columns.Add("Col1", System.Type.GetType("System.String"))
        Me.DataGridView1.DataSource = dt1
    End Sub

 

[Kieran777]

Hello RiverGuy,

Thanks for your reply and code example.

I have adjusted it slightly to depict my situation.
What I need to happen is when the user clicks on column header to sort the column I need to have the displayed values sorted in Ascending or Decending order.

The example code sorts on the column value, I need have the sorting done on the display value.

To replicate my problem

Create a new Project and paste code into/replace form load event:

Code:

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Set Up DataGridView
        Me.DataGridView1.AutoGenerateColumns = False
        Me.DataGridView1.Columns.Add(New DataGridViewComboBoxColumn())
        Me.DataGridView1.Columns(0).DataPropertyName = "Col1"
        Me.DataGridView1.Columns(0).SortMode = DataGridViewColumnSortMode.Automatic


        'DataTable for ComboBox source
        Dim dt2 As New DataTable
        dt2.TableName = "Lookup"
        dt2.Columns.Add("Value", System.Type.GetType("System.Int32"))
        dt2.Columns.Add("Desc", System.Type.GetType("System.String"))

        dt2.Rows.Add(New Object() {1, "Smith"})
        dt2.Rows.Add(New Object() {10, "Adams"})
        dt2.Rows.Add(New Object() {12, "Baker"})
        dt2.Rows.Add(New Object() {43, "Jones"})
        dt2.Rows.Add(New Object() {5, "Zeta"})

        'DataView to sort the items
        Dim dv As New DataView
        dv.Table = dt2
        dv.Sort = "Desc"

        Dim cb As DataGridViewComboBoxColumn = Me.DataGridView1.Columns(0)
        cb.DataSource = dv
        cb.DisplayMember = "Desc"
        cb.ValueMember = "Value"

        'DataTable to bind to DataGridView
        Dim dt1 As New DataTable
        dt1.TableName = "Values"
        dt1.Columns.Add("Col1", System.Type.GetType("System.Int32"))
        Me.DataGridView1.DataSource = dt1
    End Sub

Run and select one of each name from the combobox in separate rows of the datgrid. Then click on the column header to sort. Note the sort order is not on the display value.

What I need to achieve is to have the column sorted on the column displayed value rather than the column value.

All help appreciated.

regards
Kieran

 

[RiverGuy]

I understand your issue now. I can't think of a simplistic solution. If all else fails, what I would probably end up doing would be to:

1. Modify the source query for your DataTable which fills the ComboBox to use a concatenated ValueMember. So, instead of having "1" as the value for Smith, the value would be something like "Smith00001."

2. Modify your Select query for your DataAdapter which fills the DataTable for your main table. So, you would have a join to your Employees table. Something like "SELECT t.*, e.LastName + RIGHT('0000' + CONVERT(VARCHAR(6), t.employee_id),5) AS Employee FROM transactions t INNER JOIN employees e ON......"

3. Modify your Insert, Update and Delete commands for your DataAdapter to supply just the employee_id to your parameters collection instead of the concatenated value.

It won't be elegant code by any means, but off the top of my head, that's the only way I can think of at the moment to accomplish this.