Reading DBF with VFPOLEDB driver problem.

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;
        }
    }

Answer 1

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.

Answer 2

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.

Answer 3

I found the solution here:

http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/035b6676-441f-47df-9272-3b2bc4f8861a

Select Cast(FieldName As NUMERIC(11,3)) From TableName