Sort a DataGridView by DisplayMember

Question

Hi,

I have a DataGridView that is bound to a DataTable. In this table there are some foreign keys. I am then using the CellFormatting event to get the corresponding text from another database table for each foreign key.

I want to sort the DataGridView when the user clicks the header. Automatic sorting works but is not correct as it is Sorting on the ValueMember (ForeignKey ID) and not on the DisplayMember (the text).

I tried using the SortCompare event but then I read that it does not work on DataGridViews that use the DataSource property.

How can this be done?

Thanks

Answer 1

Fixed this by adding a nested select statement within the initial statement.

Was before:

SELECT id, column1, column2, column3_fk FROM db_table

To:

SELECT id, column1, column2, column3_fk, (select desc from db_table2 where id = db_table1.column3_fk) AS fk_description FROM db_table1