SqlBulkCopy - The given value of type String from the data source cannot be converted to type money of the specified target column
Solution 1
@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.
Solution 2
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.
Solution 3
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);
}
}
}
}
}
Solution 4
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.
Solution 5
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.
Ricketts
Experienced in Microsoft .NET development as well as many other fields. Expert in standard web coding of xHtml and CSS. Also very experienced in ASP.NET, C#, Javascript, JQuery, Database Development and Graphic Design. I have designed and developed hundreds of websites for many different industries. I own and operate Ricketts Web Design: http://www.RickettsWebDesign.com, where we develop custom promotional website and custom web applications. I also write an online blog: http://www.WebDeveloperPost.com, where I give tips and tricks on how to do different things.
Updated on July 05, 2022Comments
-
Ricketts almost 2 years
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 about 10 yearsIf 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.
-
Micro over 9 yearsAlthough 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.
-
DotThoughts almost 8 yearsI 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.
-
Chris almost 8 yearsThis 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.
-
Mark Hagers over 7 yearsI 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.
-
Nicklas Møller Jepsen over 7 yearsWhy did this get downvoted? I had the problem of this exception due to wrong column order.
-
Secret Squirrel about 7 yearsI 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.
-
dbruning almost 7 yearsI 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)); }
-
Mathews Sunny about 6 yearsIt is just comment quality answer
-
trailmax over 5 yearsThat! Thank you very much. This explained a lot of issues I've been having.
-
David Peters over 4 yearsI was getting a similar exception and even aligning my class properties to the table did not resolve it. Great solution!
-
JWiley almost 4 yearsDataTable.Columns doesn't have a Cast method
-
Suamere almost 4 years@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 theCtrl+.
Control Period shortcut (by default). That should include the required base libraries. -
HerrimanCoder over 3 yearsExcellent, this bullseyes the problem for me!
-
Elikill58 over 2 yearsPrefer make a long answer with a quick summary at begin than just a quick answer
-
j.hull almost 2 yearsBless 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