Import from Excel to Sql

Question

User-1499457942 posted

Hi 

  IN below code if i upload first 4 values then it works . If i upload 6 values then it gives error No Value given for one or more required parameters

dtExcelData.Columns.AddRange(new DataColumn[6] { new DataColumn("Category", typeof(string)),
                    new DataColumn("Name", typeof(string)),
                    new DataColumn("Department",typeof(string)),
                    new DataColumn("Item", typeof(string)),
                    new DataColumn("D1", typeof(decimal)),
                    new DataColumn("B1",typeof(decimal))

using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT Category,Name,Department,Item,D1,B1 FROM [" + sheet1 + "]", cnnExcel))
                    
                    {
                        oda.Fill(dtExcelData);

                    }
 SqlBulkCopy objbulkInsert = new SqlBulkCopy(con);
objbulkInsert.DestinationTableName = "[Test]";
objbulkInsert.WriteToServer(dtExcelData);

Thanks

Answer 1

User-893317190 posted

Hi JagjitSingh,

Please check whether there are Category,Name,Department,Item,D1,B1 columns in your excel files,this may be because there are no such columns in your excel files.

Best regards,

Ackerly Xu

Answer 2

User-1499457942 posted

Hi

   In Excel Sheet there are all Columns

Thanks

Answer 3

User409696431 posted

Is the data you are providing consistent with the data types, and nullable or not, definitions for the database columns?  (And are the column names identical?)