title: sqlbulkcopy - does not allow DBNull.Value
body: <p>i just want to make one xls mapping facility. here is my sql table :</p>
  
  <pre><code>Id --&gt; int(Not NULL) auto incremented
  Name --&gt; varchar(50) (Not NULL)
  type_id --&gt; int(Not NULL)
  Ownername --&gt; varchar (Allow NULL)
  Ownermob --&gt; varchar (Allow NULL)
  Room --&gt; varchar (Allow NULL)
  Build --&gt; varchar (Allow NULL)
  Road --&gt; varchar (Allow NULL)
  Area --&gt; varchar (Allow NULL)
  City --&gt; varchar (Allow NULL)
  Phone --&gt; varchar (Allow NULL)
  Mobile --&gt; varchar (Allow NULL)
  Email --&gt; varchar (Allow NULL)
  ContactPerson --&gt; varchar (Allow NULL)
  ContactPersonmob --&gt; varchar (Allow NULL)
  UserOFC --&gt; Bool (Allow NULL)
  UserVAT --&gt; Bool (Allow NULL)
  UserINV --&gt; Bool (Allow NULL)
  UserNone --&gt; Bool (Allow NULL)
  state_id --&gt; int (Allow NULL)
  country_id --&gt; int (Allow NULL)
  Remark --&gt; text (Allow NULL)
  Register_Date --&gt; Datetime (Not NUll)
  User_id --&gt; varchar (Not NUll)
  </code></pre>
  
  <p>here is my code :</p>
  
  <pre><code>protected void lbut_import_Click(object sender, EventArgs e)
      {
          Page.Validate("ImportXLS");
          if (FileUpload1.HasFile)
          {
              if (FileUpload1.FileContent.Length &gt; 0)
              {
                  string Foldername;
                  string Extension = System.IO.Path.GetExtension(FileUpload1.PostedFile.FileName);
                  string filename = DateTime.Now.ToString("ddMMyyyy_HHmmss");
                  if (Extension == ".XLS" || Extension == ".XLSX" || Extension == ".xls" || Extension == ".xlsx")
                  {
                      Foldername = Server.MapPath("~/Files/");
                      FileUpload1.PostedFile.SaveAs(Foldername + filename + Extension);
                      string conString = string.Empty;
                      switch (Extension)
                      {
                          case ".xls": //Excel 97-03
                              conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                              break;
  
                          case ".xlsx": //Excel 07
                              conString = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
                              break;
                      }
                      conString = string.Format(conString, Foldername + filename + Extension);
                      using (OleDbConnection excel_con = new OleDbConnection(conString))
                      {
                          excel_con.Open();
                          string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
                          DataTable dtExcelData = new DataTable();
  
                          //[OPTIONAL]: It is recommended as otherwise the data will be considered as String by default.
                          dtExcelData.Columns.AddRange(new DataColumn[23] 
                                                          {new DataColumn("Name", typeof(string)),
                                                           new DataColumn("type_id", typeof(int)),
                                                           new DataColumn("Ownername",typeof(string)),
                                                           new DataColumn("Ownermob",typeof(string)),
                                                           new DataColumn("Room",typeof(string)),
                                                           new DataColumn("Build",typeof(string)),
                                                           new DataColumn("Road",typeof(string)),
                                                           new DataColumn("Area",typeof(string)),
                                                           new DataColumn("City",typeof(string)),
                                                           new DataColumn("Phone",typeof(string)),
                                                           new DataColumn("Mobile",typeof(string)),
                                                           new DataColumn("Email",typeof(string)),
                                                           new DataColumn("ContactPerson",typeof(string)),
                                                           new DataColumn("ContactPersonmob",typeof(string)),
                                                           new DataColumn("UserOFC",typeof(bool)),
                                                           new DataColumn("UserVAT",typeof(bool)),
                                                           new DataColumn("UserINV",typeof(bool)),
                                                           new DataColumn("UserNone",typeof(bool)),
                                                           new DataColumn("state_id",typeof(int)),
                                                           new DataColumn("country_id",typeof(int)),
                                                           new DataColumn("Remark",typeof(string)),
                                                           new DataColumn("Register_Date",typeof(DateTime)),
                                                           new DataColumn("User_id",typeof(string)),
                                                          });
  
                          using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
                          {
                              oda.Fill(dtExcelData);
                          }
                          excel_con.Close();
                          string consString = ConfigurationManager.ConnectionStrings["CRMConnectionString"].ToString();
                          using (SqlConnection con = new SqlConnection(consString))
                          {
                              using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                              {
                                  //Set the database table name
                                  sqlBulkCopy.DestinationTableName = "tbl_Party_master";
                                  //[OPTIONAL]: Map the Excel columns with that of the database table
                                  sqlBulkCopy.ColumnMappings.Add(dtExcelData.Columns[int.Parse(txt_col.Text.Trim())].ToString(), "Name");
                                  sqlBulkCopy.ColumnMappings.Add(dtExcelData.Columns[int.Parse(txt_col1.Text.Trim())].ToString(), "type_id");
                                  if (!string.IsNullOrEmpty(txt_col2.Text.Trim()) &amp;&amp; !string.IsNullOrEmpty(dtExcelData.Columns[int.Parse(txt_col2.Text.Trim())].ToString()))
                                  {
                                      sqlBulkCopy.ColumnMappings.Add(dtExcelData.Columns[int.Parse(txt_col2.Text.Trim())].ToString(), "Ownername");
                                  }
                                  if (!string.IsNullOrEmpty(txt_col3.Text.Trim()) &amp;&amp; !string.IsNullOrEmpty(dtExcelData.Columns[int.Parse(txt_col3.Text.Trim())].ToString()))
                                  {
                                      sqlBulkCopy.ColumnMappings.Add(dtExcelData.Columns[int.Parse(txt_col3.Text.Trim())].ToString(), "Ownermob");
                                  }
                                  if (!string.IsNullOrEmpty(txt_col4.Text.Trim()) &amp;&amp; !string.IsNullOrEmpty(dtExcelData.Columns[int.Parse(txt_col4.Text.Trim())].ToString()))
                                  {
                                      sqlBulkCopy.ColumnMappings.Add(dtExcelData.Columns[int.Parse(txt_col4.Text.Trim())].ToString(), "Room");
                                  }
                                  if (!string.IsNullOrEmpty(txt_col5.Text.Trim()) &amp;&amp; !string.IsNullOrEmpty(dtExcelData.Columns[int.Parse(txt_col5.Text.Trim())].ToString()))
                                  {
                                      sqlBulkCopy.ColumnMappings.Add(dtExcelData.Columns[int.Parse(txt_col5.Text.Trim())].ToString(), "Build");
                                  }
                                  if (!string.IsNullOrEmpty(txt_col6.Text.Trim()) &amp;&amp; !string.IsNullOrEmpty(dtExcelData.Columns[int.Parse(txt_col6.Text.Trim())].ToString()))
                                  {
                                      sqlBulkCopy.ColumnMappings.Add(dtExcelData.Columns[int.Parse(txt_col6.Text.Trim())].ToString(), "Road");
                                  }
                                  if (!string.IsNullOrEmpty(txt_col7.Text.Trim()) &amp;&amp; !string.IsNullOrEmpty(dtExcelData.Columns[int.Parse(txt_col7.Text.Trim())].ToString()))
                                  {
                                      sqlBulkCopy.ColumnMappings.Add(dtExcelData.Columns[int.Parse(txt_col7.Text.Trim())].ToString(), "Area");
                                  }
                                  if (!string.IsNullOrEmpty(txt_col8.Text.Trim()) &amp;&amp; !string.IsNullOrEmpty(dtExcelData.Columns[int.Parse(txt_col8.Text.Trim())].ToString()))
                                  {
                                      sqlBulkCopy.ColumnMappings.Add(dtExcelData.Columns[int.Parse(txt_col8.Text.Trim())].ToString(), "City");
                                  }
                                  if (!string.IsNullOrEmpty(txt_col9.Text.Trim()) &amp;&amp; !string.IsNullOrEmpty(dtExcelData.Columns[int.Parse(txt_col9.Text.Trim())].ToString()))
                                  {
                                      sqlBulkCopy.ColumnMappings.Add(dtExcelData.Columns[int.Parse(txt_col9.Text.Trim())].ToString(), "Phone");
                                  }
                                  if (!string.IsNullOrEmpty(txt_col10.Text.Trim()) &amp;&amp; !string.IsNullOrEmpty(dtExcelData.Columns[int.Parse(txt_col10.Text.Trim())].ToString()))
                                  {
                                      sqlBulkCopy.ColumnMappings.Add(dtExcelData.Columns[int.Parse(txt_col10.Text.Trim())].ToString(), "Mobile");
                                  }
                                  if (!string.IsNullOrEmpty(txt_col11.Text.Trim()) &amp;&amp; !string.IsNullOrEmpty(dtExcelData.Columns[int.Parse(txt_col11.Text.Trim())].ToString()))
                                  {
                                      sqlBulkCopy.ColumnMappings.Add(dtExcelData.Columns[int.Parse(txt_col11.Text.Trim())].ToString(), "Email");
                                  }
                                  if (!string.IsNullOrEmpty(txt_col12.Text.Trim()) &amp;&amp; !string.IsNullOrEmpty(dtExcelData.Columns[int.Parse(txt_col12.Text.Trim())].ToString()))
                                  {
                                      sqlBulkCopy.ColumnMappings.Add(dtExcelData.Columns[int.Parse(txt_col12.Text.Trim())].ToString(), "ContactPerson");
                                  }
                                  if (!string.IsNullOrEmpty(txt_col13.Text.Trim()) &amp;&amp; !string.IsNullOrEmpty(dtExcelData.Columns[int.Parse(txt_col13.Text.Trim())].ToString()))
                                  {
                                      sqlBulkCopy.ColumnMappings.Add(dtExcelData.Columns[int.Parse(txt_col13.Text.Trim())].ToString(), "ContactPersonmob");
                                  }
                                  if (!string.IsNullOrEmpty(txt_col14.Text.Trim()) &amp;&amp; !string.IsNullOrEmpty(dtExcelData.Columns[int.Parse(txt_col14.Text.Trim())].ToString()))
                                  {
                                      sqlBulkCopy.ColumnMappings.Add(dtExcelData.Columns[int.Parse(txt_col14.Text.Trim())].ToString(), "UserOFC");
                                  }
                                  if (!string.IsNullOrEmpty(txt_col15.Text.Trim()) &amp;&amp; !string.IsNullOrEmpty(dtExcelData.Columns[int.Parse(txt_col15.Text.Trim())].ToString()))
                                  {
                                      sqlBulkCopy.ColumnMappings.Add(dtExcelData.Columns[int.Parse(txt_col15.Text.Trim())].ToString(), "UserVAT");
                                  }
                                  if (!string.IsNullOrEmpty(txt_col16.Text.Trim()) &amp;&amp; !string.IsNullOrEmpty(dtExcelData.Columns[int.Parse(txt_col16.Text.Trim())].ToString()))
                                  {
                                      sqlBulkCopy.ColumnMappings.Add(dtExcelData.Columns[int.Parse(txt_col16.Text.Trim())].ToString(), "UserINV");
                                  }
                                  if (!string.IsNullOrEmpty(txt_col17.Text.Trim()) &amp;&amp; !string.IsNullOrEmpty(dtExcelData.Columns[int.Parse(txt_col17.Text.Trim())].ToString()))
                                  {
                                      sqlBulkCopy.ColumnMappings.Add(dtExcelData.Columns[int.Parse(txt_col17.Text.Trim())].ToString(), "UserNone");
                                  }
                                  if (!string.IsNullOrEmpty(txt_col18.Text.Trim()) &amp;&amp; !string.IsNullOrEmpty(dtExcelData.Columns[int.Parse(txt_col18.Text.Trim())].ToString()))
                                  {
                                      sqlBulkCopy.ColumnMappings.Add(dtExcelData.Columns[int.Parse(txt_col18.Text.Trim())].ToString(), "state_id");
                                  }
                                  if (!string.IsNullOrEmpty(txt_col19.Text.Trim()) &amp;&amp; !string.IsNullOrEmpty(dtExcelData.Columns[int.Parse(txt_col19.Text.Trim())].ToString()))
                                  {
                                      sqlBulkCopy.ColumnMappings.Add(dtExcelData.Columns[int.Parse(txt_col19.Text.Trim())].ToString(), "country_id");
                                  }
                                  if (!string.IsNullOrEmpty(txt_col20.Text.Trim()) &amp;&amp; !string.IsNullOrEmpty(dtExcelData.Columns[int.Parse(txt_col20.Text.Trim())].ToString()))
                                  {
                                      sqlBulkCopy.ColumnMappings.Add(dtExcelData.Columns[int.Parse(txt_col20.Text.Trim())].ToString(), "Remark");
                                  }
                                  sqlBulkCopy.ColumnMappings.Add(dtExcelData.Columns[int.Parse(txt_col21.Text.Trim())].ToString(), "Register_Date");
                                  sqlBulkCopy.ColumnMappings.Add(dtExcelData.Columns[int.Parse(txt_col22.Text.Trim())].ToString(), "User_id");
                                  con.Open();
                                  sqlBulkCopy.WriteToServer(dtExcelData);
                                  con.Close();
                              }
                          }
                          StringBuilder sb = new StringBuilder();
                          sb.Append("&lt;script type = 'text/javascript'&gt;");
                          sb.Append("alert('");
                          sb.Append(dtExcelData.Rows.Count.ToString());
                          sb.Append(" Rows(s) are Inserted.');");
                          sb.Append("&lt;/script&gt;");
                          ClientScript.RegisterStartupScript(this.GetType(), "script", sb.ToString());
                          if (System.IO.File.Exists(Foldername + filename + Extension))
                          {
                              System.IO.File.Delete(Foldername + filename + Extension);
                          }
                      }
                  }
              }
              ibtnimexls_ModalPopupExtender.Show();
          }
      }
  </code></pre>
  
  <p>here i'm from <code>txt_col.Text</code> 0 to 22 i just to column mapping with this destination column. how ever i debug this things and add watching facility i just conform identical order with source to destination column.</p>
  
  <p>what i'm going wrong here....</p>
  
  <p>please help me......</p>