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

c# linq linq-to-xml sqlbulkcopy

Question

I'm attempting to read values from an xml file and then bulkcopy the information into my database.

I'm using a special class that 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; }
    }

I interpreted the data as follows:

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();

I then add that.customersList to a table of data 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 do this to add the data to my database:

nonetheless, I made this exemption.

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 can see, I already utilize the cast to when I extract data from my xml.int and tostring and it is effective. but why did I get that exception while adding into the database?

Note

This is the whole code in order to send it to you.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;
        }
1
4
11/21/2014 1:02:37 PM

Accepted Answer

When specifying the column as an integer, you must do so.datatable . such like this:

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

Edit:
The second factor, in my opinion, is that the order of the columns in your datatable (by which I mean how you are binding it) doesn't line up with the order in the database table. For this reason, I believe default mapping is Index to Index rather than Name to Name.SqlBulkCopy . Please verify the order of your database tables again; it should be as follows:

CustomerID (INT)
FirstName (VARCHAR\NVARCHAR)
LastName (VARCHAR\NVARCHAR)
ShowsNumber (INT)
VisitNumber (INT)
Cancellation (INT)
6
11/21/2014 12:25:04 PM

Popular Answer

To my knowledge, if the Type of the column on the datatable is not set, it will assume that it is a string (because nearly everything can be converted as such).

Prior to setting the values, try:

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

Alternatively you may do that by specifying it while creating the columns:

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


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow