c# - BULK UPLOAD ERROR “错误来自数据源的 String 类型的给定值无法转换为指定目标列的 int 类型。”

Question

我正在尝试使用 BulkUpload 将数据从 CSV 导入数据库，我可以复制 varchar 列，但是当我尝试将“部门”数据导入 Usertale 时无法导入。

---

错误：来自数据源的 String 类型的给定值无法转换为指定目标列的 int 类型。

---

数据库截图

用户表截图从这里上传

需要导入的 MY Excel 文件

      public ActionResult ImportFromExcel(HttpPostedFileBase postedFile)
        {
            if (ModelState.IsValid)
            {
                if (postedFile != null && postedFile.ContentLength > (1024 * 1024 * 50))  // 50MB limit  
                {
                    ModelState.AddModelError("postedFile", "Your file is to large. Maximum size allowed is 50MB !");
                }

                else
                {
                    string path = Server.MapPath("~/Uploads/");
                    if (!Directory.Exists(path))
                    {
                        Directory.CreateDirectory(path);
                    }

                    string filePath = path + Path.GetFileName(postedFile.FileName);
                    string extension = Path.GetExtension(postedFile.FileName);
                    postedFile.SaveAs(filePath);

                    string conString = string.Empty;
                    switch (extension)
                    {
                        case ".xls": //For Excel 97-03.  
                            conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                            break;
                        case ".xlsx": //For Excel 07 and above.  
                            conString = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
                            break;
                    }

                    try
                    {
                        DataTable dt = new DataTable();
                        conString = string.Format(conString, filePath);

                        using (OleDbConnection connExcel = new OleDbConnection(conString))
                        {
                            using (OleDbCommand cmdExcel = new OleDbCommand())
                            {
                                using (OleDbDataAdapter odaExcel = new OleDbDataAdapter())
                                {
                                    cmdExcel.Connection = connExcel;

                                    //Get the name of First Sheet.  
                                    connExcel.Open();
                                    DataTable dtExcelSchema;
                                    dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                                    string sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
                              
                                    connExcel.Close();

                                    //Read Data from First Sheet.  
                                    connExcel.Open();
                                    cmdExcel.CommandText = "SELECT * From [" + sheetName + "]";

                                    odaExcel.SelectCommand = cmdExcel;
                                    odaExcel.Fill(dt);
                                    connExcel.Close();
                                }
                            }
                        }

                        conString = ConfigurationManager.ConnectionStrings["SLOT_TESTING_DB"].ConnectionString;
                        using (SqlConnection con = new SqlConnection(conString))
                        {
                            con.Open();
                            using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                            {
                                //Set the database table name.  
                                sqlBulkCopy.DestinationTableName = "UserTables";
                                sqlBulkCopy.ColumnMappings.Add("User_id", "User_id");
                                sqlBulkCopy.ColumnMappings.Add("Name", "Name");
                                sqlBulkCopy.ColumnMappings.Add("Email", "Email");
                                sqlBulkCopy.ColumnMappings.Add("UserType", "UserType");

                          
                               sqlBulkCopy.ColumnMappings.Add("Department_Name", "DepartmentId");


                                //int dptname = sqlBulkCopy.ParseInt();
                                //sqlBulkCopy.DepartmentId.tryParseToDepartment_Name();

                                //sqlBulkCopy.ColumnMappings.Add("TermId", "TermId");

                                //Int32.TryParse(Department.Text, out int x);
                                //Convert.ToInt64("Department_Name");
                                sqlBulkCopy.WriteToServer(dt);
                                Response.Write("Bulk data stored successfully");
                                Console.WriteLine("File uploaded successfully");


                            }
                        }
                    }

                    //catch (Exception ex)  
                    //{  
                    //    throw ex;  
                    //}  
                    catch (Exception e)
                    {
                        return Json("error" + e.Message);
                    }
                    //return RedirectToAction("Index");  
                }
            }
            //return View(postedFile);  
            return Json("File uploaded successfully");
            //Console.WriteLine("File uploaded successfully");
        }

标签： c#asp.netasp.net-mvcmodel-view-controllersqlbulkcopy