protected void btnUploadPo_Click(object sender, EventArgs e) { con1.ConnectionString = ConfigurationManager.ConnectionStrings["elegantcrm7ConnectionString"].ConnectionString; DataSet ds = new DataSet(); DataTable dt = new DataTable(); iProductMasterClient productClient = new iProductMasterClient(); CustomProfile profile = CustomProfile.GetProfile(); Label1.Text = ""; try { if (ddlcompany.SelectedValue != "0" && ddldepartment.SelectedValue != "0") { productClient.DeleteSKUPricetemp(profile.DBConnection._constr); string connString = ""; string path = FileuploadPO.PostedFile.FileName; string strFileType = System.IO.Path.GetExtension(path).ToString().ToLower(); string Fullpath = Server.MapPath("~/Product/ImportPrice/" + path); //if (strFileType.Trim() != ".xls") //{ // Label1.Text = "Please upload only Excel files with .xls extention!"; //} //else //{ Label1.Text = ""; FileuploadPO.PostedFile.SaveAs(Server.MapPath("~/Product/ImportPrice/" + path)); if (strFileType.Trim() == ".xls") { connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Fullpath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\""; } else if (strFileType.Trim() == ".xlsx") { connString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Fullpath + ";Extended Properties='Excel 12.0;HDR=Yes'"; } OleDbConnection excelConnection = new OleDbConnection(connString); OleDbCommand cmd1 = new OleDbCommand("Select * from [Template$]", excelConnection); excelConnection.Open(); OleDbDataReader dReader; dReader = cmd1.ExecuteReader(); SqlBulkCopy sqlBulk = new SqlBulkCopy(con1); con1.Open(); sqlBulk.DestinationTableName = "TempPrice"; sqlBulk.WriteToServer(dReader); excelConnection.Close(); ds = productClient.GetSkuPriceTemp(profile.DBConnection._constr); dt = ds.Tables[0]; if (dt.Rows.Count > 0) { //System.Threading.Thread.Sleep(8000); Label1.Text = "Upload Successful !!!"; lblmessagesuccess.Visible = true; Button12.Enabled = true; Button12.CssClass = "class2"; // adm.UpdateStudenttrim(); } else { WebMsgBox.MsgBox.Show("Upload Not Successful, Please Upload Right Template"); ScriptManager.RegisterStartupScript(this, GetType(), "showalert", "alert('Upload Not Successful,Please upload Right template!');", true); } //adm.UpdateStudenttrim(); //} } else { WebMsgBox.MsgBox.Show("Please Select Company and Department"); } } catch (Exception ex) { ScriptManager.RegisterStartupScript(this, GetType(), "showAlert", "showAlert('" + ex.ToString() + "','Error','#');", true); ScriptManager.RegisterStartupScript(this, GetType(), "showalert", "alert('Exception in Catch!');", true); } finally { con1.Close(); productClient.Close(); } }