-
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>
-
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!
-
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.
-
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.
-
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!
-
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>
-
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!
-
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
-
By sunilmskr in forum .NET
Replies: 0
Last Post: 06-13-2005, 02:40 AM
-
Replies: 0
Last Post: 05-01-2003, 05:42 PM
-
By Guy Smith in forum Architecture and Design
Replies: 13
Last Post: 10-10-2002, 01:52 PM
-
By Tim in forum xml.announcements
Replies: 0
Last Post: 10-11-2001, 04:00 PM
-
By Tim Frost in forum xml.announcements
Replies: 0
Last Post: 04-02-2001, 10: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
-
Forum Rules
|
Development Centers
-- Android Development Center
-- Cloud Development Project Center
-- HTML5 Development Center
-- Windows Mobile Development Center
|