Click here to Skip to main content
15,881,516 members
Please Sign up or sign in to vote.
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
Updated 17-Nov-22 22:39pm
v2

1 solution

 
Share this answer
 
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

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900