MacedonZero MacedonZero - 4 months ago 56
Vb.net Question

filtering and sorting xml data by date in vb.net

Ultimately, I'm trying to find a way to successfully sort and filter my xml data in a gridview display.

I have an xml sheet like this:

<?xml version="1.0" standalone="yes"?>
<Notification>
<Info>
<Event>Template</Event>
<Date>1899/01/01</Date>
</Info>
<Info>
<Event>picnic</Event>
<Date>2016/07/15</Date>
</Info>
<Info>
<Event>party</Event>
<Date>2015/10/29</Date>
</Info>
</Notification>


That I need to filter and sort into an asp.net gridview on two different pages. On one page I keep a large record of all events and allow the addition, update, and deletion of records. The second gridview is in the corner of my main page that should show current/upcoming events. I define them like so (the 2nd one has smaller size dimensions, but that's the only difference):

<asp:GridView ID="GridView1" runat="server" HeaderStyle-ForeColor="#FF5A09" RowStyle-ForeColor="#FF9900"
AutoGenerateColumns="false" BorderWidth="2px"
Width="1294px" Height="350px" AllowPaging="true"
OnPageIndexChanging="OnPageIndexChanging" AllowSorting="true" >

<Columns >
<asp:BoundField DataField="Event" HeaderText="Event" ItemStyle-Width="150" />
<asp:BoundField DataField="Date" HeaderText="Date" ItemStyle-Width="150" />
<asp:CommandField ShowEditButton="True" ItemStyle-Width="30"/>
<asp:CommandField ShowDeleteButton="True" ItemStyle-Width="30"/>

</Columns>


I'm using this vb.net function to bind my xml data to my gridview

Private Sub BindGrid()

Dim ds As New DataSet
ds.ReadXml(Server.MapPath("~/Event_Info.xml"))
GridView1.DataSource = ds
GridView1.DataBind()
GridView1.HeaderRow.TableSection = TableRowSection.TableHeader

End Sub


My problem is that whenever I read the xml data, or try loading it with
Dim doc as XDocument=XDocument.Load("Path to my xml")
, the data read in is a string in the date column, so the only way I could find to sort them is by changing up my BindGrid() function like so:

Private Sub BindGrid()

Dim ds As New DataSet
ds.Tables[0].DefaultView.Sort = "Date desc"
ds.ReadXml(Server.MapPath("~/Event_Info.xml"))
GridView1.DataSource = ds.Tables[0].DefaultView
GridView1.DataBind()
GridView1.HeaderRow.TableSection = TableRowSection.TableHeader

End Sub


This allowed me to sort if I only entered the date yyyy/mm/dd, but my add, delete, and update functions were no longer working.

if you want to see them, I will post them here, but you could probably skip over this bit: The Button_click here at the top is for adding a record to the gridview

Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

BindGrid()
Dim oDs As DataSet = GridView1.DataSource
Dim oDr As DataRow = oDs.Tables(0).NewRow
oDr("Event") = TextBox1.Text
oDr("Date") = TextBox2.Text

oDs.Tables(0).Rows.Add(oDr)
oDs.WriteXml(Request.PhysicalApplicationPath + "Event_Info.xml")
BindGrid()

TextBox1.Text = String.Empty
TextBox2.Text = String.Empty

End Sub

Protected Sub GridView1_RowDeleting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewDeleteEventArgs) Handles GridView1.RowDeleting
BindGrid()
Dim oDs As DataSet = GridView1.DataSource
oDs.Tables(0).Rows(GridView1.Rows(e.RowIndex).DataItemIndex).Delete()
oDs.WriteXml(Request.PhysicalApplicationPath + "Event_Info.xml")
BindGrid()
End Sub


Protected Sub GridView1_RowEditing(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewEditEventArgs) Handles GridView1.RowEditing
GridView1.EditIndex = e.NewEditIndex
BindGrid()
End Sub


Protected Sub GridView1_RowCancelingEdit(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCancelEditEventArgs) Handles GridView1.RowCancelingEdit
GridView1.EditIndex = -1
BindGrid()
End Sub


Protected Sub GridView1_RowUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdateEventArgs) Handles GridView1.RowUpdating
' Get the new values from the GridView controls
Dim i As Integer = GridView1.Rows(e.RowIndex).DataItemIndex
Dim n As String = CType(GridView1.Rows(e.RowIndex).Cells(0).Controls(0), TextBox).Text
Dim rn As String = CType(GridView1.Rows(e.RowIndex).Cells(1).Controls(0), TextBox).Text

GridView1.EditIndex = -1
BindGrid()
' Update the XML file using the new values

Dim oDs As DataSet = GridView1.DataSource
oDs.Tables(0).Rows(i).Item(0) = n
oDs.Tables(0).Rows(i).Item(1) = rn
oDs.WriteXml(Request.PhysicalApplicationPath + "Event_Info.xml")
BindGrid()
End Sub


End the skip bit

I also have no idea how to successfully filter the data on the main page so that it shows only current and future events. I've tried to find ways to apply a "where" clause of sorts on the date, but have been unsuccessful

Edit: marked a section as code that I forgot to mark as code

Answer

I found the solution to my problem. I needed to be using dataviews instead of datasets to alter, sort, and filter my data.

My bindgrid function became this:

Private Sub BindGrid()

        Dim ds As New DataSet
        ds.ReadXml(Server.MapPath("~/Event_Info.xml"))
        Dim myView As New DataView

        myView = ds.Tables(0).DefaultView

        myView.Sort = "Date desc"

        GridView1.DataSource = myView

        GridView1.DataBind()


    End Sub

Note this larger change meant that my add/delete/update functions had to change. I ended up writing them like this if anyone wants to see them:

Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        BindGrid()
        Dim dv As DataView = GridView1.DataSource
        Dim oDr As DataRowView = dv.AddNew()
        oDr("Event") = TextBox1.Text
        oDr("Date") = TextBox2.Text
        oDr.EndEdit()

        dv.DataViewManager.DataSet.WriteXml(Request.PhysicalApplicationPath + "Event_Info.xml", XmlWriteMode.WriteSchema)
        dv.Sort = "Date desc"
        BindGrid()

        TextBox1.Text = String.Empty
        TextBox2.Text = String.Empty

    End Sub

    Protected Sub GridView1_RowDeleting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewDeleteEventArgs) Handles GridView1.RowDeleting
        BindGrid()
        Dim oDv As DataView = GridView1.DataSource
        oDv.Delete(GridView1.Rows(e.RowIndex).RowIndex)
        oDv.DataViewManager.DataSet.WriteXml(Request.PhysicalApplicationPath + "Event_Info.xml", XmlWriteMode.WriteSchema)
        oDv.Sort = "Date desc"
        BindGrid()
    End Sub

Protected Sub GridView1_RowUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdateEventArgs) Handles GridView1.RowUpdating
        ' Get the new values from the GridView controls
        Dim i As Integer = GridView1.Rows(e.RowIndex).DataItemIndex
        Dim n As String = CType(GridView1.Rows(e.RowIndex).Cells(0).Controls(0), TextBox).Text
        Dim rn As String = CType(GridView1.Rows(e.RowIndex).Cells(1).Controls(0), TextBox).Text

        GridView1.EditIndex = -1
        BindGrid()
        ' Update the XML file using the new values

        Dim oDv As DataView = GridView1.DataSource
        oDv.DataViewManager.DataSet.Tables(0).Rows(i).Item(0) = n
        oDv.DataViewManager.DataSet.Tables(0).Rows(i).Item(1) = rn
        oDv.DataViewManager.DataSet.WriteXml(Request.PhysicalApplicationPath + "Event_Info.xml", XmlWriteMode.WriteSchema)
        BindGrid()
    End Sub

Of course, as was suggested in the comments, I defined an xml schema to ensure that the date column would be written into/read from the xml data as a date

<?xml version="1.0" encoding="utf-8"?>
<xs:schema id="EventsSchema"
    targetNamespace="http://tempuri.org/EventsSchema.xsd"
    elementFormDefault="qualified"
    xmlns="http://tempuri.org/EventsSchema.xsd"
    xmlns:mstns="http://tempuri.org/EventsSchema.xsd"
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
>

  <xs:element name="Notification">
    <xs:complexType>
      <xs:sequence>

        <xs:element name="Info" minOccurs="0" maxOccurs="unbounded">
          <xs:complexType>
            <xs:sequence>

              <xs:element name="Event" type="xs:string"></xs:element>
              <xs:element name="Date" type="xs:dateTime"></xs:element>

            </xs:sequence>
          </xs:complexType>
        </xs:element>

      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>
Comments