[RESOLVED] gridview sorting

[jlbantang]

hi I have a Gridview attached to sqldatasource that I want to be able to sort using specific bound column named Dated. It seems the ORDER BY in sqldatasource select command do not work at this time. What exactly happen when I hit the Date Column the gridview become empty.

I found these old post http://www.vbforums.com/showthread.p...=sort+gridview but Im not getting the result.

HTML

<asp:GridView ID="delaylogsGridView" DataSourceID="delaylogssqldatasource" DataKeyNames="id" 
                    AllowPaging="True"  AutoGenerateColumns="False" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None" 
                  AllowSorting="true"
                   >
                    <Columns>

                        
                        
                        <asp:TemplateField >
                            <ItemTemplate><asp:Image ID="editImage" runat="server" ImageUrl="~/images/edit.gif" />
                            
                            </ItemTemplate>
                             <ItemStyle HorizontalAlign="Left" VerticalAlign="Top" />
                        </asp:TemplateField> 
                                            
                        <asp:BoundField DataField="eventcode" HeaderText="Code" >
                            
                            <ItemStyle HorizontalAlign="Center" VerticalAlign="Top" />
                        </asp:BoundField>
                        
                        <asp:BoundField DataField="dated"  SortExpression="dated" HeaderText="Dated" 
                         >
                            
                            <ItemStyle HorizontalAlign="Center" VerticalAlign="Top" Width="80px" />
                        </asp:BoundField>
                        
                        <asp:BoundField DataField="reference" HeaderText="Reference" >
                            <ItemStyle HorizontalAlign="Left" VerticalAlign="Top" />
                        </asp:BoundField>
                         
                        <asp:BoundField DataField="subject" HeaderText="Subject" >
                            <ItemStyle HorizontalAlign="Left" VerticalAlign="Top" Width="150px"/>
                        </asp:BoundField>
                                                
                        <asp:BoundField DataField="content" HeaderText="Content" />
                        <asp:BoundField DataField="location" HeaderText="Location" >
                            <ItemStyle HorizontalAlign="Center" VerticalAlign="Top" />
                        </asp:BoundField>
                        
                        <asp:BoundField DataField="trade_element" HeaderText="Trade/Element" >
                            <ItemStyle HorizontalAlign="Center" VerticalAlign="Top" />
                        </asp:BoundField>
                        

                    </Columns>
                    <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
                    <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                    <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Left" />
                    <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
                    <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                    <EditRowStyle BackColor="#999999" />
                    <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
                    <PagerSettings FirstPageImageUrl="~/images/first16.gif" LastPageImageUrl="~/images/last16.gif"
                        Mode="NumericFirstLast" NextPageImageUrl="~/images/next16.gif" PreviousPageImageUrl="~/images/previous16.gif" />
                </asp:GridView>

VB

Dim tsql As String = "SELECT ...  "
        tsql += " FROM [DRC].[dbo].[tbl] WHERE 1=1 "
        Dim criteria As String = String.Empty

        If codeTextBox.Text.ToString <> _ALL And codeTextBox.Text.Trim.Length > 0 Then
            criteria += " AND eventcode=" & Val(codeTextBox.Text.ToString)
        Else
            codeTextBox.Text = _ALL
        End If

        If locationTextBox.Text.ToString <> _ALL And locationTextBox.Text.Trim.Length > 0 Then
            criteria += " AND location like '" & locationTextBox.Text.ToString & "'"
        Else
            locationTextBox.Text = _ALL
        End If

        If trade_elementTextBox.Text.ToString <> _ALL And trade_elementTextBox.Text.Trim.Length > 0 Then
            criteria += " AND trade_element like '" & trade_elementTextBox.Text.ToString & "'"
        Else
            trade_elementTextBox.Text = _ALL
        End If



        If projectDropDownList.Text.ToUpper <> "-- SELECT ALL --" Then
            criteria += " AND project='" & projectDropDownList.Text.ToString & "'"
        End If


        tsql += criteria + " ORDER BY dated"

        delaylogssqldatasource.SelectCommand = tsql

Thanks.

[jlbantang]

fix it:

because of this code in tsql : convert(varchar(20),[dated],106) as dated and the date was already converted to string and so the sorting wont work.