OleDB Could Not Determine the Decimal Value

Question

I am trying to get a table from FoxPro into C#. I am using the following statement;

sqlCmd.CommandText = @"SELECT ew.ew_pplid, ew.ew_name, ew.ew_from, ew_to, ew.ew_totdays
                       FROM empwork ew
                       INNER JOIN employs emp ON ew.ew_pplid = emp.em_pplid
                       WHERE emp.em_netname NOT LIKE '' 
                       AND TRIM(emp.em_netname) <> '' 
                       AND emp.em_type <> 2
                       AND ew.ew_from > ?";
sqlCmd.Parameters.AddWithValue("pDate", Convert.ToDateTime("01/08/2016"));

but I am getting an error I have never seen before;

The Provider could not determine the Decimal value. For example, the row was just created, the default for the Decimal column was not available, and the consumer had not yet set a new Decimal value.

Has anyone seen this error before, and if so how can I get around this?

Answer 1

IF if is choking on the date time with a false message, you could try with a slight alteration via

 AND ew.ew_from > CTOD( ? )";

and the parameter as a string of just the date

sqlCmd.Parameters.AddWithValue("pDate", "01/08/2016");

VFP's command CTOD() means Character-To-Date, and since you are already in expected month/day/year format, should go through.

If the failure is actually on another field such as the "ew.ew_totdays", you could always start by updating the query to limit the fields returned to just an ID key, leave the where clause alone. Then if it goes through, add one field back at a time. You may have a NULL value somewhere in the data.

If all the fields are ok, then you might have to apply the query based on the WHERE clause and stripping criteria one part at a time until all are applied.