Issue on insert from dataTable to SQL table RRS feed

  • Question

  • User-1738841732 posted

    I created a dataTable

    DataTable dt = new DataTable();
    DataRow dr = dt.NewRow();
    dt.Columns.Add("Col1", typeof(string));
    dt.Columns.Add("Col2", typeof(string));
    dt.Columns.Add("Col3", typeof(int));
    For Loop
    dr = dt.NewRow();
    dr["Col1"] = res.Element(ns + "FirstName").Value;
    dr["Col2"] = res.Element(ns + "LastName").Value;
    dr["Col3"] = Convert.ToInt32(res.Element(ns + "BadgeNo").Value);
    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(con))
    bulkCopy.DestinationTableName = "dbo.EMP_TABLE";

    Upon bulk insert, I get the below error
    The given value of type String from the data source cannot be converted to type int of the specified target column.

    The problem is with the badgeNo, i tried converting all the ways also send hardcode data, i still get the same error.
    int values=10;
    dr["Col3"] = value;

    in the SQL table, badge number is defined as int.
    If i send only the FirstName and LastName, it works. When i add the BadgeNo, I get this issue.

    Friday, May 22, 2020 2:10 PM


  • User1535942433 posted

    Hi mansooraabid,

    Accroding to your description and codes,I create a test and it works fine.As far as I think,SqlBulkCopy.WriteToServer(DataTable) fails with confusing messages if the column order of the DataTable differs from the column order of the table definition in your database (when this causes a type or length incompatibility).

    I suggest you could try to add like this:

    sqlBulkCopy.ColumnMappings.Add("Id", "Id");
    sqlBulkCopy.ColumnMappings.Add("num", "num");
    sqlBulkCopy.ColumnMappings.Add("total", "total");

    More details,you could refer to below codes:

     protected void Button1_Click(object sender, EventArgs e)
                string consString = System.Configuration.ConfigurationManager.ConnectionStrings["aspnet-TestApplicationWithDatabase-20190820030542"].ConnectionString;
                DataTable dt = new DataTable();
                DataRow dr;
                dt.Columns.Add("Id", typeof(string));
                dt.Columns.Add("num", typeof(string));
                dt.Columns.Add("total", typeof(int));
                dr = dt.NewRow();
                dr["Id"] = TextBox1.Text;
                dr["num"] = TextBox2.Text;
                dr["total"] = Convert.ToInt32(TextBox3.Text);
                using (SqlConnection con = new SqlConnection(consString))
                    using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                        //Set the database table name
                        sqlBulkCopy.DestinationTableName = "Test";
                        //[OPTIONAL]: Map the DataTable columns with that of the database table
                        sqlBulkCopy.ColumnMappings.Add("Id", "Id");
                        sqlBulkCopy.ColumnMappings.Add("num", "num");
                        sqlBulkCopy.ColumnMappings.Add("total", "total");


    Besides,I suggest you could post your full codes and database to us.It will help us to solve your problems.

    Best regards,

    Yijing Sun

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, May 25, 2020 6:21 AM