Solved

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

Posted on 2011-09-27
18
745 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 8
18 Comments
 
LVL 75

Expert Comment

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

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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
LVL 75

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 75

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 75

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
 

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 75

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 75

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 75

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help with error in Query 2 39
XML extra information 8 35
VB.net Duplicating a table - primary key not created 3 38
Get sourcecode path 14 46
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…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

762 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