Solved

How to query xml in vb.net like sql to populate datagridview

Posted on 2011-09-27
18
698 Views
Last Modified: 2012-08-14
I have this problem, I am new to the XML thing in visual basic.net.I am not doing asp this is for a windows form.

What i neeed is...

I have a database that has student names,student ids, image location, graudation yes;
I want to query the student id lets say student id='5993153492' and it pulls the students entire row from the xml file

I currently have this working using sql lite but for ease of distribution i need to use xml.

so just need to query the xml file called c:\test.xml that populates fine just need to ppull the data into a datagridview.

Im used to sql query such as "select * from test.xml where student id='5993153492'

Please help me

I have no code for this button currently becuase i dont know what to do.


See my xml file below
<?xml version="1.0" standalone="yes"?>
<NewDataSet>
  <Student>
    <StudentName />
    <StudentID />
    <Image />
    <Location />
  </Student>
  <Student>
    <StudentName>Corey Gashlin</StudentName>
    <StudentID>5993153492</StudentID>
    <Image>5993153492.jpg</Image>
    <Location>c:\</Location>
  </Student>
  <Student>
    <StudentName>Bob Johnson</StudentName>
    <StudentID>123456</StudentID>
    <Image>123465.jpg</Image>
    <Location>c:\</Location>
  </Student>
  <Student>
    <StudentName>Johnny Knoxville</StudentName>
    <StudentID>444</StudentID>
    <Image>.444.jpg</Image>
    <Location>c:\</Location>
  </Student>
  <Student>
    <StudentName>Jason</StudentName>
    <StudentID>4875</StudentID>
    <Image>7845</Image>
    <Location>456</Location>
  </Student>
</NewDataSet>

Open in new window

0
Comment
Question by:desiredforsome
  • 10
  • 8
18 Comments
 
LVL 74

Expert Comment

by:käµfm³d 👽
ID: 36713797
Which version of the framework are you targeting?
0
 
LVL 74

Expert Comment

by:käµfm³d 👽
ID: 36713816
0
 

Author Comment

by:desiredforsome
ID: 36713857
I am targeting whatever framework i can use 3.5 or 4.0 Sample codes would be helpfuull
0
 
LVL 74

Assisted Solution

by:käµfm³d 👽
käµfm³d   👽 earned 250 total points
ID: 36713887
Sample codes would be helpfuull
Hence the links  ; )

Since you are accustomed to SQL, you might find LINQ to be a bit more intuitive. For example, using the document you posted, to get all of the student IDs you could do something like this:

Module Module1

    Sub Main()
        Dim xdoc As System.Xml.Linq.XDocument

        xdoc = System.Xml.Linq.XDocument.Load("input.xml")

        Dim studentQuery = From student In xdoc.Descendants("Student") _
                           Where student.Element("StudentID") = "5993153492" _
                           Select student

        For Each student In studentQuery
            Console.WriteLine("{0}, {1}", student.Element("StudentName").Value, student.Element("Image").Value)
        Next

        Console.ReadKey()

    End Sub

End Module

Open in new window

0
 

Author Comment

by:desiredforsome
ID: 36714253
But i need it in a datagridview not console line
0
 
LVL 74

Expert Comment

by:käµfm³d 👽
ID: 36714542
The console was just an example. Extract the relevant parts (or strikeout the irrelevant parts) of the post. For your purposes, lines 4 - 10 show how to write the actual query; lines 12 - 14 show how to access the individual fields of a Student node (e.g. StudentName & Image).

If any part of the above code is confusing, please post back identifying the part you'd like clarification on  = )
0
 
LVL 74

Assisted Solution

by:käµfm³d 👽
käµfm³d   👽 earned 250 total points
ID: 36714602
P.S.

With LINQ, you can bind the results of a query directly to a data component. You can modify your query to return new, anonymous objects (anonymous in that they are classes, but they don't have a name). You can give your anonymous object's fields new, meaningful names. For example, you could modify the above query to display all the students in the file:

Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim xdoc As System.Xml.Linq.XDocument

        xdoc = System.Xml.Linq.XDocument.Load("input.xml")

        Dim studentQuery = From student In xdoc.Descendants("Student") _
                          Select New With _
                          { _
                            .Name = student.Element("StudentName").Value, _
                            .ID = student.Element("StudentID").Value, _
                            .Image = student.Element("Image").Value _
                          }

        Me.DataGridView1.AutoGenerateColumns = True
        Me.DataGridView1.DataSource = studentQuery.ToList()

    End Sub
End Class

Open in new window


To get results such as:


Untitled.png
0
 

Author Comment

by:desiredforsome
ID: 36717403
Kinda getting it to work by combining both samples of cod ebut still not what i want. attached is the end result. if i search for id 123456789

I want it to display the entire record set of the student.


xmldb.png
0
 

Author Comment

by:desiredforsome
ID: 36717441
I got it to kinda work now how i want however, its out of order. i want it to be like my pic righ tnow it shows.

ID,Location,Name

I need Name,Location,ID. i switched the code around a bit still no go on that.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:desiredforsome
ID: 36717532
Looks like its making the rows done in alphabetical order I dont want that. I need ti in my order.
0
 

Author Comment

by:desiredforsome
ID: 36717542
Below is what i have for my button.
Dim studentnumber As String = TextBox1.Text
        Dim xdoc As System.Xml.Linq.XDocument
        xdoc = System.Xml.Linq.XDocument.Load("c:\test.xml")
        Dim studentQuery = From student In xdoc.Descendants("Student") _
                           Where student.Element("StudentID") = studentnumber _
                          Select New With _
                          { _
                            .Name = student.Element("StudentName").Value, _
                            .StudentID = student.Element("StudentID").Value, _
                            .Image = student.Element("Image").Value, _
                            .Location = student.Element("Location").Value _
                                  }
        Me.DataGridView1.DataSource = studentQuery.ToArray()

Open in new window

0
 

Author Comment

by:desiredforsome
ID: 36717552
Sorry i meant columns not rows.
0
 
LVL 74

Expert Comment

by:käµfm³d 👽
ID: 36717584
righ tnow it shows.

ID,Location,Name
Of course! The example only selects those columns. You need to add in the other columns:

Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim xdoc As System.Xml.Linq.XDocument

        xdoc = System.Xml.Linq.XDocument.Load("input.xml")

        Dim studentQuery = From student In xdoc.Descendants("Student") _
                          Select New With _
                          { _
                            .Name = student.Element("StudentName").Value, _
                            .ID = student.Element("StudentID").Value, _
                            .Image = student.Element("Image").Value, _
                            .Location = student.Element("Location").Value _      '  <-----   Added the Location node
                          }

        Me.DataGridView1.AutoGenerateColumns = True
        Me.DataGridView1.DataSource = studentQuery.ToList()

    End Sub
End Class

Open in new window


You don't appear to have a "Smart Speech" node in the sample document you posted. If you have that node, then you can add it as I did in line 14 with the "Location" node.

Can you clarify what you mean by, "its out of order"?
0
 

Author Comment

by:desiredforsome
ID: 36717605
I want it to display on the datagrid view,

StudentName,StudentID,SmartSpeech,Image,Location

What ive noticed is it runs in alphabetical order on the datagridview so it is displaying

Image, Location, Name, StudentID.

Thats all i need then i can reward points.
0
 
LVL 74

Accepted Solution

by:
käµfm³d   👽 earned 250 total points
ID: 36717652
That is rather strange. Last night, I posted from Win 7, and the columns appear in my post as you describe. When I'm running it from XP, they appear as they are laid out in the query. Hmpf!

As a workaround, we can set the column order explicitly. Add a DataBindingComplete handler for the DataGridView and you can specify the column order there:

Private Sub DataGridView1_DataBindingComplete(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewBindingCompleteEventArgs) Handles DataGridView1.DataBindingComplete
    Me.DataGridView1.Columns("Name").DisplayIndex = 0
    Me.DataGridView1.Columns("ID").DisplayIndex = 1
    Me.DataGridView1.Columns("Image").DisplayIndex = 2
    Me.DataGridView1.Columns("Location").DisplayIndex = 3
End Sub

Open in new window


The strings which represent the column names should correspond the the identifiers we created in the query. If you change the query identifiers, then you would need to update the names in the above as well.

Putting it all together you get:

Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim xdoc As System.Xml.Linq.XDocument

        xdoc = System.Xml.Linq.XDocument.Load("input.xml")

        Dim studentQuery = From student In xdoc.Descendants("Student") _
                           Select New With _
                           { _
                             .Name = student.Element("StudentName").Value, _
                             .ID = student.Element("StudentID").Value, _
                             .Image = student.Element("Image").Value, _
                             .Location = student.Element("Location").Value _
                           }

        Me.DataGridView1.AutoGenerateColumns = True

        Me.DataGridView1.DataSource = studentQuery.ToList()

    End Sub

    Private Sub DataGridView1_DataBindingComplete(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewBindingCompleteEventArgs) Handles DataGridView1.DataBindingComplete
        Me.DataGridView1.Columns("Name").DisplayIndex = 0
        Me.DataGridView1.Columns("ID").DisplayIndex = 1
        Me.DataGridView1.Columns("Image").DisplayIndex = 2
        Me.DataGridView1.Columns("Location").DisplayIndex = 3
    End Sub
End Class

Open in new window

0
 

Author Comment

by:desiredforsome
ID: 36717715
Nope does not work.

"Object reference not set to an instance of an object"

I tried creating the columns manually but no go. Then it shows the columns i created in addition to the incorrectly sorted columns after them.
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim studentnumber As String = TextBox1.Text
        Dim xdoc As System.Xml.Linq.XDocument
        xdoc = System.Xml.Linq.XDocument.Load("c:\test.gpc")
        Dim studentQuery = From student In xdoc.Descendants("Student") _
                           Where student.Element("StudentID") = studentnumber _
                          Select New With _
                          { _
                            .Name = student.Element("StudentName").Value, _
                            .StudentID = student.Element("StudentID").Value, _
                            .Image = student.Element("Image").Value, _
                            .Location = student.Element("Location").Value _
                                  }

        Me.DataGridView1.DataSource = studentQuery.ToArray()


    End Sub
    Private Sub DataGridView1_DataBindingComplete(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewBindingCompleteEventArgs) Handles DataGridView1.DataBindingComplete
        Me.DataGridView1.Columns("StudentName").DisplayIndex = 0
        Me.DataGridView1.Columns("StudentID").DisplayIndex = 1
        Me.DataGridView1.Columns("Image").DisplayIndex = 2
        Me.DataGridView1.Columns("Location").DisplayIndex = 3
    End Su

Open in new window

0
 
LVL 74

Expert Comment

by:käµfm³d 👽
ID: 36717776
That's my fault...  I didn't make what I was referring to very clear. In lines 20 - 23, you are referring to the names of the XML nodes, not the names of the fields we created in the query. For example, in line 20, you put "StudentName", but you really need that to be "Name", because in line 9 we gave the anonymous class a field called "Name". Use those identifiers (i.e. the ones preceded with a dot) in lines 20 - 23. See lines 24 - 27 in my last post.
0
 

Author Closing Comment

by:desiredforsome
ID: 36717818
After comining all help the problem has been resolved thanks alot everyone.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
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, …
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…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

708 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

16 Experts available now in Live!

Get 1:1 Help Now