[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 263
  • Last Modified:

Help with displaying partial data from data field

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
vcharles
Asked:
vcharles
  • 12
  • 7
  • 3
1 Solution
 
Bob BenderCommented:
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
 
vcharlesAuthor Commented:
Thanks, will look up the link and get back to you.
0
 
vcharlesAuthor Commented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Bob BenderCommented:
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
 
vcharlesAuthor Commented:
{ } 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
 
Fernando SotoRetiredCommented:
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
 
Bob BenderCommented:
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
 
vcharlesAuthor Commented:
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
 
Fernando SotoRetiredCommented:
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
 
vcharlesAuthor Commented:
Thank You. I will try it and get back to you in the morning EST.
0
 
vcharlesAuthor Commented:
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
 
Fernando SotoRetiredCommented:
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
 
vcharlesAuthor Commented:
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
 
Fernando SotoRetiredCommented:
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
 
vcharlesAuthor Commented:
THANK YOU!
0
 
vcharlesAuthor Commented:
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
 
Fernando SotoRetiredCommented:
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
 
vcharlesAuthor Commented:
OK, I'll figure it out.

Thanks.

Victor
0
 
vcharlesAuthor Commented:
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
 
Fernando SotoRetiredCommented:
You need to use the table that the data is in.

TableName.DefaultView.Sort = "SN"
0
 
vcharlesAuthor Commented:
Git it.

Thanks,

Victor
0
 
Fernando SotoRetiredCommented:
Not a problem.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 12
  • 7
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now