Solved

Help with displaying partial data from data field

Posted on 2014-02-24
22
252 Views
Last Modified: 2014-02-26
Hi,

If my my data field (scountry)contains BEL 10411, how do I only display 10411 on my grid?

I am using the following code to load the grid.

FieldDataTable.DefaultView.ToTable ("True, New String ({"SCOUNTRY"})

Thanks,

Victor
0
Comment
Question by:vcharles
  • 12
  • 7
  • 3
22 Comments
 
LVL 5

Expert Comment

by:Bob Bender
ID: 39884409
Assuming that the data is ALWAYS in the same format, here is a link to a dotnetperls page to show you how it works.

http://www.dotnetperls.com/substring-vbnet

In the case of the shown example...

Dim countrySubstring As String = literal.Substring(4, 5)

Note, string handling positioning start at 0, not 1.   Hence the 4 means the 5th position.
0
 

Author Comment

by:vcharles
ID: 39884568
Thanks, will look up the link and get back to you.
0
 

Author Comment

by:vcharles
ID: 39884641
I'm afraid the link didn't help much, I need to include (SCOUNTRY, LEN(SCOUNTRY) -4, 5) to only display the last 5 digits, but the syntax is incorrect.

 Me.C1Screen2.DataSource = FilteredDTA.DefaultView.ToTable(True, New String() {"Substring(SCOUNTRY, LEN(SCOUNTRY) -4, 5)", "AGD1", "REMARKS"})

Thanks,

V.
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 5

Expert Comment

by:Bob Bender
ID: 39884674
My sample link is based on a string variable being before the SUUBSTRING phrase.  


Substring needs to prefixed by your string value that contains the BEL 10411 and followed by a period


I am not sure what the { } means in this case.
0
 

Author Comment

by:vcharles
ID: 39884677
{ } is part of the format to display the columns, I can not  declare SCOUNTRY as string similar to the example from the link because it's a column value from a table.
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 39884706
Hi Victor;

In the following statement you posted :

FilteredDTA.DefaultView.ToTable(True, New String() {"Substring(SCOUNTRY, LEN(SCOUNTRY) -4, 5)", "AGD1", "REMARKS"})

Open in new window


The ToTable method parameters are as follows:

public DataTable ToTable(
	bool distinct,               ' If true, the returned DataTable contains rows that have distinct values for all its columns
	params string[] columnNames  ' A string array that contains a list of the column names to be included in the returned DataTable.
)

Open in new window

So the columnNames parameter can only take a column name you want in the new DataTable only you can NOT include what part of that column to include.
0
 
LVL 5

Expert Comment

by:Bob Bender
ID: 39884709
From what I see, you are not using the Substring correctly.  If not mistaken, it is a method of the string class.

And, you should be able to get the column info out, I think.  

Maybe I am not the right person to help here   :)
0
 

Author Comment

by:vcharles
ID: 39884720
I don't think it can be done, to improvise I will need to loop through the table, copy the first three characters to a country column and the last 5 digits to an SN column.
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 39884772
Hi Victor;

Something like this should work

' 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: 39884777
Thank You. I will try it and get back to you in the morning EST.
0
 

Author Comment

by:vcharles
ID: 39886576
Hi,

I received error message: Variable declaration without an 'As' clause, type of Oject assumed. on the word Dim results. How do I fix this error?

Thanks,

Victor
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 39886619
Inside Visual Studio on the main menu click on, Project -> "ProjectName Properties ...", the last entry on the drop down menu. In the Window that opens, on the left side, click on Compile tab. Then on the right side find a combo box with the label, "Option: Infer", and make sure that On is the one selected from the list.
0
 

Author Comment

by:vcharles
ID: 39886938
Hi,

It worked. How do I handle situation where SCOUNTRY contains

BEL110411
BEL210411

I think you are spliting them by " " but in these cases there are no " " between country and SN.

Thank You.

Victor
0
 
LVL 62

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 39887109
Then you could change the query to this.

        Dim results = dt1.DefaultView.ToTable(True, New String() {"SCountry", "AGD1", "REMARKS"}).AsEnumerable() _
                .Select(Function(r) New With { _
                .Country = If(r.Field(Of String)("SCountry").Contains(" "), _
                              r.Field(Of String)("SCountry").Split(" ")(0).Trim(), _
                              r.Field(Of String)("SCountry").Substring(0, 3)), _
                .SN = If(r.Field(Of String)("SCountry").Contains(" "), _
                         r.Field(Of String)("SCountry").Split(" ")(1).Trim(), _
                         r.Field(Of String)("SCountry").Substring(3, 5)), _
                .AGD1 = r.Field(Of String)("AGD1"), _
                .REMARKS = r.Field(Of String)("REMARKS")})

Open in new window

0
 

Author Comment

by:vcharles
ID: 39887338
THANK YOU!
0
 

Author Comment

by:vcharles
ID: 39887371
Hi again,

Based on the code below, shouldn't the first two columns be Country and SN? For some reason they appear as the last two columns in the Grid. How do I displays them in the first two columns?


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


Thanks,

Victor
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 39887420
They should be in the order your code shows it. I tested it and it is displaying as expected. I would need to see your code how you created the table and all the code you used to fill the table.
0
 

Author Comment

by:vcharles
ID: 39887426
OK, I'll figure it out.

Thanks.

Victor
0
 

Author Comment

by:vcharles
ID: 39890137
Hi again,

Since I'm using the new table (dt) to display the data, how do you sort by SN? the old code "DVA.Sort = "SN" does not work.

Thanks,

Victor
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 39890421
You need to use the table that the data is in.

TableName.DefaultView.Sort = "SN"
0
 

Author Comment

by:vcharles
ID: 39890557
Git it.

Thanks,

Victor
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 39890612
Not a problem.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

813 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now