How do I custom sort a datatable column by 3 values in VB.NET

Question

0.00/5 (No votes)

See more:

I am transitioning VBA code to VB.net. I ran into an issue where I needed to create a custom sort based on three text values: Employee, Spouse, Child.

There are a few articles on custom sorts but they all revolve around either numerical values or dates. '

Here is what I have so far:

VB.NET

Sub Sort_information()

    Dim sort_str As String
    Dim dv As DataView
    Dim col = 2 'Or name

    dv = individual.dt.DefaultView


    Dim anyHasValue = Demographic_data_view.Rows.
        OfType(Of DataGridViewRow).
        Where(Function(r) Not r.IsNewRow AndAlso r.Cells.
            OfType(Of DataGridViewTextBoxCell).
            Any(Function(c) c.OwningColumn.Index = col AndAlso
                If(c.Value, String.Empty).ToString().Trim().Length > 0)).
        Any()

    'this line of code allows you to sort a range of columns that contain the data
    If anyHasValue = True Then
        sort_str = "Employee_SSN ASC, "
    Else
        'Sort by individual ID column
        sort_str = "Individual_ID ASC, "
    End If

    'dv = New DataView(individual.dt,, "type Asc", DataViewRowState.CurrentRows)

    '.SortFields.Add Key:=Range("H10:H" & vRowc), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:="Employee,Spouse,Child,"
    '.SortFields.Add Key:=Range("D10:D" & vRowc), SortOn:=xlSortOnValues, Order:=xlAscending
    '.SortFields.Add Key:=Range("E10:E" & vRowc), SortOn:=xlSortOnValues, Order:=xlAscending
    '.SortFields.Add Key:=Range("W10:W" & vRowc), SortOn:=xlSortOnValues, Order:=xlAscending

    'the excel sortfilds will be converted over to vb.net as text values incorportated into the sort_str variable

    dv.Sort = sort_str
    Demographic_data_view.DataSource = dv
End Sub

The goal will be to sort the information based on SSN or ID (depends if SSN is provided), "Employee,Spouse,Child,", first_name, Last_name, and Plan_name

If I exclude the custom sort I can easily create a string variable to make a sort for the other columns. However, the order of employee, spouse, and child is absolutely necessary.

I really appreciate any help on this matter.

What I have tried:

I tried to find articles that were specific to custom sorting on 3 text values. but I could not come up with anything.

Posted 17-Nov-22 21:53pm

Member 11856456

Updated 17-Nov-22 22:39pm

Richard Deeming

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RickZeeland · Answer 1 · 2022-11-17T22:04:00

Solution 1

See answer here: vb-net-table-sort-with-multiple-columns[^]

And: DataView.Sort Property (System.Data) | Microsoft Learn[^]

Posted 17-Nov-22 22:04pm

RickZeeland

Updated 17-Nov-22 22:10pm

v2

Comments

Member 11856456 19-Nov-22 13:14pm

These are the same types of articles I have already read. These do not help with custom sorts with mulitple values in 1 column.

RickZeeland 19-Nov-22 15:48pm

If the data comes from a database, then it might be better to use SQL to do the sorting.

Member 11856456 22-Nov-22 10:25am

I am still at a loss, I even have looked into linq to help solve the issue. But I cant figure out how to code the linq query correctly.

the query has to be set up so that SSN or Individual_ID is sorted first, the list of(EMployee, Spouse, Partner, Child) is second, then last_name, and finally first_name.

Dim anyHasValue = Demographic_data_view.Rows.
OfType(Of DataGridViewRow).
Where(Function(r) Not r.IsNewRow AndAlso r.Cells.
OfType(Of DataGridViewTextBoxCell).
Any(Function(c) c.OwningColumn.Index = col AndAlso
If(c.Value, String.Empty).ToString().Trim().Length > 0)).
Any()

'this line of code allows you to sort a range of columns that contain the data
If anyHasValue = True Then
sort_str = "Employee_SSN ASC, "
'sort_str = "Employee_SSN"
Else
'Sort by individual ID column
sort_str = "Individual_ID ASC, "
'sort_str = "Individual_ID"
End If

'.SortFields.Add Key:=Range("H10:H" & vRowc), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:="Employee,Spouse,Child,"

'the excel sortfilds will be converted over to vb.net as text values incorportated into the sort_str variable
dt.DefaultView.Sort = sort_str + " Last_name ASC, First_name ASC, Benefit_type ASC"
dt = dt.DefaultView.ToTable()

'Dim order As List(Of String) = New List(Of String)() From {
' "Employee",
' "Spouse",
' "Partner",
' "Child"
'}
'Dim query = From item In order Join row In individual.dt.AsEnumerable() On item Equals row.Field(Of String)("Relationship") Select row
'Dim result = query.CopyToDataTable()
'Demographic_data_view.DataSource = result