SqlBulkCopy - The given value of type String from the data source cannot be converted to type money of the specified target column

c# datatable sqlbulkcopy

Question

When attempting to do a SqlBulkCopy from a DataTable, I see this issue.

Error Message: The given value of type String from the data source cannot be converted to type money of the specified target column.
Target Site: System.Object ConvertValue(System.Object, System.Data.SqlClient._SqlMetaData, Boolean, Boolean ByRef, Boolean ByRef)

I understand what the error is trying to tell me, but how can I find out further information, such the row or field where this is occurring? The datatable, which may have up to 200 columns and 10,000 rows, is filled in by a third party. The request made to the third party determines the columns that are returned. The datatable column types are all strings. Because not all of the columns in my database are varchar, I prepare the datatable values using the following code (non-important code deleted) before running the insert:

//--- create lists to hold the special data type columns
List<DataColumn> IntColumns = new List<DataColumn>();
List<DataColumn> DecimalColumns = new List<DataColumn>();
List<DataColumn> BoolColumns = new List<DataColumn>();
List<DataColumn> DateColumns = new List<DataColumn>();

foreach (DataColumn Column in dtData.Columns)
{
    //--- find the field map that tells the system where to put this piece of data from the 3rd party
    FieldMap ColumnMap = AllFieldMaps.Find(a => a.SourceFieldID.ToLower() == Column.ColumnName.ToLower());

    //--- get the datatype for this field in our system
    Type FieldDataType = Nullable.GetUnderlyingType(DestinationType.Property(ColumnMap.DestinationFieldName).PropertyType);

    //--- find the field data type and add to respective list
    switch (Type.GetTypeCode(FieldDataType))
    {
        case TypeCode.Int16:
        case TypeCode.Int32:
        case TypeCode.Int64: { IntColumns.Add(Column); break; }
        case TypeCode.Boolean: { BoolColumns.Add(Column); break; }
        case TypeCode.Double:
        case TypeCode.Decimal: { DecimalColumns.Add(Column); break; }
        case TypeCode.DateTime: { DateColumns.Add(Column); break; }
    }

    //--- add the mapping for the column on the BulkCopy object
    BulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(Column.ColumnName, ColumnMap.DestinationFieldName));
}

//--- loop through all rows and convert the values to data types that match our database's data type for that field
foreach (DataRow dr in dtData.Rows)
{
    //--- convert int values
    foreach (DataColumn IntCol in IntColumns)
        dr[IntCol] = Helpers.CleanNum(dr[IntCol].ToString());

    //--- convert decimal values
    foreach (DataColumn DecCol in DecimalColumns)
        dr[DecCol] = Helpers.CleanDecimal(dr[DecCol].ToString());

    //--- convert bool values
    foreach (DataColumn BoolCol in BoolColumns)
        dr[BoolCol] = Helpers.ConvertStringToBool(dr[BoolCol].ToString());

    //--- convert date values
    foreach (DataColumn DateCol in DateColumns)
        dr[DateCol] = dr[DateCol].ToString().Replace("T", " ");
}

try
{
    //--- do bulk insert
    BulkCopy.WriteToServer(dtData);
    transaction.Commit();
}
catch (Exception ex)
{
    transaction.Rollback();

    //--- handles error
    //--- this is where I need to find the row & column having an issue
}

All values should be formatted using this code for their final fields. Any character other than 0-9 or will be removed by the function that cleans up this decimal mistake (decimal point). This error-producing field would be nullable in the database.

This mistake appears in the level 2 exception:

Error Message: Failed to convert parameter value from a String to a Decimal.
Target Site: System.Object CoerceValue(System.Object, System.Data.SqlClient.MetaType, Boolean ByRef, Boolean ByRef, Boolean)

and this mistake may be seen in the level 3 exception:

Error Message: Input string was not in a correct format
Target Site: Void StringToNumber(System.String, System.Globalization.NumberStyles, NumberBuffer ByRef, System.Globalization.NumberFormatInfo, Boolean)

Does anybody have suggestions for a fix? Any suggestions about how to learn more?

1
54
8/9/2013 4:25:08 AM

Accepted Answer

@Corey - Simply said, it removes all incorrect characters. But your remark got me to thinking about the solution.

My database's large number of nullable columns was the source of the issue. An empty string is not added as a null value when using SqlBulkCopy. Therefore, it was attempting to put an empty string into my fields that aren't varchar (bit, int, decimal, datetime, etc.), which is plainly invalid for those data types.

The answer was to change the loop in my code where I check the values for validity (repeated for each datatype that is not string)

//--- convert decimal values
foreach (DataColumn DecCol in DecimalColumns)
{
     if(string.IsNullOrEmpty(dr[DecCol].ToString()))
          dr[DecCol] = null; //--- this had to be set to null, not empty
     else
          dr[DecCol] = Helpers.CleanDecimal(dr[DecCol].ToString());
}

Everything inserts without any problems after making the aforementioned changes.

22
8/9/2013 2:36:07 PM

Popular Answer

For anybody encountering this inquiry and receiving a similar error message about a nvarchar in place of money:

The given value of type String from the data source cannot be converted to type nvarchar of the specified target column.

A short column might be to blame for this.

For instance, if the definition of your column isnvarchar(20) you can see this problem if your string is 40 characters or more.

Source



Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow