Hi Friends,

 I have a gridview and a sqldatasource in my page. I am handling the DataBinding event to modify the Select Command text of the SqlDataSource which looks like this - 

    <asp:SqlDataSource ID="sds1" runat="server" ConnectionString="<%$ ConnectionStrings:MyConnectionString %>"
        SelectCommand="SELECT * FROM [MyTable]">
    </asp:SqlDataSource>

 And the GridView's Databinding event-

        protected void GridLogs_DataBinding(object sender, EventArgs e)
        {
            string select = "SELECT * FROM [MyTable]";
            bool and = false;

            if (ddlUser.SelectedValue != "0")
            {
                if (and) select += " AND "; else select += " Where ";
                select += "[UserId] = " + ddlUser.SelectedValue;
                and = true;
            }

            if (ddlType.SelectedValue != "0")
            {
                if (and) select += " AND "; else select += " Where ";
                select += "[AuditActionTypeId] = " + ddlType.SelectedValue;
                and = true;
            }
            if (!string.IsNullOrEmpty(tbFrom.Text))
            {
                if (and) select += " AND "; else select += " Where ";
                select += "[DateLogged] >= '" + tbFrom.Text + " 00:00:00'";
                and = true;
            }
            if (!string.IsNullOrEmpty(tbTo.Text))
            {
                if (and) select += " AND "; else select += " Where ";
                select += "[DateLogged] <= '" + tbTo.Text + " 23:59:59'";
                and = true;
            }
            sds1.SelectCommand = select;
        }

Say on the first load there are 50 records and 5 pages are shown to accommodate the records. The pager works ok. The problem is that when some filters are specified, i.e the Select statement is modified.

When a filter is specified say a tbFrom date, the result count should be 15, i.e 2 pages in the gridview with the second one having 5 records. Insted what happens is the pager behaves in a weird way. When I click the page 2 link of the grid the page changes, but insted of showing the last 5 it shows 10 rows,i.e continues with rows as if the select command had no where params. The pager also changes to show 1,2,3,4,5 as with no filters, those links.. i.e 3,4,5 pages are unclickable, i.e the page just posts back and nothing is changed.

 Never mind guys.. My mistake was using the DataBinding event to modify the select command insted of the SqlDataSource.Selecting event.

Hope it will help others.