question row filter (more of sql query question)

Discussion in 'ASP .Net' started by =?Utf-8?B?YW5kcmV3MDA3?=, Oct 5, 2005.

  1. I have a question about dataset rowfilter.
    I have a list of event table in a database. each event has start-date and
    end-date column. I grab these from db and save to dataset. And then I have
    to do rowfilter of dataset to filter based on user's input.

    Users can select the following as dropdown for filtering.
    - last 30 days
    - last 7 days
    - today
    - all pasts

    for example for past 30 days filtering I build the following rowfiltering.

    string dFrom = today.AddDays(-30);
    string dTo = today;
    sFilter += " ([event-start-date] <= #" + dFrom.ToShortDateString() + "# AND
    [event-end-date] >= #" + dTo.ToShortDateString() + "#) OR " ;
    sFilter += " ([event-start-date] <= #" + dFrom.ToShortDateString() + "# AND
    [event-end-date] >= #"+ dFrom.ToShortDateString() +"# AND [event-end-date]
    <= #" + dTo.ToShortDateString() + "#) OR " ;
    sFilter += " ([event-start-date] >= #" + dFrom.ToShortDateString() + "# AND
    [event-start-date] <= #"+ dTo.ToShortDateString() + "# AND [event-end-date]
    >= #" + dTo.ToShortDateString() + "#) OR " ;

    sFilter += " ([event-start-date] >= #" + dFrom.ToShortDateString() + "# AND
    [event-end-date] <= #" + dTo.ToShortDateString() + "#) " ;

    myDataView.RowFilter = sFilter;

    But the above didn't return the correct resultset.

    since each event has a start/end date and not easy to filter around to get
    the correct result set. Please help!
    =?Utf-8?B?YW5kcmV3MDA3?=, Oct 5, 2005
    #1
    1. Advertising

  2. =?Utf-8?B?YW5kcmV3MDA3?=

    Guest

    hi

    why are you not doing this using SQL on the database?
    , Oct 6, 2005
    #2
    1. Advertising

  3. =?Utf-8?B?YW5kcmV3MDA3?=

    Scott Allen Guest

    I think you have the following 4 cases to include, where x is a day
    and e is an event.

    xxxxxxxxx
    1) eeeee
    2) eeeee
    3) eeeee
    4) eeeeeeeeeeeee


    You could include these events with three OR'ed conditions:

    1) event_end_date is between FromDate and ToDate

    OR

    2) event_start_date is between FromDate and ToDate

    OR

    3) ToDate is between event_start_date and event_end_date

    I think your query is too restrictive.

    --
    Scott
    http://www.OdeToCode.com/blogs/scott/

    On Wed, 5 Oct 2005 15:26:13 -0700, andrew007 <> wrote:

    >I have a question about dataset rowfilter.
    >I have a list of event table in a database. each event has start-date and
    >end-date column. I grab these from db and save to dataset. And then I have
    >to do rowfilter of dataset to filter based on user's input.
    >
    >Users can select the following as dropdown for filtering.
    >- last 30 days
    >- last 7 days
    >- today
    >- all pasts
    >
    >for example for past 30 days filtering I build the following rowfiltering.
    >
    >string dFrom = today.AddDays(-30);
    >string dTo = today;
    >sFilter += " ([event-start-date] <= #" + dFrom.ToShortDateString() + "# AND
    >[event-end-date] >= #" + dTo.ToShortDateString() + "#) OR " ;
    >sFilter += " ([event-start-date] <= #" + dFrom.ToShortDateString() + "# AND
    >[event-end-date] >= #"+ dFrom.ToShortDateString() +"# AND [event-end-date]
    ><= #" + dTo.ToShortDateString() + "#) OR " ;
    >sFilter += " ([event-start-date] >= #" + dFrom.ToShortDateString() + "# AND
    >[event-start-date] <= #"+ dTo.ToShortDateString() + "# AND [event-end-date]
    >>= #" + dTo.ToShortDateString() + "#) OR " ;

    >sFilter += " ([event-start-date] >= #" + dFrom.ToShortDateString() + "# AND
    >[event-end-date] <= #" + dTo.ToShortDateString() + "#) " ;
    >
    >myDataView.RowFilter = sFilter;
    >
    >But the above didn't return the correct resultset.
    >
    >since each event has a start/end date and not easy to filter around to get
    >the correct result set. Please help!
    Scott Allen, Oct 6, 2005
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Anonymous
    Replies:
    0
    Views:
    1,427
    Anonymous
    Oct 13, 2005
  2. Michael
    Replies:
    4
    Views:
    392
    Matt Hammond
    Jun 26, 2006
  3. troutbum

    Cannot Find Row Index after using row filter

    troutbum, Jan 22, 2004, in forum: ASP .Net Datagrid Control
    Replies:
    10
    Views:
    454
    Andre
    Feb 19, 2004
  4. D
    Replies:
    0
    Views:
    192
  5. Robert Klemme

    With a Ruby Yell: more, more more!

    Robert Klemme, Sep 28, 2005, in forum: Ruby
    Replies:
    5
    Views:
    195
    Jeff Wood
    Sep 29, 2005
Loading...

Share This Page