protected void btn_Submit_Click(object sender, EventArgs e) { try { //Deleting Blank Lines of Gridview DataTable dtCurrentTable = (DataTable)ViewState["CurrentTable"]; Boolean flag = true; for (int i = dtCurrentTable.Rows.Count - 1; i >= 0; i--) { if (dtCurrentTable.Rows[i]["CustomerName"].ToString() == string.Empty && dtCurrentTable.Rows[i]["Location"].ToString() == string.Empty && dtCurrentTable.Rows[i]["ItemList"].ToString() == Convert.ToString(0)) { dtCurrentTable.Rows[i].Delete(); } else if (dtCurrentTable.Rows[i]["CustomerName"].ToString() == string.Empty && dtCurrentTable.Rows[i]["ItemList"].ToString() == Convert.ToString(0)) { dtCurrentTable.Rows[i].Delete(); } } dtCurrentTable.AcceptChanges(); DataTable dt = dtCurrentTable; //Deleting Blank Lines of Gridview //Checked validations of Gridview fields for (int i = 0; i < dtCurrentTable.Rows.Count; i++) { TextBox lb_CustomerName = (TextBox)Gridview3.Rows[i].Cells[1].FindControl("lb_CustomerName"); TextBox lb_Location = (TextBox)Gridview3.Rows[i].Cells[2].FindControl("lb_Location"); TextBox lb_Quantity = (TextBox)Gridview3.Rows[i].Cells[7].FindControl("lb_Quantity"); DropDownList ddlMonth = (DropDownList)Gridview3.Rows[i].Cells[8].FindControl("ddlMonth"); DropDownList ddl_ItemList = (DropDownList)Gridview3.Rows[i].Cells[4].FindControl("ddl_ItemList"); if (lb_CustomerName.Text == string.Empty) { var message1 = new JavaScriptSerializer().Serialize("Please enter Customer Name."); var script1 = string.Format("alert({0});", message1); ScriptManager.RegisterClientScriptBlock(Page, Page.GetType(), "", script1, true); //ClientScript.RegisterStartupScript(this.GetType(), "alert", "ShowPopup();", true); //this.lblMessage.Text = "Please enter Customer Name."; flag = false; lb_CustomerName.Focus(); break; } else if (lb_Location.Text == string.Empty) { var message1 = new JavaScriptSerializer().Serialize("Please enter Location."); var script1 = string.Format("alert({0});", message1); ScriptManager.RegisterClientScriptBlock(Page, Page.GetType(), "", script1, true); flag = false; lb_Location.Focus(); break; } else if (ddl_ItemList.SelectedIndex == 0) { var message1 = new JavaScriptSerializer().Serialize("Please Select Product."); var script1 = string.Format("alert({0});", message1); ScriptManager.RegisterClientScriptBlock(Page, Page.GetType(), "", script1, true); ddl_ItemList.Focus(); flag = false; break; } else if (lb_Quantity.Text == string.Empty && lb_Quantity.Text == "0") { var message1 = new JavaScriptSerializer().Serialize("Please enter Quantity."); var script1 = string.Format("alert({0});", message1); ScriptManager.RegisterClientScriptBlock(Page, Page.GetType(), "", script1, true); lb_Quantity.Focus(); flag = false; break; } else if (ddlMonth.SelectedValue == "0") { var message1 = new JavaScriptSerializer().Serialize("Please select Month."); var script1 = string.Format("alert({0});", message1); ScriptManager.RegisterClientScriptBlock(Page, Page.GetType(), "", script1, true); ddlMonth.Focus(); flag = false; break; } } //Checked validations of Gridview fields if (flag == true) { //Created Lines in SQL InternalDB Database's [Secondary Sales Details] Table string ItemNo = string.Empty; string Description = string.Empty; String strConnString = ConfigurationManager.ConnectionStrings["WA_JAYCHEM_INTDB"].ConnectionString; using (SqlConnection con = new SqlConnection(strConnString)) { for (int i = 0; i < dtCurrentTable.Rows.Count; i++) { SqlCommand cmd = new SqlCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "SP_SecondarySalesDetails"; String[] CodeAndDesc = dtCurrentTable.Rows[i]["ItemList"].ToString().Split('-'); if (CodeAndDesc.Length >= 1) { ItemNo = CodeAndDesc[0].Trim(); Description = CodeAndDesc[1].Trim(); } //string[] sDate = Convert.ToString(dtCurrentTable.Rows[i]["Date"]).Split('/'); //string sDateTime = sDate[1] + '/' + sDate[2]; string st = "01/" + dtCurrentTable.Rows[i]["Month"] + "/" + DateTime.Now.Year.ToString(); DateTime dtt = Convert.ToDateTime(st); DateTimeFormatInfo dinfo = new DateTimeFormatInfo(); string MonthYear = dinfo.GetMonthName(Convert.ToInt16(dtCurrentTable.Rows[i]["Month"])) + "-" + DateTime.Now.Year.ToString(); cmd.Parameters.Add("@Date", SqlDbType.DateTime).Value = Convert.ToDateTime(dtt); cmd.Parameters.Add("@CustomerName", SqlDbType.VarChar).Value = Convert.ToString(dtCurrentTable.Rows[i]["CustomerName"]); cmd.Parameters.Add("@Location", SqlDbType.VarChar).Value = Convert.ToString(dtCurrentTable.Rows[i]["Location"]); cmd.Parameters.Add("@ItemNo", SqlDbType.VarChar).Value = Convert.ToString(ItemNo); cmd.Parameters.Add("@Description", SqlDbType.VarChar).Value = Convert.ToString(Description); cmd.Parameters.Add("@ItemCategoryCode", SqlDbType.VarChar).Value = Convert.ToString(dtCurrentTable.Rows[i]["ProductCategory"]); cmd.Parameters.Add("@Range", SqlDbType.VarChar).Value = Convert.ToString(dtCurrentTable.Rows[i]["Range"]); cmd.Parameters.Add("@Quantity", SqlDbType.VarChar).Value = Convert.ToDecimal(dtCurrentTable.Rows[i]["Quantity"]); cmd.Parameters.Add("@MonthYear", SqlDbType.VarChar).Value = Convert.ToString(MonthYear); cmd.Connection = con; con.Open(); cmd.ExecuteNonQuery(); con.Close(); } con.Dispose(); } //Created Lines in SQL InternalDB Database's [Secondary Sales Details] Table //Deleted SecondarySales.xls files if exist in Shared Folder string path = ConfigurationManager.AppSettings["FilePath"] + "SecondarySales.xls"; FileInfo file = new FileInfo(path); if (file.Exists)//check file exsit or not { file.Delete(); } //Deleted SecondarySales.xls files if exist in Shared Folder //Generated SecondarySales.xls file in Shared Folder with Design of excel string Desc = string.Empty; StreamWriter wr = new StreamWriter(ConfigurationManager.AppSettings["FilePath"] + "SecondarySales.xls"); try { wr.Write("<HTML><HEAD>"); wr.Write("<style> {font-family:Verdana; font-size: 11px; } </style>"); wr.Write("</HEAD><BODY>"); wr.Write("<TABLE border='1' width='100%' >"); wr.Write("<TR>"); for (int i = 0; i < dt.Columns.Count; i++) { wr.Write("<TD align='center' style='color: White; font-weight: bold;background-color:#8A0886;font-size: 12px'>"); wr.Write(dt.Columns[i].ToString().ToUpper() + "\t"); wr.Write("</TD>"); } wr.Write("</TR>"); wr.WriteLine(); for (int i = 0; i < (dt.Rows.Count); i++) { wr.Write("<TR>"); for (int j = 0; j < dt.Columns.Count; j++) { wr.Write("<style> {font-family:Verdana; font-size: 11px; } </style>"); wr.Write("<TD align='center'>"); if (dt.Rows[i][j] != null) { if (dt.Rows[i][j] == dt.Rows[i][4]) { String[] CodeAndDesc = dt.Rows[i][4].ToString().Split('-'); if (CodeAndDesc.Length >= 1) { ItemNo = CodeAndDesc[0]; Desc = CodeAndDesc[1].Trim(); } wr.Write(Convert.ToString(Desc).ToUpper() + "\t"); } else if (dt.Rows[i][j] == dt.Rows[i][8]) { DateTimeFormatInfo dinfo = new DateTimeFormatInfo(); string MonthYear12 = Convert.ToString(dinfo.GetMonthName(Convert.ToInt16(dt.Rows[i]["Month"])));// + "-" + DateTime.Now.Year.ToString() wr.Write((MonthYear12).ToUpper() + "-" + DateTime.Now.Year.ToString() + "\t"); } else if (dt.Rows[i][j] == dt.Rows[i][3]) { wr.Write(ItemNo + "\t"); } else { wr.Write(Convert.ToString(dt.Rows[i][j]).ToUpper() + "\t"); } } else { wr.Write("\t"); } wr.WriteLine(); wr.Write("</TD>"); } wr.Write("</TR>"); } wr.Write("</TABLE>"); wr.Write("</BODY>"); wr.Close(); //Generated SecondarySales.xls file in Shared Folder with Design of excel //Mailing to Agents Email as well as General Ledger's Secondary Sales Internal Email NetworkCredential NetCredentials = new NetworkCredential(); NetCredentials.UserName = ConfigurationManager.AppSettings["UserName"]; NetCredentials.Password = ConfigurationManager.AppSettings["Password"]; Web_Order_Mail objser1 = new Web_Order_Mail(); objser1.UseDefaultCredentials = true; objser1.Credentials = NetCredentials; objser1.SendMailForSecondarySalesExcel(ConfigurationManager.AppSettings["FilePath"] + "SecondarySales.xls", Convert.ToString(Request["CompanyCode"])); objser1 = null; } catch (Exception ex) { var message = new JavaScriptSerializer().Serialize(ex.Message.ToString()); var script = string.Format("alert({0});", message); ScriptManager.RegisterClientScriptBlock(Page, Page.GetType(), "", script, true); } var message1 = new JavaScriptSerializer().Serialize("Mail Sent successfully."); var script1 = string.Format("alert({0});window.location='DashBoard.aspx';", message1); ScriptManager.RegisterClientScriptBlock(Page, Page.GetType(), "", script1, true); } } catch (Exception ex) { var message = new JavaScriptSerializer().Serialize(ex.Message.ToString()); var script = string.Format("alert({0});", message); ScriptManager.RegisterClientScriptBlock(Page, Page.GetType(), "", script, true); } }