Random programming things I'd want to remember

Friday, June 27, 2014

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.

Saturday, June 14, 2014

XAML: change an element's property based on another element's property

Another one for the power and beauty of XAML. This one allows one to change an element's state based on another element's state. Example:

<CheckBox x:Name="chb1" Content="Check/Uncheck this" />
<Button IsEnabled="{Binding ElementName=chb1, Path=IsChecked}" Content="Click here" />