I had a difficult time finding a solution to this issue (the issue with no solution has been posted here at least a couple of times). It has to do with the VFP OLEDB data provider blowing up in .NET when reading certain DBFs.
Apparently, the issue stems from the way VFP allows you to take digits from the right side of the decimal point and use them on the left side. For example if the column is defined as N(5,3) it should only hold numbers up to 9.999, but if you don't need all three decimal places on the right side of the decimal point, you can use the space left over for digits on the left side. So this column will hold 999.9! In any case, it throws an InvalidOperationException. This prevents you from using a DataAdapter's Fill() method to read the data. The work-around, is to use a data reader, build a DataTable from scratch and populate each column, one at a time in each row. When the exception occurs, insert null for the value. Here's some C# 2.0 code:
public DataTable GetVfpDataTable(string dbfFullPath)
{
string sqlString = "SELECT * FROM " + Path.GetFileName(dbfFullPath);
OleDbConnection oleConn = new OleDbConnection("Provider=VFPOLEDB.1;Data Source=" +
Path.GetDirectoryName(dbfFullPath) + ";Collating Sequence=MACHINE;");
OleDbCommand oleCmd = new OleDbCommand(sqlString, oleConn);
OleDbDataReader oleReader;
DataColumn col;
DataRow row;
DataTable dbfTable = new DataTable();
try
{
oleConn.Open();
//Bug in VFP OLE Data Adapter blows up with error
// "The provider could not determine the decimal value..."
// must read with a data reader row by row, column by column
oleReader = oleCmd.ExecuteReader();
for (int i = 0; i < oleReader.FieldCount; i++)
{
col = new DataColumn(oleReader.GetName(i), oleReader.GetFieldType(i));
dbfTable.Columns.Add(col);
}
while (oleReader.Read())
{
row = dbfTable.NewRow();
for (int i = 0; i < oleReader.FieldCount; i++)
{
try
{
row[i] = oleReader[i];
}
catch (InvalidOperationException)
{
row[i] = DBNull.Value; //work-around for VFP OLEDB Provider glitch
}
}
dbfTable.Rows.Add(row);
}
}
catch (Exception eX)
{
throw new FileLoadException("Error Importing From VFP File\n" +
eX.Message + "\nVFPDataAccess.cs:GetVfpDataTable()", dbfFullPath);
}
finally
{
oleConn.Close();
}
return dbfTable;
}
