Solved

Help with sorting partial data from data column

Posted on 2014-02-24
9
209 Views
Last Modified: 2014-02-25
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
0
Comment
Question by:vcharles
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
9 Comments
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 39884683
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
 

Author Comment

by:vcharles
ID: 39884697
Hi,

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

Thanks,

V.
0
 

Author Comment

by:vcharles
ID: 39884703
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:vcharles
ID: 39884710
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
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 39884715
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
 

Author Comment

by:vcharles
ID: 39884736
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
 
LVL 63

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 39884773
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
 

Author Comment

by:vcharles
ID: 39884781
Thanks again, will get back to you in the morning.

Victor
0
 

Author Closing Comment

by:vcharles
ID: 39887348
Thank You.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question