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

Question

I am trying to read values from xml file and then use the bulkcopy to insert the data into my database.

I am using a custom class which is:

class Customer
    {
        public int CustomerID { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public int ShowsNumber { get; set; }
        public int VisitNumber { get; set; }
        public int Cancellation { get; set; }
    }

and I read the data like this:

List<Customer> customersList =
                (
                    from e in XDocument.Load(file).Root.Elements("cust")
                    select new Customer
                    {
                        CustomerID = (int)e.Attribute("custid"),
                        FirstName = (string)e.Attribute("fname"),
                        LastName = (string)e.Attribute("lname"),
                        ShowsNumber = (int)e.Attribute("count_noshow"),
                        VisitNumber = (int)e.Attribute("count_resos"),
                        Cancellation = (int)e.Attribute("count_cancel"),
                    }).ToList();

Then I insert that customersList to a datatabe like this:

DataTable dataTable = getBasicDataTable();
for (int i = 0; i < customersList.Count; i++)
                {
                    DataRow datarows = dataTable.NewRow();
                    datarows[0] = customersList[i].CustomerID;
                    datarows[1] = customersList[i].FirstName;
                    datarows[2] = customersList[i].LastName;
                    datarows[3] = customersList[i].ShowsNumber;
                    datarows[4] = customersList[i].VisitNumber;
                    datarows[5] = customersList[i].Cancellation;
                    dataTable.Rows.Add(datarows);
                }

then I insert the data to my database like this:

but I got this exception

using (SqlBulkCopy sbc = new SqlBulkCopy(GetConnectionString()))
            {
                sbc.DestinationTableName = XMLReader.databaseTable;
                sbc.WriteToServer(dataTable);
            }

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

as you see, when I extract the data from my xml, I already use the cast to int and to string and it is working. so why when inserting to the database I got that exception?

Note

In order to give you the whole code, this is the getBasicDataTable fuction

private DataTable getBasicDataTable()
        {
            DataTable dataTable = new DataTable();
            dataTable.Clear();
            dataTable.Columns.Add("customerID");
            dataTable.Columns.Add("firstName");
            dataTable.Columns.Add("lastName");
            dataTable.Columns.Add("showsNumber");
            dataTable.Columns.Add("visitNumber");
            dataTable.Columns.Add("cancellation");
            return dataTable;
        }

Solution 1

You need to specify the column as integer while defining the datatable. Like this:-

dataTable.Columns.Add("customerID", typeof(int));

Edit:
The other reason i suspect is probably the way you are binding your datatable (I mean the order of columns) doesn't match with that of database table. Reason being I think default mapping is not Name to Name rather its Index to Index in SqlBulkCopy. So Kindly re-check your database table order, It should look like:-

CustomerID (INT)
FirstName (VARCHAR\NVARCHAR)
LastName (VARCHAR\NVARCHAR)
ShowsNumber (INT)
VisitNumber (INT)
Cancellation (INT)

Solution 2

As far as I am aware, you need to set the Type of the column on the datatable, otherwise it will presume string (because nearly everything can be converted as such).

Before you set the values, try:

dataTable.Columns[2].Type = typeof (int);

or, alternatively, you can specify it when defining the columns:

dataTable.Columns.Add("ShowsNumber", typeof(int));

Comments

[Marco Dinatsoli]

I still got the same exact exception

[Rahul Singh]

@MarcoDinatsoli - Are you sure all your columns in database are of type INT? I mean the ones you have defined in your Type definition like CustomerID,ShowsNumber etc?

[Steve Lillis]

Update your post to feature the updated code and we can take a look.

[Rahul Singh]

@MarcoDinatsoli - Please check my edit and see if that helps.

[Marco Dinatsoli]

I asked another question about the same issue, stackoverflow.com/questions/27060852/…

[Rahul Singh]

@MarcoDinatsoli - Oops exactly the same I thought :D Column Mapping as i mentioned in my edit. I went for tea and couldn't help you quicky. Apologies :)