RSS Feed

Plugin Developer Discussion

I'm working on a plugin that will report development activities for a user-selected date range.  The database on my development box is MS Access.  I cannot seem to get the WHERE clause to work properly.  I've tried the following:

CSelectQuery query = api.Database.NewSelectQuery("Bug");
query.AddInnerJoin("TimeInterval", "Bug.ixBug = TimeInterval.ixBug");
query.AddWhere("TimeInterval.dtStart <= '" + strStartDate + "'");
query.AddWhere("TimeInterval.dtEnd <= '" + strEndDate + "'");

I've also tried surrounding the date with #, like this:
query.AddWhere("TimeInterval.dtEnd <= #" + strEndDate + "#");

Neither of these work - they both produce exceptions. If I do this:
query.AddWhere("TimeInterval.dtStart >= " + strStartDate);
query.AddWhere("TimeInterval.dtEnd <= " + strEndDate);

there is no exception, but the results are incorrect - it returns an empty set when I know for sure I have cases that fall in this interval.  What is the correct way to programatically select from a date range like this?  I've not been able to find an example that shows how to do this.

Thanks!

Hi Shawn,

The correct way to do this is to use a query parameter:

query.AddWhere("TimeInterval.dtStart >= @dtStart");
query.AddWhere("TimeInterval.dtEnd <= @dtEnd");
query.SetParamDate("dtStart", dtStart);
query.SetParamDate("dtEnd", dtEnd);

Thanks!  That worked marvelously.