CODEWITHSUNDEEP
c#sqlasp.net
Excalibur Morgan
Excalibur Morgan

Reputation: 21

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

I'm working on an insert method but it gives me 2 errors I can't seem to resolve, I am also an intern who doesn't get enough guidance in this so that's why I'm asking it here.

int werknemerId = 12345; 
int knowhowLenght = knowhowMatches.Count;
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn("Kantoor", typeof(int)));
dt.Columns.Add(new DataColumn("Taal", typeof(string)));
dt.Columns.Add(new DataColumn("Spreken", typeof(string)));
dt.Columns.Add(new DataColumn("Lezen", typeof(string)));
dt.Columns.Add(new DataColumn("Schrijven", typeof(string)));
dt.Columns.Add(new DataColumn("Talen_op_werknemerID", typeof(int)));
for (int x = 0; x < languageMatches.Count; x++) 
{
    string lang = languageMatches[x];
    string known = knowhowMatches[x];
    dt.Rows.Add(1, new string[] 
    {
        lang, known, known, known
    }, werknemerId);
}
using(SqlConnection Conn = new SqlConnection(connstring)) 
{
    Conn.Open();
    using(SqlBulkCopy bc = new SqlBulkCopy(Conn)) 
    {
        bc.DestinationTableName = "UzkTalen";
        bc.WriteToServer(dt);
        bc.WriteToServer(dt);
    }
}

I keep getting this exception:

System.InvalidOperationException: 'The given value of type String from the data source cannot be converted to type int of the specified target column.'

and these two inner exceptions:

FormatException: Failed to convert parameter value from a String to an Int32.

FormatException: Input string was not in a correct format.

If someone could help me with this that would be great so I could learn from it!

Upvotes: 2

Views: 543

Answers (1)

Ramil Aliyev 007
Ramil Aliyev 007

Reputation: 5442

I faced this problem at today. Problem is your table column order is not same with dataTable's column order.

For example:

If your table like as below:

enter image description here

Then your code must be like as below:

table.Columns.Add("Firm", typeof(short));
table.Columns.Add("TigerId", typeof(int));
table.Columns.Add("LogixId", typeof(int));
table.Columns.Add("Code", typeof(string));
table.Columns.Add("TigerItemId", typeof(int));
table.Columns.Add("RegisteredDate", typeof(DateTime));

If we change Code column's order (that it is string) to first, then bulk insert will not work. For example below code doesn't work:

table.Columns.Add("Code", typeof(string)); // It is string    
table.Columns.Add("Firm", typeof(short));
table.Columns.Add("TigerId", typeof(int));
table.Columns.Add("LogixId", typeof(int));
table.Columns.Add("TigerItemId", typeof(int));
table.Columns.Add("RegisteredDate", typeof(DateTime));

Bulk insert prefers column order, meaning if dataTable's column order is not same database's table like above example, then bulk insert will try insert Code data to Firm column that it will throw exception.

Upvotes: 1

Related Questions