I'm using OLEDB to query an excel file using date time picker but I keep getting a Data type mismatch in cireria expression error.
The format in excel for the date is "6/08/2012 10:00"
DateTime time = dateTimePicker1.Value;
MyCommand = new OleDbDataAdapter("select * from [CR$] where ([Req Start Date] >='" + time + "')", MyConnection);
DtSet = new System.Data.DataSet();
MyCommand.Fill(DtSet);
bindingSource1 = new BindingSource();
bindingSource1.DataSource = DtSet;
bindingSource1.DataMember = DtSet.Tables[0].TableName;
dataGridView1.DataSource = bindingSource1;
MyConnection.Close();
You are passing time to the query as a string, so you could ToString() it to make it work:
MyCommand = new OleDbDataAdapter("select * from [CR$] where ([Req Start Date] >='" + time.ToString("%M/dd/yyyy HH:mm") + "')", MyConnection);
But you really should make it a parameter. Plus, it's safer that way.
using (OleDbConnection connection = new OleDbConnection(yourConnectionString))
{
OleDbDataAdapter adapter = new OleDbDataAdapter("select * from [CR$] where [Req Start Date] >= ?", connection);
adapter.SelectCommand.Parameters.Add("@p1", OleDbType.Date);
adapter.SelectCommand.Parameters["@p1"].Value = time;
try
{
connection.Open();
adapter.Fill(DtSet);
}
catch (Exception ex)
{
//handle error
}
}
Find out more: OleDbParameter Class