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 lt DataColumn gt  IntColumns   new List lt DataColumn gt     List lt DataColumn gt  DecimalColumns   new List lt DataColumn gt     List lt DataColumn gt  BoolColumns   new List lt DataColumn gt     List lt DataColumn gt  DateColumns   new List lt DataColumn gt      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   gt  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  amp  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

User · Answer

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

User · Answer

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

User · Answer

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 lt DataColumn gt    ToList   ForEach x   gt       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

User · Answer

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

User · Answer

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

User · Answer

Please use SqlBulkCopyColumnMapping  Example  private void SaveFileToDatabase string filePath        string strConnection   System Configuration ConfigurationManager ConnectionStrings  quot MHMRA TexMedEvsConnectionString quot   ConnectionString ToString         String excelConnString   String Format  quot Provider Microsoft ACE OLEDB 12 0 Data Source  0  Extended Properties   quot Excel 12 0  quot  quot   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  quot Select   from  Crosswalk   quot   excelConnection                         excelConnection Open                using  OleDbDataReader dReader   cmd ExecuteReader                                  using  SqlBulkCopy sqlBulk   new SqlBulkCopy strConnection                                           Give your Destination table name                      sqlBulk DestinationTableName    quot PaySrcCrosswalk quot                           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

User · Answer

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

User · Answer

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

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

Examples related to c#

Examples related to datatable

Examples related to sqlbulkcopy