execute query in dbf from c#

Question

i am doing a program in C# and i´m trying to execute a query in a DBF files that i think is ok but the value of the HasRows property of the DataReader is false. I think the problem is with the dates. This is my code:

 string Con = @"Provider=VFPOLEDB.1;Data Source=\\Server\ges_01";
 OleDbConnection ConnectionHandler = new OleDbConnection(Con);
ConnectionHandler.Open();

 string SQL = "SELECT codalb FROM BALBARA WHERE FECALB BETWEEN CTOD('2015/12/07') AND CTOD('2015/12/13') AND CODCLI LIKE '%9' ORDER BY CODALB"
 OleDbCommand Query = new OleDbCommand(SQL, ConnectionHandler);
 OleDbDataReader datareader = Query.ExecuteReader();
while(datareader.Read())
{}

I know the rest is ok because if put string SQL="select codalb from balbara"; Works fine.

Any one can say me what i´m doing wrong

Answer 1

The problem here is that the CTOD() function isn't supported by the OLE DB Provider.

Change your query to use DTOS() which:

Returns a character-string date in a yyyymmdd format from a specified Date or DateTime expression.

So concluding your query might become to:

string SQL = String.Format(
    @"SELECT 
        codalb 
    FROM
        balbara 
    WHERE 
        DTOS(fecalb) BETWEEN '{0}' AND '{1}'
        AND codcli LIKE '%9' 
    ORDER BY 
        codalb", 
        dateTimeVariable1.ToString("yyyyMMdd"),
        dateTimeVariable2.ToString("yyyyMMdd"));

Note 1: check out the indenting to help you write readable code, also I suggest to write all the column/table names lowercase so you can make an easy difference what is your "data" and what is SQL.

Note 2: you can find offical format string examples about the DateTime type here.

---

EDIT: As a good advice in comments from @AlanB, you should always strive to use parametrized queries instead of a string to prevent SQL Injection attacks.

A remark about the OLEDB parameters:

The OLE DB .NET Provider does not support named parameters for passing parameters to an SQL statement or a stored procedure called by an OleDbCommand when CommandType is set to Text. In this case, the question mark (?) placeholder must be used.

And about the order of parameters:

Therefore, the order in which OleDbParameter objects are added to the OleDbParameterCollection must directly correspond to the position of the question mark placeholder for the parameter in the command text.

So all these informations given your query could look this example:

OleDbCommand Query = new OleDbCommand();
Query.Connection = ConnectionHandler;
Query.CommandText = 
    @"SELECT 
        codalb 
    FROM
        balbara 
    WHERE 
        DTOS(fecalb) BETWEEN ? AND ?
        AND codcli LIKE '%9' 
    ORDER BY 
        codalb";

Query.Parameters.Add(dateTimeVariable1.ToString("yyyyMMdd"));
Query.Parameters.Add(dateTimeVariable2.ToString("yyyyMMdd"));
OleDbDataReader datareader = Query.ExecuteReader();