Solved

Help with displaying partial data from data field

Posted on 2014-02-24
22
256 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
[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
  • 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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 63

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 63

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 63

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 63

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 63

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 63

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 63

Expert Comment

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

Featured Post

Ready to get started with anonymous questions?

It's easy! Check out this step-by-step guide for asking an anonymous question on Experts Exchange.

Question has a verified solution.

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

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

617 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