//save button sends mySQLupdate or mySQLinsert commands with validation and UTC private void SaveButton_Click(object sender, EventArgs e) { int entryReady = 0; bool existsAlready = false; foreach (DataRow row in returnedIDTestTable.Rows) { if (AppIDTextBox != null && AppIDTextBox.Text != "" && row.ItemArray[0].ToString() == AppIDTextBox.Text) { existsAlready = true; break; } } if (TypeTextbox.Text.ToString() != "" && TypeTextbox.Text.Length > 3) { entryReady++; } else { MessageBox.Show("Please enter valid type. Must be 3 characters or more."); } if (dateTimePicker1.Value > DateTime.Now && dateTimePicker2.Value > DateTime.Now && dateTimePicker1.Value < dateTimePicker2.Value) { entryReady++; } else { MessageBox.Show("Dates must be in the future. And end date must be after start date."); } if (dateTimePicker1.Value.DayOfWeek != DayOfWeek.Saturday && dateTimePicker1.Value.DayOfWeek != DayOfWeek.Sunday && dateTimePicker2.Value.DayOfWeek != DayOfWeek.Saturday && dateTimePicker2.Value.DayOfWeek != DayOfWeek.Sunday && dateTimePicker1.Value.Hour >= 9 && dateTimePicker2.Value.Hour <= 17) { entryReady++; } else { MessageBox.Show("Appointment times must be within business hours."); } bool overlapAppts = false; foreach (DataRow row in returnedIDTestTable.Rows) { if ((DateTime.Parse(row.ItemArray[9].ToString()).ToLocalTime() <= dateTimePicker1.Value && dateTimePicker1.Value <= DateTime.Parse(row.ItemArray[10].ToString()).ToLocalTime()) || (DateTime.Parse(row.ItemArray[9].ToString()).ToLocalTime() <= dateTimePicker2.Value && dateTimePicker2.Value <= DateTime.Parse(row.ItemArray[10].ToString()).ToLocalTime()) || (DateTime.Parse(row.ItemArray[9].ToString()).ToLocalTime() >= dateTimePicker1.Value && dateTimePicker2.Value >= DateTime.Parse(row.ItemArray[10].ToString()).ToLocalTime())) { overlapAppts = true; break; } } if (overlapAppts) { MessageBox.Show("Appointments cannot overlap."); } else { entryReady++; } if (existsAlready && entryReady == 4) { FormModel.UpdateNonQuery($"UPDATE appointment SET " + $"customerID = '{comboboxSelectTable.Rows[comboBox1.SelectedIndex].ItemArray[1].ToString()}', " + $"userID = '{FormModel.CurrentUserID.ToString()}', " + $"type = '{TypeTextbox.Text}', " + $"start = '{DateTime.Parse(dateTimePicker1.Value.ToString()).ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss tt")}', " + $"end = '{DateTime.Parse(dateTimePicker2.Value.ToString()).ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss tt")}', " + $"lastUpdate = '{DateTime.Now.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss tt")}', " + $"lastupdateby = \"{FormModel.CurrentUser}\" " + $"WHERE (appointmentID = '{AppIDTextBox.Text}');"); AppointmentSelectTableLoad(comboboxSelectTable.Rows[comboBox1.SelectedIndex].ItemArray[0].ToString()); } else if (entryReady == 4) { FormModel.InsertNonQuery("insert into appointment " + "(customerID, userID, type, start, end, createDate, createdBy) VALUES " + $"('{comboboxSelectTable.Rows[comboBox1.SelectedIndex].ItemArray[1].ToString()}', " + $"'{FormModel.CurrentUserID}', '{TypeTextbox.Text}', " + $"'{DateTime.Parse(dateTimePicker1.Value.ToString()).ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss tt")}', " + $"'{DateTime.Parse(dateTimePicker2.Value.ToString()).ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss tt")}', " + $"now(), '{FormModel.CurrentUser}');"); AppointmentSelectTableLoad(comboboxSelectTable.Rows[comboBox1.SelectedIndex].ItemArray[0].ToString()); } else { MessageBox.Show("Entry Failed"); } comboBox1_SelectionChangeCommitted(new object(), new EventArgs()); returnedIDTestTableLoad(); combobox2SelectTable = FormModel.SearchQuery("select distinct type from appointment"); comboBox2.DataSource = combobox2SelectTable; comboBox2.DisplayMember = "Type"; comboBox2.SelectedItem = null; comboBox2.Text = "-type select-"; }
private void SaveButton_Click(object sender, EventArgs e) { DataTable tempTable = new DataTable { }; string isfoundCustomerID = null; string isfoundCountry = null; string isfoundCity = null; string isfoundAddressPhoneID = null; DialogResult confirmResult; int readyToEnter = 0; if (nameTextBox.Text == "" || countryTextBox.Text == "" || address1TextBox.Text == "" || phoneTextBox.Text == "" || zipTextBox.Text == "" || cityTextBox.Text == "") { MessageBox.Show("Please be sure to fill all fields."); } else { readyToEnter++; } if (zipTextBox.Text.Length > 10) { MessageBox.Show("Zip Code is too long."); } else { readyToEnter++; } if (phoneTextBox.Text.Length > 20) { MessageBox.Show("Phone Number is too long."); } else { readyToEnter++; } if (readyToEnter == 3) { foreach (DataRow row in returnedSelectTable.Rows) { if (IDTextBox.Text == row.ItemArray[0].ToString()) { isfoundCustomerID = row.ItemArray[0].ToString(); } if (countryTextBox.Text == row.ItemArray[7].ToString()) { isfoundCountry = row.ItemArray[11].ToString(); } if (cityTextBox.Text == row.ItemArray[5].ToString()) { isfoundCity = row.ItemArray[10].ToString(); } if (isfoundCustomerID != null && address1TextBox.Text == row.ItemArray[5].ToString() && phoneTextBox.Text == row.ItemArray[2].ToString()) { isfoundAddressPhoneID = row.ItemArray[9].ToString(); } } if (isfoundCustomerID != null) { confirmResult = MessageBox.Show("Are you sure to update this Customer?", "Confirm update?", MessageBoxButtons.YesNo); } else { confirmResult = MessageBox.Show("Are you sure to create this Customer?", "Confirm creation?", MessageBoxButtons.YesNo); } if (confirmResult == DialogResult.Yes) { if (isfoundCountry == null) { FormModel.InsertNonQuery("Insert into country " + $"(country, createdate, createdby, lastupdate, lastupdateby) " + $"values (\"{countryTextBox.Text}\", " + $"'{DateTime.Now.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss tt")}', " + $"\"{FormModel.CurrentUser}\", " + $"'{DateTime.Now.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss tt")}', " + $"\"{FormModel.CurrentUser}\");"); tempTable = FormModel.SearchQuery("select * from country;"); foreach (DataRow row in tempTable.Rows) { if (row.ItemArray[1].ToString() == countryTextBox.Text) { isfoundCountry = row.ItemArray[0].ToString(); break; } } } if (isfoundCity == null) { FormModel.InsertNonQuery("Insert into city " + $"(city, countryID, createdate, createdby, lastupdate, lastupdateby) " + $"values (\"{cityTextBox.Text}\", {isfoundCountry}, " + $"'{DateTime.Now.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss tt")}', " + $"\"{FormModel.CurrentUser}\", " + $"'{DateTime.Now.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss tt")}', " + $"\"{FormModel.CurrentUser}\");"); tempTable = FormModel.SearchQuery("select * from city;"); foreach (DataRow row in tempTable.Rows) { if (row.ItemArray[1].ToString() == cityTextBox.Text) { isfoundCity = row.ItemArray[0].ToString(); break; } } } if (isfoundAddressPhoneID == null) { FormModel.InsertNonQuery("Insert into address (address, " + "cityID, postalCode, Phone, createdate, createdby, lastupdate, lastupdateby) " + $"values (\"{address1TextBox.Text}\", {isfoundCity}, {zipTextBox.Text}, \"{phoneTextBox.Text}\", " + $"'{DateTime.Now.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss tt")}', " + $"\"{FormModel.CurrentUser}\", " + $"'{DateTime.Now.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss tt")}', " + $"\"{FormModel.CurrentUser}\");"); tempTable = FormModel.SearchQuery("select * from address;"); foreach (DataRow row in tempTable.Rows) { if (address1TextBox.Text == row.ItemArray[1].ToString() && phoneTextBox.Text == row.ItemArray[5].ToString() && DateTime.ParseExact(row.ItemArray[6].ToString(), "M/d/yyyy h:mm:ss tt", System.Globalization.CultureInfo.InstalledUICulture).Hour == DateTime.UtcNow.Hour) { isfoundAddressPhoneID = row.ItemArray[0].ToString(); break; } } } if (isfoundCustomerID == null) { FormModel.InsertNonQuery("Insert into customer " + "(customerName, addressID, active, createdate, createdby, lastupdate, lastupdateby) " + $"values (\"{nameTextBox.Text}\", {isfoundAddressPhoneID}, {yesRadioButton.Checked}, " + $"'{DateTime.Now.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss tt")}', " + $"\"{FormModel.CurrentUser}\", " + $"'{DateTime.Now.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss tt")}', " + $"\"{FormModel.CurrentUser}\");"); } else { FormModel.UpdateNonQuery("update customer " + $"set customerName = \"{nameTextBox.Text}\", " + $"addressID = {isfoundAddressPhoneID}, " + $"active = {yesRadioButton.Checked}, " + $"lastUpdate = '{DateTime.Now.ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss tt")}', " + $"lastupdateby = \"{FormModel.CurrentUser}\" " + $"where customerID = {isfoundCustomerID};"); } } CustomerSelectTableLoad(); //called 2x in code wasnt enough to make a full method when inline lambda was faster Action <string> indicator = customerName => { foreach (DataGridViewRow rowSearch in customerDGV.Rows) { if (rowSearch.Cells[1].Value.ToString().Equals(customerName)) { rowSearch.Selected = true; CustIndx = rowSearch.Index; break; } } }; indicator(CustomerNamePassed); } }