How may I use the IN operator when using a RowFilter on a DataView?

  • Thread starter Thread starter mark4asp
  • Start date Start date
M

mark4asp

How may I use the IN operator when using a RowFilter on a DataView? -
Such that I can query whether a value is in a column from the Table
associated with the DataView.

Suppose I have the following table

myTable = new DataTable();
myTable.Columns.Add("ActivityID", typeof(int));
myTable.Columns.Add("MandateID", typeof(int));
myTable.Columns.Add("ConsultantID", typeof(int));
myTable.Columns.Add("ConsultantName", typeof(string));
myTable.Columns.Add("ManagerID", typeof(int));
myTable.Columns.Add("ManagerName", typeof(string));
myTable.Columns.Add("ACIS_IDs", typeof(string));
myTable.Columns.Add("AssetClassList", typeof(string));
myTable.Columns.Add("EntryDate", typeof(DateTime));


The 7th column (ACIS_IDs) currently has values like this ",
2,16,24,". i.e. A list a integers separated by a comma and starting
and ending with a comma.

The c# method to build the relevant part of the RowFiler search looks
like this:

public static string AssetClassCodeToSearchFilter(int code)
{
if (code == 0)
return "";
else
return "ACIS_IDs LIKE '%," + code.ToString() + ",%' AND ";
}

Q: How can I change this to use the IN operator instead of the LIKE
operator?

For instance so that the last line of the method is:

return code.ToString() + " IN ACIS_IDs AND "

When the 7th column of the table (ACIS_IDs) has values like this
"(2,16,24)".

I get an error message:

Syntax error: The items following the IN keyword must be separated by
commas and be enclosed in parentheses.

An example final filter looks something like this:

"16 IN ACIS_IDs AND EntryDate >= '2002-01-01' AND EntryDate <=
'2007-06-30'"

Will this be impossible to do? I suppose it's telling me that it wants
a literal value rather than a column name for ACIS_IDs.
 
¤ How may I use the IN operator when using a RowFilter on a DataView? -
¤ Such that I can query whether a value is in a column from the Table
¤ associated with the DataView.
¤
¤ Suppose I have the following table
¤
¤ myTable = new DataTable();
¤ myTable.Columns.Add("ActivityID", typeof(int));
¤ myTable.Columns.Add("MandateID", typeof(int));
¤ myTable.Columns.Add("ConsultantID", typeof(int));
¤ myTable.Columns.Add("ConsultantName", typeof(string));
¤ myTable.Columns.Add("ManagerID", typeof(int));
¤ myTable.Columns.Add("ManagerName", typeof(string));
¤ myTable.Columns.Add("ACIS_IDs", typeof(string));
¤ myTable.Columns.Add("AssetClassList", typeof(string));
¤ myTable.Columns.Add("EntryDate", typeof(DateTime));
¤
¤
¤ The 7th column (ACIS_IDs) currently has values like this ",
¤ 2,16,24,". i.e. A list a integers separated by a comma and starting
¤ and ending with a comma.
¤
¤ The c# method to build the relevant part of the RowFiler search looks
¤ like this:
¤
¤ public static string AssetClassCodeToSearchFilter(int code)
¤ {
¤ if (code == 0)
¤ return "";
¤ else
¤ return "ACIS_IDs LIKE '%," + code.ToString() + ",%' AND ";
¤ }
¤
¤ Q: How can I change this to use the IN operator instead of the LIKE
¤ operator?
¤
¤ For instance so that the last line of the method is:
¤
¤ return code.ToString() + " IN ACIS_IDs AND "
¤
¤ When the 7th column of the table (ACIS_IDs) has values like this
¤ "(2,16,24)".
¤
¤ I get an error message:
¤
¤ Syntax error: The items following the IN keyword must be separated by
¤ commas and be enclosed in parentheses.
¤
¤ An example final filter looks something like this:
¤
¤ "16 IN ACIS_IDs AND EntryDate >= '2002-01-01' AND EntryDate <=
¤ '2007-06-30'"
¤
¤ Will this be impossible to do? I suppose it's telling me that it wants
¤ a literal value rather than a column name for ACIS_IDs.

The correct syntax would be:

ColName IN (x,y,z)

where x, y and z are your literal values.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Back
Top