Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Help with displaying partial data from data field

Posted on 2014-02-24
22
Medium Priority
?
258 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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 64

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 64

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 64

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 64

Accepted Solution

by:
Fernando Soto earned 2000 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 64

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 64

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 64

Expert Comment

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

Featured Post

Congratulations! You’re Certified – Now What?

Starting a new career can be overwhelming. Becoming certified in your field of expertise is a great start, but where do you go from here?  Here are some tips to help you on your career journey.

Question has a verified solution.

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

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.
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
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…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

719 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