An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in Select query C#

Question

I have a table in a MS Access database (.mdb) that has a bunch of columns. I ran the following SQL query and it returned everything in the table to my C# application (Visual Studio 2012 Express).

Select OperatorNo, DateTime, FuelIssued, PreviousCredit, Term 
From TransAll 
Where Term = 'Invalid'

Now I am trying to add more criteria to the where clause. I am trying to do a date range on the table name DateTime to be between a specific range.

Here is the SQL select statement I created via code:

Select OperatorNo, DateTime, FuelIssued, PreviousCredit, Term 
From TransAll  
Where Term = 'Invalid' 
  And DateTime Between '2018/6/24 00:00:00' and '2018/6/30 23:59:59'

I imported the access database into SQL Server express so I could manually figure out the SQL select command then fixed the code. The code is working in the SQL Server database. But in the C# code I am now I am getting an error:

Unhandled exception of type system.data.oledb.oledbexception occurred in system.data.dll. Additional information: Data type mismatch in criteria expression.

Here is my code in question:

string tmpString = strSelectQuery + strWhereClause;
private DataSet myDS = new DataSet();
oledbDataAdapter MyAdapter = new oledbDataAdapter(tmpString, MyConnection);
MyAdapter.Fill(myDS); //this is where the error happens when I run my code
dgvResults.AutoGenerateColumns = true;
dgvResults.AutoSize = true;
dgvResults.DataSource = myDS;
dgvResults.DataMember = "Table";
dgvResults.ReadOnly = true;

if (dgvResults.RowCount == 0)
     MessageBox.Show("No Data for that Date Range/Week");

Any help would be greatly appreciated.

Answer 1

To make your SQL work, use Access SQL syntax for the string expressions for the date values:

Select 
    OperatorNo, DateTime, FuelIssued, PreviousCredit, Term 
From 
    TransAll  
Where 
    Term = 'Invalid' 
    And 
    DateTime Between #2018/6/24 00:00:00# And #2018/6/30 23:59:59#

But, as already noted, use parameters. Much easier to debug.