Remove From My Forums

Issue on insert from dataTable to SQL table

Question
0

Sign in to vote

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);
//loop
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(con))
{
bulkCopy.DestinationTableName = "dbo.EMP_TABLE";
bulkCopy.WriteToServer(dt);
}

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.
example:
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

Answers

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);
            
            dt.Rows.Add(dr);

           
            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");
                    con.Open();
                    sqlBulkCopy.WriteToServer(dt);
                    con.Close();
                }
            }
}

Database:

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

Answered by:

Issue on insert from dataTable to SQL table

Question

Answers