Update field

Question

User-1499457942 posted

Hi

  I am uploading data from Excel to Sql using below code . In Database i have 1 field which is a combination of 2-3 fields. How this field can be updated . I have field Id which is Primary Key in Database . I want to concatenate with ItemId+Category+Location field

DbDataReader dr = command.ExecuteReader();

SqlBulkCopy objbulkInsert = new SqlBulkCopy(con);

objbulkInsert.DestinationTableName = "[Test]";

objbulkInsert.WriteToServer(dr);

Thanks

Answer 1

User-893317190 posted

Hi JagjitSingh,

You could combine the 2-3 field together in your sql.

For example, I have excel. I want to combine id+firstname+lastname into the single field name in databse.

     firstname Salary	lastname
	nancy	1000	kity
	mike	1200	bit
	jerry	1500	tom
	helen	1200	keil
	lili	1000	mity

Below is my code.

  using (OleDbConnection connection = new OleDbConnection(conString))
            {
               
                connection.Open();
// get the name of first sheet
                string sheet2 = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
                DataTable dtExcelData = new DataTable();

           
                dtExcelData.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(int)),
            new DataColumn("Name", typeof(string)),
            new DataColumn("Salary",typeof(decimal)) });
 // make the id field of datatable auto increment
                dtExcelData.Columns["Id"].AutoIncrement = true;
                dtExcelData.Columns["Id"].AutoIncrementSeed = 1;
                dtExcelData.Columns["Id"].AutoIncrementStep = 1;

                using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT STR(id)+''+lastname+' '+firstname as Name,Salary FROM [" + sheet2 + "]", connection))
                {
                    oda.Fill(dtExcelData);
                    Response.Write(dtExcelData.Rows.Count);

                }
                string consString = ConfigurationManager.ConnectionStrings["UserItemDb3"].ConnectionString;
                using (SqlConnection con = new SqlConnection(consString))
                {
                    using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                    {
                        //Set the database table name
                        sqlBulkCopy.DestinationTableName = "dbo.salary";
                        ////[OPTIONAL]: Map the Excel columns with that of the database table
                        sqlBulkCopy.ColumnMappings.Add(0, 0);

                        sqlBulkCopy.ColumnMappings.Add(1, 1);
                        sqlBulkCopy.ColumnMappings.Add(2, 2);
                       
                        con.Open();
                        Response.Write(dtExcelData.Rows.Count);
                        sqlBulkCopy.WriteToServer(dtExcelData);
                        con.Close();
                    }
                }
            }

Please pay attention to the sql SELECT STR(id)+''+lastname+' '+firstname as Name,Salary,if your datatable fields has a field of type int, you should use Str() function to convert it to string, or it will show error.

The result

id	name	        salary
1	 1kity nancy	1000
2	 3bit mike	1200
3	 5tom jerry	1500
4	 6keil helen	1200
5	 7mity lili	1000

Best regards,

Ackerly Xu

Answer 2

User-1499457942 posted

Hi Ackerly

  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 3

User-893317190 posted

Hi JagjitSingh,

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

Best regards,

Ackerly Xu

Answer 4

User-1499457942 posted

Hi

   In Excel Sheet there are all Columns

Thanks