Pull data by date from MS Access using C# no records returned

I am playing with MS Access database and a WPF app. I generated a bunch of data and the C# DateTime values of, say, 6/27/2010, inserted in the database turned out as 6/27/2010 12:00:00. Then the funny thing happened, I was trying to pull the following query:

using(OleDbCommand cmd = "SELECT * FROM Table1 WHERE MyDate = ?")
{
  cmd.Parameters.Add("@val1", OleDbType.Date).Value = myDate;
  try
  {
    conn.Open();
    ...
  }
}

And it just did not pull any rows. I tried running it just in Access, and it worked fine in the query designer, I was getting rows back. This puzzled me until I read this KB. I then ran the following query in MS Access:

SELECT DISTINCT MyDate, CDbl(MyDate) FROM Table1 ORDER BY 1

And I got some interesting results:

Expr1000         Expr1001
6/27/2010         40356
6/27/2010 12:00:00 40356.5

So if a datae is stored as just 6/27/2010, it's a midnight of the beginning of the day. But if it has 12:00:00 time suffix, it is a noon of that day, so it's not the same as midnight and my query above would not pull it. But the funny thing is is that when I created data, I only specified the date part and nothing on time, so something is happening between C# and MS Access that C# sends 12:00:00 as midnight and MS Access understands it as noon. I even tried the following to no avail:

cmd.Parameters.Add("@val1", OleDbType.DBDate).Value = myDate;
cmd.Parameters.Add("@val1", OleDbType.Date).Value = myDate.Date;
cmd.Parameters.Add("@val1", OleDbType.Date).Value = new DateTime(myDate.Year, myDate.Month, myDate.Day);

Same thing, no results from the command, while the query in MS Access worked just fine.

A workaround:

using(OleDbCommand cmd = "SELECT * FROM Table1 WHERE MyDate BETWEEN ? AND ?")
{
  cmd.Parameters.Add("@val1", OleDbType.Date).Value = myDate.Date;
  cmd.Parameters.Add("@val2", OleDbType.Date).Value = myDate.Date.AddDays(1);
  try
  {
    conn.Open();
    ...
  }
}

And that works.

Extract data from Excel into binary text file

In an Excel document (.xlsx, for .xls use

connStr

as the connection string), name one worksheet "Questions" and another "Answers" (no quotes in both cases). All columns are in Excel General format; the allowable format is noted in parentheses: Questions worksheet: QuestionID (Number), QuestionText (Text) Answers worksheet: AnswerID (Number), AnswerText (Text), QuestionID (Number), Correct (Text, but the only acceptable values are TRUE and FALSE).

var fileName = string.Format("{0}\\dbase.xlsx", Directory.GetCurrentDirectory());
var connStr = string.Format(
  "Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;", fileName);
var connectionString = string.Format(
  "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;", fileName);
var adapter1 = new OleDbDataAdapter("SELECT * FROM [Questions$]", connectionString);
var adapter2 = new OleDbDataAdapter("SELECT * FROM [Answers$]", connectionString);

var ds = new DataSet();

adapter1.Fill(ds, "Questions");
adapter2.Fill(ds, "Answers");

var answersTable = ds.Tables["Answers"].AsEnumerable();
var questionsTable = ds.Tables["Questions"].AsEnumerable();

List answers = answersTable.Where(
  x => x.Field("AnswerID") > 0.0).Select(x =>
     new TestQuestionAnswer
     {
         AnswerID = Convert.ToInt32(x.Field("AnswerID")),
         AnswerText = x.Field("AnswerText"),
         AnswerCorrect = x.Field("Correct"),
         QuestionID = Convert.ToInt32(x.Field("QuestionID"))
     }).ToList();

List questions = questionsTable.Where(
  x => x.Field("QuestionID") > 0.0).Select(x =>
    new TestQuestion
    {
        QuestionID = Convert.ToInt32(x.Field("QuestionID")),
        QuestionText = x.Field("QuestionText")
    }).ToList();


foreach (TestQuestion tq in questions)
{
    List a = answers.Where(x => x.QuestionID == tq.QuestionID).ToList();
    tq.Answers = a;
}

using (FileStream fs = new FileStream("data.dat", FileMode.Create))
{
    using (var writer = new BinaryWriter(fs))
    {
        writer.WriteList(questions);
    }
}
//fix for Syntax Highlighter plugin glitch:
//