Solved

Help with sorting partial data from data column

Posted on 2014-02-24
9
210 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

696 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