Applying filter on paginated page shows empty result

Question

I have a problem with paginated pages to apply filter. I have used ListView Control to display data from database & having some filters. Now problem is suppose If I view all products (18 Products per page) & having 187 Products So If I switch to 7th page & then apply price range filter then it find products from that range but showing empty result. This is my live site Link http://foxboxretail.in/products

My Datapager Code

protected void OnPagePropertiesChanging(object sender, PagePropertiesChangingEventArgs e)
{
    (products.FindControl("DataPager1") as DataPager).SetPageProperties(e.StartRowIndex, e.MaximumRows, false);
    products.PageIndex = 0;
    buildWhereClause();
}

Filter Applying Code

public string buildWhereClause()
{
    string pageName = string.Empty;    
    string constr = ConfigurationManager.ConnectionStrings("conio2").ConnectionString;
    string query = "select * from products";
    string joiner = "";

    string condition = string.Empty;
    string whereClause = string.Empty;
    string priceCondition = string.Empty;

    try {
        for (i = 0; i <= priceFilter.Items.Count - 1; i++) {
            if (priceFilter.Items(i).Selected) {
                string price = priceFilter.Items(i).ToString;
                priceCondition = string.Concat(priceCondition, joiner, string.Format("'{0}'", price));
                if (string.IsNullOrEmpty(joiner))
                    joiner = ",";
            }
        }

        joiner = " where ";
        if (!string.IsNullOrEmpty(priceCondition)) {
            whereClause = string.Concat(whereClause, joiner, string.Format("price_range IN ({0})", priceCondition));
            joiner = " and ";
        }


        string masterClause = string.Empty;
        if (whereClause == string.Empty) {
            masterClause = (query + " Where type = @type and status = @status");
        } else {
            masterClause = (query + whereClause + " and type = @type and status = @status");
        }

        using (MySqlConnection con = new MySqlConnection(constr)) {
            using (MySqlCommand cmd = new MySqlCommand(masterClause)) {
                using (MySqlDataAdapter sda = new MySqlDataAdapter(cmd)) {
                    cmd.Parameters.AddWithValue("@type", "product");
                    cmd.Parameters.AddWithValue("@status", "active");
                    cmd.Connection = con;
                    using (DataTable dt = new DataTable()) {
                        sda.Fill(dt);
                        products.DataSource = dt;
                        products.DataBind();
                        itemCount.Text = dt.Rows.Count.ToString;
                    }
                }
            }
        }
    } catch (Exception ex) {
        Response.Write(ex);
        return string.Concat(query, whereClause);
    }
}

UPDATE (Snapshot of datapager)

score 0 · Answer 1 · answered Sep 01 '16 at 05:56

0

The results count says the records are found (I applied a "Rs.501 - Rs.1000" filter and there were 33 results - at least that is what the label shown) but the list is trying to show the 7th page (and there are only 2)

Try to reset the page index after filtering data.

answered Sep 01 '16 at 05:56

owczarek

335
2
10

Please let me know how to reset page? – SUN Sep 01 '16 at 06:05
Check this answer: http://stackoverflow.com/questions/2698954/set-pageindex-of-datapager – owczarek Sep 01 '16 at 06:15
This is not working for me. Is that anything I need to change in my code? – SUN Sep 01 '16 at 06:45

Richa Garg · Answer 2 · 2016-09-20T06:15:39.140

0

Change the PageIndex property of the gridview to the first page or the last page whenever you are changing the filters. Remember to bind the gridview after changing the pageindex

To set the page index to first page:

mygridView.PageIndex = 0;

As you are using the DataPager for paging, you need to set as follows after the buildWhereClause :

(products.FindControl("DataPager1") as DataPager).SetPageProperties(e.StartRowIndex, e.MaximumRows, true);

Set the last input as true in the SetPagerProperties

edited Sep 20 '16 at 06:15

answered Sep 01 '16 at 06:13

Richa Garg

1,888
12
23

Can you tell me how to reset pageIndex? – SUN Sep 14 '16 at 11:40
Updated my answer – Richa Garg Sep 15 '16 at 04:34
PageIndex = 0 not coming in intellisense. – SUN Sep 16 '16 at 14:46
I have updated my code in my post in Datapager Code Section. Please review it – SUN Sep 17 '16 at 15:20
Move the line of PageIndex after the function call `buildWhereClause` – Richa Garg Sep 18 '16 at 07:08
Please view my update on post. I have posted snap of my problem. Can you please view & let me know? – SUN Sep 20 '16 at 05:36
@SUN, Please check now and let me know – Richa Garg Sep 20 '16 at 06:03
No. This also didn't worked. It keeps on loading page when witching to 1 to 2 page here is the link of live site which consist this problem http://foxboxretail.in/products – SUN Sep 21 '16 at 06:40
you also not getting where's the problem? – SUN Sep 24 '16 at 05:32

Applying filter on paginated page shows empty result

2 Answers2