I am having a problem with C# and MS Access, where I expect the following call to return one record:
c = Shift.Get(ProfileID, Start, null, null, null, null, null, null);
Where Start is "1/7/2015 3:30:00 PM" and ProfileID is "******16732" and the method is:
public static ObservableCollection<Shift> Get(string profileID, DateTime? start,
DateTime? stop, string fullName, bool? closed, bool? archived = null,
Database db = null, string sort="ASC")
{
OleDbCommand cmd = new OleDbCommand(
"SELECT profiles.profile_id, profiles.full_name, shifts.start, " +
"shifts.stop, shifts.start_log, shifts.stop_log, shifts.notes, " +
"shifts.closed, shifts.archived FROM shifts, profiles WHERE " +
(profileID != null ? "(shifts.profile_id=@profile_id) AND " : "") +
(start.HasValue ? "(shifts.start>=@start) AND " : "") +
(stop.HasValue ? "(shifts.stop<=@stop) AND " : "") +
(fullName != null ? "profiles.full_name=@full_name AND " : "") +
(closed.HasValue ? "shifts.closed=@closed AND " : "") +
(archived.HasValue ? "shifts.archived=@archived AND " : "") +
"(shifts.profile_id=profiles.profile_id) " +
"ORDER BY shifts.start " + sort
);
if (profileID != null)
cmd.Parameters.AddWithValue("@profile_id", profileID);
if (start.HasValue)
cmd.Parameters.AddWithValue("@start", start.Value.ToString());
if (stop.HasValue)
cmd.Parameters.AddWithValue("@stop", stop.Value.ToString());
if (fullName != null)
cmd.Parameters.AddWithValue("@full_name", fullName);
if (closed.HasValue)
cmd.Parameters.AddWithValue("@closed", closed.Value);
if (archived.HasValue)
cmd.Parameters.AddWithValue("@archived", archived.Value);
....
}
Given the following shifts table:
profile_id start stop start_log stop_log notes closed archived
******45544 1/7/2015 3:30:00 PM 1/2/2015 11:30:00 PM 1/7/2015 3:06:02 PM 1/2/2015 11:32:40 PM "" Yes No
******12956 1/7/2015 3:30:00 PM 1/2/2015 9:00:00 PM 1/7/2015 3:08:10 PM 1/2/2015 9:15:29 PM "" Yes No
******17392 1/7/2015 2:00:00 PM 1/2/2015 11:30:00 PM 1/7/2015 1:46:07 PM 1/2/2015 11:33:09 PM "" Yes No
******16732 1/7/2015 3:30:00 PM 1/2/2015 6:30:00 PM 1/7/2015 3:08:38 PM 1/2/2015 6:35:03 PM "" Yes No
******15503 1/7/2015 2:00:00 PM 1/2/2015 10:00:00 PM 1/7/2015 1:46:43 PM 1/2/2015 10:01:24 PM "" Yes No
******14536 1/7/2015 3:30:00 PM 1/2/2015 11:30:00 PM 1/7/2015 3:04:12 PM 1/2/2015 11:35:19 PM "" Yes No
However, I get a return of no record, and no errors. That is surprising, because I do have a shifts.start >= @start
in the WHERE clause of SQL Statement and the data exists.
Note, the ProfileID is obfuscated, because it is sensitive, and the start date started after the stop date, which is obviously wrong, but that is the test data and should have no bearing on the result. There is a default database connection if db is not supplied.
I had to do some editing and hopefully that I didn't mis-type anywhere.
Any clues?