[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Load XML into DataGridView

Posted on 2009-04-29
9
Medium Priority
?
565 Views
Last Modified: 2013-12-20
I need to load part of an XML file into a DataGridView component. Right now, using the code shown below, I can get all of the file to load. Here's the file:

<sample>
    <listset id="FIRST">
        <list>
            <a>First data A1</a>
            <b>First data B1</b>
            <c>First data C1</c>
        </list>
        <list>
            <a>First data A2</a>
            <b>First data B2</b>
            <c>First data C2</c>
        </list>
    </listset>
    <listset id="SECOND">
        <list>
            <a>Second data A1</a>
            <b>Second data B1</b>
            <c>Second data C1</c>
        </list>
    </listset>
</sample>

But, I want just the data contained in <listset id="FIRST"> to be loaded. How do I specify just this data?
Private Function LoadGrid(ByVal sInputFile As String)
        Try
 
            Dim xmlFile As XmlReader
            xmlFile = XmlReader.Create(sInputFile, New XmlReaderSettings())
            Dim ds As New DataSet
            ds.ReadXml(xmlFile)
            DataGridView1.DataSource = ds.Tables(1)
            xmlFile.Close()
 
        Catch ex As SqlException
            MessageBox.Show(ex.Message)
        End Try
 
    End Function

Open in new window

0
Comment
Question by:surfbored
  • 6
  • 3
9 Comments
 
LVL 1

Expert Comment

by:TrueGeekSGF
ID: 24264538
Use a DataView to filter the data you want to view and then bind to your grid.  See the link below, except you will bind to your grid instead of a textbox.

http://msdn.microsoft.com/en-us/library/system.data.dataview.rowfilter.aspx
0
 
LVL 1

Author Comment

by:surfbored
ID: 24264831
@TrueGeekSGF:

This looks like it might be close, but I can't quite figure how it is to be used in the context of my program. Trying the code below, I get the error:

"Cannot find column [id]"

Suggestions?

    Dim xmlFile As XmlReader
    xmlFile = XmlReader.Create(sInputFile, New XmlReaderSettings())
    Dim ds As New DataSet
    ds.ReadXml(xmlFile)
 
    Dim view As New DataView
    With view
        .Table = ds.Tables(1)
        .AllowDelete = True
        .AllowEdit = True
        .AllowNew = True
        .RowFilter = "id = 'FIRST'"
        .RowStateFilter = DataViewRowState.ModifiedCurrent
    End With
 
    DataGridView1.DataSource = view

Open in new window

0
 
LVL 1

Expert Comment

by:TrueGeekSGF
ID: 24265298
I would recommend that you modify this XML to be in a better schema (see below).  If it is out of your control, then you have to go with what you have.  Hope the code below helps.

<xml>
  <FirstTable>
      <list>
        <a>First data A1</a>
        <b>First data B1</b>
        <c>First data C1</c>
      </list>
      <list>
        <a>First data A2</a>
        <b>First data B2</b>
        <c>First data C2</c>
      </list>
  </FirstTable>
  <SecondTable>
    <list>
      <a>Second data A1</a>
      <b>Second data B1</b>
      <c>Second data C1</c>
    </list>
  </SecondTable>
</xml>
    Private Sub LoadGrid(ByVal sInputFile As String)
 
        Dim xmlFile As System.Xml.XmlReader
        xmlFile = System.Xml.XmlReader.Create(sInputFile, New System.Xml.XmlReaderSettings())
        Dim ds As New System.Data.DataSet
        ds.ReadXml(xmlFile)
 
        Dim view As New System.Data.DataView
        With view
            .Table = ds.Tables(1)
            .AllowDelete = True
            .AllowEdit = True
            .AllowNew = True
            .RowFilter = "listset_Id = '0'"
            .RowStateFilter = System.Data.DataViewRowState.CurrentRows
        End With
 
        DataGridView1.DataSource = view
        DataGridView1.DataBind()
    End Sub

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 1

Author Comment

by:surfbored
ID: 24280851
@TrueGeekSGF:

Unfortunately I have no control over the XML format, so I'm stuck with it, warts and all.

I tried the code you provided, but the results were exactly the same. That is, I got values from the first listset and the second listset.

Also, I had to leave off the "DataGridView1.DataBind()" as it does not show as a member of DataGridView.

Is there something I need to change in the code you provided?

Thanks.
0
 
LVL 1

Author Comment

by:surfbored
ID: 24281381
@TrueGeekSGF:

CORRECTION!!!

I actually do get different results (sorry, I was using the wrong input file, duh!), but there is still a problem with this coding.

Your code specifies the first table, regardless of the name/id. So if the order of the XML changes (which is possible), then I pick up the wrong table. I tried switching list_id from "0" to "FIRST", but that just throws an error.

Can you suggest a way to make sure I grab the table I want and not just the first table?

Thanks again.
0
 
LVL 1

Author Comment

by:surfbored
ID: 24304508
Perhaps this is a bit harder than I thought? I'll bump up the point value in hopes of generating new/more interest!


0
 
LVL 1

Accepted Solution

by:
TrueGeekSGF earned 1600 total points
ID: 24351309
If you examine the returned dataset, the first table will contain 1 row each for the attributes in your sample XML.  All you will have to do is find the row you want in the first table and use the ID in your filter.
    'Sample Call 1 LoadGrid("test.xml", "listset", "id", "FIRST")
    'Sample Call 2 LoadGrid("test.xml", "listset", "id", "SECOND")
    Private Sub LoadGrid(ByVal sInputFile As String, ByVal TableName As String, ByVal ElementName As String, _
     ByVal ElementToFilter As String)
 
        Dim xmlFile As System.Xml.XmlReader
        xmlFile = System.Xml.XmlReader.Create(sInputFile, New System.Xml.XmlReaderSettings())
        Dim ds As New System.Data.DataSet
        Dim dr As System.Data.DataRow
        Dim strID As String = ""
 
        ds.ReadXml(xmlFile)
 
        'First column contains the tableID based on the table attribute
        For Each dr In ds.Tables(0).Rows
            If dr(ElementName).ToString = ElementToFilter Then
                strID = dr(TableName & "_Id").ToString
                Exit For
            End If
        Next
 
        Dim view As New System.Data.DataView
 
        With view
            .Table = ds.Tables(1)
            .AllowDelete = True
            .AllowEdit = True
            .AllowNew = True
            .RowFilter = TableName & "_Id = '" & strID & "'"
            .RowStateFilter = DataViewRowState.CurrentRows
        End With
 
        DataGridView1.DataSource = view
        DataGridView1.DataBind()
 
    End Sub

Open in new window

0
 
LVL 1

Author Comment

by:surfbored
ID: 24355959
@TrueGeekSGF:

Whew, I thought maybe you had given up on me! :)

Except for the DataGridView1.DataBind(), this code seems to work exactly as I needed it to. Thank you for all the help.

BTW, the DataBind throws the following error:
'DataBind' is not a member of 'System.Windows.Forms.DataGridView'

Again, thanks!

SB

0
 
LVL 1

Author Closing Comment

by:surfbored
ID: 31576136
Thanks again!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

The Confluence of Individual Knowledge and the Collective Intelligence At this writing (summer 2013) the term API (http://dictionary.reference.com/browse/API?s=t) has made its way into the popular lexicon of the English language.  A few years ago, …
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

834 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