private void button2_Click(object sender, EventArgs e) { if (string.IsNullOrEmpty(annualTxt.Text)) { MessageBox.Show("", "Please input the annual projection value !", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } string test = ""; try { test = Annual.List("SELECT * from budget WHERE year='" + yearCbx.Text + "' AND weekly = '" + weeklyTxt.Text + "'").First().Annuals.ToString(); } catch (Exception y) { // Helper.Exceptions(y.Message, "on adding inventory auto fill the category list selected item"); } if (!string.IsNullOrEmpty(test)) { MessageBox.Show("Value inserted !"); return; } string ID = Guid.NewGuid().ToString(); Annual i = new Annual(ID, yearCbx.Text, Convert.ToDouble(annualTxt.Text), Convert.ToDouble(weeklyTxt.Text)); DBConnect.Insert(i); MessageBox.Show("Information Saved"); annualTxt.Text = ""; weeklyTxt.Text = ""; }
private void button6_Click(object sender, EventArgs e) { if (nameTxt.Text == "") { nameTxt.BackColor = Color.Red; return; } MemoryStream stream = ImageToStream(imgCapture.Image, System.Drawing.Imaging.ImageFormat.Jpeg); string fullimage = ImageToBase64(stream); if (!string.IsNullOrEmpty(existingID)) { if (MessageBox.Show("YES or No?", "Are you sure you want to update the current existing information ? ", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes) { Staff j = new Staff(existingID, nameTxt.Text, contactTxt.Text, departmentCbx.Text, fullimage, emailTxt.Text); DBConnect.Update(j, existingID); existingID = ""; return; } } existingID = ""; string id = Guid.NewGuid().ToString(); Staff _user = new Staff(id, nameTxt.Text, contactTxt.Text, departmentCbx.Text, fullimage, emailTxt.Text); if (DBConnect.Insert(_user) != "") { MessageBox.Show("Information Saved"); this.DialogResult = DialogResult.OK; this.Dispose(); } }
private void button4_Click(object sender, EventArgs e) { if (string.IsNullOrEmpty(amountTxt.Text)) { amountTxt.BackColor = Color.Red; return; } if (string.IsNullOrEmpty(itemTxt.Text)) { itemTxt.BackColor = Color.Red; return; } if (!string.IsNullOrEmpty(existingID)) { if (MessageBox.Show("YES or No?", "Are you sure you want to update the current existing information ? ", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes) { Expense j = new Expense(existingID, Convert.ToDateTime(dateTxt.Text).Year.ToString(), Convert.ToInt32(weekLbl.Text), startLbl.Text, endLbl.Text, itemTxt.Text, categoryTxt.Text, Convert.ToDouble(amountTxt.Text), month); DBConnect.Update(j, existingID); existingID = ""; } return; } existingID = ""; string ID = Guid.NewGuid().ToString(); Expense i = new Expense(ID, Convert.ToDateTime(dateTxt.Text).Year.ToString(), Convert.ToInt32(weekLbl.Text), startLbl.Text, endLbl.Text, itemTxt.Text, categoryTxt.Text, Convert.ToDouble(amountTxt.Text), month); DBConnect.Insert(i); MessageBox.Show("Information Saved "); itemTxt.Text = ""; amountTxt.Text = ""; autocompleteCategory(); autocomplete(); }
private void button4_Click(object sender, EventArgs e) { if (string.IsNullOrEmpty(endTxt.Text)) { endTxt.BackColor = Color.Red; return; } if (string.IsNullOrEmpty(categoryTxt.Text)) { categoryTxt.BackColor = Color.Red; return; } if (!string.IsNullOrEmpty(existingID)) { if (MessageBox.Show("YES or No?", "Are you sure you want to update the current existing information ? ", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes) { Cogs j = new Cogs(existingID, Convert.ToDateTime(dateTxt.Text).Year.ToString(), Convert.ToInt32(weekLbl.Text), startLbl.Text, endLbl.Text, categoryTxt.Text, Convert.ToDouble(begTxt.Text), Convert.ToDouble(endTxt.Text), Convert.ToDouble(cogsTxt.Text), month); DBConnect.Update(j, existingID); existingID = ""; return; } else { return; } } existingID = ""; string ID = Guid.NewGuid().ToString(); Cogs i = new Cogs(ID, Convert.ToDateTime(dateTxt.Text).Year.ToString(), Convert.ToInt32(weekLbl.Text), startLbl.Text, endLbl.Text, categoryTxt.Text, Convert.ToDouble(begTxt.Text), Convert.ToDouble(endTxt.Text), Convert.ToDouble(cogsTxt.Text), month); DBConnect.Insert(i); MessageBox.Show("Information Saved "); categoryTxt.Text = ""; endTxt.Text = ""; }
private void button4_Click(object sender, EventArgs e) { if (string.IsNullOrEmpty(amountTxt.Text)) { amountTxt.BackColor = Color.Red; return; } if (string.IsNullOrEmpty(itemTxt.Text)) { itemTxt.BackColor = Color.Red; return; } if (!string.IsNullOrEmpty(existingID)) { if (MessageBox.Show("YES or No?", "Are you sure you want to update the current existing information ? ", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes) { Sale j = new Sale(existingID, Convert.ToDateTime(dateTxt.Text).Year.ToString(), Convert.ToInt32(weekLbl.Text), startLbl.Text, endLbl.Text, itemTxt.Text, Convert.ToDouble(amountTxt.Text), itemTxt.Text, month); DBConnect.Update(j, existingID); existingID = ""; return; } else { return; } } else { string test = ""; try { test = Sale.List("SELECT * from sale WHERE item='" + itemTxt.Text + "' AND week = '" + weekLbl.Text + "' AND date = '" + Convert.ToDateTime(dateTxt.Text).Year.ToString() + "'").First().Amount.ToString(); } catch (Exception y) { // Helper.Exceptions(y.Message, "on adding inventory auto fill the category list selected item"); } if (!string.IsNullOrEmpty(test)) { MessageBox.Show("Value seems to be inserted already"); return; } Helper.CurrentWeek = Convert.ToInt32(weekLbl.Text); Helper.CurrentStarting = startLbl.Text; Helper.CurrentEnding = endLbl.Text; existingID = ""; string ID = Guid.NewGuid().ToString(); Sale i = new Sale(ID, Convert.ToDateTime(dateTxt.Text).Year.ToString(), Convert.ToInt32(weekLbl.Text), startLbl.Text, endLbl.Text, itemTxt.Text, Convert.ToDouble(amountTxt.Text), itemTxt.Text, month); DBConnect.Insert(i); MessageBox.Show("Information Saved "); itemTxt.Text = ""; amountTxt.Text = ""; } }
private void button4_Click(object sender, EventArgs e) { if (string.IsNullOrEmpty(annualTxt.Text)) { annualTxt.BackColor = Color.Red; return; } string ID = Guid.NewGuid().ToString(); Salary i = new Salary(ID, staffTxt.Text, departmentCbx.Text, categoryCbx.Text, Convert.ToDouble(annualTxt.Text), Convert.ToDouble(weeklyTxt.Text), Convert.ToDouble(biweeklyTxt.Text), Convert.ToDouble(0), Convert.ToDouble(ourTxt.Text)); DBConnect.Insert(i); MessageBox.Show("Information Saved "); annualTxt.Text = ""; autocompleteDepartment(); }
private void button7_Click(object sender, EventArgs e) { if (Helper.UserLevel < 3) { MessageBox.Show("Access Denied !"); return; } if (nameTxt.Text == "") { nameTxt.BackColor = Color.Red; return; } if (levelCbx.Text == "") { levelCbx.BackColor = Color.Red; return; } if (contactTxt.Text == "") { contactTxt.BackColor = Color.Red; return; } if (pass2Txt.Text != passTxt.Text) { MessageBox.Show("Passwords do not match"); passTxt.BackColor = Color.Red; return; } if (String.IsNullOrEmpty(passTxt.Text)) { passTxt.BackColor = Color.Red; return; } MemoryStream stream = Helper.ImageToStream(imgCapture.Image, System.Drawing.Imaging.ImageFormat.Jpeg); string fullimage = Helper.ImageToBase64(stream); string id = Guid.NewGuid().ToString(); User _user = new User(id, nameTxt.Text, contactTxt.Text, Helper.MD5Hash(pass2Txt.Text), fullimage, Convert.ToInt32(levelCbx.Text)); if (DBConnect.Insert(_user) != "") { MessageBox.Show("Information Saved"); this.DialogResult = DialogResult.OK; this.Dispose(); } }
private void button2_Click_1(object sender, EventArgs e) { if (string.IsNullOrEmpty(pctTxt.Text)) { pctTxt.BackColor = Color.Red; return; } if (string.IsNullOrEmpty(itemTxt.Text)) { itemTxt.BackColor = Color.Red; return; } string ID = Guid.NewGuid().ToString(); Budget i = new Budget(ID, itemTxt.Text, CategoryCbx.Text, CategoryCbx.Text, Convert.ToDouble(pctTxt.Text), 0, yearTxt.Text); DBConnect.Insert(i); MessageBox.Show("Information Saved "); pctTxt.Text = ""; }
private void button2_Click(object sender, EventArgs e) { if (nameTxt.Text == "") { nameTxt.BackColor = Color.Red; return; } MemoryStream stream = Helper.ImageToStream(imgCapture.Image, System.Drawing.Imaging.ImageFormat.Jpeg); string fullimage = Helper.ImageToBase64(stream); string id = Guid.NewGuid().ToString(); Company _company = new Company(id, nameTxt.Text, addressTxt.Text, fullimage, currentTxt.Text); if (DBConnect.Insert(_company) != "") { MessageBox.Show("Information Saved"); this.DialogResult = DialogResult.OK; this.Dispose(); } }
private void button5_Click(object sender, EventArgs e) { if (startHrTxt.Text == "" || endHrTxt.Text == "") { MessageBox.Show("Please input the start time and end time for the meeting /schedule "); return; } string ID = Guid.NewGuid().ToString(); var start = Convert.ToDateTime(this.openedDate.Text).ToString("yyyy-MM-dd") + "T" + this.startHrTxt.Text + ":" + startMinTxt.Text + ":00"; var end = Convert.ToDateTime(this.openedDate.Text).ToString("yyyy-MM-dd") + "T" + this.endHrTxt.Text + ":" + endMinTxt.Text + ":00"; string priority = "Medium"; if (!String.IsNullOrEmpty(priorityCbx.Text)) { priority = priorityCbx.Text; } Events _event = new Events(ID, Helper.CleanString(this.detailsTxt.Text), start, end, "", "", DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss"), "", "due", "", Convert.ToDateTime(this.openedDate.Text).ToString("yyyy-MM-dd"), "", priority, DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss"), "f", "", "", "", "", "0", ""); DBConnect.Insert(_event); MessageBox.Show("Information saved" + start + " to" + end); }
public void ReadExcel(string fileName, string fileExt) { string conn = string.Empty; DataTable dtexcel = new DataTable(); if (fileExt.CompareTo(".xls") == 0 || fileExt.CompareTo(".xlsx") == 0) { conn = @"provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties='Excel 8.0;HRD=Yes;IMEX=1';"; //for below excel 2007 } else { conn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0;HDR=NO';"; //for above excel 2007 } using (OleDbConnection con = new OleDbConnection(conn)) { //try //{ OleDbDataAdapter oleAdpt = new OleDbDataAdapter("select * from [Sheet1$]", con); //here we read data from sheet1 oleAdpt.Fill(dtexcel); //fill excel data into dataTable int h = 1; //for (int u = 1; u < dtexcel.Columns.Count; u++) for (int u = 1; u < 53; u++) { Console.WriteLine("NEW WEEK :" + h); DateTime startWeek = Helper.FirstDateOfWeek(Convert.ToInt32(Helper.CurrentYear), h - 1); foreach (DataRow row in dtexcel.Rows) { //MessageBox.Show(); if (!string.IsNullOrEmpty(row[0].ToString()) && !string.IsNullOrEmpty(row[u].ToString())) { if (categoryTxt.Text.Contains("COGS")) { if (row[0].ToString().Contains("Beginning")) { begs = row[u].ToString(); } if (row[0].ToString().Contains("Ending")) { ends = row[u].ToString(); } if (row[0].ToString().Contains("Cost") || row[0].ToString().Contains("Total")) { cogs = row[u].ToString(); } Console.WriteLine("this is the line " + row[0].ToString() + " Value" + row[u].ToString()); try { existingID = Cogs.List("SELECT * from cogs WHERE category='" + subcategory.Text + "' AND week = '" + h + "' AND date = '" + Convert.ToDateTime(dateTxt.Text).Year.ToString() + "'").First().Id.ToString(); string Query = "DELETE from cogs WHERE id ='" + existingID + "'"; DBConnect.save(Query); Console.WriteLine("DELETE: " + row[0].ToString() + " " + row[u].ToString() + " Week:" + h + " Week starting " + startWeek.ToString("dd-MM-yyyy") + " Week ending " + startWeek.AddDays(7).ToString("dd-MM-yyyy")); } catch { existingID = ""; } if (!string.IsNullOrEmpty(cogs) && cogs != "0") { string ID = Guid.NewGuid().ToString(); Cogs i = new Cogs(ID, Convert.ToDateTime(dateTxt.Text).Year.ToString(), h, startWeek.ToString("dd-MM-yyyy"), startWeek.AddDays(7).ToString("dd-MM-yyyy"), subcategory.Text, Convert.ToDouble(begs), Convert.ToDouble(ends), Convert.ToDouble(cogs), startWeek.AddDays(7).ToString("MMMM")); DBConnect.Insert(i); } } // Console.WriteLine(row[0].ToString() + " " + row[u].ToString() + " Week:" + h + " Week starting " + startWeek.ToString("dd-MM-yyyy") + " Week ending " + startWeek.AddDays(7).ToString("dd-MM-yyyy")); if (!row[0].ToString().Contains("Total") && !row[0].ToString().Contains("Week") && !row[0].ToString().Contains("Week:")) { if (categoryTxt.Text.Contains("Complimentary")) { existingID = ""; try { existingID = Comp.List("SELECT * from comp WHERE item='" + row[0].ToString().Replace("\"", string.Empty).Replace("'", string.Empty) + "' AND week = '" + h + "' AND date = '" + Convert.ToDateTime(dateTxt.Text).Year.ToString() + "'").First().Id.ToString(); string Query = "DELETE from comp WHERE id ='" + existingID + "'"; DBConnect.save(Query); } catch { existingID = ""; } string ID = Guid.NewGuid().ToString(); Comp i = new Comp(ID, Convert.ToDateTime(dateTxt.Text).Year.ToString(), h, startWeek.ToString("dd-MM-yyyy"), startWeek.AddDays(7).ToString("dd-MM-yyyy"), row[0].ToString().Replace("\"", string.Empty).Replace("'", string.Empty), Convert.ToDouble(row[u].ToString()), startWeek.AddDays(7).ToString("MMMM")); DBConnect.Insert(i); } if (categoryTxt.Text.Contains("Purchases")) { existingID = ""; try { existingID = Inventory.List("SELECT * from inventory WHERE item='" + row[0].ToString().Replace("\"", string.Empty).Replace("'", string.Empty) + "' AND week = '" + h + "' AND date = '" + Convert.ToDateTime(dateTxt.Text).Year.ToString() + "'").First().Id.ToString(); string Query = "DELETE from inventory WHERE id ='" + existingID + "'"; DBConnect.save(Query); } catch { existingID = ""; } string ID = Guid.NewGuid().ToString(); Inventory i = new Inventory(ID, Convert.ToDateTime(dateTxt.Text).Year.ToString(), h, startWeek.ToString("dd-MM-yyyy"), startWeek.AddDays(7).ToString("dd-MM-yyyy"), row[0].ToString().Replace("\"", string.Empty).Replace("'", string.Empty), subcategory.Text, Convert.ToDouble(row[u].ToString()), 0, 0, 0, startWeek.AddDays(7).ToString("MMMM"), startWeek.AddDays(7).ToString("dd-MM-yyyy")); DBConnect.Insert(i); } if (categoryTxt.Text.Contains("Sales")) { existingID = ""; try { existingID = Sale.List("SELECT * from sale WHERE item='" + row[0].ToString().Replace("\"", string.Empty).Replace("'", string.Empty) + "' AND week = '" + h + "' AND date = '" + Convert.ToDateTime(dateTxt.Text).Year.ToString() + "'").First().Id.ToString(); string Query = "DELETE from sale WHERE id ='" + existingID + "'"; DBConnect.save(Query); } catch { existingID = ""; } string ID = Guid.NewGuid().ToString(); Sale i = new Sale(ID, Convert.ToDateTime(dateTxt.Text).Year.ToString(), h, startWeek.ToString("dd-MM-yyyy"), startWeek.AddDays(7).ToString("dd-MM-yyyy"), row[0].ToString().Replace("\"", string.Empty).Replace("'", string.Empty), Convert.ToDouble(row[u].ToString()), row[0].ToString().Replace("\"", string.Empty).Replace("'", string.Empty), startWeek.AddDays(7).ToString("MMMM")); DBConnect.Insert(i); } if (categoryTxt.Text.Contains("Labor")) { existingID = ""; try { existingID = Labor.List("SELECT * from labor WHERE department='" + row[0].ToString().Replace("\"", string.Empty).Replace("'", string.Empty) + "' AND week = '" + h + "' AND date = '" + Convert.ToDateTime(dateTxt.Text).Year.ToString() + "'").First().Id.ToString(); string Query = "DELETE from labor WHERE id ='" + existingID + "'"; DBConnect.save(Query); } catch { existingID = ""; } string ID = Guid.NewGuid().ToString(); Labor i = new Labor(ID, Convert.ToDateTime(dateTxt.Text).Year.ToString(), h, startWeek.ToString("dd-MM-yyyy"), startWeek.AddDays(7).ToString("dd-MM-yyyy"), row[0].ToString().Replace("\"", string.Empty).Replace("'", string.Empty), Convert.ToDouble(row[u].ToString()), startWeek.AddDays(7).ToString("MMMM")); DBConnect.Insert(i); } if (categoryTxt.Text.Contains("Payroll")) { existingID = ""; try { existingID = Taxes.List("SELECT * from taxes WHERE name='" + row[0].ToString().Replace("\"", string.Empty).Replace("'", string.Empty) + "' AND week = '" + h + "' AND date = '" + Convert.ToDateTime(dateTxt.Text).Year.ToString() + "'").First().Id.ToString(); string Query = "DELETE from taxes WHERE id ='" + existingID + "'"; DBConnect.save(Query); } catch { existingID = ""; } string ID = Guid.NewGuid().ToString(); Taxes i = new Taxes(ID, Convert.ToDateTime(dateTxt.Text).Year.ToString(), h, startWeek.ToString("dd-MM-yyyy"), startWeek.AddDays(7).ToString("dd-MM-yyyy"), row[0].ToString().Replace("\"", string.Empty).Replace("'", string.Empty), Convert.ToDouble(row[u].ToString()), startWeek.AddDays(7).ToString("MMMM")); DBConnect.Insert(i); } if (categoryTxt.Text.Contains("Supplies")) { existingID = ""; try { existingID = Supplies.List("SELECT * from supplies WHERE supplier='" + row[0].ToString().Replace("\"", string.Empty).Replace("'", string.Empty) + "' AND week = '" + h + "' AND date = '" + Convert.ToDateTime(dateTxt.Text).Year.ToString() + "'").First().Id.ToString(); string Query = "DELETE from supplies WHERE id ='" + existingID + "'"; DBConnect.save(Query); } catch { existingID = ""; } string ID = Guid.NewGuid().ToString(); Supplies i = new Supplies(ID, Convert.ToDateTime(dateTxt.Text).Year.ToString(), h, startWeek.ToString("dd-MM-yyyy"), startWeek.AddDays(7).ToString("dd-MM-yyyy"), row[0].ToString().Replace("\"", string.Empty).Replace("'", string.Empty), Convert.ToDouble(row[u].ToString()), startWeek.AddDays(7).ToString("MMMM")); DBConnect.Insert(i); } if (categoryTxt.Text.Contains("Repairs")) { existingID = ""; try { existingID = Repair.List("SELECT * from repair WHERE supplier='" + row[0].ToString().Replace("\"", string.Empty).Replace("'", string.Empty) + "' AND week = '" + h + "' AND date = '" + Convert.ToDateTime(dateTxt.Text).Year.ToString() + "'").First().Id.ToString(); string Query = "DELETE from repair WHERE id ='" + existingID + "'"; DBConnect.save(Query); } catch { existingID = ""; } string ID = Guid.NewGuid().ToString(); Repair i = new Repair(ID, Convert.ToDateTime(dateTxt.Text).Year.ToString(), h, startWeek.ToString("dd-MM-yyyy"), startWeek.AddDays(7).ToString("dd-MM-yyyy"), row[0].ToString().Replace("\"", string.Empty).Replace("'", string.Empty), Convert.ToDouble(row[u].ToString()), startWeek.AddDays(7).ToString("MMMM")); DBConnect.Insert(i); } if (categoryTxt.Text.Contains("Equipment")) { existingID = ""; try { existingID = Equipment.List("SELECT * from equipment WHERE name='" + row[0].ToString().Replace("\"", string.Empty).Replace("'", string.Empty) + "' AND week = '" + h + "' AND date = '" + Convert.ToDateTime(dateTxt.Text).Year.ToString() + "'").First().Id.ToString(); string Query = "DELETE from equipment WHERE id ='" + existingID + "'"; DBConnect.save(Query); } catch { existingID = ""; } if (!string.IsNullOrEmpty(row[0].ToString()) && !string.IsNullOrEmpty(row[u].ToString())) { Console.WriteLine(row[0].ToString() + " " + row[u].ToString() + " Week:" + h + " Week starting " + startWeek.ToString("dd-MM-yyyy") + " Week ending " + startWeek.AddDays(7).ToString("dd-MM-yyyy")); string ID = Guid.NewGuid().ToString(); try { Equipment i = new Equipment(ID, Convert.ToDateTime(dateTxt.Text).Year.ToString(), h, startWeek.ToString("dd-MM-yyyy"), startWeek.AddDays(7).ToString("dd-MM-yyyy"), row[0].ToString().Replace("\"", string.Empty).Replace("'", string.Empty), Convert.ToDouble(row[u].ToString()), startWeek.AddDays(7).ToString("MMMM")); DBConnect.Insert(i); } catch { } } } if (categoryTxt.Text.Contains("Expenses")) { existingID = ""; try { existingID = Expense.List("SELECT * from expense WHERE name='" + row[0].ToString().Replace("\"", string.Empty).Replace("'", string.Empty) + "' AND week = '" + h + "' AND date = '" + Convert.ToDateTime(dateTxt.Text).Year.ToString() + "'").First().Id.ToString(); string Query = "DELETE from expense WHERE id ='" + existingID + "'"; DBConnect.save(Query); } catch { existingID = ""; } if (!string.IsNullOrEmpty(row[0].ToString()) && !string.IsNullOrEmpty(row[u].ToString())) { Console.WriteLine(row[0].ToString() + " " + row[u].ToString() + " Week:" + h + " Week starting " + startWeek.ToString("dd-MM-yyyy") + " Week ending " + startWeek.AddDays(7).ToString("dd-MM-yyyy")); string ID = Guid.NewGuid().ToString(); try { Expense i = new Expense(ID, Convert.ToDateTime(dateTxt.Text).Year.ToString(), h, startWeek.ToString("dd-MM-yyyy"), startWeek.AddDays(7).ToString("dd-MM-yyyy"), row[0].ToString().Replace("\"", string.Empty).Replace("'", string.Empty), subcategory.Text, Convert.ToDouble(row[u].ToString()), startWeek.AddDays(7).ToString("MMMM")); DBConnect.Insert(i); } catch { } } } } } } h++; } //} //catch { } } }
public void ReadExcel(string fileName, string fileExt) { string conn = string.Empty; DataTable dtexcel = new DataTable(); if (fileExt.CompareTo(".xls") == 0 || fileExt.CompareTo(".xlsx") == 0) { conn = @"provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties='Excel 8.0;HRD=Yes;IMEX=1';"; //for below excel 2007 } else { conn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0;HDR=NO';"; //for above excel 2007 } using (OleDbConnection con = new OleDbConnection(conn)) { //try //{ OleDbDataAdapter oleAdpt = new OleDbDataAdapter("select * from [Sheet1$]", con); //here we read data from sheet1 oleAdpt.Fill(dtexcel); //fill excel data into dataTable int h = 1; //for (int u = 1; u < dtexcel.Columns.Count; u++) for (int u = 1; u < 60; u++) { Console.WriteLine("NEW WEEK :" + h); DateTime startWeek = Helper.FirstDateOfWeek(Convert.ToInt32(Helper.CurrentYear), h - 1); foreach (DataRow row in dtexcel.Rows) { try { //MessageBox.Show(); if (!string.IsNullOrEmpty(row[0].ToString()) && !string.IsNullOrEmpty(row[u].ToString())) { // Console.WriteLine(row[0].ToString() + " " + row[u].ToString() + " Week:" + h + " Week starting " + startWeek.ToString("dd-MM-yyyy") + " Week ending " + startWeek.AddDays(7).ToString("dd-MM-yyyy")); if (!row[0].ToString().Contains("Inventory") && !row[0].ToString().Contains("Total") && !row[0].ToString().Contains("Week")) { existingID = ""; try { existingID = Inventory.List("SELECT * from inventory WHERE name='" + row[0].ToString().Replace("\"", string.Empty).Replace("'", string.Empty) + "' AND week = '" + h + "' AND date = '" + Convert.ToDateTime(dateTxt.Text).Year.ToString() + "'").First().Id.ToString(); string Query = "DELETE from inventory WHERE id ='" + existingID + "'"; DBConnect.save(Query); Console.WriteLine("DELETE: " + row[0].ToString() + " " + row[u].ToString() + " Week:" + h + " Week starting " + startWeek.ToString("dd-MM-yyyy") + " Week ending " + startWeek.AddDays(7).ToString("dd-MM-yyyy")); } catch { existingID = ""; } string ID = Guid.NewGuid().ToString(); Inventory i = new Inventory(ID, Convert.ToDateTime(dateTxt.Text).Year.ToString(), h, startWeek.ToString("dd-MM-yyyy"), startWeek.AddDays(7).ToString("dd-MM-yyyy"), row[0].ToString().Replace("\"", string.Empty).Replace("'", string.Empty), categoryTxt.Text, Convert.ToDouble(row[u].ToString()), 0, 0, 0, startWeek.AddDays(7).ToString("MMMM"), Convert.ToDateTime(dateTxt.Text).ToString("dd-MM-yyyy")); DBConnect.Insert(i); Console.WriteLine("INSERT: " + row[0].ToString() + " " + row[u].ToString().Replace("'", @"\'") + " Week:" + h + " Week starting " + startWeek.ToString("dd-MM-yyyy") + " Week ending " + startWeek.AddDays(7).ToString("dd-MM-yyyy")); } } } catch { } } h++; } //} //catch { } } }