This is a migrated thread and some comments may be shown as answers.

Filtering GridViewDateTimeColumn

7 Answers 189 Views
GridView
This is a migrated thread and some comments may be shown as answers.
ALED
Top achievements
Rank 1
ALED asked on 12 Jul 2016, 03:39 AM

Hello All,

I have problem when catch filtering datetime in radgrid, in my case i want get a result of filter ,

this is my snippet result of Expression Filter :

ModifiedAt = #07/14/2016 00:00:00#

a problem come from character "#" , i used an expression to my SQl query , show i want to remove # and replace with single quote " ' ", like this

ModifiedAt = '07/14/2016 00:00:00' 

so how its work?

7 Answers, 1 is accepted

Sort by
0
Dess | Tech Support Engineer, Principal
Telerik team
answered on 13 Jul 2016, 09:50 AM
Hello Aled,

Thank you for writing. 
 
When you have a DateTime value in the filter cell, the FilterDescriptor.FilterExpression uses a specific format for the DateTime value: "#{0}#". This is by design. In order to use the expression in the SQL query, you have two options: the first one is to pass to the query the expression by replacing the " " symbol with " ". An alternative solution is to handle the RadGridView.FilterChanged event and construct the expression manually in the desired format considering the FilterDescriptor's properties.
 
I hope this information helps. Should you have further questions I would be glad to help.

Regards,
Dess
Telerik by Progress
Check out the Windows Forms project converter, which aids the conversion process from standard Windows Forms applications written in C# or VB to Telerik UI for WinForms.For more information check out this blog post and share your thoughts.
0
ALED
Top achievements
Rank 1
answered on 13 Jul 2016, 09:56 AM

Dear Dess, 

thanks for reply, i think replacing " # " with " ' " symbol is not a good solution, because in my mind, a time a symbol contains in my string because the grid design have a field description, event description string have a filter string contains " # ", its will be replacing too, and this not good, so any another way for this case, really i was spent 2 day tou out from this problem, please give me another way. thanks

0
Dess | Tech Support Engineer, Principal
Telerik team
answered on 13 Jul 2016, 11:21 AM
Hello Aled,

Thank you for writing back. 

Feel free to use the suggested alternative solution from my previous post: handle the RadGridView.FilterChanged event and construct the expression manually in the desired format considering the FilterDescriptor's properties (Value, Operator, PropertyName).

I hope this information helps. If you have any additional questions, please let me know.

Regards,
Dess
Telerik by Progress
Check out the Windows Forms project converter, which aids the conversion process from standard Windows Forms applications written in C# or VB to Telerik UI for WinForms.For more information check out this blog post and share your thoughts.
0
ALED
Top achievements
Rank 1
answered on 13 Jul 2016, 11:41 AM

Dear dess,

thanks for reply

but i am really confused, what do you mean? could you sent me a snippet code, please.

thanks

0
Dess | Tech Support Engineer, Principal
Telerik team
answered on 13 Jul 2016, 12:51 PM
Hello Aled,

Thank you for writing back. 

You can find below a sample approach how you can manually build the filter expression:
string filterExpression = string.Empty;
 
private void radGridView1_FilterChanged(object sender, GridViewCollectionChangedEventArgs e)
{
    if (e.Action != NotifyCollectionChangedAction.Remove)
    {
        foreach (FilterDescriptor fd in e.NewItems)
        {
            DateFilterDescriptor dateTimeFilterDescriptor = fd as DateFilterDescriptor;
            if (dateTimeFilterDescriptor != null)
            {
                filterExpression = BuildFilterExpression(dateTimeFilterDescriptor);
                Console.WriteLine(filterExpression);
            }
        }
    }
}
 
private string BuildFilterExpression(DateFilterDescriptor fd)
{
    StringBuilder sb = new StringBuilder();
    string dateBegin = null;
    string dateEnd = null;
    if (fd.Value != null)
    {
        dateBegin = String.Format(CultureInfo.InvariantCulture, "'{0}'", fd.Value.Value.Date);
        dateEnd = String.Format(CultureInfo.InvariantCulture, "'{0}'", fd.Value.Value.Date.AddDays(1));
    }
    switch (fd.Operator)
    {
        case FilterOperator.None:
            return String.Empty;
 
        case FilterOperator.IsNull:
            sb.Append(string.Format("{0} IS NULL", fd.PropertyName));
            break;
        case FilterOperator.IsNotNull:
            sb.Append(string.Format("NOT ({0} IS NULL)", fd.PropertyName));
            break;
        case FilterOperator.IsLessThan:
            sb.Append(string.Format("{0} < {1}", fd.PropertyName, dateBegin));
            break;
        case FilterOperator.IsLessThanOrEqualTo:
            sb.Append(string.Format("{0} < {1}", fd.PropertyName, dateEnd));
            break;
        case FilterOperator.IsLike:
        case FilterOperator.IsEqualTo:
            sb.Append(string.Format("{0} = {1}", fd.PropertyName, dateBegin));
            break;
        case FilterOperator.IsNotLike:
        case FilterOperator.IsNotEqualTo:
            sb.Append(string.Format("{0} < {1} OR {0} >= {2}", fd.PropertyName, dateBegin, dateEnd));
            break;
        case FilterOperator.IsGreaterThanOrEqualTo:
            sb.Append(string.Format("{0} >= {1}", fd.PropertyName, dateBegin));
            break;
        case FilterOperator.IsGreaterThan:
            sb.Append(string.Format("{0} >= {1}", fd.PropertyName, dateEnd));
            break;
        case FilterOperator.StartsWith:
        case FilterOperator.EndsWith:
        case FilterOperator.Contains:
        case FilterOperator.NotContains:
        case FilterOperator.IsContainedIn:
        case FilterOperator.IsNotContainedIn:
        default:
            return String.Empty;
    }
    return sb.ToString();
}

Note that this is just a sample approach and it may not cover all possible cases. Feel free to modify it in a way which suits your requirement best.

I hope this information helps. If you have any additional questions, please let me know.

Regards,
Dess
Telerik by Progress
Check out the Windows Forms project converter, which aids the conversion process from standard Windows Forms applications written in C# or VB to Telerik UI for WinForms.For more information check out this blog post and share your thoughts.
0
ALED
Top achievements
Rank 1
answered on 13 Jul 2016, 02:21 PM

Dear Dess,

thanks so much,

but actually i not want used FilterChanged Event, cause every user type is enter to event, in another choice i used FilterExpressionChanged, could you tell me if u sed FilterChangedEvent to reformat Filter like ur code before? thanks

0
Dess | Tech Support Engineer, Principal
Telerik team
answered on 14 Jul 2016, 06:59 AM
Hello Aled,

Thank you for writing back. 

The FilterChanged event is fired each time the data in RadGridView is filtered. Each entered in the filter cell changes the filter. Hence, the FilterChanged event is fired each time. However, the FilterExpressionChanged event is fired as well every time the filter value is modified. I can suggest you to handle the CellEndEdit event for the filter cell and construct the filter expression when the filter cell is not in edit mode yet. But have in mind that the grid data will be already filtered: 
private void radGridView1_CellEndEdit(object sender, GridViewCellEventArgs e)
 {
     if (e.Row is GridViewFilteringRowInfo)
     {
         foreach (FilterDescriptor fd in this.radGridView1.FilterDescriptors)
         {
             DateFilterDescriptor dateTimeFilterDescriptor = fd as DateFilterDescriptor;
             if (dateTimeFilterDescriptor != null)
             {
                 filterExpression = BuildFilterExpression(dateTimeFilterDescriptor);
                 Console.WriteLine(filterExpression);
             }
         }
     }
 }
 
//... the next code part

I hope this information helps. If you have any additional questions, please let me know.

Regards,
Dess
Telerik by Progress
Check out the Windows Forms project converter, which aids the conversion process from standard Windows Forms applications written in C# or VB to Telerik UI for WinForms.For more information check out this blog post and share your thoughts.
Tags
GridView
Asked by
ALED
Top achievements
Rank 1
Answers by
Dess | Tech Support Engineer, Principal
Telerik team
ALED
Top achievements
Rank 1
Share this question
or