How to filter a grid with an expression of multiple columns

[Manula]

Hi,

I am using a Telerik Rad grid in my application from where I need to filter data from an expression which creates with selected multiple columns. In my grid there is a DateTimeColumn and other several columns to filter data. When I select each column separately filter is working but for selected multiple columns the filter expression is not working  well.  So I need to filter data from multiple columns with the filter expression.
Here is the code listing i have done with the grid.

protected void grdUserType_ItemDataBound(object sender, GridItemEventArgs e)
    {
        if (e.Item is GridDataItem)
        {
            GridDataItem item = (GridDataItem)e.Item;
            string Datakey = item.GetDataKeyValue("RegBy") == null ? "-1" : item.GetDataKeyValue("RegBy").ToString();
            DataRowView DataItem = (DataRowView)e.Item.DataItem;
            item["ActionType"].Text = string.Format("{0} ({1})", DataItem["ActionType"], DataItem["Description"]);              
            HyperLink hy = new HyperLink();
            hy.Text = item.GetDataKeyValue("FullName").ToString();
            hy.Attributes.Add("onclick", "javascript:calluserpopup('" + Datakey + "');return false;");
            hy.NavigateUrl = "#";
            hy.CssClass = "pagelinks";
            item["RegBy"].Controls.Add(hy);

            Label lblDt = (Label)item["RegDateOnly"].FindControl("lblDt");
            lblDt.Text = string.Format("{0}", Convert.ToDateTime(DataItem["RegDate"]));
        }
       
        if (e.Item is GridFilteringItem)
        {
            Control ctl = (e.Item as GridFilteringItem)["RegDateOnly"].Controls[1];
            RadDatePicker tbPattern = ctl as RadDatePicker;
            if (Session["OldPattern"] != null )
            tbPattern.SelectedDate = Convert.ToDateTime(Session["OldPattern"]);

        }
    }

protected void grdUserType_ItemCommand(object source, GridCommandEventArgs e)
     {
        switch (e.CommandName)
        {
            case RadGrid.FilterCommandName:
                System.Web.UI.Pair pair = (System.Web.UI.Pair)e.CommandArgument;
                if (pair.First.ToString() == "EqualTo" | pair.First.ToString() == "GreaterThan" | pair.First.ToString() == "LessThan" )
                {
                    if (pair.Second.ToString() == "RegDateOnly" )
                    {
                        DatePatternfunction = pair.First.ToString();
                        Control ctl = (e.Item as GridFilteringItem)["RegDateOnly"].Controls[1];
                       RadDatePicker tbPattern = ctl as RadDatePicker;

                        DateTime dt2 = tbPattern.SelectedDate.Value;

                        // Convert the date to date type of the database
                        string dt = dt2.ToString("d", new System.Globalization.CultureInfo("en-Gb"));
                        GridFilteringItem filterItem = (GridFilteringItem)e.Item;

                        string filterPattern = string.Empty;
                       
                        filterPattern = dt;
                        Session["filterPattern"] = filterPattern;
                        Session["OldPattern"] = string.Format("{0:d}", dt2.Date);
                               
                    }
                }
                if (pair.First.ToString() == "NoFilter")
                {
                    Session["filterPattern"] = null ;
                   Session["OldPattern"] = null;

               }
              break ;
        }
    } 

protected void grdUserType_NeedDataSource(object source, GridNeedDataSourceEventArgs e)
    {
        if (Session["filterPattern"] != null)
        {
               string Expression = grdUserType.MasterTableView.FilterExpression;
           
               // To convert the date format which accepts the data base
               Expression = Expression.Replace(Session["OldPattern"].ToString(), Session["filterPattern"].ToString());

               grdUserType.MasterTableView.FilterExpression = Expression;
               DatePatternfunction = string.Empty; 
                Session["filterPattern"] = null;
        }
       // To get the data source to the grid
        grdUserType.DataSource = this.GetDataTable(1);
    }

[Veli]

Hi Manula,

We are not able to identify the cause of this issue from the sample code you provided. Can you explain what are you getting? Does an exception occur, the grid doesn't filter at all or the filtering is not correct? Also, what do you mean by "selected multiple columns the filter expression is not working  well", and particularly how do you select multiple columns? Any further relevant code we can look at would be useful.

Greetings,
Veli
the Telerik team

[Manula]

Hi,

Normally we can filter data of each column separately. Here  what I particularly want is to filter data from several columns once, by selecting filtering values of those columns.

Let say first I filter values of 1st column of the grid. Then second time, I filter values of 2nd column. Then the filter expression(Query) should be a combination of 1st time filtering expression and 2nd time filtering expression to filter values.

Specially when I do this with columns which have DateTime values, the Date value format  is converted to  a different format which is suitable to accept by the filter expression of the grid.

That is the problem I have.

Thanks....

[Veli]

Hello Manula,

Thank you for the clarification. When modifying the FilterExpression in RadGrid, you need to make sure the expression is kept valid after the modification. What is the resulting filter expression you are getting? If filtering by separate columns works OK, it means your data format in the resulting filter expression is correct. Not being able to filter by multiple columns probably indicates a malformed filter expression.

Veli
the Telerik team