使用 FoxPro OLEDB 驱动程序导入数据时出错
我正在使用 FoxPro OLE-DB 驱动程序将一些数据从 FoxPro 数据库导入到 Sql Server 数据库。我采用的方法是循环遍历 FoxPro 表，将所有记录选择到 DataTable 中，然后使用 SqlBulkCopy 将该表插入到 Sql Server 中。这工作正常，除了少数情况下我收到以下错误：
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.
我已经对此进行了调查并记录了它与哪些行一起出现，问题是 FoxPro 表的数值具有固定宽度。 1 存储为 1.00，但 10 存储为 10.0，它是导致问题的小数点后的一位数。现在发现了这个问题，但我正在努力解决它。以下函数是我用来将 OLEDBReader 转换为 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;
}
我想做的是检查是否存在小数点后 1 位问题的值，但在这些情况下我根本无法从 datareader 中读取数据。我本以为我可以在有问题的行上使用 dr.GetString(i) 但是这会返回以下错误：
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.
我无法更新 FoxPro 数据，因为该列不允许这样做，我如何从中读取记录DataReader 并修复它？我已经尝试了casting / dr.GetValue / dr.GetData的所有组合，并且所有组合都给出了相同错误的变化。
FoxPro 表的结构如下：
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
导致问题的是 av_length 列
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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
我不知道您是否有权获取 Visual Foxpro，但它有一个升迁“向导”，允许直接上传到 SQL Server。
它看起来像是可以通过 下载 Visual Foxpro 9, SP2
这可能是备忘录/blob 类型列未得到正确解释的问题。
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.
您提到了类型转换，但不确定您是如何尝试的...在您的 try/catch 中，
您可能想要显式测试列数据类型并强制它...类似于（不是对象的正数）下面的 DataType.ToString() 参考，但您必须在运行/调试期间发现它，
您显然也可以测试其他类型......
You mentioned type-casting, but not sure how you've attempted it... In your try/catch where you have
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.
You could obviously test for other types too...
从您列出的表结构来看，它所做的事情是正确的。在列出的表结构的VFP中，AV_LENGTH是数字类型，长度为4，2分配给小数点。因此它的值最多为“9.99”。 VFP 强制数字字段的输入最多 2 位小数，其中 1 位为小数点，其余为整数部分。
其余基于数字的字段是具有长度的数字，但没有小数位，这表明它们都是没有小数位的整数，因此有资格作为整数数据类型。带小数的数字应采用浮点或双列类型。
话虽这么说，我不知道你是如何得到 4、2 位小数的 10.0 值的。这是我第一次看到强制将大于所保存结构的分配意图的数字实际存储在这样的字段中。
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.
我不记得 FoxPro 出现此问题的原因。我认为这与数字的存储方式有关。不管怎样，解决方案是 (A) 清理数据或 (B) 重新调整字段大小以允许更大的值。下面的示例代码演示了该问题。
* 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 ENDIFI 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