Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2011-09-27
18
Medium Priority
?
798 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
 

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75

Assisted Solution

by:käµfm³d 👽
käµfm³d   👽 earned 1000 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 1000 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 1000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses

963 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