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

Question

I'm getting this exception when trying to do an SqlBulkCopy from a DataTable.

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 saying, but how I can I get more information, such as the row/field this is happening on? The datatable is populated by a 3rd party and can contain up to 200 columns and up to 10k rows. The columns that are returned depend on the request sent to the 3rd party. All of the datatable columns are of string type. The columns in my database are not all varchar, therefore, prior to executing the insert, I format the datatable values using the following code (non important code removed):

//--- 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
}

This code should format all values for their destination fields. In the case of this error, the decimal, the function that cleans that up will remove any character that is not 0-9 or . (decimal point). This field that is throwing the error would be nullable in the database.

The level 2 exception has this error:

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 the level 3 exception has this error:

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 anyone have any ideas to fix? or any ideas to get more info?

Answer 1

For the people stumbling across this question and getting a similar error message in regards to an nvarchar instead of money:

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

This could be caused by a too-short column.

For example, if your column is defined as nvarchar(20) and you have a 40 character string, you may get this error.

Source

Answer 2

Please use SqlBulkCopyColumnMapping.

Example:

private void SaveFileToDatabase(string filePath)
{
    string strConnection = System.Configuration.ConfigurationManager.ConnectionStrings["MHMRA_TexMedEvsConnectionString"].ConnectionString.ToString();

    String excelConnString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0\"", filePath);
    //Create Connection to Excel work book 
    using (OleDbConnection excelConnection = new OleDbConnection(excelConnString))
    {
        //Create OleDbCommand to fetch data from Excel 
        using (OleDbCommand cmd = new OleDbCommand("Select * from [Crosswalk$]", excelConnection))
        {
            excelConnection.Open();
            using (OleDbDataReader dReader = cmd.ExecuteReader())
            {
                using (SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection))
                {
                    //Give your Destination table name 
                    sqlBulk.DestinationTableName = "PaySrcCrosswalk";

                    // this is a simpler alternative to explicit column mappings, if the column names are the same on both sides and data types match
                    foreach(DataColumn column in dt.Columns) {
                         s.ColumnMappings.Add(new SqlBulkCopyColumnMapping(column.ColumnName, column.ColumnName));
                     }
                   
                    sqlBulk.WriteToServer(dReader);
                }
            }
        }
    }
}