Help with sorting partial data from data column

vcharles
vcharles used Ask the Experts™
on
Hi,

I'm using the code in part A to sort SCOUNTRY (i.e BEL 10411). How do I modify the code in part B to sort by the last 5 digits (i.e. 10411)?

A:
 DVA.Sort = "SCOUNTRY"

B:
DVA.Sort = "Substring(SCOUNTRY, LEN(SCOUNTRY) -4, 5)"

Thanks,

Victor
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Fernando SotoRetired
Distinguished Expert 2017

Commented:
Hi Victor;

I am going to assume that DVA is a DefaultView object for a DataTable.

The Sort property is set as follows, "A string that contains the column name followed by "ASC" (ascending) or "DESC" (descending). Columns are sorted ascending by default. Multiple columns can be separated by commas.", so you can not sort on part of the column.

Author

Commented:
Hi,

Since I can not sort on part of the column, can I group by part of the column?

Thanks,

V.

Author

Commented:
Hi,

Or is it possible to loop through the table and copy the last 5 digits in the Scountry column to
an new column (i.e. SN) for every row? This would then allow me to sort by SN instead of Scountry.
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Author

Commented:
I just realized, I will also need to copy the first three characters to another column because I will need to sort by SN and Country.

Thanks,

V.
Fernando SotoRetired
Distinguished Expert 2017

Commented:
How is this Table and Columns being created?
It may be simpler to create a Table manually the way you want the columns set up and then fill the rows.

Author

Commented:
I will create two new columns, but will need help to populate them before I view the data in the Grid.  I use the code below to convert my xml file to a dataset than to a table. SearchCriteria is not included to simplify my example.

 fsLinkAO = New System.IO.FileStream(Application.StartupPath + "\AO.xml", IO.FileMode.Open)
        dtsetLinkAO.Clear()
        dtsetLinkAO.ReadXml(fsLinkAO)
        fsLinkAO.Close()

            Dim FilteredDTA As DataTable
            Dim DVA As New DataView(dtsetLinkAO.Tables(0), SearchCriteria, Nothing, DataViewRowState.CurrentRows)
            FilteredDTA = DVA.ToTable
Retired
Distinguished Expert 2017
Commented:
Hi Victor;

Something like this should work. You now have two columns for SCountry and can sort on either.

' Create a DataTable to have SCountry split into two columns
Dim dt As New DataTable()
Dim dc As New DataColumn("Country", GetType(String))
dt.Columns.Add(dc)
dc = New DataColumn("SN", GetType(String))
dt.Columns.Add(dc)
dc = New DataColumn("AGD1", GetType(String))
dt.Columns.Add(dc)
dc = New DataColumn("REMARKS", GetType(String))
dt.Columns.Add(dc)

' Query original table for the columns to make the new table with spliting SCountry column
Dim results = FilteredDTA.DefaultView.ToTable(True, New String() {"SCountry", "AGD1", "REMARKS"}).AsEnumerable() _
              .Select(Function(r) New With { _
                      .Country = r.Field(Of String)("SCountry").Split(" ")(0).Trim(), _
                      .SN = r.Field(Of String)("SCountry").Split(" ")(1).Trim(), _
                      .AGD1 = r.Field(Of String)("AGD1"), _
                      .REMARKS = r.Field(Of String)("REMARKS")})

' Loop through the rows of the results and add to new Table                      
For Each row In results
    Dim dr As DataRow = dt.NewRow()
    dr(0) = row.Country
    dr(1) = row.SN
    dr(2) = row.AGD1
    dr(3) = row.REMARKS
    dt.Rows.Add(dr)
Next

DataGridView2.DataSource = dt

Open in new window

Author

Commented:
Thanks again, will get back to you in the morning.

Victor

Author

Commented:
Thank You.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial