?
Solved

Help with sorting partial data from data column

Posted on 2014-02-24
9
Medium Priority
?
212 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

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 2000 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

How To Reduce Deployment Times With Pre-Baked AMIs

Even if we can't include all the files in the base image, we can sometimes include some of the larger files that we would otherwise have to download, and we can also sometimes remove the most time-consuming steps. This can help a lot with reducing deployment times.

Question has a verified solution.

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

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Suggested Courses

801 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