Querying dBase file in C#

Question

My problem specifically is I can't filter by a Date field.

Here is my code:

string connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\Tools;Extended Properties=dBASE IV;User ID=Admin;Password=;";

using (OleDbConnection con = new OleDbConnection(connstr))
{
    string sql = "select USERNUMBER,FIRSTNAME,LASTNAME, LASTACCESS from EMP WHERE TERMINATED=\"Y\" AND [LASTACCESS]<\"2001/10/20\"";
    OleDbCommand cmd = new OleDbCommand(sql, con);

    con.Open();
    OleDbDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);

It works fine if I omit the date field. I just can't figure out what the format is. If I look at the table with a dbf viewer utility the LASTACCESS field is in dd.mm.yyyy (with the periods as separators, but I don't know if that is just a behavior of the utility).

If I omit the forward slashes from the date field, it works but returns zero records (even though I know there are).

Answer 1

Try this and forget date time hell

string sql = "select USERNUMBER,FIRSTNAME,LASTNAME, LASTACCESS from EMP WHERE TERMINATED=\"Y\" AND [LASTACCESS]<@StartDate";
OleDbCommand cmd = new OleDbCommand(sql, con);
cmd.Parameters.AddWithValue("@StartDate", new DateTime(2001, 10, 20));

Just use parameters and forget about formatting them yourself .

The framework is here to help you and provide solutions already tested and working like a charm