Grid View with Custom Sorting and Paging

[raphael232]

Hi, i am trying to do custom paging and sorting on my site for the grid view control. So far i have the following:

    <asp:TextBox ID="txtSearch" Text="" runat="server"></asp:TextBox>
    <asp:Button ID="btnSearch" runat="server" Text="Search" /><br />
    <br />
    <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AutoGenerateColumns="False"
        CssClass="Table" DataKeyNames="ID" DataSourceID="ObjectDataSource1" AllowSorting="True">
        <Columns>
            <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
            <asp:BoundField DataField="fldPublishDate" HeaderText="Publish Date" SortExpression="fldPublishDate" />
            <asp:BoundField DataField="fldTitle" HeaderText="Title" SortExpression="fldTitle" />
        </Columns>
    </asp:GridView>
    <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" DeleteMethod="Delete"
        OldValuesParameterFormatString="original_{0}" EnablePaging="True" SortParameterName="SortBy" SelectMethod="GetNewsSubset" SelectCountMethod="GetNewsCountBySearch" TypeName="KITTableAdapters.tbdNewsTableAdapter"
        UpdateMethod="Update">
        <DeleteParameters>
            <asp:Parameter Name="Original_ID" Type="Int32" />
        </DeleteParameters>
        <UpdateParameters>
            <asp:Parameter Name="fldPublishDate" Type="DateTime" />
            <asp:Parameter Name="fldTitle" Type="String" />
        </UpdateParameters>
        <SelectParameters>
            <asp:ControlParameter ControlID="txtSearch" Name="SearchBy" PropertyName="Text" Type="String" />
        </SelectParameters>
    </asp:ObjectDataSource>

I have setup 2 querys in my tbdNewsTableAdapter table adapter:

1. GetNewsCountBySearch(@SearchBy):

SELECT COUNT(*) FROM qryNews WHERE fldTitle LIKE '%' + @SearchBy + '%'

2. GetNewsSubset(@SearchBy, @SortBy, @StartRowIndex, @MaximumRows) - which calls the stored procedure:

ALTER PROCEDURE [dbo].[spGetNewsSubset]
	@SearchBy VARCHAR(255),
	@SortBy VARCHAR(255),
	@StartRowIndex INT,
	@MaximumRows INT
AS
BEGIN
	WITH NewsEntries AS (
		SELECT ROW_NUMBER() OVER (ORDER BY @SortBy) AS RowNum, *
		FROM qryNews
		WHERE fldTitle LIKE '%' + @SearchBy + '%'
	)

	SELECT *
	FROM NewsEntries
	WHERE RowNum BETWEEN @StartRowIndex + 1 AND @StartRowIndex + @MaximumRows
END

I'm getting two problems:

1. I seem to have the custom paging working fine but the sorting does not seem to do anything.

2. The other problem i'm getting is that if the txtSearchBy is empty it is sending to GetNewsCountBySearch as null and subsequently returning 0. However i wish for it to return all the results if the txtSearchBy is empty.

Appreciate if someone could take the time to have a look. Thanks

 

[vbkris]

>>1. I seem to have the custom paging working fine but the sorting does not seem to do anything.

how are you confirming this? does the sort event itself not fire?
one more thing, are you adding dynamic columns to the gridview (using code)...

Known is handfull, Unknown is worldfull