I am importing some data from a FoxPro database to a Sql Server database using the FoxPro OLE-DB driver. The approach I am taking is to loop through the FoxPro tables, select all records into a DataTable and then use SqlBulkCopy to insert that table into Sql Server. This works fine except for a few instances where I get the following error:
System.InvalidOperationException: 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.
I have investigated this and logged which rows it appears with and the issue is that the FoxPro table has a fixed width for a numeric value. 1 is stored as 1.00 however 10 is stored as 10.0 and it is the single digit after the decimal point which is causing the issues. Having now found the issue I am struggling to fix it though. The following function is what I am using to convert an OLEDBReader to a DataTable:
private DataTable FPReaderToDataTable(OleDbDataReader dr, string TableName)
{
DataTable dt = new DataTable();
//get datareader schema
DataTable SchemaTable = dr.GetSchemaTable();
List<DataColumn> cols = new List<DataColumn>();
if (SchemaTable != null)
{
foreach (DataRow drow in SchemaTable.Rows)
{
string columnName = drow["ColumnName"].ToString();
DataColumn col = new DataColumn(columnName, (Type)(drow["DataType"]));
col.Unique = (bool)drow["IsUnique"];
col.AllowDBNull = (bool)drow["AllowDBNull"];
col.AutoIncrement = (bool)drow["IsAutoIncrement"];
cols.Add(col);
dt.Columns.Add(col);
}
}
//populate data
int RowCount = 1;
while (dr.Read())
{
DataRow row = dt.NewRow();
for (int i = 0; i < cols.Count; i++)
{
try
{
row[((DataColumn)cols[i])] = dr[i];
}
catch (Exception ex) {
if (i > 0)
{
LogImportError(TableName, cols[i].ColumnName, RowCount, ex.ToString(), dr[0].ToString());
}
else
{
LogImportError(TableName, cols[i].ColumnName, RowCount, ex.ToString(), "");
}
}
}
RowCount++;
dt.Rows.Add(row);
}
return dt;
}
What I would like to do is check for values that have the 1 decimal place issue but I am unable to read from the datareader at all in these cases. I would have thought that I could have used dr.GetString(i) on the offending rows however this then returns the following error:
The provider could not determine the String value. For example, the row was just created, the default for the String column was not available, and the consumer had not yet set a new String value.
I am unable to update the FoxPro data as the column does not allow this, how can I read the record from the DataReader and fix it? I have tried all combinations of casting / dr.GetValue / dr.GetData and all give variations on the same error.
The structure of the FoxPro table is as follows:
Number of data records: 1664
Date of last update: 11/15/10
Code Page: 1252
Field Field Name Type Width Dec Index Collate Nulls Next Step
1 AV_KEY Numeric 6 Asc Machine No
2 AV_TEAM Numeric 6 No
3 AV_DATE Date 8 No
4 AV_CYCLE Numeric 2 No
5 AV_DAY Numeric 1 No
6 AV_START Character 8 No
7 AV_END Character 8 No
8 AV_SERVICE Numeric 6 No
9 AV_SYS Character 1 No
10 AV_LENGTH Numeric 4 2 No
11 AV_CWEEKS Numeric 2 No
12 AV_CSTART Date 8 No
** Total ** 61
It is the av_length column which is causing the problem
