Solved

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

Posted on 2011-09-27
18
720 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 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VS.net 2010 11 36
Pagebreak issue while printing the aspx page 3 26
Write to file when application closes (by logoff) - VB.NET 1 32
Creating a route in asp.net webforms 2 30
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

831 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