How to get the column and table name when InvalidOperationException in SQLbulkcopy

Question

1.80/5 (2 votes)

See more:

i am using below code , while insert the data I got;

Actual output:
InvalidOperationException: The given value of type String from the data source cannot be converted to type int of the specified target column
I have more than one type int rows, how can i catch which row rises this Error in catch block.

Desired output:
The given value of type String from the data source cannot be converted to type int in OWN_ACCT_NUM
or
The given value of type String from the data source cannot be converted to type int in OWN_NUM

Please help me, Error prevention is not my aim, Need show the which column rises this error, is my ultimate aim.

C#

SqlConnection DestinationConn = new SqlConnection(DestConnString);

bc.DestinationTableName = mytable;
bc.ColumnMappings.Add(2, "OWN_NUM");
bc.ColumnMappings.Add(2, "OWN_ACCT_NUM");
bc.ColumnMappings.Add(3, "OWNER_ADDR_1");
bc.ColumnMappings.Add(4, "OWNER_ADDR_2");
bc.ColumnMappings.Add(5, "OWNER_CITY");
bc.ColumnMappings.Add(6, "OWNER_NAME_1");
bc.ColumnMappings.Add(7, "OWNER_NAME_2"); bc.WriteToServer(dtable);

Posted 1-May-15 20:01pm

Baskar Gs

Updated 2-May-15 0:56am

Tomas Takac

v2

Add a Solution

Comments

Tomas Takac 2-May-15 2:25am

I don't think this information is available. In my previous job we had an import tool based on SqlBulkCopy. Before initiating bulk copy it examined data types in source and target and printed warnings in the log if there was a type mismatch. This helped while detecting errors because the error messages were as you said not very informative.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

phil.o · Answer 1 · 2015-05-01T20:26:00

Solution 1

The given value of type String from the data source cannot be converted to type int in OWN_ACCT_NU

The given value of type String from the data source cannot be converted to type int in OWN_NUM

Column names are stated explicitely. Why do you need help to read them?

And why not using integer type instead of string? Handling every variable as string is a terrible habit that should be avoided in any case. .NET Framework provides you all types you need, so please use them accordingly.

Posted 1-May-15 20:26pm

phil.o

Comments

Tomas Takac 2-May-15 6:53am

No the columns are not stated explicitly. The latter examples are what OP would like to have. I highlighted it in the post.

phil.o 2-May-15 7:28am

Ok I see; my bad.

Baskar Gs 6-May-15 6:32am

Actually the my code loading the data from the excel and have to insert that data into table. If user put string values instead of integer value in the(Excel Column) excel file. That time, I have to show the error message to the user as "String value cannot be accepted in int type column with column name". i was able to get the error message but need to show the column name with that message.

Ex : I can able to get column name while catching SqlException "Received an invalid column length" using below code.

string pattern = @"\d+";
////return ex.me

Match match = Regex.Match(ex.Message.ToString(), pattern);
var index = Convert.ToInt32(match.Value) - 1;

FieldInfo fi = typeof(SqlBulkCopy).GetField("_sortedColumnMappings", BindingFlags.NonPublic | BindingFlags.Instance);
var sortedColumns = fi.GetValue(bc);
var items = (Object[])sortedColumns.GetType().GetField("_items", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(sortedColumns);

FieldInfo itemdata = items[index].GetType().GetField("_metadata", BindingFlags.NonPublic | BindingFlags.Instance);
var metadata = itemdata.GetValue(items[index]);

var column = metadata.GetType().GetField("column", BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance).GetValue(metadata);
var length = metadata.GetType().GetField("length", BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance).GetValue(metadata);
retvalue = String.Format("Column: {0} contains data with a length greater than: {1}", column, length);

Same as above have to show Column name in my error message.

Baskar Gs · Answer 2 · 2015-05-06T00:29:00

Actually the my code loading the data from the excel and have to insert that data into table. If user put string values instead of integer value in the(Excel Column) excel file. That time, I have to show the error message to the user as "String value cannot be accepted in int type column with column name". i was able to get the error message but need to show the column name with that message.

Ex : I can able to get column name while catching SqlException "Received an invalid column length" using below code.

string pattern = @"\d+";
////return ex.me

Match match = Regex.Match(ex.Message.ToString(), pattern);
var index = Convert.ToInt32(match.Value) - 1;

FieldInfo fi = typeof(SqlBulkCopy).GetField("_sortedColumnMappings", BindingFlags.NonPublic | BindingFlags.Instance);
var sortedColumns = fi.GetValue(bc);
var items = (Object[])sortedColumns.GetType().GetField("_items", BindingFlags.NonPublic | BindingFlags.Instance).GetValue(sortedColumns);

FieldInfo itemdata = items[index].GetType().GetField("_metadata", BindingFlags.NonPublic | BindingFlags.Instance);
var metadata = itemdata.GetValue(items[index]);

var column = metadata.GetType().GetField("column", BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance).GetValue(metadata);
var length = metadata.GetType().GetField("length", BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance).GetValue(metadata);
retvalue = String.Format("Column: {0} contains data with a length greater than: {1}", column, length);

Same as above have to show Column name in my error message.