How to copy dropdown list data from gridview to database using SqlbulkCopy

See more:

ASP.NET

SQL-Server-2008R2

I have two datatable Tab1 and Tab2. First i add data to Tab1 which is visible in gridview. Then i have to select multiple rows using checkbox and on clicking a button the selected rows are copied to table Tab2. First column in gridview is a dropdown list StockCat. Im able to copy data from all the columns to datatable Tab2 except from dropdown list value. I dont have any error. In SQL Server 2008, when i checked Tab2, dropdown list values are empty but all other column values are copied. pls refer the code below and suggest the changes.

protected void btnGetUsedStock_Click(object sender, EventArgs e)
   {
       DataTable dt = new DataTable();
       dt.Columns.AddRange(new DataColumn[6] { new DataColumn("Stockcat", typeof(string)),
                       new DataColumn("Stockid", typeof(string)),
                        new DataColumn("Company", typeof(string)),
                       new DataColumn("Date",typeof(DateTime)),
      new DataColumn("Name", typeof(string)),new DataColumn("Size", typeof(string)),});
       foreach (GridViewRow row in GridView1.Rows)
       {
           if ((row.FindControl("chkSelect") as CheckBox).Checked)
           {
               string dd1 = row.Cells[1].Text;
               string Stockid = row.Cells[2].Text;
               string company = row.Cells[3].Text;
              string date = row.Cells[4].Text;
               string name = row.Cells[5].Text;
               string size = row.Cells[6].Text;
               dt.Rows.Add(dd1,Stockid,company,date, name, size);
           }
       }
       if (dt.Rows.Count > 0)
       {
           string consString = ConfigurationManager.ConnectionStrings["CMSConnectionString"].ConnectionString;
           using (SqlConnection con = new SqlConnection(consString))
           {
               using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
               {
                   //Set the database table name
                   sqlBulkCopy.DestinationTableName = "dbo.Tab2";

                   //[OPTIONAL]: Map the DataTable columns with that of the database table
                   sqlBulkCopy.ColumnMappings.Add("Stockcat", "StockCat");
                   sqlBulkCopy.ColumnMappings.Add("Stockid", "StockID");
                   sqlBulkCopy.ColumnMappings.Add("Company", "Company");
                   sqlBulkCopy.ColumnMappings.Add("Date", "Date");
                   sqlBulkCopy.ColumnMappings.Add("Name", "Name");
                   sqlBulkCopy.ColumnMappings.Add("Size", "Size");
                   con.Open();
                   sqlBulkCopy.WriteToServer(dt);
                   con.Close();
               }
           }
       }

   }