-----------------------------------------------------------
New Message on BDOTNET
-----------------------------------------------------------
From: Yesu
Message 6 in Discussion
Hi
Raju,
I
don't think we can use distinct in
dataset/dataview/datatable
please check the url, if u have MSDN in ur machine
ms-help://MS.VSCC/MS.MSDNVS/cpref/html/frlrfsystemdatadatacolumnclassexpressiontopic.htm
or
just check the doucument
Gets or sets the expresssion used to filter rows, calculate the values in a
column, or create an aggregate column.[Visual Basic]
Public Property Expression As String[C#]
public string Expression {get; set;}[C++]
public: __property String* get_Expression();
public: __property void set_Expression(String*);[JScript]
public function get Expression() : String;
public function set Expression(String);
Property Value
An expression to calculate the value of a column, or create an aggregate
column. The return type of an expression is determined by the DataType
of the column.
Exceptions
Exception Type
Condition
ArgumentException
The AutoIncrement
or Unique
property is set to true.
FormatException
When using the CONVERT function, the expression
evaluates to a string, but the string doesn't contain a representation
that can be converted to the type parameter.
InvalidCastException
When using the CONVERT function, the requested cast is
not possible. See the Conversion function below for details on possible
casts.
ArgumentOutOfRangeException
When using the SUBSTRING function, the start argument is
out of range.
-Or-
When using the SUBSTRING function, the length argument is out of
range.
Exception
When using the LEN function or the TRIM function, the
expression does not evaluate to a string. This includes expressions that
evaluate to Char.
Remarks
One use of the Expression property is to create calculated columns.
For example, to calculate a tax value, the unit price is multiplied by a tax
rate of a given region. Since tax rates vary from region to region, it would
be impossible to put a single tax rate in a column; instead, the value is
calculated using the Expression property, as shown in the Visual Basic
code below:
DataSet1.Tables("Products").Columns("tax").Expression = "UnitPrice *
0.086"
A second use is to create an aggregate column. Similar to a calculated
value, an aggregate performs an operation based on the entire set of rows in
the DataTable.
A simple example is to count the number of rows returned in the set, which is
the method you would use to count the number of transactions completed by a
particular salesperson, as shown in this Visual Basic
code:DataSet1.Tables("Orders").Columns("OrderCount").Expression = "Count(OrderID)"
EXPRESSION SYNTAX
When creating an expression, use the ColumnName
property to refer to columns. For example, if the ColumnName for one
column is "UnitPrice", and another "Quantity", the expression would be:
"UnitPrice * Quantity"
When creating an expression for a filter, enclose strings with single
quotes:
"LastName = 'Jones'"
The following characters are special characters and must be escaped, as
explained below, if they are to be used in a column name:
\n (newline)
\t (tab)
\r (carriage return)
~
(
)
#
\
/
=
>
<
+
-
*
%
&
|
^
'
"
[
]
If a column name contains one of the above characters, the name must be
wrapped in brackets. For example to use a column named "Column#" in an
expression, you would write "[Column#]":
Total * [Column#]
Because brackets are special characters, you must use a slash ("\") to
escape the bracket, if it is part of a column name. For example, a column
named "Column[]" would be written:
Total * [Column[\]]
(Only the second bracket must be escaped.)
USER-DEFINED VALUES
User-defined values may be used within expressions to be compared against
column values. String values should be enclosed within single quotes. Date
values should be enclosed within pound signs (#). 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"
OPERATORS
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 creating comparison expressions, the following operators are
allowed:
<
>
<=
>=
<>
=
IN
LIKE
The following arithmetic operators are also supported in expressions:
+ (addition)
- (subtraction)
* (multiplication)
/ (division)
% (modulus)
STRING OPERATORS
To concatenate a string, use the + character. Whether string comparisons
are case-sensitive or not is determined by the value of the DataSet
class's CaseSensitive
property. However, you can override that value with the DataTable
class's CaseSensitive
property.
WILDCARD CHARACTERS
Both the * and % can be used interchangeably for wildcards in a LIKE
comparison. If the string in a LIKE clause contains a * or %, those characters
should be escaped in brackets ([]). If a bracket is in the clause, the bracket
characters should be escaped in brackets (for example [[] or []]). A wildcard
is allowed at the beginning and end of a pattern, or at the end of a pattern,
or at the beginning of a pattern. For example:
"ItemName LIKE '*product*'"
"ItemName LIKE '*product'"
"ItemName LIKE 'product*'"
Wildcards are not allowed in the middle of a string. For example, 'te*xt'
is not allowed.
PARENT/CHILD RELATION REFERENCING
A column in a child table may be referenced in an expresion by prepending
the column name with "Child." For example, "Child.Price" would reference 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 Employee and Titles, and the
DataRelation
objects are named "Publishers2Employee: and "Publishers2Titles," the reference
would be:
Child(Publishers2Employee).fname
Child(Publishers2Titles).title
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."
AGGREGATES
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 usually performed along relationships. Create an aggregate
expression by using one of the functions listed above and a child table column
as detailed in PARENT/CHILD RELATION REFERENCING above. 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)
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.
If a table has no rows, the aggregate functions will return a null
reference (Nothing in Visual Basic).
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 below.
FUNCTIONS
The following functions are also supported:
CONVERT
Description
Converts given 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
Description
Gets the length of a string
Syntax
LEN(expression)
Arguments
expression-- The string to be
evaluated.
Example: myDataColumn.Expression="Len(ItemName)"
ISNULL
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 a null reference
(Nothing), replacementvalue is
returned.
Example: myDataColumn.Expression="IsNull(price, -1)"
IIF
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
Description
Removes all leading and trailing blank characters
like\r,\n,\t, ' '
Syntax
TRIM(expression)
Arguments
expression-- The expression to
trim.
SUBSTRING
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 begins.
length-- Integer that specifies the length of the
substring.
Example: myDataColumn.Expression = "SUBSTRING(phone, 7, 8)"
Note 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.
-----------------------------------------------------------
To stop getting this e-mail, or change how often it arrives, go to your E-mail
Settings.
http://groups.msn.com/bdotnet/_emailsettings.msnw
Need help? If you've forgotten your password, please go to Passport Member Services.
http://groups.msn.com/_passportredir.msnw?ppmprop=help
For other questions or feedback, go to our Contact Us page.
http://groups.msn.com/contact
If you do not want to receive future e-mail from this MSN group, or if you received
this message by mistake, please click the "Remove" link below. On the pre-addressed
e-mail message that opens, simply click "Send". Your e-mail address will be deleted
from this group's mailing list.
mailto:[EMAIL PROTECTED]
