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.

 

[JohnH]

I would go for the expression column.

Even though basically the same as previous sample, I updated the sample in post 12 to include a strongly typed dataset against a SQL Server database file. You can see in dataset designer relation and helper column is added, and Parent expression set there, and the column is not part of database table.

 

[divjoy]

Hi JohnH,

Thank you very much for taking the time to put together a sample for my benefit.

I am studying it now and its beginning to make sense.

You have three columns Parent column pcol, Child column ccol and Helper Column hcol

pcol contains the parent id in the parent table ptable

ccol contains the parent id in the child table ctable

and hcol is the helper column added to the child table.

Then both tables are added to the dataset ds together with the relation, i've used relations before, between 2 tables so I am clear so far, then you use

hcol.Expression = "Parent.ParentName" I am not clear here as there is no mention of Parent in any of the datatables as far as I can see


Then the event handles a click on any column heading but compares to see if e.ColumnIndex = ColumnParentID index?

I dont see where the column name 'ColumnParentID' is created?

I am not clear here exactly how it all works, but it does work!, so I will stick with it, I usually have to read it a few time and let it sink in to get it, But thank you again for going to the effort and solving what is quite a tricky problem.

I also enjoyed reading your code, it great to see someone else's work.

Private Sub DataGridView1_ColumnHeaderMouseClick(sender As Object, e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles DataGridView1.ColumnHeaderMouseClick
        Dim c = Me.DataGridView1.Columns("ColumnParentID")
        If e.ColumnIndex = c.Index Then 'programmatically sort by helper column

Thanks again....

 

[JohnH]

hcol.Expression = "Parent.ParentName" I am not clear here as there is no mention of Parent in any of the datatables as far as I can see

See post 11, I linked to article for you to read about Expression property, in particular the section about Parent/Child Relation Referencing. In short, "Parent" is a reference to the parent table through the relation between the parent/child tables.

Also, since you're using tableadapter/strongly typed dataset, reviewing the sample for SQL db and the dataset in designer would be more relevant for you. It's the same approach and result though.

 

[divjoy]

Hi JohnH,

Thank you for the excellent explanation and examples, I see now in post #11 its all explained there, I missed it first time around.

Its great to see other peoples code too.

Looks simple enough now to implement, so I'll do it shortly.

I wonder in the next gen of vb has this problem been sorted (excuse the pun!)

kind regards

Gerry
Thanks again