Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 811
  • Last Modified:

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

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
desiredforsome
Asked:
desiredforsome
  • 10
  • 8
3 Solutions
 
käµfm³d 👽Commented:
Which version of the framework are you targeting?
0
 
desiredforsomeAuthor Commented:
I am targeting whatever framework i can use 3.5 or 4.0 Sample codes would be helpfuull
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
käµfm³d 👽Commented:
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
 
desiredforsomeAuthor Commented:
But i need it in a datagridview not console line
0
 
käµfm³d 👽Commented:
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
 
käµfm³d 👽Commented:
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
 
desiredforsomeAuthor Commented:
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
 
desiredforsomeAuthor Commented:
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
 
desiredforsomeAuthor Commented:
Looks like its making the rows done in alphabetical order I dont want that. I need ti in my order.
0
 
desiredforsomeAuthor Commented:
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
 
desiredforsomeAuthor Commented:
Sorry i meant columns not rows.
0
 
käµfm³d 👽Commented:
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
 
desiredforsomeAuthor Commented:
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
 
käµfm³d 👽Commented:
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
 
desiredforsomeAuthor Commented:
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
 
käµfm³d 👽Commented:
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
 
desiredforsomeAuthor Commented:
After comining all help the problem has been resolved thanks alot everyone.
0

Featured Post

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.

  • 10
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now