protected void grdMain_RowCommand(object sender, GridViewCommandEventArgs e) { string strMsg = string.Empty; if (e.CommandName == "cmddelete") { Int64 UserIdno = Convert.ToInt64(Session["UserIdno"]); GrPrepOntohireDAL obj = new GrPrepOntohireDAL(); Int32 intValue = obj.DeleteHireGrDetail(Convert.ToInt32(e.CommandArgument)); obj = null; if (intValue > 0) { this.BindGrid(); strMsg = "Record deleted successfully."; } else { if (intValue == -1) { strMsg = "Record can not be deleted. It is in use."; } else { strMsg = "Record not deleted."; } } ScriptManager.RegisterStartupScript(this, this.GetType(), "alertstrMsg", "PassMessage('" + strMsg + "')", true); } }
private void BindGrid() { GrPrepOntohireDAL obj = new GrPrepOntohireDAL(); DataTable dtRcptDetl = new DataTable(); dtRcptDetl = obj.SelectGrPrep(Convert.ToInt64(ddldateRange.SelectedValue), txtReceiptDatefrom.Text, txtReceiptDateto.Text, ApplicationFunction.ConnectionString()); if ((dtRcptDetl != null && dtRcptDetl.Rows.Count > 0)) { grdMain.DataSource = dtRcptDetl; grdMain.DataBind(); lblTotalRecord.Text = "T. Record (s): " + dtRcptDetl.Rows.Count; Double TotalNetAmount = 0; for (int i = 0; i < dtRcptDetl.Rows.Count; i++) { TotalNetAmount += Convert.ToDouble((dtRcptDetl.Rows[i]["Total_Amnt"])); } lblNetTotalAmount.Text = TotalNetAmount.ToString("N2"); int startRowOnPage = (grdMain.PageIndex * grdMain.PageSize) + 1; int lastRowOnPage = startRowOnPage + grdMain.Rows.Count - 1; lblcontant.Text = "Showing " + startRowOnPage.ToString() + " - " + lastRowOnPage.ToString() + " of " + dtRcptDetl.Rows.Count.ToString(); lblcontant.Visible = true; divpaging.Visible = true; imgBtnExcel.Visible = true; ViewState["Dt"] = dtRcptDetl; } else { grdMain.DataSource = null; grdMain.DataBind(); lblTotalRecord.Text = "T. Record (s): 0 "; lblcontant.Visible = false; divpaging.Visible = false; imgBtnExcel.Visible = false; } }
protected void lnkbtnUpload_Click(object sender, EventArgs e) { string msg = string.Empty; if (FileUpload.HasFile) { GrPrepOntohireDAL obj = new GrPrepOntohireDAL(); string excelfilename = string.Empty; #region UPLOAD EXCEL AT SERVER excelfilename = ApplicationFunction.UploadFileServerControl(FileUpload, "ItemsGronhire", "Grprepexcel"); #endregion if ((System.IO.Path.GetExtension(excelfilename) == ".xls") || (System.IO.Path.GetExtension(excelfilename) == ".xlsx")) { DataTable dt = new DataTable(); DataTable dtnew = new DataTable(); BindDropdownDAL objDal = new BindDropdownDAL(); string filepath = Server.MapPath(@"~/ItemsGronhire/" + excelfilename); string constring = string.Empty; if (System.IO.Path.GetExtension(filepath) == ".xls") { constring = "Provider=Microsoft.Jet.OLEDB.4.0;OLE DB Services=-4;Data Source='" + filepath + "';Extended Properties=\"Excel 8.0;HDR=Yes;\""; } else if (System.IO.Path.GetExtension(filepath) == ".xlsx") { constring = "Provider= Microsoft.ACE.OLEDB.12.0;OLE DB Services=-4;Data Source='" + filepath + "'; Extended Properties=\"Excel 12.0;HDR=YES;\""; } #region Select Excel OleDbConnection con = new OleDbConnection(constring); con.Open(); DataTable ExcelTable = new DataTable(); ExcelTable = con.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); string SheetName = Convert.ToString(ExcelTable.Rows[0][2]); OleDbDataAdapter da = new OleDbDataAdapter("select * from [" + SheetName + "] WHERE [GRNo] IS NOT NULL OR [GRDate] IS NOT NULL OR [CHDate] IS NOT NULL OR [CHNo] IS NOT NULL OR [From] IS NOT NULL OR [To] IS NOT NULL OR [LrryNo] IS NOT NULL OR [OwnerName] IS NOT NULL OR [PanNo] IS NOT NULL OR [DspQty] IS NOT NULL OR [Rate] IS NOT NULL OR [Amount] IS NOT NULL", con); DataSet ds = new DataSet(); da.Fill(ds); #endregion if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0) { if (ds.Tables[0].Rows.Count > 0 && ds.Tables[0].Columns[0].Caption == "SrNo" && ds.Tables[0].Columns[1].Caption == "GRNo" && ds.Tables[0].Columns[2].Caption == "GRDate" && ds.Tables[0].Columns[3].Caption == "CHDate" && ds.Tables[0].Columns[4].Caption == "CHNo" && ds.Tables[0].Columns[5].Caption == "From" && ds.Tables[0].Columns[6].Caption == "To" && ds.Tables[0].Columns[7].Caption == "LrryNo" && ds.Tables[0].Columns[8].Caption == "OwnerName" && ds.Tables[0].Columns[9].Caption == "PanNo" && ds.Tables[0].Columns[10].Caption == "DspQty" && ds.Tables[0].Columns[11].Caption == "Rate" && ds.Tables[0].Columns[12].Caption == "Amount") { #region INSERT RECORD IN tblChlnUploadFromExcel TABLE Int64 intResult = 0; using (TransactionScope Tran = new TransactionScope(TransactionScopeOption.Required)) { for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { intResult = obj.InsertInGrExcel( Convert.ToDateTime(Convert.ToString(ds.Tables[0].Rows[i]["GRDate"].ToString() == "" ? "" : ds.Tables[0].Rows[i]["GRDate"])), Convert.ToInt64(Convert.ToString(ds.Tables[0].Rows[i]["GRNo"]) == "" ? "0" : ds.Tables[0].Rows[i]["GRNo"]), Convert.ToDateTime(Convert.ToString(ds.Tables[0].Rows[i]["CHDate"].ToString() == "" ? "" : ds.Tables[0].Rows[i]["CHDate"])), Convert.ToInt64(Convert.ToString(ds.Tables[0].Rows[i]["CHNo"]) == "" ? "0" : ds.Tables[0].Rows[i]["CHNo"]), Convert.ToString(Convert.ToString(ds.Tables[0].Rows[i]["From"]) == "" ? "" : ds.Tables[0].Rows[i]["From"]), Convert.ToString(Convert.ToString(ds.Tables[0].Rows[i]["To"]) == "" ? "" : ds.Tables[0].Rows[i]["To"]), Convert.ToString(Convert.ToString(ds.Tables[0].Rows[i]["LrryNo"]) == "" ? "" : ds.Tables[0].Rows[i]["LrryNo"]), Convert.ToString(Convert.ToString(ds.Tables[0].Rows[i]["OwnerName"]) == "" ? "" : ds.Tables[0].Rows[i]["OwnerName"]), Convert.ToString(Convert.ToString(ds.Tables[0].Rows[i]["PanNo"]) == "" ? "" : ds.Tables[0].Rows[i]["PanNo"]), Convert.ToInt64(Convert.ToString(ds.Tables[0].Rows[i]["DspQty"]) == "" ? "0" : ds.Tables[0].Rows[i]["DspQty"]), Convert.ToDouble(Convert.ToString(ds.Tables[0].Rows[i]["Rate"]) == "" ? "" : ds.Tables[0].Rows[i]["Rate"]), Convert.ToDouble(Convert.ToString(ds.Tables[0].Rows[i]["Amount"]) == "" ? "" : ds.Tables[0].Rows[i]["Amount"]) , Convert.ToInt64(ddldateRange.SelectedValue == "" ? "0" : ddldateRange.SelectedValue)); } if (intResult > 0) { msg = "Excel uploaded successfully"; ShowMessage(msg); Tran.Complete(); } else { Tran.Dispose(); } } #endregion } else { msg = "Excel is blank or Excel is not in correct format."; ShowMessageErr(msg); return; } } } else { msg = "Please Upload Correct Excel File"; ShowMessageErr(msg); return; } } }