Help with sorting partial data from data column

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
vcharlesAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Fernando SotoConnect With a Mentor RetiredCommented:
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

0
 
Fernando SotoRetiredCommented:
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.
0
 
vcharlesAuthor Commented:
Hi,

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

Thanks,

V.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
vcharlesAuthor 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.
0
 
vcharlesAuthor 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.
0
 
Fernando SotoRetiredCommented:
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.
0
 
vcharlesAuthor 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
0
 
vcharlesAuthor Commented:
Thanks again, will get back to you in the morning.

Victor
0
 
vcharlesAuthor Commented:
Thank You.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.