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
I dont know if you have access to getting Visual Foxpro, but it has an upsizing "wizard" that will allow uploading directly to SQL Server.
It looks like a free download for trial at MS via Download Visual Foxpro 9, SP2
it may be an issue with memo / blob type columns that are not getting properly interpretted.
You mentioned type-casting, but not sure how you've attempted it... In your try/catch where you have
row[((DataColumn)cols[i])] = dr[i];
you might want to explicitly test the columns data type and FORCE it... something like (not positive of the object reference for DataType.ToString() below, but you'll have to find that during your running / debugging.
if( cols[i].DataType.ToString().ToLower().Contains( "int" ))
row[((DataColumn)cols[i])] = (int)dr[i];
else
row[((DataColumn)cols[i])] = dr[i];
You could obviously test for other types too...
From your listed structure of the table, that IS CORRECT what it is doing. In VFP for the table structure listed, the AV_LENGTH is of type numeric, length of 4, 2 being allocated for decimal positons. So it will at MOST have a value of "9.99". VFP forces the input of the numeric field to a maximum of 2 decimal positions, 1 for decimal point and the rest as whole number portion.
The rest of the numeric based fields are Numeric with a length, but NO decimal positions which indicates they are all WHOLE numbers with no decimal position hence would qualify as integer data types. Numeric with decimal should go into a float or double column type.
That being said, I don't know HOW you are even getting a 10.0 value in a numeric 4, 2 decimal. This is the FIRST time I've ever seen forcing a number larger than the allocated intent of the structure being saved actually be stored in the field like this.
I don't recall the reason why FoxPro has this problem. I think it has something to do with how numbers are stored. Regardless of that, the solution is either (A) clean up the data or (B) re-size the field to allow a larger value. The sample code below demonstrates the problem.
* create a table that can store a value between -0.99 and 99.99
CREATE TABLE "TEST.DBF" (av_length N(4,2))
* insert values between 1.10 and 22,222.22222
INSERT INTO "TEST" (av_length) VALUES(1.1)
INSERT INTO "TEST" (av_length) VALUES(2.2)
INSERT INTO "TEST" (av_length) VALUES(11.11)
INSERT INTO "TEST" (av_length) VALUES(22.22)
INSERT INTO "TEST" (av_length) VALUES(111.111)
INSERT INTO "TEST" (av_length) VALUES(222.222)
INSERT INTO "TEST" (av_length) VALUES(1111.1111)
INSERT INTO "TEST" (av_length) VALUES(2222.2222)
INSERT INTO "TEST" (av_length) VALUES(11111.11111)
INSERT INTO "TEST" (av_length) VALUES(22222.22222)
* view the contents of the table
* note that records 3 to 10 do not match the field definition
BROWSE NORMAL
IF MESSAGEBOX("Fix the Data? Select to Change the Field Definition", 0+4+32) = 6
* Solution A: fix the data, and view the table contents again
REPLACE ALL av_length WITH MIN(av_length, 9.99) IN "TEST"
BROWSE NORMAL
ELSE
* Solution B: change the field definition, and view the table contents again
* note that records 9 & 10 still need to be fixed
ALTER TABLE "TEST.DBF" ALTER COLUMN av_length N(12,6)
BROWSE NORMAL
ENDIF
![Interactive visualization of a graph in python [closed]](https://www.devze.com/res/2023/04-10/09/92d32fe8c0d22fb96bd6f6e8b7d1f457.gif)
加载中,请稍侯......
精彩评论