Error importing data with FoxPro OLEDB driver

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