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.
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.