protected void BtnSheet_Click(object sender, EventArgs e) { string ProjectID = Request.QueryString["projectID"]; Project.ProjectDataTable p = projectBLL.GetDataByID(ProjectID); Project.ProjectRow row = p.Rows[0] as Project.ProjectRow; String originalJobSheetPath = Server.MapPath("JobSheet_2018-12-09.xls"); String modifiedJobSheetPath = Server.MapPath("newJobsheet1.xls"); File.Copy(originalJobSheetPath, modifiedJobSheetPath, true); String conn = String.Format(ExcelConnectionString, modifiedJobSheetPath); String updateQuery = "update [Project Sheet${0}:{0}] set F1 = \"{1}\""; using (OleDbConnection connection = new OleDbConnection(conn)) { connection.Open(); OleDbCommand cmd = new OleDbCommand { Connection = connection }; if (!row.IsProject_CodeNull()) { cmd.CommandText = String.Format(updateQuery, "G5", row.Project_Code); cmd.ExecuteNonQuery(); } var introducer = row["Introducer"].ToString(); if (!string.IsNullOrWhiteSpace(introducer)) { cmd.CommandText = String.Format(updateQuery, "G7", introducer); cmd.ExecuteNonQuery(); } var sectors = row["SectorList"].ToString(); if (!string.IsNullOrWhiteSpace(sectors)) { cmd.CommandText = String.Format(updateQuery, "G8", sectors); cmd.ExecuteNonQuery(); } var startDate = row["StartDate"].ToString(); if (!string.IsNullOrWhiteSpace(startDate)) { var formattedStartDate = Convert.ToDateTime(startDate).ToShortDateString(); cmd.CommandText = String.Format(updateQuery, "G10", formattedStartDate); cmd.ExecuteNonQuery(); } if (!row.IsStatusNull()) { cmd.CommandText = String.Format(updateQuery, "G11", row.Status.Trim()); cmd.ExecuteNonQuery(); } if (!row.IsDescriptionNull()) { cmd.CommandText = String.Format(updateQuery, "G13", row.Description); cmd.ExecuteNonQuery(); } var clientCompanyName = row["ClientCompanyName"].ToString(); if (!string.IsNullOrWhiteSpace(clientCompanyName)) { cmd.CommandText = String.Format(updateQuery, "G18", clientCompanyName); cmd.ExecuteNonQuery(); } var clientType = row["ClientType"].ToString(); if (!string.IsNullOrWhiteSpace(clientType)) { cmd.CommandText = String.Format(updateQuery, "G20", clientType); cmd.ExecuteNonQuery(); } if (!row.IsContactNull()) { cmd.CommandText = String.Format(updateQuery, "A23", row.Contact); cmd.ExecuteNonQuery(); } var clientAddressFields = GetAddressFieldNames("Client", row); var rowIndex = 26; foreach (var field in clientAddressFields) { cmd.CommandText = String.Format(updateQuery, "A" + rowIndex.ToString(), field); cmd.ExecuteNonQuery(); rowIndex++; } var invoiceContact = row["InvoiceContact"].ToString(); if (!string.IsNullOrWhiteSpace(invoiceContact)) { cmd.CommandText = String.Format(updateQuery, "G23", invoiceContact); cmd.ExecuteNonQuery(); } var invoiceAddressFields = GetAddressFieldNames("Invoice", row); rowIndex = 26; foreach (var field in invoiceAddressFields) { cmd.CommandText = String.Format(updateQuery, "G" + rowIndex.ToString(), field); cmd.ExecuteNonQuery(); rowIndex++; } connection.Close(); } Response.Redirect("newJobsheet1.xls"); }