Solved

Help with displaying partial data from data field

Posted on 2014-02-24
22
250 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
Comment Utility
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
Comment Utility
Thanks, will look up the link and get back to you.
0
 

Author Comment

by:vcharles
Comment Utility
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
 
LVL 5

Expert Comment

by:Bob Bender
Comment Utility
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
Comment Utility
{ } 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thank You. I will try it and get back to you in the morning EST.
0
 

Author Comment

by:vcharles
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 62

Expert Comment

by:Fernando Soto
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
THANK YOU!
0
 

Author Comment

by:vcharles
Comment Utility
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
Comment Utility
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
Comment Utility
OK, I'll figure it out.

Thanks.

Victor
0
 

Author Comment

by:vcharles
Comment Utility
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
Comment Utility
You need to use the table that the data is in.

TableName.DefaultView.Sort = "SN"
0
 

Author Comment

by:vcharles
Comment Utility
Git it.

Thanks,

Victor
0
 
LVL 62

Expert Comment

by:Fernando Soto
Comment Utility
Not a problem.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Recently while returning home from work my wife (another .NET developer) was murmuring something. On further poking she said that she has been assigned a task where she has to serialize and deserialize objects and she is afraid of serialization. Wha…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

744 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

15 Experts available now in Live!

Get 1:1 Help Now