ASPForums.Net RSS Feedhttp://www.aspforums.net/Handlers/RSS.ashxLatest additions to the content that appears on ASPForums.Net(c) 2019 www.aspforums.com. All rights reserved.Perform Insert Update after SqlBulkCopy Excel import is completed using C#Hi @smuthu,<br />Please try the following<br /><br /><a href = 'link(Threads/137501/Perform-Insert-Update-after-SqlBulkCopy-Excel-import-is-completed-in-C-and-VBNet/)'>Perform Insert Update after SqlBulkCopy Excel import is completed in C# and VB.Net</a><br /><br />It might help you.<br /><br />Cheers Andrea.https://www.aspforums.net:443/Threads/118224/Perform-Insert-Update-after-SqlBulkCopy-Excel-import-is-completed-using-C/https://www.aspforums.net:443/Threads/118224/Perform-Insert-Update-after-SqlBulkCopy-Excel-import-is-completed-using-C/Mon, 17 Oct 2016 04:40:27 GMTPerform Insert Update after SqlBulkCopy Excel import is completed using C#<p>sir ..,</p> <p>i will try this article not working .</p> <p>&nbsp;</p> <p>&nbsp;i want one colunm add in sql server &nbsp;but not add in escel sheet , i was saved &nbsp;that colunm values &nbsp;show excel sheet all rows this value save</p>https://www.aspforums.net:443/Threads/118224/Perform-Insert-Update-after-SqlBulkCopy-Excel-import-is-completed-using-C/https://www.aspforums.net:443/Threads/118224/Perform-Insert-Update-after-SqlBulkCopy-Excel-import-is-completed-using-C/Mon, 17 Oct 2016 05:37:09 GMTPerform Insert Update after SqlBulkCopy Excel import is completed using C#<p>Hi <a class="username" rel="smuthu"> smuthu</a>,</p> <p>Write more detailed explanation. Unless we understand we can't help.</p>https://www.aspforums.net:443/Threads/118224/Perform-Insert-Update-after-SqlBulkCopy-Excel-import-is-completed-using-C/https://www.aspforums.net:443/Threads/118224/Perform-Insert-Update-after-SqlBulkCopy-Excel-import-is-completed-using-C/Mon, 17 Oct 2016 07:57:20 GMTPerform Insert Update after SqlBulkCopy Excel import is completed using C#<p><strong>EXCEl :</strong></p> <p>1.Tran Date</p> <p>2.VAlueDate</p> <p>3.CHQNO</p> <p>4.Transaction Particulars</p> <p>5.Amount</p> <p>6.DR|CR</p> <p>7.Branch Name</p> <p><strong>SQL:</strong></p> <p>1.Tran Date</p> <p>2.VAlueDate</p> <p>3.CHQNO</p> <p>4.Transaction Particulars</p> <p>5.Amount</p> <p>6.DR|CR</p> <p>7.Branch Name</p> <p>8.BankName</p> <p><strong>DESIGN:</strong></p> <pre class="brush: html">&lt;div&gt; &lt;table align=&#34;center&#34;&gt; &lt;tr&gt; &lt;td&gt; BANKNAME &lt;/td&gt; &lt;td&gt; &lt;asp:TextBox ID=&#34;TextBox1&#34; runat=&#34;server&#34;&gt;&lt;/asp:TextBox&gt; &lt;/td&gt; &lt;/tr&gt; &lt;tr&gt; &lt;td&gt; UPLOAD &lt;/td&gt; &lt;td&gt; &lt;asp:FileUpload ID=&#34;FileUpload1&#34; runat=&#34;server&#34; /&gt; &lt;/td&gt; &lt;/tr&gt; &lt;tr&gt; &lt;td&gt; &lt;/td&gt; &lt;td&gt; &lt;/td&gt; &lt;/tr&gt; &lt;tr&gt; &lt;td&gt; &lt;/td&gt; &lt;td&gt; &lt;asp:Button ID=&#34;upload&#34; runat=&#34;server&#34; Text=&#34;SUBMIT&#34; OnClick=&#34;upload_Click&#34; /&gt; &lt;/td&gt; &lt;/tr&gt; &lt;/table&gt; &lt;/div&gt;</pre> <p><strong>SOURCE:</strong> </p> <pre class="brush: csharp">protected void upload_Click(object sender, EventArgs e) { //Upload and save the file string excelPath = Server.MapPath(&#34;~/Files/&#34;) + Path.GetFileName(FileUpload1.PostedFile.FileName); FileUpload1.SaveAs(excelPath); string conString = string.Empty; string extension = Path.GetExtension(FileUpload1.PostedFile.FileName); switch (extension) { case &#34;.xls&#34;: //Excel 97-03 conString = ConfigurationManager.ConnectionStrings[&#34;Excel03ConString&#34;].ConnectionString; break; case &#34;.xlsx&#34;: //Excel 07 or higher conString = ConfigurationManager.ConnectionStrings[&#34;Excel07+ConString&#34;].ConnectionString; break; } conString = string.Format(conString, excelPath); using (OleDbConnection excel_con = new OleDbConnection(conString)) { excel_con.Open(); string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0][&#34;TABLE_NAME&#34;].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[7] { new DataColumn(&#34;Tran Date&#34;, typeof(DateTime)), new DataColumn(&#34;Value Date&#34;, typeof(DateTime)), new DataColumn(&#34;CHQNO&#34;,typeof(string)), new DataColumn(&#34;Transaction Particulars&#34;,typeof(string)), new DataColumn(&#34;Amount&#34;,typeof(string)), new DataColumn(&#34;DR|CR&#34;,typeof(string)), new DataColumn(&#34;Branch Name&#34;,typeof(string)), }); using (OleDbDataAdapter oda = new OleDbDataAdapter(&#34;SELECT * FROM [&#34; + sheet1 + &#34;]&#34;, excel_con)) { oda.Fill(dtExcelData); } excel_con.Close(); string consString = ConfigurationManager.ConnectionStrings[&#34;constr&#34;].ConnectionString; using (SqlConnection con = new SqlConnection(consString)) { using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con)) { //Set the database table name sqlBulkCopy.DestinationTableName = &#34;dbo.tbl_BankTransaction&#34;; //[OPTIONAL]: Map the Excel columns with that of the database table sqlBulkCopy.ColumnMappings.Add(&#34;Tran Date&#34;, &#34;TranDate&#34;); sqlBulkCopy.ColumnMappings.Add(&#34;Value Date&#34;, &#34;ValueDate&#34;); sqlBulkCopy.ColumnMappings.Add(&#34;CHQNO&#34;, &#34;ChekNo&#34;); sqlBulkCopy.ColumnMappings.Add(&#34;Transaction Particulars&#34;, &#34;Remark&#34;); sqlBulkCopy.ColumnMappings.Add(&#34;Amount&#34;, &#34;Amount&#34;); sqlBulkCopy.ColumnMappings.Add(&#34;DR|CR&#34;, &#34;Type&#34;); sqlBulkCopy.ColumnMappings.Add(&#34;Branch Name&#34;, &#34;BranchName&#34;); con.Open(); SqlCommand cmd = new SqlCommand(&#34;insert into tbl_BankTransaction (BankName) values (&#39;&#34; + TextBox1.Text + &#34;&#39;)&#34;, con); cmd.ExecuteNonQuery(); sqlBulkCopy.WriteToServer(dtExcelData); con.Close(); } } } }</pre> <p><strong>ACTUAL RESULT:</strong></p> <p>14.10.2016 14.10.2016 &nbsp;12354 NEFT &nbsp;2500 DR &nbsp;AXIS(KERALA) NULL</p> <p>14.10.2016 14.10.2016 &nbsp;12354 NEFT &nbsp;2500 DR &nbsp;AXIS(KERALA) NULL</p> <p>14.10.2016 14.10.2016 &nbsp;12354 NEFT &nbsp;2500 DR &nbsp;AXIS(KERALA) NULL</p> <p>14.10.2016 14.10.2016 &nbsp;12354 NEFT &nbsp;2500 DR &nbsp;AXIS(KERALA) NULL</p> <p>NULL &nbsp; &nbsp; &nbsp; &nbsp; NULL &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NULL NULL &nbsp;NULL NULL &nbsp; NULL &nbsp; &nbsp; &nbsp; &nbsp; AXIS</p> <p><strong>EXCEPTING RESULT:</strong></p> <p>14.10.2016 14.10.2016 &nbsp;12354 NEFT &nbsp;2500 DR &nbsp;AXIS(KERALA)&nbsp;AXIS</p> <p>14.10.2016 14.10.2016 &nbsp;12354 NEFT &nbsp;2500 DR &nbsp;AXIS(KERALA)&nbsp;AXIS</p> <p>14.10.2016 14.10.2016 &nbsp;12354 NEFT &nbsp;2500 DR &nbsp;AXIS(KERALA)&nbsp;AXIS</p> <p>14.10.2016 14.10.2016 &nbsp;12354 NEFT &nbsp;2500 DR &nbsp;AXIS(KERALA)&nbsp;AXIS</p> <p>&nbsp;</p> <p>I WANT TO &nbsp;ABOVE RESULT , WHAT TO DO HELP SIR ...</p>https://www.aspforums.net:443/Threads/118224/Perform-Insert-Update-after-SqlBulkCopy-Excel-import-is-completed-using-C/https://www.aspforums.net:443/Threads/118224/Perform-Insert-Update-after-SqlBulkCopy-Excel-import-is-completed-using-C/Tue, 18 Oct 2016 00:10:17 GMTPerform Insert Update after SqlBulkCopy Excel import is completed using C#<p>Hi <a class="username" rel="smuthu"> smuthu</a>,</p> <p>Will you please share the excel sheet data.</p>https://www.aspforums.net:443/Threads/118224/Perform-Insert-Update-after-SqlBulkCopy-Excel-import-is-completed-using-C/https://www.aspforums.net:443/Threads/118224/Perform-Insert-Update-after-SqlBulkCopy-Excel-import-is-completed-using-C/Tue, 18 Oct 2016 02:27:19 GMTPerform Insert Update after SqlBulkCopy Excel import is completed using C#<p>&nbsp;</p> <pre class="brush: csharp">Tran Date Value Date CHQNO Transaction Particulars Amount(MONEY) DR|CR Branch Name 14-10-2016 14-10-2016 - To Transfer/PRI26/25:407300657:0/2520/NS 2520.00 DR MARATHAHALLI, BANGALORE [KT] 14-10-2016 14-10-2016 - TO NEFT/PRI27/25:407300658:0 995659.00 DR MARATHAHALLI, BANGALORE [KT] 14-10-2016 14-10-2016 - TO NEFT/PRI27/25:407300659:0 1200.00 DR MARATHAHALLI, BANGALORE [KT] 14-10-2016 14-10-2016 - TO NEFT/PRI28/25:407300946:0 2734671.00 DR MARATHAHALLI, BANGALORE [KT] </pre> <p>&nbsp;</p>https://www.aspforums.net:443/Threads/118224/Perform-Insert-Update-after-SqlBulkCopy-Excel-import-is-completed-using-C/https://www.aspforums.net:443/Threads/118224/Perform-Insert-Update-after-SqlBulkCopy-Excel-import-is-completed-using-C/Tue, 18 Oct 2016 03:38:41 GMTPerform Insert Update after SqlBulkCopy Excel import is completed using C#<p>Hi <a class="username" rel="smuthu">smuthu</a>,</p> <p>I have modified your code. Refer the below code.</p> <pre class="brush: csharp">protected void upload_Click(object sender, EventArgs e) { //Upload and save the file string excelPath = Server.MapPath(&#34;~/Files/&#34;) + Path.GetFileName(FileUpload1.PostedFile.FileName); FileUpload1.SaveAs(excelPath); string conString = string.Empty; string extension = Path.GetExtension(FileUpload1.PostedFile.FileName); switch (extension) { case &#34;.xls&#34;: //Excel 97-03 conString = ConfigurationManager.ConnectionStrings[&#34;Excel03ConString&#34;].ConnectionString; break; case &#34;.xlsx&#34;: //Excel 07 or higher conString = ConfigurationManager.ConnectionStrings[&#34;Excel07+ConString&#34;].ConnectionString; break; } conString = string.Format(conString, excelPath); using (OleDbConnection excel_con = new OleDbConnection(conString)) { excel_con.Open(); string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0][&#34;TABLE_NAME&#34;].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[8] { new DataColumn(&#34;Tran Date&#34;, typeof(DateTime)), new DataColumn(&#34;Value Date&#34;, typeof(DateTime)), new DataColumn(&#34;CHQNO&#34;,typeof(string)), new DataColumn(&#34;Transaction Particulars&#34;,typeof(string)), new DataColumn(&#34;Amount&#34;,typeof(decimal)), new DataColumn(&#34;DR|CR&#34;,typeof(string)), new DataColumn(&#34;Branch Name&#34;,typeof(string)), new DataColumn(&#34;Bank Name&#34;,typeof(string)) }); dtExcelData.Columns[&#34;Bank Name&#34;].DefaultValue = TextBox1.Text.Trim(); using (OleDbDataAdapter oda = new OleDbDataAdapter(&#34;SELECT * FROM [&#34; + sheet1 + &#34;]&#34;, excel_con)) { oda.Fill(dtExcelData); } excel_con.Close(); string consString = ConfigurationManager.ConnectionStrings[&#34;constr&#34;].ConnectionString; using (SqlConnection con = new SqlConnection(consString)) { using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con)) { //Set the database table name sqlBulkCopy.DestinationTableName = &#34;dbo.tbl_BankTransaction&#34;; //[OPTIONAL]: Map the Excel columns with that of the database table sqlBulkCopy.ColumnMappings.Add(&#34;Tran Date&#34;, &#34;TranDate&#34;); sqlBulkCopy.ColumnMappings.Add(&#34;Value Date&#34;, &#34;ValueDate&#34;); sqlBulkCopy.ColumnMappings.Add(&#34;CHQNO&#34;, &#34;ChekNo&#34;); sqlBulkCopy.ColumnMappings.Add(&#34;Transaction Particulars&#34;, &#34;Remark&#34;); sqlBulkCopy.ColumnMappings.Add(&#34;Amount&#34;, &#34;Amount&#34;); sqlBulkCopy.ColumnMappings.Add(&#34;DR|CR&#34;, &#34;Type&#34;); sqlBulkCopy.ColumnMappings.Add(&#34;Branch Name&#34;, &#34;BranchName&#34;); sqlBulkCopy.ColumnMappings.Add(&#34;Bank Name&#34;, &#34;BankName&#34;); con.Open(); sqlBulkCopy.WriteToServer(dtExcelData); con.Close(); } } } }</pre> <p><span style="text-decoration: underline;">Screenshot</span></p> <p><img src="https://i.imgur.com/6GBtglU.jpg" alt="" width="472" height="305" /></p>https://www.aspforums.net:443/Threads/118224/Perform-Insert-Update-after-SqlBulkCopy-Excel-import-is-completed-using-C/https://www.aspforums.net:443/Threads/118224/Perform-Insert-Update-after-SqlBulkCopy-Excel-import-is-completed-using-C/Tue, 18 Oct 2016 04:17:21 GMTPerform Insert Update after SqlBulkCopy Excel import is completed using C#<p>Hai Sir..,</p> <p>&nbsp;</p> <p>&nbsp;VERY GOOD logical Thinking &nbsp;sir ..</p> <p>&nbsp;</p> <p>very very &nbsp;thank you so much....sir</p>https://www.aspforums.net:443/Threads/118224/Perform-Insert-Update-after-SqlBulkCopy-Excel-import-is-completed-using-C/https://www.aspforums.net:443/Threads/118224/Perform-Insert-Update-after-SqlBulkCopy-Excel-import-is-completed-using-C/Tue, 18 Oct 2016 04:50:56 GMTPerform Insert Update after SqlBulkCopy Excel import is completed using C#Instead of saying thanks mark the reply or replies(if multiple) that helped as Answer.https://www.aspforums.net:443/Threads/118224/Perform-Insert-Update-after-SqlBulkCopy-Excel-import-is-completed-using-C/https://www.aspforums.net:443/Threads/118224/Perform-Insert-Update-after-SqlBulkCopy-Excel-import-is-completed-using-C/Tue, 18 Oct 2016 04:58:52 GMT