none
How to use IF in filterExpression RRS feed

  • Question

  • I have a column in datatable that contains multiple type of data (as a string). Column datatype is string.

    . It contains date with format 'yyyy-MM-dd'

    . It contains date with format 'MM/dd/yyyy'

    . It contains 'today'

    . It contains 'not available.

    Now, I've to check the data in specified range, and show it in custom format, e.g., if (col equals 'today' OR  (Convert(col, 'System.DateTime) equals DateTime.Now) show record.

    Or, if (col == 'not available' AND CustomDate == DateRangeFinish) show record

    Now, problem is I've to use IF condition in my datatable.select(filterExpression). Can anybody give me some hint how to use IF (as I didn't find any example over internet)



    beginner
    Friday, June 18, 2010 11:45 AM

Answers

  • A general strategy would be to nest IIf functions.

    An else if is translated into an IIf that goes into the falsepart of the IIf.

    The final falsepart should be the keyword false.

    For example, if you have two conditions:

    IIf(conda, resulta, IIf(condb, resultb, false))

    For example, if you have three conditions:

    IIf(conda, resulta, IIf(condb, resultb, IIf(condc, resultc, false)))

    Also note that since some of your data ("today", etc.) will not convert to datetime, using CONVERT will be a problem because it will fail with an exception.

    You haven't really explained what CustomDate, DateRangeFinish are.  Are they all columns?  Or variable in your program?  What does the data look like (you explained the possibilites for col, but not the other two)?  Because I couldn't tell your requirements, I didn't try to write up working code for this.

    Also, please write back if you are open to using LINQ to build your DataView instead of a filter expression.  This might be an easier way to accomplish this task.  This will only be an option on Visual Studio 2008 and up.

     

     

    Friday, June 18, 2010 11:58 PM
  • How do you populate the data table? and how often? If the user doesn't restart the app, is "today" still vaild? I rather have that column of type datetime instead of string.

    Now to answer your question, you don't need an IF statement. The select function uses column expression which is so similar to the where clause syntax in TSQL but there are differences.

     

    For example

    e.g., if (col equals 'today' OR  (Convert(col, 'System.DateTime) equals DateTime.Now) show record

    assuming you are using c#, you can use the following or use string.format to have cleaner syntax

    myTable.Select("myColumn = 'today' OR myColumn = '" + DateTime.Today.ToString("yyyy-mm-dd") + "' OR myColumn = '" + DateTime.Today.ToString("d") + "'");

     

    Sunday, June 20, 2010 4:04 AM

All replies

  • A general strategy would be to nest IIf functions.

    An else if is translated into an IIf that goes into the falsepart of the IIf.

    The final falsepart should be the keyword false.

    For example, if you have two conditions:

    IIf(conda, resulta, IIf(condb, resultb, false))

    For example, if you have three conditions:

    IIf(conda, resulta, IIf(condb, resultb, IIf(condc, resultc, false)))

    Also note that since some of your data ("today", etc.) will not convert to datetime, using CONVERT will be a problem because it will fail with an exception.

    You haven't really explained what CustomDate, DateRangeFinish are.  Are they all columns?  Or variable in your program?  What does the data look like (you explained the possibilites for col, but not the other two)?  Because I couldn't tell your requirements, I didn't try to write up working code for this.

    Also, please write back if you are open to using LINQ to build your DataView instead of a filter expression.  This might be an easier way to accomplish this task.  This will only be an option on Visual Studio 2008 and up.

     

     

    Friday, June 18, 2010 11:58 PM
  • How do you populate the data table? and how often? If the user doesn't restart the app, is "today" still vaild? I rather have that column of type datetime instead of string.

    Now to answer your question, you don't need an IF statement. The select function uses column expression which is so similar to the where clause syntax in TSQL but there are differences.

     

    For example

    e.g., if (col equals 'today' OR  (Convert(col, 'System.DateTime) equals DateTime.Now) show record

    assuming you are using c#, you can use the following or use string.format to have cleaner syntax

    myTable.Select("myColumn = 'today' OR myColumn = '" + DateTime.Today.ToString("yyyy-mm-dd") + "' OR myColumn = '" + DateTime.Today.ToString("d") + "'");

     

    Sunday, June 20, 2010 4:04 AM