Help Please with sorting XML data in datagrid


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 8 of 8

Thread: Help Please with sorting XML data in datagrid

  1. #1
    Join Date
    Jan 2007
    Posts
    7

    Help Please with sorting XML data in datagrid

    Hi All,

    Will keep this brief and include all code below. I have an XML file with 3000+ records being loaded into a datagrid for presentation. This part is working correctly. The problem is sorting the data correctly on a column with a 'date' type. I am trying to tell the datagrid to initially order the data by this date field. Once the grid is published, I am also attempting to allow the user the ability to sort the dates but this is not working. The datagrid is treating the date data as text. I have a XSD schema which I think is telling the XML document and datagrid that the field is a date field but it just isn't working.

    The code below will publish the datagrid by calling in an XML file but will not initially sort the data correctly by date or allow the user to sort the date data correctly.

    There are four files in the source below - an .ASPX page, VB codebehind, XML file and the XSD file.

    Can anyone help with what I am missing to get this data to sort by date correctly?? Any help would be greatly appreciated.



    ==================================================
    ASPX PAGE - awardees.aspx
    ==================================================

    Code:
    <%@ Page Language="vb" Src="winners.vb" Codebehind="winners.vb" 
    Inherits="Police.awardwinners"%>
    
    <form runat="server">
    	
    <asp:DataGrid id="dtgCust" 
            runat="server" 
    	CellPadding="3"
    	AllowSorting = "True"
            AllowPaging = "True"
    	ItemStyle-BackColor="#FFFFCC"
            AlternatingItemStyle-BackColor="#EEEEEE"
    	pagesize="200">
    		
    
    <PagerStyle Mode="NumericPages" Position="TopAndBottom" 
    HorizontalAlign="Right" PageButtonCount = "10"></PagerStyle>
            
    <HeaderStyle BackColor="Navy" HorizontalAlign="Center" 
                       ForeColor="White" Font-Bold="True" />
    			  
    </asp:DataGrid>
    	  	  
    </form>
    ==================================================
    VB Codebehind - winners.vb
    ==================================================

    Code:
    Imports System
    Imports System.Xml
    Imports System.Data
    Imports System.Web.UI.WebControls
    Imports System.Web.Caching
    
    Namespace Police
    
    Public Class awardwinners
      Inherits System.Web.UI.Page
    
      Protected WithEvents dtgCust As System.Web.UI.WebControls.DataGrid
      Dim strOrderBy As String
      Dim dv As New DataView
    
    ' " Web Form Designer Generated Code Omitted "
    
      Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    
        If Not IsPostBack Then
          
    	  strOrderBy = "AWARD_DATE ASC"
          ViewState("strOrderBy") = strOrderBy
          ViewState("Column") = "AWARD_DATE"
          ViewState("Order") = "ASC"
    	  
    	End If
        
        dtgCustBind()
    
      End Sub 
      
      
      
      Public Sub dtgCustBind()
    
        Dim ds As DataSet
        ds = New DataSet
    
        ds.ReadXMLSchema(MapPath("awardsch.xsd"))
    	
    	Try
          ds.ReadXml(MapPath("awards.xml"))
        Catch ex As Exception
          Response.Write(ex.Message.ToString())
        Finally
          ds.Dispose()
        End Try
    
        Dim dtbl As DataTable = ds.Tables(0)
        dv = New DataView(dtbl)
        dv.Sort = ViewState("strOrderBy")
    	
        dtgCust.DataSource = dv
        dtgCust.DataBind()
    
      End Sub 
      
      
      
      Private Sub dtgCust_SortCommand1(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridSortCommandEventArgs) Handles dtgCust.SortCommand
    
        If ViewState("Order") = "ASC" Then
          strOrderBy = e.SortExpression & " DESC"
          ViewState("Order") = "DESC"
        Else
          strOrderBy = e.SortExpression & " ASC"
          ViewState("Order") = "ASC"
        End If
    
        ViewState("strOrderBy") = strOrderBy
        ViewState("Column") = e.SortExpression()
        dtgCustBind()
    
      End Sub
    
      Private Sub dgResults_PageIndexChanged1(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridPageChangedEventArgs) Handles dtgCust.PageIndexChanged
    
         Dim ds As DataSet
         ds = New DataSet
    	
    	ds.ReadXml(MapPath("awards.xml"))
    	Dim dtbl As DataTable = ds.Tables(0)
        dv = New DataView(dtbl)
    	
    	dtgCust.CurrentPageIndex = e.NewPageIndex
        dv.Sort = ViewState("Column") & " " & ViewState("Order")
        dtgCust.DataSource = dv
        dtgCust.DataBind()
    
      End Sub
    
    End Class 
    
    End Namespace
    ==================================================
    XML - awards.xml
    ==================================================
    Code:
    <root>
    <row>
    <DEPARTMENT>Police Department 1</DEPARTMENT>
    <CITY>Smallville</CITY>
    <STATE>OH</STATE>
    <PROGRAM>Safety</PROGRAM>
    <AWARD>34440</AWARD>
    <ACTIVITY>Equipment ($1,000</ACTIVITY>
    <AWARD_DATE>1/12/2007</AWARD_DATE></row>
    
    <row>
    <DEPARTMENT>Police Department 2</DEPARTMENT>
    <CITY>Somewhere</CITY>
    <STATE>OH</STATE>
    <PROGRAM>Operations</PROGRAM>
    <AWARD>14440</AWARD>
    <ACTIVITY>Equipment ($12,726)</ACTIVITY>
    <AWARD_DATE>1/15/2007</AWARD_DATE>
    </row>
    
    <row>
    <DEPARTMENT>Police Department 3</DEPARTMENT>
    <CITY>Summerville</CITY>
    <STATE>OH</STATE>
    <PROGRAM>Ops</PROGRAM>
    <AWARD>24440</AWARD>
    <ACTIVITY>Equipment ($1,000</ACTIVITY>
    <AWARD_DATE>1/16/2006</AWARD_DATE>
    </row>
    </root>
    ==================================================
    XSD - awardsch.xsd
    ==================================================
    Code:
    <Schema xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes">
    
    <ElementType name="DEPARTMENT" content="textOnly"/>
    <ElementType name="CITY" content="textOnly"/>
    <ElementType name="STATE" content="textOnly"/>
    <ElementType name="PROGRAM" content="textOnly"/>
    <ElementType name="AWARD" content="textOnly" dt:type="ui4"/>
    <ElementType name="ACTIVITY" content="textOnly"/>
    <ElementType name="AWARD_DATE" content="textOnly" dt:type="date"/>
    
    </Schema>

  2. #2
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    You have a couple of problems. First, your awardsch.xsd file is not valid XSD. It should look like this:
    Code:
    <?xml version="1.0" encoding="Windows-1252"?>
    <xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
      <xs:element name="root">
        <xs:complexType>
          <xs:sequence>
            <xs:element maxOccurs="unbounded" name="row">
              <xs:complexType>
                <xs:sequence>
                  <xs:element name="DEPARTMENT" type="xs:string" />
                  <xs:element name="CITY" type="xs:string" />
                  <xs:element name="STATE" type="xs:string" />
                  <xs:element name="PROGRAM" type="xs:string" />
                  <xs:element name="AWARD" type="xs:unsignedShort" />
                  <xs:element name="ACTIVITY" type="xs:string" />
                  <xs:element name="AWARD_DATE" type="xs:date" />
                </xs:sequence>
              </xs:complexType>
            </xs:element>
          </xs:sequence>
        </xs:complexType>
      </xs:element>
    </xs:schema>
    Second, XSD only understands dates in the format YYYY-MM-DD. If you correct your XSD file and reformat your dates, they will sort correctly.

    However, if you allow the DataGrid to autogenerate columns, it will display the dates as date/times, with a default time of midnight. If you want to customize the date format, you'll have to set AutoGenerateColumns to False and add BoundColumns to the grid:
    Code:
    <asp:DataGrid ID="dtgCust" runat="server" CellPadding="3" AllowSorting="True" AllowPaging="True"
        ItemStyle-BackColor="#FFFFCC" AlternatingItemStyle-BackColor="#EEEEEE" PageSize="200" AutoGenerateColumns="False">
        <PagerStyle Mode="NumericPages" Position="TopAndBottom" HorizontalAlign="Right">
        </PagerStyle>
        <HeaderStyle BackColor="Navy" HorizontalAlign="Center" ForeColor="White" Font-Bold="True" />
        <AlternatingItemStyle BackColor="#EEEEEE" />
        <ItemStyle BackColor="#FFFFCC" />
        <Columns>
            <asp:BoundColumn DataField="DEPARTMENT" HeaderText="DEPARTMENT" SortExpression="DEPARTMENT"></asp:BoundColumn>
            <asp:BoundColumn DataField="CITY" HeaderText="CITY" SortExpression="CITY"></asp:BoundColumn>
            <asp:BoundColumn DataField="STATE" HeaderText="STATE" SortExpression="STATE"></asp:BoundColumn>
            <asp:BoundColumn DataField="PROGRAM" HeaderText="PROGRAM" SortExpression="PROGRAM"></asp:BoundColumn>
            <asp:BoundColumn DataField="AWARD" HeaderText="AWARD" SortExpression="AWARD"></asp:BoundColumn>
            <asp:BoundColumn DataField="ACTIVITY" HeaderText="ACTIVITY" SortExpression="ACTIVITY"></asp:BoundColumn>
            <asp:BoundColumn DataField="AWARD_DATE" DataFormatString="{0:d}" HeaderText="AWARD DATE" SortExpression="AWARD_DATE"></asp:BoundColumn>
        </Columns>
    </asp:DataGrid>
    Phil Weber
    http://www.philweber.com

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

  3. #3
    Join Date
    Jan 2007
    Posts
    7

    Thumbs up Got it

    Thank you Phil!! - thank you for taking the time to look at it -that is extremely helpful and worked when implemented.

    Have a great day.

  4. #4
    Join Date
    Jan 2007
    Posts
    7

    Sorting XML currency

    Is there a way to get the award column to format as currency and sort correctly?

    I tried adding the following to the datagrid....

    Code:
    <asp:BoundColumn DataField="AWARD" DataFormatString="{0:c}" HeaderText="AWARD" SortExpression="AWARD"></asp:BoundColumn>
    but it wouldn't format. And the numeric values don't sort correctly in that
    three values are sorted as

    121000
    130000
    190

    when it should be...

    190
    121000
    130000.


    Is this related to the XSD file??? Any help would be greatly appreciated again.

  5. #5
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    If you change the data type of the AWARD column to xs:decimal, you can format it as currency.
    Phil Weber
    http://www.philweber.com

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

  6. #6
    Join Date
    Jan 2007
    Posts
    7
    Thanks Phil - I changed the AWARD column to xs:decimal in the XSD file but the data in that column still doesn't format as currency or sort correctly. Maybe I am missing something else?? Does it have to be formatted as currency in the XML document?

    Datagrid

    Code:
    <asp:DataGrid ID="dtgCust" runat="server" CellPadding="3" AllowSorting="True" AllowPaging="True"
        ItemStyle-BackColor="#FFFFCC" AlternatingItemStyle-BackColor="#EEEEEE" PageSize="200" AutoGenerateColumns="False">
        <PagerStyle Mode="NumericPages" Position="TopAndBottom" HorizontalAlign="Right">
        </PagerStyle>
        <HeaderStyle BackColor="Navy" HorizontalAlign="Center" ForeColor="White" Font-Bold="True" />
        <AlternatingItemStyle BackColor="#EEEEEE" />
        <ItemStyle BackColor="#FFFFCC" />
        <Columns>
            <asp:BoundColumn DataField="DEPARTMENT" HeaderText="DEPARTMENT" SortExpression="DEPARTMENT"></asp:BoundColumn>
            <asp:BoundColumn DataField="CITY" HeaderText="CITY" SortExpression="CITY"></asp:BoundColumn>
            <asp:BoundColumn DataField="STATE" HeaderText="STATE" SortExpression="STATE"></asp:BoundColumn>
            <asp:BoundColumn DataField="PROGRAM" HeaderText="PROGRAM" SortExpression="PROGRAM"></asp:BoundColumn>
            <asp:BoundColumn DataField="AWARD" DataFormatString="{0:c}" HeaderText="AWARD" SortExpression="AWARD"></asp:BoundColumn>
            <asp:BoundColumn DataField="ACTIVITY" HeaderText="ACTIVITY" SortExpression="ACTIVITY"></asp:BoundColumn>
            <asp:BoundColumn DataField="AWARD_DATE" DataFormatString="{0:d}" HeaderText="AWARD DATE" SortExpression="AWARD_DATE"></asp:BoundColumn>
        </Columns>
    </asp:DataGrid>
    XSD file

    Code:
    <?xml version="1.0" encoding="Windows-1252"?>
    <xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
      <xs:element name="root">
        <xs:complexType>
          <xs:sequence>
            <xs:element maxOccurs="unbounded" name="row">
              <xs:complexType>
                <xs:sequence>
                  <xs:element name="DEPARTMENT" type="xs:string" />
                  <xs:element name="CITY" type="xs:string" />
                  <xs:element name="STATE" type="xs:string" />
                  <xs:element name="PROGRAM" type="xs:string" />
                  <xs:element name="AWARD" type="xs:decimal" />
                  <xs:element name="ACTIVITY" type="xs:string" />
                  <xs:element name="AWARD_DATE" type="xs:date" />
                </xs:sequence>
              </xs:complexType>
            </xs:element>
          </xs:sequence>
        </xs:complexType>
      </xs:element>
    </xs:schema>

  7. #7
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    That's all I did: changed the AWARD type to xs:decimal in the xsd, and added a DataFormatString of "{0:c}" to the BoundColumn in the aspx file. It works correctly for me. I'm using VS 2005; could that be significant?
    Phil Weber
    http://www.philweber.com

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

  8. #8
    Join Date
    Jan 2007
    Posts
    7

    Thanks

    I got it now Phil. Thank you once again for your assistance with this and taking the time to look at it so closely - it was a huge help.

Similar Threads

  1. Export to excel data from Datagrid rows
    By sunilmskr in forum .NET
    Replies: 0
    Last Post: 06-13-2005, 03:40 AM
  2. Replies: 0
    Last Post: 05-01-2003, 06:42 PM
  3. XML to pass data between tiers?
    By Guy Smith in forum Architecture and Design
    Replies: 13
    Last Post: 10-10-2002, 02:52 PM
  4. Try XML Junction
    By Tim in forum xml.announcements
    Replies: 0
    Last Post: 10-11-2001, 05:00 PM
  5. Data Junction Announces XML Junction 7.51
    By Tim Frost in forum xml.announcements
    Replies: 0
    Last Post: 04-02-2001, 11:53 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center