Solved

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

Posted on 2013-12-18
31
383 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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
 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

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:…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

777 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