C# Datetimepicker -Date search is not same as return result

I have a problem with my stand alone C#.net application when comes to datetimepicker. It tooks me weeks and not able to get it solve. I hope you can give me a hand.

I have a [create booking], [Search Booking], and [DB] using ms access.

1) Create booking date using the datetimepicker. format= short cmd.CommandText = "insert into booking(cname, bdate, btime, ccontact, sname) Values('" + txt_cname.Text + "','" + **dtp_bdate.Value.Date** + "','" + dtp_btime.Value + "','" + txt_ccontact.Text + "','" + txt_sname.Text + "')";

2) Data store in DB is correct. Example: 01/10/2011 (it is 1st of October 2011)

3) Search Booking date using the datetimepicker. 开发者_开发技巧format= short

string strSql = String.Format("SELECT * FROM booking WHERE bdate = #{0}#", dtp_search.Value.Date);

When I try to search as in 01/10/2011. It doesn't return the result for me. But when I try to search as in 10/1/2011, it then appeared the result.

I checked on the DB and confirm the date format is saved as 01/10/2011. But I don't understand why and how this weird thing happen.

Can any kind man give me a hand?

Truly appreciated in advance.

Thank you, Gary Yee

Looks like Access allows fixed format dates in sql queries (MM/DD/YYYY). And the date is displayed formatted according to the current culture of the database browser (so you get DD/MM/YYYY). Just use (MM/DD/YYYY) in queries.

"IF" your data is correct, try using parameters instead:

  DataTable dt = new DataTable();

  using (var cn = new OleDbConnection(strConn))
  {
    cn.Open();
    using (var cmd = new OleDbCommand("SELECT * FROM booking WHERE bdate = @bdate", cn))
    {
      cmd.Parameters.AddWithValue("@bdate", dtp_bdate.Value.Date);
      using (OleDbDataAdapter oda = new OleDbDataAdapter(cmd))
        oda.Fill(dt);
    }
  }

Otherwise, try debugging your statement by trying >= or <= for your "WHERE bdate = @bdate" statement to see if that changes the results.