78

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?

Ricketts
  • 5,025
  • 4
  • 35
  • 48
  • 2
    Looking at the doc, you can use [`NotifyAfter`](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.notifyafter.aspx) property which will raise events when N rows are copied. It can help you figure the batch in which the row where default occurred. – shahkalpesh Aug 09 '13 at 04:58
  • 1
    Does your `CleanDecimal` method try to validate the data, or just strip invalid characters? Have you tried using `Decimal.TryParse()` to see if the data is actually in the right format? Give that a go and see if it turns up the problematic value. – Corey Aug 09 '13 at 05:05
  • Here is how you can get more info about the specific column causing the issue https://medium.com/@shokrano/solving-the-the-given-value-of-type-string-from-the-data-source-cannot-be-converted-to-type-9e34ea3d815d – Shahar Shokrani Mar 15 '23 at 07:51

11 Answers11

105

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

Robotnik
  • 3,643
  • 3
  • 31
  • 49
43

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);
                }
            }
        }
    }
}
HerrimanCoder
  • 6,835
  • 24
  • 78
  • 158
Joshy Joseph
  • 575
  • 4
  • 4
  • 7
    If you look at the question's code example, ColumnMappings are being set. This had nothing to do with mapping the columns, rather the format of the data going into those columns. – Ricketts May 08 '14 at 20:53
  • 9
    Although they say SqlBulkCopy auto-maps these columns, the fact is you really need to specify them otherwise you will get these types of errors. That is what worked for me at least. – Micro Nov 13 '14 at 16:10
  • 1
    I have used auto-mapping before but I just ran into the same problem with a bigint (rather than a varchar) in a dataset with almost 120 columns. Simply explicitly mapping the columns resolved it. Go figure. – DotThoughts May 13 '16 at 01:16
  • 5
    This worked for me. I was using a ToDataTable() extension method from another answer, but it was failing. The reason it was failing was because the column order in the database was different than the property order in the class. – Christopher Jul 05 '16 at 23:51
  • I had a Guid column in my DataTable and in the Database table. All the columns in the DataTable and the DB table were named exactly the same. I got a similar error on the WriteToServer method (The given value of type Guid from the data source cannot be converted to type int of the specified target column.). The error disappeared when I added all the Column mappings explicitly. – Mark Hagers Dec 28 '16 at 10:39
  • I had a different conversion datatype issue and setting up the mappings fixed it for me, even though there was nothing wrong with my column order or datatypes. – Squirrel5853 Apr 28 '17 at 09:34
  • 1
    I also had random errors until I manually specified the column mapping. In my case, because I had already setup the bulk copy table columns manually, I could auto-create the mappings: foreach (DataColumn column in bulkCopyTable.Columns) { sqlBulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping( column.ColumnName, column.ColumnName)); } – dbruning Jul 11 '17 at 22:24
  • Excellent, this bullseyes the problem for me! – HerrimanCoder Oct 19 '20 at 17:25
39

Since I don't believe "Please use..." plus some random code that is unrelated to the question is a good answer, but I do believe the spirit was correct, I decided to answer this correctly.

When you are using Sql Bulk Copy, it attempts to align your input data directly with the data on the server. So, it takes the Server Table and performs a SQL statement similar to this:

INSERT INTO [schema].[table] (col1, col2, col3) VALUES

Therefore, if you give it Columns 1, 3, and 2, EVEN THOUGH your names may match (e.g.: col1, col3, col2). It will insert like so:

INSERT INTO [schema].[table] (col1, col2, col3) VALUES
                          ('col1', 'col3', 'col2')

It would be extra work and overhead for the Sql Bulk Insert to have to determine a Column Mapping. So it instead allows you to choose... Either ensure your Code and your SQL Table columns are in the same order, or explicitly state to align by Column Name.

Therefore, if your issue is mis-alignment of the columns, which is probably the majority of the cause of this error, this answer is for you.

TLDR

using System.Data;
//...
myDataTable.Columns.Cast<DataColumn>().ToList().ForEach(x => 
    bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(x.ColumnName, x.ColumnName)));

This will take your existing DataTable, which you are attempt to insert into your created BulkCopy object, and it will just explicitly map name to name. Of course if, for some reason, you decided to name your DataTable Columns differently than your SQL Server Columns... that's on you.

Suamere
  • 5,691
  • 2
  • 44
  • 58
  • That! Thank you very much. This explained a lot of issues I've been having. – trailmax Oct 11 '18 at 00:00
  • 1
    I was getting a similar exception and even aligning my class properties to the table did not resolve it. Great solution! – David Peters Jan 15 '20 at 16:31
  • DataTable.Columns doesn't have a Cast method – JWiley Jul 29 '20 at 05:40
  • @JWiley You gotta be a LINQ user to see the Cast extension. To include LINQ functionality, type `using System.Linq;` at the top of the file. Or, write out the `.Columns.Cast<...` call, get the error, place your cursor on the error, and use the `Ctrl+.` Control Period shortcut (by default). That should include the required base libraries. – Suamere Jul 30 '20 at 02:30
  • Bless you and thank you!! I got this error after stopping work for a few weeks (when it worked) had a DB refresh and an older table structure was put back. I did not realize that two columns were switched until I read this. You saved me some work and while I think column mapping is viable, I'm old school enough to have my data source match my table directly for bulkcopy – j.hull Jun 22 '22 at 13:59
25

@Corey - It just simply strips out all invalid characters. However, your comment made me think of the answer.

The problem was that many of the fields in my database are nullable. When using SqlBulkCopy, an empty string is not inserted as a null value. So in the case of my fields that are not varchar (bit, int, decimal, datetime, etc) it was trying to insert an empty string, which obviously is not valid for that data type.

The solution was to modify my loop where I validate the values to this (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());
}

After making the adjustments above, everything inserts without issues.

Ricketts
  • 5,025
  • 4
  • 35
  • 48
7

Make sure that the column values u added in entity class having get set properties also in the same order which is present in target table.

santhoshraj
  • 89
  • 1
  • 1
5

There is another issue you have to take care of it when you try mapping column which is string length, for example TK_NO nvarchar(50) you will have to map to the same length as the destination field.

d219
  • 2,707
  • 5
  • 31
  • 36
Medhat Makram
  • 61
  • 1
  • 3
5

Not going to be everyone's fix, but it was for me:

So, i ran across this exact issue. The problem I seemed to have was when my DataTable didnt have an ID column, but the target destination had one with a primary key.

When i adapted my DataTable to have an id, the copy worked perfectly.

In my scenario, the Id column isnt very important to have the primary key so i deleted this column from the target destination table and the SqlBulkCopy is working without issue.

Ashetynw
  • 198
  • 1
  • 12
2

I got the same error "occasionally". Note: column mapping was all correct, and that is why code worked most of the time.

And I found the root cause was string length issue. The target table column had datatype as nvarchar(255) - whereas the value being sent was of length more then 255 chars in string.

Fixed it by increasing column length in DB.

p.s.: Sadly the error msg wont tell you which column of table is causing this error. That you have to guess/figure it out manually.

Hemant Chauhan
  • 31
  • 3
0

My issue was with the column mapping rather than the values. I was doing an extract from a dev system, creating the destination table, bulk copying the content, extracting from a prod system, adjusting the destination table and bulk copying the content so the column order from the 2 bulk copies wasn't matching

// explicitly setting the column mapping even though the source & destination column names
// are the same as the column orders will affect the bulk copy giving data conversion errors
foreach (DataColumn column in p_dataTable.Columns)
{
  bulkCopy.ColumnMappings.Add(
    new()
    {
      SourceColumn = column.ColumnName,
      DestinationColumn = column.ColumnName
    }
  );
}

bulkCopy.WriteToServer(p_dataTable);
Pierre Collard Suero
  • 73
  • 1
  • 7
0

short answer: change type from nvarchar(size) to nvarchar(Max) it is issue of string length size

note: all above suggestions made me write this short answer

suntosh aqula
  • 27
  • 7
-3

Check The data you are writing to Server. May be data has delimiter which is not used.

like 

045|2272575|0.000|0.000|2013-10-07
045|2272585|0.000|0.000;2013-10-07

your delimiter is '|' but data has a delimiter ';'. So for this you are getting the error.

Md Kauser Ahmmed
  • 311
  • 3
  • 4