The FilterExpression tag in ASP.NET does not support the between operator. If you try to specify a clause like “between x and y” as your filter expression, please use where clause like "smp >= x AND smp<= y" to achieve this.
Please mark the replies as answers if they help or unmark if not.
Feedback to us
The expression contains unsupported operator 'Between'.
to know which items are supported or not, PLZ FOLLOW THE RULES:
When you create an expression for a filter, enclose strings with single quotation marks:
"LastName = 'Jones'"
If a column name contains any non-alphanumeric characters or starts with a digit or matches (case-insensitively) any of the following reserved words, it requires special handling, as described in the following paragraphs.
And
Between
Child
False
In
Is
Like
Not
Null
Or
Parent
True
If a column name satisfies one of the above conditions, it must be wrapped in either square brackets or the "`" (grave accent) quotes. For example, to use a column named "Column#" in an expression, you would write either "[Column#]":
Total * [Column#]
or "`Column#`":
Total * `Column#`
If the column name is enclosed in square brackets then any ']' and '\' characters (but not any other characters) in it must be escaped by prepending them with the backslash ("\") character. If the column name is enclosed in grave accent characters then it
must not contain any grave accent characters in it. For example, a column named "Column[]\" would be written:
Total * [Column[\]\\]
or
Total * `Column[]\`
User-Defined Values
<div class="subsection">
User-defined values may be used within expressions to be compared with column values. String values should be enclosed within single quotation marks (and each single quotation character in a string value has to be escaped by prepending it with another single
quotation character). Date values should be enclosed within pound signs (#) or single quotes (') based on the data provider. Decimals and scientific notation are permissible for numeric values. For example:
"FirstName = 'John'"
"Price <= 50.00"
"Birthdate < #1/31/82#"
For columns that contain enumeration values, cast the value to an integer data type. For example:
"EnumColumn = 5"
</div>
Parsing Literal Expressions
<div class="subsection">
All literal expressions must be expressed in the invariant culture locale. When
DataSet parses and converts literal expressions, it always uses the invariant culture, not the current culture.
String literals are identified when there are single quotes surrounding the value. For example:
'John'
Boolean literals are true and false; they are not quoted in expressions.
Integer literals [+-]?[0-9]+ are treated as
System.Int32, System.Int64 or
System.Double. System.Double can lose precision depending on how large the number is. For example, if the number in the literal is 2147483650,
DataSet will first attempt to parse the number as an
Int32. This will not succeed because the number is too large. In this case
DataSet will parse the number as an Int64, which will succeed. If the literal was a number larger than the maximum value of an Int64,
DataSet will parse the literal using Double.
Real literals using scientific notation, such as 4.42372E-30, are parsed using
System.Double.
Real literals without scientific notation, but with a decimal point, are treated as
System.Decimal. If the number exceeds the maximum or minimum values supported by
System.Decimal, then it is parsed as a System.Double. For example:
142526.144524 will be converted to a Decimal.
345262.78036719560925667 will be treated as a Double.
</div>
Operators
<div class="subsection">
Concatenation is allowed using Boolean AND, OR, and NOT operators. You can use parentheses to group clauses and force precedence. The AND operator has precedence over other operators. For example:
(LastName = 'Smith' OR LastName = 'Jones') AND FirstName = 'John'
When you create comparison expressions, the following operators are allowed:
<
>
<=
>=
<>
=
IN
LIKE
The following arithmetic operators are also supported in expressions:
+ (addition)
- (subtraction)
* (multiplication)
/ (division)
% (modulus)
</div>
String Operators
<div class="subsection">
To concatenate a string, use the + character. The value of the CaseSensitive property of the
DataSet class determines whether string comparisons are case-sensitive. However, you can override that value with the
CaseSensitive property of the
DataTable class.
</div>
Wildcard Characters
<div class="subsection">
Both the * and % can be used interchangeably for wildcard characters in a LIKE comparison. If the string in a LIKE clause contains a * or %, those characters should be enclosed in brackets ([]). If a bracket is in the clause, each bracket character should
be enclosed in brackets (for example [[] or []]). A wildcard is allowed at the start and end of a pattern, or at the end of a pattern, or at the start of a pattern. For example:
"ItemName LIKE '*product*'"
"ItemName LIKE '*product'"
"ItemName LIKE 'product*'"
Wildcard characters are not allowed in the middle of a string. For example, 'te*xt' is not allowed.
</div>
Parent/Child Relation Referencing
<div class="subsection">
A parent table may be referenced in an expression by prepending the column name with
Parent. For example, the Parent.Price references the parent table's column named
Price.
When a child has more than one parent row, use Parent(RelationName).ColumnName. For example, the Parent(RelationName).Price references the parent table’s column named Price via the relation.
A column in a child table may be referenced in an expression by prepending the column name with
Child. However, because child relationships may return multiple rows, you must include the reference to the child column in an aggregate function. For example,
Sum(Child.Price) would return the sum of the column named
Price in the child table.
If a table has more than one child, the syntax is: Child(RelationName). For example, if a table has two child tables named
Customers and Orders, and the
DataRelation object is named
Customers2Orders, the reference would be as follows:
Avg(Child(Customers2Orders).Quantity)
</div>
Aggregates
<div class="subsection">
The following aggregate types are supported:
Sum (Sum)
Avg (Average)
Min (Minimum)
Max (Maximum)
Count (Count)
StDev (Statistical standard deviation)
Var (Statistical variance).
Aggregates are ordinarily performed along relationships. Create an aggregate expression by using one of the functions listed earlier and a child table column as detailed in Parent/Child Relation Referencing that was discussed earlier. For example:
Avg(Child.Price)
Avg(Child(Orders2Details).Price)
An aggregate can also be performed on a single table. For example, to create a summary of figures in a column named "Price":
Sum(Price)
<div class="alert">
Note
If you use a single table to create an aggregate, there would be no group-by functionality. Instead, all rows would display the same value in the column.
</div>
If a table has no rows, the aggregate functions will return null.
Data types can always be determined by examining the DataType property of a column. You can also convert data types using the Convert function, shown
in the following section.
All conversions are valid with the following exceptions: Boolean can be coerced to and from
Byte, SByte,
Int16, Int32,
Int64, UInt16,
UInt32, UInt64,
String and itself only. Char can be coerced to and from
Int32, UInt32,
String, and itself only. DateTime can be coerced to and from
String and itself only. TimeSpan can be coerced to and from
String and itself only.
You can reset the Expression property by assigning it a null value or empty string. If a default value is set on the expression column, all previously filled rows are assigned the default value after the
Expression property is reset.
@ToughMan, you just copy a big chunck of information and its confusing to me.
The FilterExpression property value is a format string expression (a string that is processed by the
String.Format method) that uses the values in the
FilterExpression collection for any substitution parameters included in the string.
The filter expression syntax is the same syntax that is accepted by the
RowFilter property,
The FilterExpression tag in ASP.NET does not support the between operator.
No, it should SUPPORT, just as what I've said above.
It is not supported. Frank is correct. There's nothing in the huge chunk of MSDN that you copy-pasted that supports what you claim. In fact, the error message that jmerced gets also tells you this: "the expression contains unsupported operator 'Between'.
If a column name contains any non-alphanumeric characters or starts with a digit or matches (case-insensitively)
any of the following reserved words, it requires special handling, as described in the following paragraphs.
That portion of the documentation explains how to manage columns names that matcha reserved word. It does not say that any of the reserved words listed are supported as part of filter expressions. In fact, if you read further on in the stuff you pasted, you will find a section headed "Operators". That does actually list the supported
operators. Notice that "Between" is not included among them.
jmerced
Member
21 Points
171 Posts
An alternative to use "Between Operator" in "FilterExpression"
Nov 30, 2012 04:39 PM|LINK
Hello all,
I wonder if there is a alternative solution to use "Between Operator" in "FilterExpression".
I.m getting this error:
The expression contains unsupported operator 'Between'.
Thanks in advance,
Vipindas
Contributor
5514 Points
810 Posts
Re: An alternative to use "Between Operator" in "FilterExpression"
Nov 30, 2012 04:43 PM|LINK
Refer this
http://dotnetslackers.com/Community/blogs/haissam/archive/2007/03/24/Betwen-Operator-in-DataView.RowFilter.aspx
Frank Jiang ...
All-Star
16006 Points
1728 Posts
Microsoft
Re: An alternative to use "Between Operator" in "FilterExpression"
Dec 03, 2012 04:57 AM|LINK
The FilterExpression tag in ASP.NET does not support the between operator. If you try to specify a clause like “between x and y” as your filter expression, please use where clause like "smp >= x AND smp<= y" to achieve this.
Feedback to us
Develop and promote your apps in Windows Store
ToughMan
Participant
1490 Points
635 Posts
Re: An alternative to use "Between Operator" in "FilterExpression"
Dec 03, 2012 05:14 AM|LINK
to know which items are supported or not, PLZ FOLLOW THE RULES:
When you create an expression for a filter, enclose strings with single quotation marks:
"LastName = 'Jones'"
If a column name contains any non-alphanumeric characters or starts with a digit or matches (case-insensitively) any of the following reserved words, it requires special handling, as described in the following paragraphs.
And
Between
Child
False
In
Is
Like
Not
Null
Or
Parent
True
If a column name satisfies one of the above conditions, it must be wrapped in either square brackets or the "`" (grave accent) quotes. For example, to use a column named "Column#" in an expression, you would write either "[Column#]":
Total * [Column#]
or "`Column#`":
Total * `Column#`
If the column name is enclosed in square brackets then any ']' and '\' characters (but not any other characters) in it must be escaped by prepending them with the backslash ("\") character. If the column name is enclosed in grave accent characters then it must not contain any grave accent characters in it. For example, a column named "Column[]\" would be written:
Total * [Column[\]\\]
or
Total * `Column[]\`
User-Defined Values
<div class="subsection">User-defined values may be used within expressions to be compared with column values. String values should be enclosed within single quotation marks (and each single quotation character in a string value has to be escaped by prepending it with another single quotation character). Date values should be enclosed within pound signs (#) or single quotes (') based on the data provider. Decimals and scientific notation are permissible for numeric values. For example:
"FirstName = 'John'"
"Price <= 50.00"
"Birthdate < #1/31/82#"
For columns that contain enumeration values, cast the value to an integer data type. For example:
"EnumColumn = 5"
</div>Parsing Literal Expressions
<div class="subsection">All literal expressions must be expressed in the invariant culture locale. When DataSet parses and converts literal expressions, it always uses the invariant culture, not the current culture.
String literals are identified when there are single quotes surrounding the value. For example:
'John'
Boolean literals are true and false; they are not quoted in expressions.
Integer literals [+-]?[0-9]+ are treated as System.Int32, System.Int64 or System.Double. System.Double can lose precision depending on how large the number is. For example, if the number in the literal is 2147483650, DataSet will first attempt to parse the number as an Int32. This will not succeed because the number is too large. In this case DataSet will parse the number as an Int64, which will succeed. If the literal was a number larger than the maximum value of an Int64, DataSet will parse the literal using Double.
Real literals using scientific notation, such as 4.42372E-30, are parsed using System.Double.
Real literals without scientific notation, but with a decimal point, are treated as System.Decimal. If the number exceeds the maximum or minimum values supported by System.Decimal, then it is parsed as a System.Double. For example:
142526.144524 will be converted to a Decimal.
345262.78036719560925667 will be treated as a Double.
</div>Operators
<div class="subsection">Concatenation is allowed using Boolean AND, OR, and NOT operators. You can use parentheses to group clauses and force precedence. The AND operator has precedence over other operators. For example:
(LastName = 'Smith' OR LastName = 'Jones') AND FirstName = 'John'
When you create comparison expressions, the following operators are allowed:
<
>
<=
>=
<>
=
IN
LIKE
The following arithmetic operators are also supported in expressions:
+ (addition)
- (subtraction)
* (multiplication)
/ (division)
% (modulus)
</div>String Operators
<div class="subsection">To concatenate a string, use the + character. The value of the CaseSensitive property of the DataSet class determines whether string comparisons are case-sensitive. However, you can override that value with the CaseSensitive property of the DataTable class.
</div>Wildcard Characters
<div class="subsection">Both the * and % can be used interchangeably for wildcard characters in a LIKE comparison. If the string in a LIKE clause contains a * or %, those characters should be enclosed in brackets ([]). If a bracket is in the clause, each bracket character should be enclosed in brackets (for example [[] or []]). A wildcard is allowed at the start and end of a pattern, or at the end of a pattern, or at the start of a pattern. For example:
"ItemName LIKE '*product*'"
"ItemName LIKE '*product'"
"ItemName LIKE 'product*'"
Wildcard characters are not allowed in the middle of a string. For example, 'te*xt' is not allowed.
</div>Parent/Child Relation Referencing
<div class="subsection">A parent table may be referenced in an expression by prepending the column name with Parent. For example, the Parent.Price references the parent table's column named Price.
When a child has more than one parent row, use Parent(RelationName).ColumnName. For example, the Parent(RelationName).Price references the parent table’s column named Price via the relation.
A column in a child table may be referenced in an expression by prepending the column name with Child. However, because child relationships may return multiple rows, you must include the reference to the child column in an aggregate function. For example, Sum(Child.Price) would return the sum of the column named Price in the child table.
If a table has more than one child, the syntax is: Child(RelationName). For example, if a table has two child tables named Customers and Orders, and the DataRelation object is named Customers2Orders, the reference would be as follows:
Avg(Child(Customers2Orders).Quantity)
</div>Aggregates
<div class="subsection">The following aggregate types are supported:
Sum (Sum)
Avg (Average)
Min (Minimum)
Max (Maximum)
Count (Count)
StDev (Statistical standard deviation)
Var (Statistical variance).
Aggregates are ordinarily performed along relationships. Create an aggregate expression by using one of the functions listed earlier and a child table column as detailed in Parent/Child Relation Referencing that was discussed earlier. For example:
Avg(Child.Price)
Avg(Child(Orders2Details).Price)
An aggregate can also be performed on a single table. For example, to create a summary of figures in a column named "Price":
Sum(Price)
<div class="alert">If you use a single table to create an aggregate, there would be no group-by functionality. Instead, all rows would display the same value in the column.
If a table has no rows, the aggregate functions will return null.
Data types can always be determined by examining the DataType property of a column. You can also convert data types using the Convert function, shown in the following section.
</div>Functions
<div class="subsection">The following functions are also supported:
CONVERT
<div class="caption"></div> <div class="tableSection">Description
Converts particular expression to a specified .NET Framework Type.
Syntax
Convert(expression, type)
Arguments
expression -- The expression to convert.
type -- The .NET Framework type to which the value will be converted.
Example: myDataColumn.Expression="Convert(total, 'System.Int32')"
All conversions are valid with the following exceptions: Boolean can be coerced to and from Byte, SByte, Int16, Int32, Int64, UInt16, UInt32, UInt64, String and itself only. Char can be coerced to and from Int32, UInt32, String, and itself only. DateTime can be coerced to and from String and itself only. TimeSpan can be coerced to and from String and itself only.
LEN
<div class="caption"></div> <div class="tableSection">Description
Gets the length of a string
Syntax
LEN(expression)
Arguments
expression -- The string to be evaluated.
Example: myDataColumn.Expression="Len(ItemName)"
ISNULL
<div class="caption"></div> <div class="tableSection">Description
Checks an expression and either returns the checked expression or a replacement value.
Syntax
ISNULL(expression, replacementvalue)
Arguments
expression -- The expression to check.
replacementvalue -- If expression is null, replacementvalue is returned.
Example: myDataColumn.Expression="IsNull(price, -1)"
IIF
<div class="caption"></div> <div class="tableSection">Description
Gets one of two values depending on the result of a logical expression.
Syntax
IIF(expr, truepart, falsepart)
Arguments
expr -- The expression to evaluate.
truepart -- The value to return if the expression is true.
falsepart -- The value to return if the expression is false.
Example: myDataColumn.Expression = "IIF(total>1000, 'expensive', 'dear')
TRIM
<div class="caption"></div> <div class="tableSection">Description
Removes all leading and trailing blank characters like \r, \n, \t, ' '
Syntax
TRIM(expression)
Arguments
expression -- The expression to trim.
SUBSTRING
<div class="caption"></div> <div class="tableSection">Description
Gets a sub-string of a specified length, starting at a specified point in the string.
Syntax
SUBSTRING(expression, start, length)
Arguments
expression -- The source string for the substring.
start -- Integer that specifies where the substring starts.
length -- Integer that specifies the length of the substring.
Example: myDataColumn.Expression = "SUBSTRING(phone, 7, 8)"
<div class="alert">You can reset the Expression property by assigning it a null value or empty string. If a default value is set on the expression column, all previously filled rows are assigned the default value after the Expression property is reset.
ToughMan
Participant
1490 Points
635 Posts
Re: An alternative to use "Between Operator" in "FilterExpression"
Dec 03, 2012 05:20 AM|LINK
No, it should SUPPORT, just as what I've said above.
@jmerced
How did u say with "Between"?
jmerced
Member
21 Points
171 Posts
Re: An alternative to use "Between Operator" in "FilterExpression"
Dec 03, 2012 06:43 PM|LINK
@ToughMan, you just copy a big chunck of information and its confusing to me.
ToughMan
Participant
1490 Points
635 Posts
Re: An alternative to use "Between Operator" in "FilterExpression"
Dec 04, 2012 04:46 AM|LINK
The FilterExpression property value is a format string expression (a string that is processed by the String.Format method) that uses the values in the FilterExpression collection for any substitution parameters included in the string. The filter expression syntax is the same syntax that is accepted by the RowFilter property,
http://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression.aspx
Mikesdotnett...
All-Star
154864 Points
19861 Posts
Moderator
MVP
Re: An alternative to use "Between Operator" in "FilterExpression"
Dec 04, 2012 08:42 AM|LINK
It is not supported. Frank is correct. There's nothing in the huge chunk of MSDN that you copy-pasted that supports what you claim. In fact, the error message that jmerced gets also tells you this: "the expression contains unsupported operator 'Between'.
Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter
ToughMan
Participant
1490 Points
635 Posts
Re: An alternative to use "Between Operator" in "FilterExpression"
Dec 05, 2012 04:23 AM|LINK
But how can u explain this?
If a column name contains any non-alphanumeric characters or starts with a digit or matches (case-insensitively) any of the following reserved words, it requires special handling, as described in the following paragraphs.
And
Between
Mikesdotnett...
All-Star
154864 Points
19861 Posts
Moderator
MVP
Re: An alternative to use "Between Operator" in "FilterExpression"
Dec 05, 2012 04:55 AM|LINK
That portion of the documentation explains how to manage columns names that match a reserved word. It does not say that any of the reserved words listed are supported as part of filter expressions. In fact, if you read further on in the stuff you pasted, you will find a section headed "Operators". That does actually list the supported operators. Notice that "Between" is not included among them.
Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter