ASPxGridView FilterExpression doesn't filter correctly decimal values

Question

When I filter an ASPxGridView decimal column the filtering expression is not interpreted correctly. For example, I use the filter [Value]>20, but all the values greater then 3.00 are show. This behavior occurs just when we set the filtering criteria through the grid.FilterExpression. If we set the same filtering expression in the ColumnAutoFilter the decimal values are interpreted correctly.

The aspx code for the girdView is the following:

<dx:ASPxGridView SettingsBehavior-AutoFilterRowInputDelay="60000" ID="grid" KeyFieldName="Id" 
     AccessKey="G" SkinID="AspxGridView" runat="server" AutoGenerateColumns="False" ClientIDMode="AutoID" 
     OnCustomColumnDisplayText="grid_CustomColumnDisplayText" 
     OnHtmlRowCreated="grid_HtmlRowCreated" 
     OnHtmlDataCellPrepared="grid_HtmlDataCellPrepared" 
     EnableViewState="false" EnableRowsCache="false" meta:resourcekey="gridResource1" Width="100%">
     <Columns>
         <dx:GridViewDataColumn Caption="Column1" FieldName="<%$ Column1Name %>" ShowInCustomizationForm="True" Visible="False" meta:resourcekey="GridViewDataColumnResource11">
             <Settings FilterMode="Value" />
             <CellStyle HorizontalAlign="Right" />
         </dx:GridViewDataColumn>
     </Columns>
     <Settings ShowFilterRow="True" />
     <SettingsCustomizationWindow Enabled="True" />
</dx:ASPxGridView>

On Server side we add at Page_Init the filtering expression.

protected void Page_Init(object sender, EventArgs e)
{
    // initially we retrieve the filter from the DB
    filter = FilterMngr.GetItem(filterId, Settings.Default.MainConnStr)

    this.grid.SettingsCookies.Enabled = false;
    this.grid.FilterExpression = filter.FilterExpression;
}

Then at Page_Load we populate the grid with values

protected void Page_Load(object sender, EventArgs e)
{
    var gridValues = GetValuesFromDataBase();
    this.grid.DataSource = gridValues;
    grid.DataBind();
}

The filter expression is the following one: [ColumnName] > '20' And [ColumnName] < '50'

Until now I have tried the following approaches in order to solve the bug:

I verified that the filtering expression uses the FieldName not the Caption name
The FieldName is a valid name
The page is localized so decimal and thousand separators marks should be interpreted correctly
Handle the ColumnAutoFilter.

In the Aspx page we added the following property to the gridView

OnProcessColumnAutoFilter="grid_ProcessColumnAutoFilter"


protected void gridInverters_ProcessColumnAutoFilter(object sender,
DevExpress.Web.ASPxGridView.ASPxGridViewAutoFilterEventArgs e)
{
    if (e.Kind == GridViewAutoFilterEventKind.CreateCriteria)
    {
        if (!String.IsNullOrEmpty(e.Value))
        {
            decimal value;
            if (decimal.TryParse(
                e.Value, 
                NumberStyles.Currency, 
                CultureInfo.CurrentCulture, 
                out value))
                e.Criteria = new BinaryOperator(e.Column.FieldName, value);
        }
    }
}

Parse the filter expression from the database

var parsedExpression = CriteriaOperator.Parse(filter.FilterExpression); string criteria = DevExpress.Data.Filtering.CriteriaOperator.LegacyToString(parsedExpression); this.grid.FilterExpression = criteria;

At the current moment I can't figure out why this behavior occurs, so any suggestion into this matter would be very useful.

Uranus Uranus · Accepted Answer · 2014-12-04T08:13:11

[ColumnName] > '20' And [ColumnName] < '50' - This expression compares string values, not numbers. To compare numbers, the following expression should be used: [ColumnName] > 20 And [ColumnName] < 50

The syntax is described in the online documentation: Criteria Language Syntax

Also, column values should be of an appropriate type. If values are of the string type, it does not matter how they formatted. They will be compared as strings. In other words - alphabetically.

ASPxGridView FilterExpression doesn't filter correctly decimal values

1 Answers