Help Please with sorting XML data in datagrid

[Cab_0001]

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
==================================================

<%@ 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
==================================================

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
==================================================

<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
==================================================

<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>

[Phil Weber]

You have a couple of problems. First, your awardsch.xsd file is not valid XSD. It should look like this:

<?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:

<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>

[Cab_0001]

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.

[Cab_0001]

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

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

<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.

[Phil Weber]

If you change the data type of the AWARD column to xs:decimal, you can format it as currency.

[Cab_0001]

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

<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

<?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>

[Phil Weber]

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?

[Cab_0001]

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.