DBF dBase - Where clause date Operator/operand type mismatch error

Question

trying to do a query on a dbf dBase which includes a where clause to only select the rows where a date is after 01/01/2018

I have tried all options in this thread but to no avail. how to query DBF(dbase) files date type field in where and between clause

command.CommandText = string.Format(@"SELECT * FROM PAYHIST WHERE EMPLOYEE = '{0}' 
                                            AND PAY_DATE >= '#1/1/2018 12:00:00 AM#'", employee.WorksNumber);

This throws an " Operator/operand type mismatch." exception as does

between( [PAY_DATE], CTOT( "01-Jan-18 12:00:00 AM" ), CTOT( "31-Dec-18 12:00:00 AM" ))

and

between( [PAY_DATE], CTOT( '01 - Jan - 18 12:00:00 AM' ), CTOT( '31 - Dec - 18 12:00:00 AM' )) 

while

AND PAY_DATE >= {^2018-01-01}

and

PAY_DATE >= {D ‘2018-01-01’}

throws an input string error.

Im running it on VS2015

any suggestions would be most welcome.

Answer 1

when querying, you need to parameterize your queries, not embed them as strings. The OleDB command execution process will handle the data type for you. However note, parameters with OleDb against a .DBF based file use "?" as a place-holder for the parameters and need to be added to the command for each parameter needed...

command.CommandText = string.Format(
@"SELECT 
        PH.* 
    FROM 
        PAYHIST PH
    WHERE 
            PH.EMPLOYEE = ?
        AND PH.PAY_DATE >= ? ";

    command.Parameters.AddWithValue( "parmForEmployeeID", employee.WorksNumber );
    command.Parameters.AddWithValue( "parmForDateField", new date(2018,1,1) );

if doing between, you just have extra "?" place-holder

... WHERE 
            PH.EMPLOYEE = ?
        AND PH.PAY_DATE BETWEEN ? AND ? ";


    command.Parameters.AddWithValue( "parmForEmployeeID", employee.WorksNumber );
    command.Parameters.AddWithValue( "parmFromDate", new date(2018,1,1) );
    command.Parameters.AddWithValue( "parmToDate", new date(2018,12,31) );

There are PLENTY of examples on parameterizing queries... especially doing web-based to prevent SQL-Injection. -- and read-up on that too.