Solved

Help with passing data from string variable to a table to view data in DataGridView

Posted on 2013-12-18
31
379 Views
Last Modified: 2013-12-23
Hi,

I'm using the code below, to pass data match my query "BEL" to result, how to add the values to a table? I need to use the table as a datasource of my datagrid control to view all result found.

 The location of the XML file to be parsed
Dim xdoc = XDocument.Load("C:\Working Directory\test.xml")
Dim findStr As String = "BEL"

' Locate all the Donor nodes and see if it has the value we are looking for.
Dim result = (From d In xdoc.Descendants("Donor")
              Where d.Value.Contains(findStr)
              Select d.Value).ToList()

' Output the results in the List
For Each s As String In result
    Console.WriteLine( s )
Next

Thanks,

Victor
0
Comment
Question by:vcharles
  • 17
  • 11
  • 3
31 Comments
 
LVL 25

Expert Comment

by:Luis Pérez
ID: 39726527
Oh, you can do it very easy:

DataGridView1.DataSource = result

Hope that helps.
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 39726542
Hi Victor;

The results of the Linq query are comma separated strings. How do you want to fill the DataGridView, do you want to split the strings on the comma and put each individual substring into a separate column or single column?. Does the DataGridView exist or are you creating it dynamically? Please provide more information.
0
 

Author Comment

by:vcharles
ID: 39726745
Hi,

The DataGridView already exist. I would like to put the data in a single column.

Thank you for both comments.

Victor
0
 
LVL 25

Expert Comment

by:Luis Pérez
ID: 39726753
But in this case the result is a List(Of String), isn't it? Have you tried my code, DataGridView1.DataSource = result?
0
 

Author Comment

by:vcharles
ID: 39726766
Yes, but it did not work.  I need to put the data in a table because I will have multiple rows, than set the data source of the dataGridView to the table.

Thanks.

Victor
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 39726794
OK, you have a an existing DataGridView, fine. Do you already have data being displayed in this DataGridView? If so what column of this DataGridView do you want to assign the results of the Linq query too? Or is the DataGridView empty and just fill it with the results of the Linq query.
0
 

Author Comment

by:vcharles
ID: 39726833
The DataGridView is empty but I will also include data in other columns. For this part of the code I want to display the data in the "Donor" column.
0
 
LVL 25

Expert Comment

by:Luis Pérez
ID: 39726846
Oh, then you can do this:

Dim dt As DataTable = New DataTable
dt.Columns.Add(New DataColumn("Field1", Type.GetType("System.String")))
dt.Columns.Add(New DataColumn("Field2", Type.GetType("System.Int32")))
'add many fields as you need

'Now fill the table:
For Each s As String In result
    Dim row As DataRow = dt.NewRow()
    row.Item("Field1") = s 'In "Field1" you store the results of your Linq query, row by row
    row.Item("Field2") = value 'Store the rest of values in the rest of fields
    dt.Rows.Add(row)
Next

'Assign data to the DataGridView:
Me.DataGridView1.DataSource = dt

Open in new window

Hope that helps.
0
 

Author Comment

by:vcharles
ID: 39726899
I will try it and get back to you.

Thank You.
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 39727035
Hi Victor;

Try this out, it should give you what you need.

    ' Set up the DataTable to hold the data for the DataGridView    
    Dim dt As New DataTable
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        dt.Columns.Add("Donor", GetType(System.String))
        dt.Columns.Add("Other", GetType(System.String))
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        ' Clear all rows in the DataTable in getting ready to add new
        ' Items from new query
        dt.Rows.Clear()

        'The location of the XML file to be parsed
        Dim xdoc = XDocument.Load("C:\Working Directory\test.xml")
        Dim findStr As String = "BEL"

        ' Locate all the Donor nodes and see if it has the value we are looking for.
        Dim result = (From d In xdoc.Descendants("Donor")
                      Where d.Value.Contains(findStr)
                      Select d.Value).ToList()

        ' Output the results into a DataGridView
        For Each s As String In result
            ' Split the strings in each element of the linq query result
            Dim country = s.Split(",")
            ' Add each element of the array into the table
            For Each c In country
                ' Get a new table row
                Dim dr As DataRow = dt.NewRow()
                ' Assign each element of the Linq query to the Donor column
                dr("Donor") = c.Trim()
                ' Other column assignment goes here
                ' Add the new row to the table
                dt.Rows.Add(dr)
            Next
        Next
        'Assign the DataTable to the DataGrigView
        DataGridView1.DataSource = dt

    End Sub

Open in new window

0
 

Author Comment

by:vcharles
ID: 39727751
Hi,

I'm trying to integrate your code in my project but the Grid displays each country in a separate row. For example when search for "BEL" and Linkcountries contains element <Donor>BEL,ESP,PRT</Donor>
 The Grid displays:

BEL,ESP,PRT
BEL
ESP
PRT

Instead of only
BEL,ESP,PRT

How do I only obtain BEL,ESP,PRT?

Code:

dtLink = New DataTable("Link")
            dtLink.Columns.Add("Link_ID")
            dtLink.Columns.Add("Donor")
           
            '2
            Dim linker As XElement = XElement.Load(Application.StartupPath + "\LinkFiles\LinkCountries.xml”)
            '3
            For Each item As XElement In linker.Elements("Bilateral")
                Dim linkID As String = item.Element("Link_ID").Value
                Dim MaxrangeID As String = item.Element("Donor_ID").Value
               
                Dim drLink As DataRow = dtLink.NewRow()
                drLink("Link_ID") = linkID
                drLink("Donor") = MaxrangeID
                dtLink.Rows.Add(drLink)
            Next

            '************************************************
            Dim findStr As String = "BEL"

            Dim result = (From d In linker.Descendants("Donor_ID")
                      Where d.Value.Contains(findStr)
                      Select d.Value).ToList()
            MsgBox(result.ToString)

            For Each s As String In result
                ' Split the strings in each element of the linq query result
                Dim country = s.Split(",")
                ' Add each element of the array into the table
                For Each c In country
                    ' Get a new table row
                    Dim dr As DataRow = dtLink.NewRow()
                    ' Assign each element of the Linq query to the Donor column
                    dr("Donor_ID") = c.Trim()
                    ' Other column assignment goes here
                    ' Add the new row to the table
                    dtLink.Rows.Add(dr)
                Next
            Next
            'Assign the DataTable to the DataGrigView
            C1TrueDBGrid45.DataSource = dtLink

            Exit Sub

            '*********************************************            
Dim DV As New DataView(dtLink, "Donor like " & "'" & C1TrueDBGrid18.Columns(0).Value & "'" & "", Nothing, DataViewRowState.CurrentRows)
            FilteredDT = DV.ToTable
            C1TrueDBGrid45.DataSource = FilteredD


Thanks,

V.
0
 

Author Comment

by:vcharles
ID: 39727766
Hi,

Correction:

<Donor_ID>BEL,ESP,PRT</Donor_ID>

Not

<Donor>BEL,ESP,PRT</Donor>
0
 

Author Comment

by:vcharles
ID: 39732954
Hi,

I removed part of the code to avoid getting the additional rows with each country, but I'm still getting all the rows in the table with additional blank rows, how do I modify the code to only include data where BEL is found?

Code:

dtLink = New DataTable("Link")
            dtLink.Columns.Add("Link_ID")
            dtLink.Columns.Add("Donor")
           
            '2
            Dim linker As XElement = XElement.Load(Application.StartupPath + "\LinkFiles\LinkCountries.xml”)
            '3
            For Each item As XElement In linker.Elements("Bilateral")
                Dim linkID As String = item.Element("Link_ID").Value
                Dim MaxrangeID As String = item.Element("Donor_ID").Value
               
                Dim drLink As DataRow = dtLink.NewRow()
                drLink("Link_ID") = linkID
                drLink("Donor") = MaxrangeID
                dtLink.Rows.Add(drLink)
            Next

            '************************************************
            Dim findStr As String = "BEL"

            Dim result = (From d In linker.Descendants("Donor_ID")
                      Where d.Value.Contains(findStr)
                      Select d.Value).ToList()
             
                 For Each s As String In result
             
                Dim country = s
             
                For Each c In country
                    Dim dr As DataRow = dtLink.NewRow()
                    dtLink.Rows.Add(dr)
                Next
            Next
            C1TrueDBGrid45.DataSource = dtLink

Thanks,

V.
0
 
LVL 62

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 39733274
Hi Victor;

Try something like this.

Dim findStr As String = "BEL"

Dim result = (From d In linker.Descendants("Donor_ID")
              Where d.Value.Contains(findStr)
              Select d.Value).ToList()
    
For Each s As String In result
    ' Get a new table row
    Dim dr As DataRow = dtLink.NewRow()
    ' Assign each element of the Linq query to the Donor column
    dr("Donor") = s.Trim()
    ' Other column assignment goes here
    ' Add the new row to the table
    dtLink.Rows.Add(dr)
Next
    
C1TrueDBGrid45.DataSource = dtLink

Open in new window

0
 

Author Closing Comment

by:vcharles
ID: 39733590
Thank You.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 62

Expert Comment

by:Fernando Soto
ID: 39733606
Not a problem, glad I was able to help.
0
 

Author Comment

by:vcharles
ID: 39733625
Fernando,

One more question related to this issue.  After I removed the code in Part A. I'm not able to retrieve other values from the table, for example Link_ID. How do I modify the code in Part B, to include the other columns in the table?

Part A:

  Dim linker As XElement = XElement.Load(Application.StartupPath + "\LinkFiles\LinkCountries.xml”)
            '3
            For Each item As XElement In linker.Elements("Bilateral")
                Dim linkID As String = item.Element("Link_ID").Value
                Dim MaxrangeID As String = item.Element("Donor_ID").Value
               
                Dim drLink As DataRow = dtLink.NewRow()
                drLink("Link_ID") = linkID
                drLink("Donor") = MaxrangeID
                dtLink.Rows.Add(drLink)
            Next

Part B:
For Each s As String In result
    ' Get a new table row
    Dim dr As DataRow = dtLink.NewRow()
    ' Assign each element of the Linq query to the Donor column
    dr("Donor") = s.Trim()
    dr("Link_ID") = ????
    ' Other column assignment goes here
    ' Add the new row to the table
    dtLink.Rows.Add(dr)
Next

Thanks,

Victor
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 39733654
In order to do this I need to see the XML file you are getting the data from to know the format of the XML. This is because the Linq to XML query needs to change to return the needed data.
0
 

Author Comment

by:vcharles
ID: 39733674
The XML is in the following format, For Donor, I am including the countries instead of their IDs.

 <Countries>
    <Link_ID>19</Link_ID>
    <Donor_ID>BGR,ESP,USA<Donor_ID>
    <NSN_ID>58</NSN_ID>
    <NASC_ID>0</NASC_ID>
    <C_ID>5</C_ID>
  </Countries>

Thanks,

V.
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 39733688
Hi Victor;

Because of the added requirements the linq to XML query needs to change as follows.

Dim result = (From c In linker.Descendants("Countries") _
              Where c.Element("Donor_ID").Value.Contains(findStr) _
              Select Select New With _
              { _
               .Donor_ID = c.Element("Donor_ID").Value, _
               .Link_ID = c.Element("Link_ID").Value _
              }).ToList()

Now Part B should be.

Part B:
For Each s As String In result
    ' Get a new table row
    Dim dr As DataRow = dtLink.NewRow()
    ' Assign each element of the Linq query to the Donor column
    dr("Donor") = s.Donor_ID.Trim()
    dr("Link_ID") = s.Link_ID.Trim()
    ' Other column assignment goes here
    ' Add the new row to the table
    dtLink.Rows.Add(dr)
Next

Open in new window

0
 

Author Comment

by:vcharles
ID: 39733694
I will try it and get back to you.

Thank you very much.

Happy Holidays.
0
 

Author Comment

by:vcharles
ID: 39734748
Hello,

I recieved error message" Value of type <ananymous Type> line <677> cannot be converted to string"

On line:

For Each s As String In result


Line 677 contains: Select New With _


Select Select New With _ from your code gave me an error, I ssume you me to include only one of them.

Thanks,

V.
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 39734770
Please copy and paste the Linq query as it is in your code.
0
 

Author Comment

by:vcharles
ID: 39734821
Hi,

Below is the code in the RowColChange event of C1TrueDBGrid18.
Dim xxx As String
            xxx = C1TrueDBGrid18.Columns(1).Value

           
            Dim findStr As String = xxx
            Dim result = (From c In linker.Descendants("Bilateral") _
              Where c.Element("Donor_ID").Value.Contains(findStr) _
              Select New With _
              { _
               .Donor_ID = c.Element("Donor_ID").Value, _
               .Link_ID = c.Element("Link_ID").Value _
              }).ToList()


            For Each s As String In result
                ' Get a new table row
                Dim dr As DataRow = dtLink.NewRow()
                ' Assign each element of the Linq query to the Donor column
                dr("Donor_ID") = s.Donor_ID.Trim()
                dr("Link_ID") = s.Link_ID.Trim()
                ' Other column assignment goes here
                ' Add the new row to the table
                dtLink.Rows.Add(dr)
            Next

            C1TrueDBGrid45.DataSource = dtLink
            '*********************************
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 39734861
Hi Victor;

Try changing this line of code :
 
For Each s As String In result

To this

For Each s In result
0
 

Author Comment

by:vcharles
ID: 39734885
OK. Thanks, will try it and get back to you tomorrow.
0
 

Author Comment

by:vcharles
ID: 39736109
Hi,

I no longer receive the error but only the Link_ID data shows on the Grid, for some reason, the Donor column is blank. Below is the latest code.

Dim result = (From c In linker.Descendants("Countries") _
              Where c.Element("Donor_ID").Value.Contains(findStr) _
              Select Select New With _
              { _
               .Donor_ID = c.Element("Donor_ID").Value, _
               .Link_ID = c.Element("Link_ID").Value _
              }).ToList()

Now Part B should be.

Part B:
For Each s In result
    ' Get a new table row
    Dim dr As DataRow = dtLink.NewRow()
    ' Assign each element of the Linq query to the Donor column
    dr("Donor") = s.Donor_ID.Trim()
    dr("Link_ID") = s.Link_ID.Trim()
    ' Other column assignment goes here
    ' Add the new row to the table
    dtLink.Rows.Add(dr)
Next

Thanks,

Victor
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 39736140
In this line on your most recent post :

dr("Donor") = s.Donor_ID.Trim()

Show the column name in the data table as Donor, in the post before that the column name is Donor_ID, check to see which one is correct.
0
 

Author Comment

by:vcharles
ID: 39736172
Got it. Thanks.

I will open a new thread for a related question shortly. Basically, I need to convert the IDs for the other elements to actual data by comparing to the master files for each element.

For example using the data in Countries.xml and NSN.xml, the grid would display xxxx-xx-xxx where NSN_ID = 58


 <Countries>
    <Link_ID>19</Link_ID>
    <Donor_ID>BGR,ESP,USA<Donor_ID>
    <NSN_ID>58</NSN_ID>
    <NASC_ID>0</NASC_ID>
    <C_ID>5</C_ID>
  </Countries>


NSN.xml

<NSN>
    <NSN_ID>58</NSN_ID>
        <NSN_ID>xxxx-xx-xxx</NSN_ID>
  </NSN>
0
 

Author Comment

by:vcharles
ID: 39736207
Please disregard my last message, I figured it out.
Thanks.
0
 
LVL 62

Expert Comment

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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 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

12 Experts available now in Live!

Get 1:1 Help Now