Gridview Sort Date Issue

[melvin74]

I have a gridview that I want to be able to sort by date and also manipulate the color of the cells based on the date. In the mysql database I have that date field as a character field, and it would cause problems if i were to change it.

So the problem is the gridview is sorting it as a string and not a date. The column is a template field.

I am using a sqldatasource to connect. Was curiuos if way to fix this? Without having to manually create my dataset.

Thanks

[mendhak]

Show code please.

[melvin74]

This is the markup for my gridview

An example of how it would sort woudl be this.

3/1/2009
3/10/2009
3/5/2009

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
                    BackColor="#CCCCCC" BorderColor="#999999" BorderStyle="Solid" BorderWidth="3px" 
                    CellPadding="4" CellSpacing="2" Font-Size="Small" ForeColor="Black" 
                AllowPaging="True" AllowSorting="True" DataSourceID="SqlDataSource1" 
                PageSize="35" DataKeyNames="xospo,pcname" DataMember="DefaultView" 
                        style="margin-right: 0px; margin-left: 0px;" ShowFooter="True">
                <FooterStyle BackColor="#CCCCCC" />
                <RowStyle BackColor="White" />
                <Columns>
                    <asp:CommandField ButtonType="Button" ShowEditButton="True" HeaderText="Edit" />
                    <asp:BoundField DataField="xospo" HeaderText="Quote #" SortExpression="xospo" 
                        ReadOnly="True" HtmlEncode="False" />
                    <asp:BoundField DataField="xostky" HeaderText="Cust #" 
                        SortExpression="xostky" ReadOnly="True" />
                    <asp:BoundField DataField="xosoldtoname" HeaderText="Cust. Name" 
                        SortExpression="xosoldtoname" ReadOnly="True" />
                    <asp:BoundField DataField="updatedate" HeaderText=" PB Update Date"
                        HtmlEncode="False" ReadOnly="True" SortExpression="updatedate" />
                    <asp:BoundField DataField="pcname" HeaderText="PC Name" ReadOnly="True" 
                        SortExpression="pcname" />
                    <asp:BoundField DataField="xosm" HeaderText="Rep" SortExpression="xosm" 
                        ReadOnly="True" />
                    <asp:TemplateField HeaderText="Results" SortExpression="result">
                        <EditItemTemplate>
                            <asp:DropDownList ID="DropDownList2" runat="server" 
                                DataSourceID="SqlDataSource2" DataTextField="codedesc" 
                                DataValueField="codedesc" SelectedValue='<%# bind("result") %>'>
                            </asp:DropDownList>
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:Label ID="Label1" runat="server" Text='<%# bind("result") %>'></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="# Units" SortExpression="units">
                        <EditItemTemplate>
                            <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("units") %>' 
                                Width="56px"></asp:TextBox>
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:Label ID="Label2" runat="server" Text='<%# Bind("units") %>'></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Est Ship Date" SortExpression="estshipdate">
                        <EditItemTemplate>
                         <asp:Image ID="Image1" 
                                runat="server" ImageUrl="~/cal.png" style="width: 16px" />
                            &nbsp;&nbsp; &nbsp;
                             <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("estshipdate")  %>' 
                                Width="73px" ReadOnly="False"></asp:TextBox>&nbsp;
                            <ajaxToolkit:CalendarExtender ID="CalendarExtender3" runat="server" TargetControlID="textbox2"  PopupButtonID="image1" EnableViewState="False" >
                            </ajaxToolkit:CalendarExtender>
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:Label ID="Label3" runat="server" Text='<%# bind("estshipdate") %>'></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Prob %" SortExpression="prob">
                        <EditItemTemplate>
                            <asp:DropDownList ID="DropDownList3" runat="server" SelectedValue='<%# bind("prob") %>'>
                                <asp:ListItem></asp:ListItem>
                                <asp:ListItem>0</asp:ListItem>
                                <asp:ListItem>25</asp:ListItem>
                                <asp:ListItem>50</asp:ListItem>
                                <asp:ListItem>75</asp:ListItem>
                                <asp:ListItem>100</asp:ListItem>
                            </asp:DropDownList>
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:Label ID="Label4" runat="server" Text='<%# Bind("prob") %>'></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:BoundField DataField="dvalue" HeaderText="Total $" 
                        SortExpression="dvalue" HtmlEncode="False" ReadOnly="True" />
                    <asp:BoundField DataField="totalsell" HeaderText="$ Per Unit" 
                        SortExpression="totalsell" ReadOnly="True" />
                    <asp:BoundField DataField="xepn" HeaderText="Model" SortExpression="xepn" ReadOnly="True" />
                    <asp:BoundField DataField="authuser" HeaderText="Changes" SortExpression="authuser" ConvertEmptyStringToNull="False" HtmlEncode="False" ReadOnly="True" />
                </Columns>
                <PagerStyle BackColor="#CCCCCC" ForeColor="Black" HorizontalAlign="Left" />
                <SelectedRowStyle BackColor="#000099" Font-Bold="True" ForeColor="White" />
                <HeaderStyle BackColor="Black" Font-Bold="True" ForeColor="White" />
                <EditRowStyle Font-Strikeout="False" />
            </asp:GridView>

[mendhak]

The Gridview needs to know, somehow, that you've given it a date field. Add this to the column.


<asp:BoundField DataField="updatedate" HeaderText=" PB Update Date" DataFormatString="{0:MMMM dd yyyy}" HtmlEncode="false" HtmlEncode="False" ReadOnly="True" SortExpression="updatedate" />

[melvin74]

Thanks for response. But it is a template field so I dont have that property i dont think?

It is the estshipdate field.

[mendhak]

You may then be in a bit of a pickle. When specified in a bound field, the 'date' value should be recognized as a date. However, in a templatefield, it's custom. So the gridview just throws its hands up and says "OK, it's all strings to me."

The easiest way around this would be to use another date format such as

2009-04-13

So that it's sorted by year, then month, then date.

The other way is to use a SortExpression and to convert the string to a datetime and then proceed.
http://msdn.microsoft.com/en-us/library/hwf94875.aspx