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.

On Tue, 10 Jul 2007 10:02:03 -0700, mark4asp  wrote:

 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)