Framework:Syntax of expressions

When creating an expression, the ColumnName property refers to columns of a table. If e. g. the ColumnName of one column is “UnitPrice” and of another column “Quantity”, the following expression results:

"[UnitPrice] * [Quantity]"

When creating an expression for a filter, character strings are included in simple quotation marks:

"LastName = 'Jones'"

The following characters are special characters and must – as described below – be provided with escape characters if they are to be used in the name of a column:

~

(

)

#

\

/

=

>

<

+

-

*

%

&

|

^

'

"

[

]

If the name of a column contains one of the above-referenced characters, the name must be put in brackets. If e. g. you use a column named “Column#” in an expression, write "[Column#]":

Total * [Column#]

Since brackets are special characters you must use a backslash ("\") as escape character for the bracket if the latter is part of the column’s name. A column named "Column[]" will then be written as follows:

Total * [Column[\]] (Only the second bracket must be provided with an escape character.)

User defined values

User defined values can be used in expressions and be compared with column values. Character string values must be put in simple quotation marks. Date values must be put in number characters (#). Decimal values and scientific styles of writing are admissible for numeric values. Example: "FirstName = 'John'" "Price <= 50.00" "Birthdate < #1/31/82#"

In the case of columns with enumeration values, please change the value into an integer data type. Example: "EnumColumn = 5"

Operators

A linkage with the Boolean operators AND, OR and NOT is permissible. By means of brackets you can group clauses and enforce priority. The AND operator has priority over other operators.

Example:

(LastName = 'Smith' OR LastName = 'Jones') AND FirstName = 'John'

When creating relational expressions, the following operators are admissible:

<

>

<=

>=

<>

=

IN

LIKE

The following arithmetic operators are also supported in expressions:

+ (Addition)

- (Subtraktion)

* (Multiplikation)

/ (Division)

% (Modulo)

Character String Operators

Please use the plus sign (+) to link a character string. Whether comparisons of characterstrings are case sensitive or not is determined ty the CaseSensitive property of the DataSet class. You may, however, overwrite this value by means of the CaseSensitive property of the DataTable class. Wildcard

Both * and % may be used as wildcards in a LIKE comparison. If the character string in a LIKE clause contains * or %, the characters in brackets ([]) must be included as escape characters. If a bracket is contained in the clause, the parenthesis must be provided with brackets as escape characters (e. g. [[] or []]). A wildcard may be used both at the beginning and at the end of a sample, but may also be used just at the end or just at the beginning of a sample.

Example:

"ItemName LIKE '*product*'" "ItemName LIKE '*product'" "ItemName LIKE 'product*'"

Wildcards within a character string are not allowed. 'te*xt' for example is not permissible.

Functions

The following functions are also supported:

Convert

Description	Converts the indicated expression into specified .NET framework type.
Syntax	Convert(expression, type)
Arguments	expression- the expression to be converted. type- the .NET framework type into which the value is converted.
Example	myDataColumn.Expression="Convert(total,'System.Int32')"

All conversions are valid, with the following exceptions: Boolean can only be converted from and into Byte, SByte, Int16, Int32, Int64, UInt16, UInt32, UInt64, String and into itself. Char may be converted from and into Int32, UInt32, String and into itself. DateTime can only be converted from and into String and into itself. TimeSpan can only be converted from and into String and into itself.

LEN

Description	Requests the length of a character string.
Syntax	LEN(expression)
Arguments	expression- the character string to be analysed.
Example	myDataColumn.Expression="Len(ItemName)"

ISNULL

Description	Examines an expression and returns either the examined expression or a replacement value.
Syntax	ISNULL(expression, replacementvalue)
Arguments	expression- the expression to be examined. replacementvalue- if the expression is ZERO (Nothing), replacementvalue is returned.
Example	myDataColumn.Expression="IsNull(price, -1)"

IIF

Description	Requests – according to the result of a logic expression – one of two values.
Syntax	IIF(expr, truepart, falsepart)
Arguments	expr- the expression to be analysed. truepart- the value which is returned if the expression is True. falsepart- the value which is returned if the expression is False.
Example	myDataColumn.Expression = "IIF(total>1000, 'expensive', 'dear')

TRIM

Description	removes all previous and subsequent spaces, e. g.\r,\n,\t and ' '.
Syntax	TRIM(expression)
Arguments	expression- the expression that is to be shortened.

SUBSTRING

Description	Requests a partial character string of a specified length, starting at a specified point in the character string.
Syntax	SUBSTRING(expression, start, length)
Arguments	expression- the source character string for the partial character string. start- an integer which indicates the start of the partial character string. length- an integer which indicates the length of the partial character string.
Example	myDataColumn.Expression = "SUBSTRING(phone, 7, 8)"

Hint  You can reset the expression property by assigning a ZERO-value or an empty character string to it. If a standard value is determined for the expression column, all lines already completed are given the default value after the reset of the expression property.