Reading DBF with VFPOLEDB driver

Question

I am using VFPOLEDB driver to read DBF files and I keep getting this error and I am not sure why and how to fix the problem:

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.

Here is the code. I call this routine to return a DataSet of the DBF file and display the data in a DataGridView.

public DataSet GetDBFData(FileInfo fi, string tbl)
{
    using (OleDbConnection conn = new OleDbConnection(
    @"Provider=VFPOLEDB.1;Data Source=" + fi.DirectoryName + ";"))
    {
        conn.Open();
        string command = "SELECT * FROM " + tbl;
        OleDbDataAdapter da = new OleDbDataAdapter(command, conn);
        DataSet ds = new DataSet();
        da.Fill(ds);
        return ds;
    }
}

Courteous

Answer 1

I finally solved the problem by getting the table schema and then casting all of non-character fields to varchar in the select statement. Good enough for previewing the contents of the table.

Courteous

Answer 2

I found the solution here: Error reading certain numeric values with VFPOLEDB driver

SELECT CAST(FieldName As NUMERIC(11, 3)) From TableName

Lackey

Answer 3

If you add a row from your gridview, it doesn't necessarily use a default value, but rather NULLs, so you may need to pre-set your defaults, or set the schema to NOT Allow Nulls.

You could automate through the columns after the query is done and force defaults based on the columns data types, such as

foreach (DataColumn oDC in YourDataSet.Tables[0].Columns)
{
   if (oDC.DataType.ToString().Contains("String"))
    oDC.DefaultValue = "";
   else if (oDC.DataType.ToString().Contains("Int32"))
    oDC.DefaultValue = 0;
   else if (oDC.DataType.ToString().Contains("DateTime"))
    oDC.DefaultValue = DateTime.MinValue;
}

these are just 3 default types, but there could be others like boolean, decimal, float, whatever, just add into the if/else and put whatever "default" values. It MAY help where otherwise "NULL" values are getting injected in when adding new rows.

Infamy

How do I preset my defaults? I am trying to preview the contents of a arbitrary DBF file. – Courteous

Thanks for the suggestion, but I can't even get a DataSet and my code crashes at da.Fill(ds); – Courteous

see my code sample in another post at #2184990 subject title was Search in DBF file using .idx file – Infamy

I tried using your code, but it still has the same problem. I have identified the offending column in one of the DBFs files. The column contains doubles with alot of decimal places. – Courteous

Answer 4

It is a known issue. Especially, if You need to select all columns, it is much more comfortable:

Select * from some_table

One working solution is to use another provider, for example Microsoft.Jet.OLEDB.4.0. Example connection string can be found here: http://docs.30c.org/conn/dbf-foxpro.html

Pedanticism