private void CheckExisting() { try { string existName; //checks to see if patient already exists in database objDBConnect.OpenConnection(); objDBConnect.sqlCmd = new SqlCommand("SELECT COUNT(*) FROM Register WHERE R_UNAME LIKE @R_UNAME;", objDBConnect.sqlConn); //query objDBConnect.sqlCmd.Parameters.AddWithValue("@R_UNAME", userName); //parameter existName = objDBConnect.sqlCmd.ExecuteScalar().ToString(); //assigning query to variable if (int.Parse(existName) > 0) { found = true; //in database } else { found = false; //not in database } } catch (SqlException ex) { MessageBox.Show(ex.Message); } catch (Exception ex) { MessageBox.Show("Error Cannot Check If User Exists In Database: " + ex.Message); // Shows an error message } }
private void btnDelete_Click(object sender, EventArgs e) { T_ID = int.Parse(Interaction.InputBox("Please enter Timesheet ID: ", "Timesheet ID", "Default Text")); try { objDBConnect.OpenConnection(); string sql = "DELETE FROM Timesheet WHERE (T_ID ='" + T_ID + "');"; objDBConnect.sqlCmd = new SqlCommand(); objDBConnect.sqlCmd.CommandText = sql; objDBConnect.sqlCmd.Connection = objDBConnect.sqlConn; objDBConnect.sqlDR = objDBConnect.sqlCmd.ExecuteReader(); MessageBox.Show("SUCCESS"); objDBConnect.sqlDR.Close(); objDBConnect.sqlConn.Close(); } catch (SqlException ex) { MessageBox.Show(ex.Message); } catch (Exception ex) { MessageBox.Show("Error Cannot Delete Records: " + ex.Message); } }
private void btnSubmit_Click(object sender, EventArgs e) { Check check = new Check(); bool exit = false; getValues(); exit = check.CheckEmpty(T_ID, "Trip ID", exit); exit = check.CheckEmpty(V_RN, "Vehicle Reg Number", exit); if (exit == false) { if (!T_ID.Equals("")) { try { objDBConnect.OpenConnection(); objDBConnect.sqlCmd = new SqlCommand("INSERT INTO TripUsage VALUES(@Trip_ID, @Vehicle_RegNumber,@Location_ID, @Trip_DateFrom, @Trip_DateTo,NULL,NULL,NULL,'NO')", objDBConnect.sqlConn); objDBConnect.sqlCmd.Parameters.AddWithValue("@Trip_ID", T_ID); objDBConnect.sqlCmd.Parameters.AddWithValue("@Vehicle_RegNumber", V_RN); objDBConnect.sqlCmd.Parameters.AddWithValue("@Location_ID", L_ID); objDBConnect.sqlCmd.Parameters.AddWithValue("@Trip_DateFrom", SqlDbType.Date).Value = dtpDateFrom.Value.Date; objDBConnect.sqlCmd.Parameters.AddWithValue("@Trip_DateTo", SqlDbType.Date).Value = dtpDateTo.Value.Date; objDBConnect.sqlDR = objDBConnect.sqlCmd.ExecuteReader(); MessageBox.Show("Succesfully inserted"); CloseConnections(); } catch (SqlException ex) { MessageBox.Show("ERROR!!!" + ex.Message); } catch (Exception ex) { MessageBox.Show("Error Cannot Submit Details: " + ex.Message); } } else { MessageBox.Show("Please enter a Trip ID"); } } }
private void btnSubmit_Click(object sender, EventArgs e) { bool exit = false; Check check = new Check(); username = txtUsername.Text; oldPass = txtOldPass.Text; newPass1 = txtNewPass.Text; newPass2 = txtNewPass.Text; exit = check.CheckEmpty(username, "Username", exit); exit = check.CheckEmpty(oldPass, "Old Password", exit); exit = check.CheckEmpty(newPass1, "New Password", exit); exit = check.CheckEmpty(newPass2, "Confirm New Password", exit); if (exit == false) { if (newPass1 == newPass2) { DBConnect objDBConnect = new DBConnect(); objDBConnect.OpenConnection(); objDBConnect.sqlCmd = new SqlCommand("UPDATE Register SET R_PWORD = @R_PWORD WHERE R_UNAME = @R_UNAME", objDBConnect.sqlConn); objDBConnect.sqlCmd.Parameters.AddWithValue("@R_PWORD", newPass1); objDBConnect.sqlCmd.Parameters.AddWithValue("@R_UNAME", username); objDBConnect.sqlDR = objDBConnect.sqlCmd.ExecuteReader(); objDBConnect.sqlDR.Close(); MessageBox.Show("Successfully Updated"); } else { MessageBox.Show("The password fields do not match"); } } }
private void btnUpdate_Click(object sender, EventArgs e) { try { DBConnect objDBConnect = new DBConnect(); objDBConnect.OpenConnection(); objDBConnect.sqlCmd = new SqlCommand("UPDATE Customer SET Cust_Name= @Cust_Name, Cust_Surname=@Cust_Surname, Cust_Type=@Cust_Type, Cust_ContactNo=@Cust_ContactNo,Cust_Email=@Cust_Email, Cust_PayDue=@Cust_PayDue, Cust_PayMade=@Cust_PayMade WHERE Cust_ID = @Cust_ID", objDBConnect.sqlConn); objDBConnect.sqlCmd.Parameters.AddWithValue("@Cust_ID", C_ID); objDBConnect.sqlCmd.Parameters.AddWithValue("@Cust_Name", C_NAME); objDBConnect.sqlCmd.Parameters.AddWithValue("@Cust_Surname", C_SNAME); objDBConnect.sqlCmd.Parameters.AddWithValue("@Cust_Type", C_TYPE); objDBConnect.sqlCmd.Parameters.AddWithValue("@Cust_ContactNo", C_CONTACT); objDBConnect.sqlCmd.Parameters.AddWithValue("@Cust_Email", C_EMAIL); objDBConnect.sqlCmd.Parameters.AddWithValue("@Cust_PayDue", C_DUE); objDBConnect.sqlCmd.Parameters.AddWithValue("@Cust_PayMade", C_MADE); objDBConnect.sqlDR = objDBConnect.sqlCmd.ExecuteReader(); MessageBox.Show("SUCCESSFULLY UPDATED"); objDBConnect.sqlDR.Close(); objDBConnect.sqlConn.Close(); } catch (SqlException ex) { MessageBox.Show(ex.Message); } catch (Exception ex) { MessageBox.Show("Error Cannot Update Customer Details: " + ex.Message); } }
private void btnDelete_Click(object sender, EventArgs e) { getValues(); try { DBConnect objDBConnect = new DBConnect(); objDBConnect.OpenConnection(); string sql = "DELETE FROM Vehicle WHERE (Vehicle_RegNumber ='" + V_RN + "');"; objDBConnect.sqlCmd = new SqlCommand(); objDBConnect.sqlCmd.CommandText = sql; objDBConnect.sqlCmd.Connection = objDBConnect.sqlConn; objDBConnect.sqlDR = objDBConnect.sqlCmd.ExecuteReader(); MessageBox.Show("SUCCESSFUL"); objDBConnect.sqlDR.Close(); objDBConnect.sqlConn.Close(); } catch (SqlException ex) { MessageBox.Show(ex.Message); } catch (Exception ex) { MessageBox.Show("Error! You Cannot Delete Vehicle Details: " + ex.Message); } }
private void btnDelete_Click(object sender, EventArgs e) { getValues(); try { DBConnect objDBConnect = new DBConnect(); objDBConnect.OpenConnection(); string sql = "DELETE FROM Employee WHERE (Emp_ID ='" + E_ID + "');"; objDBConnect.sqlCmd = new SqlCommand(); objDBConnect.sqlCmd.CommandText = sql; objDBConnect.sqlCmd.Connection = objDBConnect.sqlConn; objDBConnect.sqlDR = objDBConnect.sqlCmd.ExecuteReader(); MessageBox.Show("SUCCESS"); objDBConnect.sqlDR.Close(); objDBConnect.sqlConn.Close(); } catch (SqlException ex) { MessageBox.Show(ex.Message); } catch (Exception ex) { MessageBox.Show("Error Cannot Delete Employee Details: " + ex.Message); // Shows an error message } }
private void btnDelete_Click(object sender, EventArgs e) { L_ID = Interaction.InputBox("Please enter Location ID: ", "Location ID", "Default Text"); try { DBConnect objDBConnect = new DBConnect(); objDBConnect.OpenConnection(); string sql = "DELETE FROM Location WHERE (Location_ID ='" + L_ID + "');"; objDBConnect.sqlCmd = new SqlCommand(); objDBConnect.sqlCmd.CommandText = sql; objDBConnect.sqlCmd.Connection = objDBConnect.sqlConn; objDBConnect.sqlDR = objDBConnect.sqlCmd.ExecuteReader(); MessageBox.Show("SUCCESS"); objDBConnect.sqlDR.Close(); objDBConnect.sqlConn.Close(); } catch (SqlException ex) { MessageBox.Show(ex.Message); } catch (Exception ex) { MessageBox.Show("Error Cannot Delete Location Details: " + ex.Message); } }
private void btnUpdate_Click(object sender, EventArgs e) { getValues(); try { DBConnect objDBConnect = new DBConnect(); objDBConnect.OpenConnection(); objDBConnect.sqlCmd = new SqlCommand("UPDATE Vehicle SET Vehicle_Type=@Vehicle_Type, Vehicle_Make=@Vehicle_Make, Vehicle_Model=@Vehicle_Model, Vehicle_Year=@Vehicle_Year,Vehicle_TotalMileage= @Vehicle_TotalMileage WHERE Vehicle_RegNumber=@Vehicle_RegNumber ", objDBConnect.sqlConn); objDBConnect.sqlCmd.Parameters.AddWithValue("@Vehicle_RegNumber", V_RN); objDBConnect.sqlCmd.Parameters.AddWithValue("@Vehicle_Type", V_TYPE); objDBConnect.sqlCmd.Parameters.AddWithValue("@Vehicle_Make", V_MAKE); objDBConnect.sqlCmd.Parameters.AddWithValue("@Vehicle_Model", V_MODEL); objDBConnect.sqlCmd.Parameters.AddWithValue("@Vehicle_Year", V_YEAR); objDBConnect.sqlCmd.Parameters.AddWithValue("@Vehicle_TotalMileage", V_MILEAGE); objDBConnect.sqlDR = objDBConnect.sqlCmd.ExecuteReader(); MessageBox.Show("SUCCESSFULLY UPDATED"); objDBConnect.sqlDR.Close(); objDBConnect.sqlConn.Close(); } catch (SqlException ex) { MessageBox.Show(ex.Message); } catch (Exception ex) { MessageBox.Show("Error! You Cannot Update Vehicle Details: " + ex.Message); } }
private void btnDelete_Click(object sender, EventArgs e) { try { DBConnect objDBConnect = new DBConnect(); objDBConnect.OpenConnection(); string sql = "DELETE FROM Customer WHERE (Cust_ID ='" + C_ID + "');"; objDBConnect.sqlCmd = new SqlCommand(); objDBConnect.sqlCmd.CommandText = sql; objDBConnect.sqlCmd.Connection = objDBConnect.sqlConn; objDBConnect.sqlDR = objDBConnect.sqlCmd.ExecuteReader(); MessageBox.Show("SUCCESS"); objDBConnect.sqlDR.Close(); objDBConnect.sqlConn.Close(); } catch (SqlException ex) { MessageBox.Show(ex.Message); } catch (Exception ex) { MessageBox.Show("Error Cannot Delete Customer Details: " + ex.Message); } }
private void btnDelete_Click(object sender, EventArgs e) { getValues(); try { DBConnect objDBConnect = new DBConnect(); objDBConnect.OpenConnection(); string sql = "DELETE FROM tblService WHERE (Service_ID ='" + S_ID + "');"; objDBConnect.sqlCmd = new SqlCommand(); objDBConnect.sqlCmd.CommandText = sql; objDBConnect.sqlCmd.Connection = objDBConnect.sqlConn; objDBConnect.sqlDR = objDBConnect.sqlCmd.ExecuteReader(); MessageBox.Show("SUCCESS"); objDBConnect.sqlDR.Close(); objDBConnect.sqlConn.Close(); } catch (SqlException ex) { MessageBox.Show(ex.Message); } catch (Exception ex) { MessageBox.Show("Error Cannot Delete Record From Service Table in Database: " + ex.Message); } }
private void btnUpdate_Click(object sender, EventArgs e) { getValues(); try { DBConnect objDBConnect = new DBConnect(); objDBConnect.OpenConnection(); objDBConnect.sqlCmd = new SqlCommand("UPDATE tblService SET Vehicle_RegNumber=@Vehicle_RegNumber,Emp_ID= @Emp_ID,Service_Date= @Service_Date,Service_Time = @Service_Time,Service_Description = @Service_Description WHERE Service_ID = @Service_ID", objDBConnect.sqlConn); objDBConnect.sqlCmd.Parameters.AddWithValue("@Service_ID", S_ID); objDBConnect.sqlCmd.Parameters.AddWithValue("@Vehicle_RegNumber", V_RN); objDBConnect.sqlCmd.Parameters.AddWithValue("@Emp_ID", E_ID); objDBConnect.sqlCmd.Parameters.AddWithValue("@Service_Date", SqlDbType.Date).Value = dtpAppointmentDate.Value.Date; objDBConnect.sqlCmd.Parameters.AddWithValue("@Service_Time", S_TIME); objDBConnect.sqlCmd.Parameters.AddWithValue("@Service_Description", S_DES); objDBConnect.sqlDR = objDBConnect.sqlCmd.ExecuteReader(); MessageBox.Show("SUCCESSFULLY UPDATED"); objDBConnect.sqlDR.Close(); objDBConnect.sqlConn.Close(); } catch (SqlException ex) { MessageBox.Show(ex.Message); } catch (Exception ex) { MessageBox.Show("Error Cannot Update Details in Service Table: " + ex.Message); } }
private void btnUpdate_Click(object sender, EventArgs e) { try { T_ID = int.Parse(Interaction.InputBox("Please enter Timesheet ID: ", "Timesheet ID", "Default Text")); T_HOURS = double.Parse(Interaction.InputBox("Please enter Number of hours worked: " + T_ID, "Hours Worked", "Default Text")); } catch (FormatException f) { MessageBox.Show("Invalid Format. Please only enter digits: " + f.Message); } try { DBConnect objDBConnect = new DBConnect(); objDBConnect.OpenConnection(); objDBConnect.sqlCmd = new SqlCommand("UPDATE Timesheet SET T_HOURS = @T_HOURS WHERE T_ID = @T_ID", objDBConnect.sqlConn); objDBConnect.sqlCmd.Parameters.AddWithValue("@T_ID", T_ID); objDBConnect.sqlCmd.Parameters.AddWithValue("@T_HOURS", T_HOURS); objDBConnect.sqlDR = objDBConnect.sqlCmd.ExecuteReader(); MessageBox.Show("SUCCESSFULLY INSERTED"); objDBConnect.sqlDR.Close(); objDBConnect.sqlConn.Close(); } catch (Exception ex) { MessageBox.Show("Error Cannot Submit Vehicle Details: " + ex.Message); } }
private void btnUpdate_Click(object sender, EventArgs e) { getValues(); try { DBConnect objDBConnect = new DBConnect(); objDBConnect.OpenConnection(); objDBConnect.sqlCmd = new SqlCommand("UPDATE Employee SET Emp_Name=@Emp_Name,Emp_Surname= @Emp_Surname,Emp_Address = @Emp_Address,Emp_Position= @Emp_Position,Emp_ContactNo= @Emp_ContactNo, Emp_Email=@Emp_Email, Emp_MonthlySalary=@Emp_MonthlySalary WHERE Emp_ID = @Emp_ID", objDBConnect.sqlConn); objDBConnect.sqlCmd.Parameters.AddWithValue("@Emp_ID", E_ID); objDBConnect.sqlCmd.Parameters.AddWithValue("@Emp_Name", E_NAME); objDBConnect.sqlCmd.Parameters.AddWithValue("@Emp_Surname", E_SNAME); objDBConnect.sqlCmd.Parameters.AddWithValue("@Emp_Address", E_ADDRESS); objDBConnect.sqlCmd.Parameters.AddWithValue("@Emp_Position", E_POS); objDBConnect.sqlCmd.Parameters.AddWithValue("@Emp_ContactNo", E_CONTACT); objDBConnect.sqlCmd.Parameters.AddWithValue("@Emp_Email", E_EMAIL); objDBConnect.sqlCmd.Parameters.AddWithValue("@Emp_MonthlySalary", E_SALARY); objDBConnect.sqlDR = objDBConnect.sqlCmd.ExecuteReader(); MessageBox.Show("SUCCESSFULLY UPDATED"); objDBConnect.sqlDR.Close(); objDBConnect.sqlConn.Close(); } catch (SqlException ex) { MessageBox.Show(ex.Message); } catch (Exception ex) { MessageBox.Show("Error Cannot Update Employee Details: " + ex.Message); // Shows an error message } }
private void btnAdd_Click(object sender, EventArgs e) { getValues(); Check check = new Check(); bool exit = false; exit = check.CheckEmpty(L_ID, "Location ID", exit); exit = check.CheckEmpty(L_MANAGER, "Manager In Charge", exit); exit = check.CheckEmpty(L_CITY, "City", exit); exit = check.CheckEmpty(L_NAME, "Location", exit); exit = check.CheckEmpty(L_PROVINCE, "Province", exit); exit = check.CheckForNumbers(L_MANAGER, "Manager In Charge"); if (exit == false) { try { bool executeSQL = check.CheckDB("Location", "Location_ID", L_ID); if (executeSQL == false) { DBConnect objDBConnect = new DBConnect(); objDBConnect.OpenConnection(); objDBConnect.sqlCmd = new SqlCommand("INSERT INTO LOCATION VALUES (@Location_ID, @Location_Name, @Location_City, @Location_NumVehicles, @Location_NumEmployees, @Location_Manager)", objDBConnect.sqlConn); objDBConnect.sqlCmd.Parameters.AddWithValue("@Location_ID", L_ID); objDBConnect.sqlCmd.Parameters.AddWithValue("@Location_Name", L_NAME); objDBConnect.sqlCmd.Parameters.AddWithValue("@Location_City", L_CITY); objDBConnect.sqlCmd.Parameters.AddWithValue("@Location_NumVehicles", L_VEHICLES); objDBConnect.sqlCmd.Parameters.AddWithValue("@Location_NumEmployees", L_EMPLOYEES); objDBConnect.sqlCmd.Parameters.AddWithValue("@Location_Manager", L_MANAGER); objDBConnect.sqlDR = objDBConnect.sqlCmd.ExecuteReader(); MessageBox.Show("SUCCESSFULLY INSERTED"); objDBConnect.sqlDR.Close(); objDBConnect.sqlConn.Close(); } else { MessageBox.Show("That Location ID already exists in the database"); } } catch (SqlException ex) { MessageBox.Show(ex.Message); } catch (Exception ex) { MessageBox.Show("Error Cannot Submit Location Details: " + ex.Message); } } }
public void populateE_ID() { try { string query = "SELECT Emp_ID from Employee;"; objDBConnect.OpenConnection(); SqlDataAdapter da = new SqlDataAdapter(query, objDBConnect.sqlConn); DataSet ds = new DataSet(); da.Fill(ds, "Employee"); cboE_ID.ValueMember = "Emp_ID"; cboE_ID.DisplayMember = "Emp_ID"; cboE_ID.DataSource = ds.Tables["Employee"]; objDBConnect.sqlConn.Close(); } catch (SqlException ex) { MessageBox.Show(ex.Message); } catch (Exception ex) { MessageBox.Show(ex.Message); // Shows an error message } }
private void frmCompletedTripDetails_Load(object sender, EventArgs e) { try { string query = "SELECT Trip_ID from TripUsage WHERE Trip_Completed like 'NO';"; objDBConnect.OpenConnection(); SqlDataAdapter da = new SqlDataAdapter(query, objDBConnect.sqlConn); DataSet ds = new DataSet(); da.Fill(ds, "TripUsage"); cboT_ID.ValueMember = "Trip_ID"; cboT_ID.DisplayMember = "Trip_ID"; cboT_ID.DataSource = ds.Tables["TripUsage"]; objDBConnect.sqlConn.Close(); } catch (SqlException ex) { MessageBox.Show(ex.Message); } catch (Exception ex) { MessageBox.Show(ex.Message); // Shows an error message } }
private void btnSubmit_Click(object sender, EventArgs e) { getValues(); Check check = new Check(); bool exit = false; exit = check.CheckEmpty(V_MILEAGE, "Vehicle Milage", exit); exit = check.CheckEmpty(V_MAKE, "Vehicle Make", exit); exit = check.CheckEmpty(V_MODEL, "Vehicle Model", exit); exit = check.CheckEmpty(V_RN, "Registration Number", exit); exit = check.CheckEmpty(V_TYPE, "Type of Vehicle", exit); exit = check.CheckForLetters(V_MILEAGE, "Vehicle Milage"); if (exit == false) { try { DBConnect objDBConnect = new DBConnect(); objDBConnect.OpenConnection(); objDBConnect.sqlCmd = new SqlCommand("INSERT INTO Vehicle VALUES (@Vehicle_RegNumber, @Vehicle_Type, @Vehicle_Make, @Vehicle_Model, @Vehicle_Year, @Vehicle_TotalMileage)", objDBConnect.sqlConn); objDBConnect.sqlCmd.Parameters.AddWithValue("@Vehicle_RegNumber", V_RN); objDBConnect.sqlCmd.Parameters.AddWithValue("@Vehicle_Type", V_TYPE); objDBConnect.sqlCmd.Parameters.AddWithValue("@Vehicle_Make", V_MAKE); objDBConnect.sqlCmd.Parameters.AddWithValue("@Vehicle_Model", V_MODEL); objDBConnect.sqlCmd.Parameters.AddWithValue("@Vehicle_Year", V_YEAR); objDBConnect.sqlCmd.Parameters.AddWithValue("@Vehicle_TotalMileage", V_MILEAGE); objDBConnect.sqlDR = objDBConnect.sqlCmd.ExecuteReader(); MessageBox.Show("Successfully Inserted"); objDBConnect.sqlDR.Close(); objDBConnect.sqlConn.Close(); } catch (SqlException ex) { MessageBox.Show("Error here" + ex.Message); } catch (Exception ex) { MessageBox.Show("Error! You Cannot Submit Vehicle Details: " + ex.Message); } } }
private void btnAdd_Click(object sender, EventArgs e) { getValues(); try { Check check = new Check(); bool executeSQL = check.CheckDB("tblService", "Service_ID", S_ID); if (executeSQL == false) { DBConnect objDBConnect = new DBConnect(); objDBConnect.OpenConnection(); objDBConnect.sqlCmd = new SqlCommand("INSERT INTO tblService VALUES (@Service_ID, @Vehicle_RegNumber, @Emp_ID, @Service_Date, @Service_Time,@Service_Description)", objDBConnect.sqlConn); objDBConnect.sqlCmd.Parameters.AddWithValue("@Service_ID", S_ID); objDBConnect.sqlCmd.Parameters.AddWithValue("@Vehicle_RegNumber", V_RN); objDBConnect.sqlCmd.Parameters.AddWithValue("@Emp_ID", E_ID); objDBConnect.sqlCmd.Parameters.AddWithValue("@Service_Date", SqlDbType.Date).Value = dtpAppointmentDate.Value.Date; objDBConnect.sqlCmd.Parameters.AddWithValue("@Service_Time", S_TIME); objDBConnect.sqlCmd.Parameters.AddWithValue("@Service_Description", S_DES); objDBConnect.sqlDR = objDBConnect.sqlCmd.ExecuteReader(); MessageBox.Show("Succesfully inserted"); objDBConnect.sqlDR.Close(); objDBConnect.sqlConn.Close(); } else { MessageBox.Show("That Service ID already exists in the database"); } } catch (SqlException ex) { MessageBox.Show(ex.Message); } catch (Exception ex) { MessageBox.Show("Error Cannot Add Record to Service Table in Database: " + ex.Message); } }
public bool CheckDB(string tableName, string fieldName, string field) { bool checkID; string exist_id; DBConnect objDBConnect = new DBConnect(); objDBConnect.OpenConnection(); objDBConnect.sqlCmd = new SqlCommand("SELECT COUNT(*) FROM " + tableName + " WHERE " + fieldName + " LIKE @" + fieldName + ";", objDBConnect.sqlConn); objDBConnect.sqlCmd.Parameters.AddWithValue("@" + fieldName, field); exist_id = objDBConnect.sqlCmd.ExecuteScalar().ToString(); if (int.Parse(exist_id) > 0) { checkID = true; } else { checkID = false; } objDBConnect.sqlConn.Close(); return(checkID); }
private void btnAdd_Click(object sender, EventArgs e) { T_ID = int.Parse(txtT_ID.Text); E_ID = cboE_ID.SelectedValue.ToString(); T_HOURS = double.Parse(updHoursWorked.Text); if (!T_ID.Equals("")) { try { DBConnect objDBConnect = new DBConnect(); objDBConnect.OpenConnection(); objDBConnect.sqlCmd = new SqlCommand("INSERT INTO Timesheet VALUES (@T_ID, @Emp_ID, @T_HOURS,GETDATE())", objDBConnect.sqlConn); objDBConnect.sqlCmd.Parameters.AddWithValue("@T_ID", T_ID); objDBConnect.sqlCmd.Parameters.AddWithValue("@Emp_ID", E_ID); objDBConnect.sqlCmd.Parameters.AddWithValue("@T_HOURS", T_HOURS); objDBConnect.sqlDR = objDBConnect.sqlCmd.ExecuteReader(); MessageBox.Show("SUCCESSFULLY INSERTED"); objDBConnect.sqlDR.Close(); objDBConnect.sqlConn.Close(); } catch (Exception ex) { MessageBox.Show("Error Cannot Submit Vehicle Details: " + ex.Message); } } else { MessageBox.Show("Please enter a Timesheet ID"); } }
private void frmSearch_Load(object sender, EventArgs e) { //cboE_ID = E_ID; try { string query = "SELECT Emp_ID from Employee;"; objDBConnect.OpenConnection(); SqlDataAdapter da = new SqlDataAdapter(query, objDBConnect.sqlConn); DataSet ds = new DataSet(); da.Fill(ds, "Employee"); cboE_ID.ValueMember = "Emp_ID"; cboE_ID.DisplayMember = "Emp_ID"; cboE_ID.DataSource = ds.Tables["Employee"]; objDBConnect.sqlConn.Close(); } catch (SqlException ex) { MessageBox.Show(ex.Message); } catch (Exception ex) { MessageBox.Show(ex.Message); // Shows an error message } try { string query = "SELECT Cust_ID from Customer;"; objDBConnect.OpenConnection(); SqlDataAdapter da = new SqlDataAdapter(query, objDBConnect.sqlConn); DataSet ds = new DataSet(); da.Fill(ds, "Customer"); cboC_ID.ValueMember = "Cust_ID"; cboC_ID.DisplayMember = "Cust_ID"; cboC_ID.DataSource = ds.Tables["Customer"]; objDBConnect.sqlConn.Close(); } catch (SqlException ex) { MessageBox.Show(ex.Message); } catch (Exception ex) { MessageBox.Show(ex.Message); // Shows an error message } try { string query = "SELECT Location_ID from Location;"; objDBConnect.OpenConnection(); SqlDataAdapter da = new SqlDataAdapter(query, objDBConnect.sqlConn); DataSet ds = new DataSet(); da.Fill(ds, "Location"); cboL_ID.ValueMember = "Location_ID"; cboL_ID.DisplayMember = "Location_ID"; cboL_ID.DataSource = ds.Tables["Location"]; objDBConnect.sqlConn.Close(); } catch (SqlException ex) { MessageBox.Show(ex.Message); } catch (Exception ex) { MessageBox.Show(ex.Message); // Shows an error message } try { string query = "SELECT Trip_ID from TripUsage;"; objDBConnect.OpenConnection(); SqlDataAdapter da = new SqlDataAdapter(query, objDBConnect.sqlConn); DataSet ds = new DataSet(); da.Fill(ds, "TripUsage"); cboT_ID.ValueMember = "Trip_ID"; cboT_ID.DisplayMember = "Trip_ID"; cboT_ID.DataSource = ds.Tables["TripUsage"]; objDBConnect.sqlConn.Close(); } catch (SqlException ex) { MessageBox.Show(ex.Message); } catch (Exception ex) { MessageBox.Show(ex.Message); // Shows an error message } try { string query = "SELECT Vehicle_RegNumber from Vehicle;"; objDBConnect.OpenConnection(); SqlDataAdapter da = new SqlDataAdapter(query, objDBConnect.sqlConn); DataSet ds = new DataSet(); da.Fill(ds, "Vehicle"); cboV_RN.ValueMember = "Vehicle_RegNumber"; cboV_RN.DisplayMember = "Vehicle_RegNumber"; cboV_RN.DataSource = ds.Tables["Vehicle"]; objDBConnect.sqlConn.Close(); } catch (SqlException ex) { MessageBox.Show(ex.Message); } catch (Exception ex) { MessageBox.Show(ex.Message); // Shows an error message } try { string query = "SELECT Service_ID from tblService;"; objDBConnect.OpenConnection(); SqlDataAdapter da = new SqlDataAdapter(query, objDBConnect.sqlConn); DataSet ds = new DataSet(); da.Fill(ds, "tblService"); cboS_ID.ValueMember = "Service_ID"; cboS_ID.DisplayMember = "Service_ID"; cboS_ID.DataSource = ds.Tables["tblService"]; objDBConnect.sqlConn.Close(); } catch (SqlException ex) { MessageBox.Show(ex.Message); } catch (Exception ex) { MessageBox.Show(ex.Message); // Shows an error message } }
private void btnPassReset_Click(object sender, EventArgs e) { //Code adapted from: http://www.codeproject.com/Tips/520998/Send-Email-from-Yahoo-Gmail-Hotmail-Csharp Check check = new Check(); GeneratePassword newPass = new GeneratePassword(); bool exit = false; string tempPass = newPass.GeneratePass(10); userName = txtUser.Text; exit = check.CheckEmpty(userName, "Username", exit); if (exit == false) { try { DBConnect objDBConnect = new DBConnect(); objDBConnect.OpenConnection(); objDBConnect.sqlCmd = new SqlCommand("UPDATE Register SET R_PWORD = @R_PWORD WHERE R_UNAME = @R_UNAME", objDBConnect.sqlConn); objDBConnect.sqlCmd.Parameters.AddWithValue("@R_PWORD", tempPass); objDBConnect.sqlCmd.Parameters.AddWithValue("@R_UNAME", userName); objDBConnect.sqlDR = objDBConnect.sqlCmd.ExecuteReader(); objDBConnect.sqlDR.Close(); MessageBox.Show("Successfully updated password"); objDBConnect.sqlCmd = new SqlCommand("SELECT R_EMAIL FROM Register WHERE R_UNAME = @R_UNAME", objDBConnect.sqlConn); objDBConnect.sqlCmd.Parameters.AddWithValue("@R_UNAME", userName); string email = ((string)objDBConnect.sqlCmd.ExecuteScalar()); objDBConnect.sqlConn.Close(); string smtpAddress = "smtp.gmail.com"; int portNumber = 587; bool enableSSL = true; string emailFrom = "*****@*****.**"; string password = "******"; string emailTo = email; string subject = "Password Reset"; string body = "You have requested a password reset, your new temporary password/Security Code is: " + tempPass + ". Please change your password as soon as possible."; using (MailMessage mail = new MailMessage()) { mail.From = new MailAddress(emailFrom); mail.To.Add(emailTo); mail.Subject = subject; mail.Body = body; mail.IsBodyHtml = false; using (SmtpClient smtp = new SmtpClient(smtpAddress, portNumber)) { smtp.Credentials = new NetworkCredential(emailFrom, password); smtp.EnableSsl = enableSSL; smtp.Send(mail); MessageBox.Show("An email has been sent. If you do not receive the email within 5 minutes check that the username you entered is correct"); } } this.Hide(); frmPasswordReset passReset = new frmPasswordReset(); passReset.Show(); } catch (SqlException ex) { MessageBox.Show(ex.Message); } catch (SmtpFailedRecipientException ex) { MessageBox.Show(ex.Message); } catch (SmtpException ex) { MessageBox.Show(ex.Message); } catch (Exception ex) { MessageBox.Show(ex.Message); } } }
private void btnUpdate_Click(object sender, EventArgs e) { try// allows user to choose which fields they want to update. { L_ID = Interaction.InputBox("Please enter Location ID: ", "Location ID", ""); update = int.Parse(Interaction.InputBox("Please enter the number associated with field you want to update:\n 1.Number of Employees\n2. Number of Vehicles\n3. Location Manager ", "Updates", "Default Text")); while (update != 1 && update != 2 && update != 3) { update = int.Parse(Interaction.InputBox("Please enter correct number associated with field you want to update:\n 1.Number of Employees\n2. Number of Vehicles\n3. Location Manager ", "Updates", "Default Text")); } if (update == 1) { L_EMPLOYEES = int.Parse(Interaction.InputBox("Please enter Number of Employees: ", "Number of Employees", "")); sql = "Location_NumEmployees= " + L_EMPLOYEES; } else { if (update == 2) { L_VEHICLES = int.Parse(Interaction.InputBox("Please enter Number of Vehicles: ", "Number of vehicles", "")); sql = "Location_NumVehicles = " + L_VEHICLES; } else { if (update == 3) { L_MANAGER = Interaction.InputBox("Please enter Location Manager: ", "Location Manager", ""); sql = "Location_Manager = " + L_MANAGER; } else { MessageBox.Show("Please enter corresponding numbers only"); } } } try { DBConnect objDBConnect = new DBConnect(); objDBConnect.OpenConnection(); objDBConnect.sqlCmd = new SqlCommand("UPDATE LOCATION SET " + sql + " WHERE Location_ID = @Location_ID", objDBConnect.sqlConn); objDBConnect.sqlCmd.Parameters.AddWithValue("@Location_ID", L_ID); objDBConnect.sqlDR = objDBConnect.sqlCmd.ExecuteReader(); MessageBox.Show("SUCCESSFULLY UPDATED"); objDBConnect.sqlDR.Close(); objDBConnect.sqlConn.Close(); } catch (SqlException ex) { MessageBox.Show(ex.Message); } catch (Exception ex) { MessageBox.Show("Error Cannot Update Location Details: " + ex.Message); } } catch (Exception) { MessageBox.Show("Invalid option: Cannot Update."); } }
private void btnAdd_Click(object sender, EventArgs e) { C_ID = txtID.Text; C_EMAIL = txtEmail.Text; try { var addr = new System.Net.Mail.MailAddress(C_EMAIL);// validates email address } catch (Exception ex) { MessageBox.Show("Error Cannot Validate Email Address: " + ex.Message); } Check check = new Check(); bool exit = false; try { C_TYPE = this.cboCustomer.GetItemText(this.cboCustomer.SelectedItem); //C_TYPE = cboCustomer.SelectedValue.ToString(); } catch (Exception ex) { MessageBox.Show("Cobobox error: " + ex.Message); } C_CONTACT = txtContact.Text; C_DUE = txtPaymentDue.Text; C_MADE = txtPaymentMade.Text; C_NAME = txtName.Text; C_SNAME = txtSurname.Text; exit = check.CheckEmpty(C_TYPE, "Customer Type", exit); exit = check.CheckEmpty(C_CONTACT, "Customer Contact Number", exit); exit = check.CheckEmpty(C_DUE, "Payment Due", exit); exit = check.CheckEmpty(C_MADE, "Payment Made", exit); exit = check.CheckEmpty(C_NAME, "Customer Name", exit); exit = check.CheckEmpty(C_SNAME, "Customer Surname", exit); exit = check.CheckForNumbers(C_NAME, "Customer Name"); exit = check.CheckForNumbers(C_SNAME, "Customer Surname"); exit = check.CheckForLetters(C_CONTACT, "Customer Contact Number"); exit = check.CheckForLetters(C_DUE, "Payment Due"); exit = check.CheckForLetters(C_MADE, "Payment Made"); if (exit == false) { try { bool executeSQL = check.CheckDB("Customer", "Cust_ID", C_ID); if (executeSQL == false) { DBConnect objDBConnect = new DBConnect(); objDBConnect.OpenConnection(); objDBConnect.sqlCmd = new SqlCommand("INSERT INTO Customer VALUES (@Cust_ID, @Cust_Name, @Cust_Surname, @Cust_Type, @Cust_ContactNo,@Cust_Email, @Cust_PayDue, @Cust_PayMade)", objDBConnect.sqlConn); objDBConnect.sqlCmd.Parameters.AddWithValue("@Cust_ID", C_ID); objDBConnect.sqlCmd.Parameters.AddWithValue("@Cust_Name", C_NAME); objDBConnect.sqlCmd.Parameters.AddWithValue("@Cust_Surname", C_SNAME); objDBConnect.sqlCmd.Parameters.AddWithValue("@Cust_Type", C_TYPE); objDBConnect.sqlCmd.Parameters.AddWithValue("@Cust_ContactNo", C_CONTACT); objDBConnect.sqlCmd.Parameters.AddWithValue("@Cust_Email", C_EMAIL); objDBConnect.sqlCmd.Parameters.AddWithValue("@Cust_PayDue", C_DUE); objDBConnect.sqlCmd.Parameters.AddWithValue("@Cust_PayMade", C_MADE); objDBConnect.sqlDR = objDBConnect.sqlCmd.ExecuteReader(); MessageBox.Show("Successfully Inserted"); objDBConnect.sqlDR.Close(); objDBConnect.sqlConn.Close(); } else { MessageBox.Show("That Customer ID already exists in the database"); } } catch (SqlException ex) { MessageBox.Show("Error cannot add customer details " + ex.Message); } catch (Exception ex) { MessageBox.Show("Error Cannot Add Customer Details: " + ex.Message + ex.Data + ex.StackTrace); } } else { MessageBox.Show("One or more fields are empty"); } }
private void btnSubmit_Click(object sender, EventArgs e) { Check check = new Check(); bool exit = false; getValues(); try { var addr = new System.Net.Mail.MailAddress(E_EMAIL);// Validates email address accepted = true; } catch (Exception ex) { MessageBox.Show("Error Invalid email address: " + ex.Message); // Shows an error message } E_SALARY = txtSalary.Text; Employee objEmp = new Employee(E_ID, E_NAME, E_SNAME, E_POS, E_CONTACT, E_EMAIL, E_SALARY); exit = check.CheckEmpty(E_NAME, "Employee Name", exit); exit = check.CheckEmpty(E_ID, "Employee ID", exit); exit = check.CheckEmpty(E_SNAME, "Employee Surname", exit); exit = check.CheckEmpty(E_POS, "Employee Position", exit); exit = check.CheckEmpty(E_CONTACT, "Employee Contact", exit); exit = check.CheckEmpty(E_EMAIL, "Employee Email", exit); exit = check.CheckForNumbers(E_NAME, "Employee Name"); exit = check.CheckForNumbers(E_SNAME, "Employee Surname"); exit = check.CheckForLetters(E_CONTACT, "Employee Contact Number"); exit = check.CheckForLetters(E_SALARY, "Employee Salary"); if (accepted == true) { if (success == true) { try { bool executeSQL = check.CheckDB("Employee", "Emp_ID", E_ID); if (executeSQL == false) { DBConnect objDBConnect = new DBConnect(); objDBConnect.OpenConnection(); objDBConnect.sqlCmd = new SqlCommand("INSERT INTO Employee VALUES (@Emp_ID, @Emp_Name, @Emp_Surname, @Emp_Position,@Emp_Address, @Emp_ContactNo, @Emp_Email, @Emp_MonthlySalary)", objDBConnect.sqlConn); objDBConnect.sqlCmd.Parameters.AddWithValue("@Emp_ID", E_ID); objDBConnect.sqlCmd.Parameters.AddWithValue("@Emp_Name", E_NAME); objDBConnect.sqlCmd.Parameters.AddWithValue("@Emp_Surname", E_SNAME); objDBConnect.sqlCmd.Parameters.AddWithValue("@Emp_Position", E_POS); objDBConnect.sqlCmd.Parameters.AddWithValue("@Emp_Address", E_ADDRESS); objDBConnect.sqlCmd.Parameters.AddWithValue("@Emp_ContactNo", E_CONTACT); objDBConnect.sqlCmd.Parameters.AddWithValue("@Emp_Email", E_EMAIL); objDBConnect.sqlCmd.Parameters.AddWithValue("@Emp_MonthlySalary", E_SALARY); objDBConnect.sqlDR = objDBConnect.sqlCmd.ExecuteReader(); string message = objEmp.SuccessMessage(); MessageBox.Show(message); objDBConnect.sqlDR.Close(); objDBConnect.sqlConn.Close(); } else { MessageBox.Show("That Employee ID already exists in the database"); } } catch (SqlException ex) { MessageBox.Show(ex.Message); } catch (Exception ex) { MessageBox.Show("Error Cannot Add Employee Details: " + ex.Message); } } } }
private void frmReports_Load(object sender, EventArgs e) { this.WindowState = FormWindowState.Maximized; try // Resizes Columns in the Datagridviews { dgvDetailedService.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells; dgvCTDaily.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells; dgvCTMonthly.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells; dgvCTWeekly.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells; dgvPTDaily.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells; dgvPTWeekly.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells; dgvSADaily.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells; // Resizes the Column dgvSAWeekly.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells; dgvServiceReq.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells; dgvTSDaily.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells; dgvTSMonthly.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells; dgvTSWeekly.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells; dgvVehicleStatus.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells; dgvVSDaily.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells; dgvVSMonthly.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells; dgvVSWeekly.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells; dgvVSYearly.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells; } catch (Exception ex) { MessageBox.Show("Problem with Resizing Data Grid Views: " + ex); // Shows Error Message if there is a Problem } db.OpenConnection(); try // Adds Data to the Data Grid Views { string selectVehicleStatus = "SELECT * FROM Vehicle ORDER BY Vehicle_Year"; // Sql For Data Grid View db.sqlDA = new SqlDataAdapter(selectVehicleStatus, db.sqlConn); SqlCommandBuilder commandBuilder = new SqlCommandBuilder(db.sqlDA); DataSet ds = new DataSet(); db.sqlDA.Fill(ds); // Fills the DataSet dgvVehicleStatus.ReadOnly = true; dgvVehicleStatus.DataSource = ds.Tables[0]; string selectServiceAppointDaily = "Select * FROM tblService WHERE Month(Service_Date) = MONTH(GETDATE()) AND YEAR(Service_Date) = YEAR(GETDATE()) AND DAY(Service_Date) = DAY(GETDATE())"; db.sqlDA = new SqlDataAdapter(selectServiceAppointDaily, db.sqlConn); SqlCommandBuilder commandBuilder2 = new SqlCommandBuilder(db.sqlDA); DataSet ds2 = new DataSet(); db.sqlDA.Fill(ds2); dgvSADaily.ReadOnly = true; dgvSADaily.DataSource = ds2.Tables[0]; string selectServiceAppointWeekly = "SELECT * FROM tblService WHERE Service_Date BETWEEN GETDATE()-1 and GETDATE()+5 ORDER BY Service_Date"; db.sqlDA = new SqlDataAdapter(selectServiceAppointWeekly, db.sqlConn); SqlCommandBuilder commandBuilder11 = new SqlCommandBuilder(db.sqlDA); DataSet ds11 = new DataSet(); db.sqlDA.Fill(ds11); dgvSAWeekly.ReadOnly = true; dgvSAWeekly.DataSource = ds11.Tables[0]; string selectServiceReq = "SELECT Vehicle_RegNumber, Service_Description, Service_Date, Service_Time FROM tblService WHERE Service_Date >= GETDATE() ORDER BY Service_Date"; db.sqlDA = new SqlDataAdapter(selectServiceReq, db.sqlConn); SqlCommandBuilder commandBuilder3 = new SqlCommandBuilder(db.sqlDA); DataSet ds3 = new DataSet(); db.sqlDA.Fill(ds3); dgvServiceReq.ReadOnly = true; dgvServiceReq.DataSource = ds3.Tables[0]; string selectVehicleServicesDaily = "SELECT * FROM tblService WHERE Month(service_date) = MONTH(GETDATE()) AND YEAR(service_date) = YEAR(GETDATE()) AND DAY(service_date) = DAY(GETDATE())"; // Sql For Data Grid View db.sqlDA = new SqlDataAdapter(selectVehicleServicesDaily, db.sqlConn); SqlCommandBuilder commandBuilder4 = new SqlCommandBuilder(db.sqlDA); DataSet ds4 = new DataSet(); db.sqlDA.Fill(ds4); // Fills the DataSet dgvVSDaily.ReadOnly = true; dgvVSDaily.DataSource = ds4.Tables[0]; string selectVehicleServicesWeekly = "SELECT * FROM tblService WHERE service_date BETWEEN getdate()-5 and getdate()"; db.sqlDA = new SqlDataAdapter(selectVehicleServicesWeekly, db.sqlConn); SqlCommandBuilder commandBuilder12 = new SqlCommandBuilder(db.sqlDA); DataSet ds12 = new DataSet(); db.sqlDA.Fill(ds12); dgvVSWeekly.ReadOnly = true; dgvVSWeekly.DataSource = ds12.Tables[0]; string selectVehicleServicesMonthly = "SELECT * FROM tblService WHERE Service_Date < getdate() and Month(Service_Date) = Month(GETDATE())"; db.sqlDA = new SqlDataAdapter(selectVehicleServicesMonthly, db.sqlConn); SqlCommandBuilder commandBuilder13 = new SqlCommandBuilder(db.sqlDA); DataSet ds13 = new DataSet(); db.sqlDA.Fill(ds13); dgvVSMonthly.ReadOnly = true; dgvVSMonthly.DataSource = ds13.Tables[0]; string selectVehicleServicesYearly = "SELECT * FROM tblService WHERE Service_Date < getdate() AND Year(Service_Date) = YEAR(GETDATE())"; db.sqlDA = new SqlDataAdapter(selectVehicleServicesYearly, db.sqlConn); SqlCommandBuilder commandBuilder14 = new SqlCommandBuilder(db.sqlDA); DataSet ds14 = new DataSet(); db.sqlDA.Fill(ds14); dgvVSYearly.ReadOnly = true; dgvVSYearly.DataSource = ds14.Tables[0]; string selectDetailService = "SELECT * FROM tblService WHERE Service_Date < GETDATE() ORDER BY Service_Date"; // Sql For Data Grid View db.sqlDA = new SqlDataAdapter(selectDetailService, db.sqlConn); SqlCommandBuilder commandBuilder5 = new SqlCommandBuilder(db.sqlDA); DataSet ds5 = new DataSet(); db.sqlDA.Fill(ds5); // Fills the DataSet dgvDetailedService.ReadOnly = true; dgvDetailedService.DataSource = ds5.Tables[0]; string selectPlannedTripDaily = "Select * FROM TripUsage WHERE Month(Trip_DateFrom) = MONTH(GETDATE()) AND YEAR(Trip_DateFrom) = YEAR(GETDATE()) AND DAY(Trip_DateFrom) = DAY(GETDATE()) AND Trip_Completed LIKE 'NO'"; db.sqlDA = new SqlDataAdapter(selectPlannedTripDaily, db.sqlConn); SqlCommandBuilder commandBuilder6 = new SqlCommandBuilder(db.sqlDA); DataSet ds6 = new DataSet(); db.sqlDA.Fill(ds6); dgvPTDaily.ReadOnly = true; dgvPTDaily.DataSource = ds6.Tables[0]; string selectPlannedTripWeekly = "SELECT * FROM TripUsage WHERE Trip_DateFrom BETWEEN GETDATE() and GETDATE()+5 AND Trip_Completed LIKE 'NO'"; db.sqlDA = new SqlDataAdapter(selectPlannedTripWeekly, db.sqlConn); SqlCommandBuilder commandBuilder15 = new SqlCommandBuilder(db.sqlDA); DataSet ds15 = new DataSet(); db.sqlDA.Fill(ds15); dgvPTWeekly.ReadOnly = true; dgvPTWeekly.DataSource = ds15.Tables[0]; string selectCompletedTripDaily = "Select * FROM TripUsage WHERE Month(Trip_DateFrom) = MONTH(GETDATE()) AND YEAR(Trip_DateFrom) = YEAR(GETDATE()) AND DAY(Trip_DateFrom) = DAY(GETDATE()) AND Trip_Completed LIKE 'YES'"; db.sqlDA = new SqlDataAdapter(selectCompletedTripDaily, db.sqlConn); SqlCommandBuilder commandBuilder7 = new SqlCommandBuilder(db.sqlDA); DataSet ds7 = new DataSet(); db.sqlDA.Fill(ds7); dgvCTDaily.ReadOnly = true; dgvCTDaily.DataSource = ds7.Tables[0]; string selectCompletedTripWeekly = "SELECT * FROM TripUsage WHERE Trip_DateFrom BETWEEN GETDATE() and GETDATE()+5 AND Trip_Completed LIKE 'YES' ORDER BY Trip_DateFrom"; db.sqlDA = new SqlDataAdapter(selectCompletedTripWeekly, db.sqlConn); SqlCommandBuilder commandBuilder16 = new SqlCommandBuilder(db.sqlDA); DataSet ds16 = new DataSet(); db.sqlDA.Fill(ds16); dgvCTWeekly.ReadOnly = true; dgvCTWeekly.DataSource = ds16.Tables[0]; string selectCompletedTripMonthly = "SELECT * FROM TripUsage WHERE MONTH(Trip_DateFrom) = MONTH(GETDATE()) AND Trip_Completed LIKE 'YES'"; db.sqlDA = new SqlDataAdapter(selectCompletedTripMonthly, db.sqlConn); SqlCommandBuilder commandBuilder17 = new SqlCommandBuilder(db.sqlDA); DataSet ds17 = new DataSet(); db.sqlDA.Fill(ds17); dgvCTMonthly.ReadOnly = true; dgvCTMonthly.DataSource = ds17.Tables[0]; string selectTimeSheetWeek = "SELECT e.Emp_Name, e.Emp_Surname, e.Emp_ID, s.[Total Hours Weekly] FROM Employee e JOIN (SELECT Emp_ID, SUM(T_HOURS) AS [Total Hours Weekly] FROM Timesheet s WHERE T_DATE BETWEEN GETDATE() and GETDATE()+5 GROUP BY Emp_ID) as s ON e.Emp_ID = s.Emp_ID"; // Sql for Data Grid View db.sqlDA = new SqlDataAdapter(selectTimeSheetWeek, db.sqlConn); SqlCommandBuilder commandBuilder8 = new SqlCommandBuilder(db.sqlDA); DataSet ds8 = new DataSet(); db.sqlDA.Fill(ds8); // Fills the DataSet dgvTSWeekly.ReadOnly = true; dgvTSWeekly.DataSource = ds8.Tables[0]; string selectTimeSheetMonth = "SELECT e.Emp_Name, e.Emp_Surname, e.Emp_ID, s.[Total Hours Monthly] FROM Employee e JOIN (SELECT Emp_ID, SUM(T_HOURS) AS [Total Hours Monthly] FROM Timesheet s WHERE Month(T_DATE) = Month(GETDATE())GROUP BY Emp_ID) as s ON e.Emp_ID = s.Emp_ID"; db.sqlDA = new SqlDataAdapter(selectTimeSheetMonth, db.sqlConn); SqlCommandBuilder commandBuilder9 = new SqlCommandBuilder(db.sqlDA); DataSet ds9 = new DataSet(); db.sqlDA.Fill(ds9); dgvTSMonthly.ReadOnly = true; dgvTSMonthly.DataSource = ds9.Tables[0]; string selectTimeSheetDaily = "SELECT e.Emp_Name, e.Emp_Surname, e.Emp_ID, t.T_ID as 'Timesheet ID', SUM(t.T_HOURS) AS 'Total Hours Worked-Daily' FROM Employee e inner join Timesheet t on e.Emp_ID = t.Emp_ID WHERE Day(T_DATE) = day(GETDATE())and Month(T_DATE) = Month(GETDATE()) and year(T_DATE) = year(GETDATE()) GROUP BY e.Emp_Name, e.Emp_Surname, e.Emp_ID,t.T_ID,t.T_HOURS;"; db.sqlDA = new SqlDataAdapter(selectTimeSheetDaily, db.sqlConn); SqlCommandBuilder commandBuilder10 = new SqlCommandBuilder(db.sqlDA); DataSet ds10 = new DataSet(); db.sqlDA.Fill(ds10); dgvTSDaily.ReadOnly = true; dgvTSDaily.DataSource = ds10.Tables[0]; } catch (SqlException ex) { MessageBox.Show(ex.Message); } catch (Exception ex) { MessageBox.Show("Error Cannot Load Sql Commands to The Data Grid Views: " + ex.Message); // Shows an error message } }
private void btnRegister_Click(object sender, EventArgs e) { Cursor.Current = Cursors.WaitCursor;//this is used to show the user that a process is occuring R_DOB = dtpDateOfBirth.Value.ToShortDateString(); R_NAME = txtName.Text; R_SNAME = txtSurname.Text; R_UNAME = txtUserName.Text; R_PWORD = txtPass.Text; R_EMAIL = txtEmail.Text; R_CURRDATE = CurrDate(R_CURRDATE); R_EMPPOS = cboEmpPosition.SelectedItem.ToString(); bool email = false; if (!R_NAME.Equals("")) { if (!R_SNAME.Equals("")) { if (!(cboEmpPosition.SelectedIndex < 0)) { if (!R_EMAIL.Equals("")) { try { var addr = new System.Net.Mail.MailAddress(R_EMAIL);// validates email address email = true; } catch { } if (email == true) { if (!R_UNAME.Equals("")) { if (!R_PWORD.Equals("")) { try { Check check = new Check(); bool executeSQL = check.CheckDB("Register", "R_UNAME", R_UNAME); if (executeSQL == false) { DBConnect objDBConnect = new DBConnect(); objDBConnect.OpenConnection(); objDBConnect.sqlCmd = new SqlCommand("INSERT INTO Register VALUES (@R_UNAME,@R_DOB, @R_NAME, @R_SNAME,@R_EMPPOS,@R_EMAIL, @R_PWORD)", objDBConnect.sqlConn); objDBConnect.sqlCmd.Parameters.AddWithValue("@R_UNAME", R_UNAME); objDBConnect.sqlCmd.Parameters.AddWithValue("@R_DOB", SqlDbType.Date).Value = dtpDateOfBirth.Value.Date; objDBConnect.sqlCmd.Parameters.AddWithValue("@R_NAME", R_NAME); objDBConnect.sqlCmd.Parameters.AddWithValue("@R_SNAME", R_SNAME); objDBConnect.sqlCmd.Parameters.AddWithValue("@R_EMPPOS", R_EMPPOS); objDBConnect.sqlCmd.Parameters.AddWithValue("@R_EMAIL", R_EMAIL); objDBConnect.sqlCmd.Parameters.AddWithValue("@R_PWORD", R_PWORD); objDBConnect.sqlDR = objDBConnect.sqlCmd.ExecuteReader(); objDBConnect.sqlDR.Close(); objDBConnect.sqlConn.Close(); string s = R_DOB + "," + R_NAME + "," + R_SNAME + "," + R_EMPPOS + "," + R_UNAME + "," + R_PWORD; MessageBox.Show("You have been successfully registered. Please be patient while an email is sent to you."); try { SmtpClient client = new SmtpClient("smtp.gmail.com"); client.Port = 587; client.EnableSsl = true; client.Timeout = 100000; client.DeliveryMethod = SmtpDeliveryMethod.Network; client.UseDefaultCredentials = false; client.Credentials = new NetworkCredential( R_MEMAIL, R_EPWORD);//logs into your email account //gets values from the textboxes MailMessage msg = new MailMessage(); msg.To.Add(txtEmail.Text); msg.From = new MailAddress(R_MEMAIL);//checks that email address exists msg.Subject = "Successful Registration - Fleet Tracking Application"; msg.Body = "Hello " + R_NAME.ToUpper() + " " + R_SNAME.ToUpper() + "\n\nThis is confirmation indicating that you have successfully registered to use the Fleet Tracking Application. \n\nDate: " + R_CURRDATE + "\nUser Name: " + R_UNAME + "\n(Use this to log into the application, along with your password)\n\nKind Regards,\nFleet Tracking Team\n(0312521212)"; client.Send(msg); MessageBox.Show("Registration Successful\nConfirmation Email Sent to: " + R_MEMAIL); Cursor.Current = Cursors.Default;// when processing is done default curser will appear } catch (Exception ex) { MessageBox.Show("Email Confirmation Not Sent:\n" + ex.Message); } } else { MessageBox.Show("That username already exists"); } } catch (SqlException ex) { MessageBox.Show(ex.Message); } catch (Exception ex) { MessageBox.Show("Error Cannot Be Registered: " + ex.Message); } } else { MessageBox.Show("Enter A Password"); } } else { MessageBox.Show("Please Enter a Username"); } } else { MessageBox.Show("Invalid Email Address: " + R_EMAIL); } } else { MessageBox.Show("Please Enter Your Email Address"); } } else { MessageBox.Show("Please Select The Employees Position"); } } else { MessageBox.Show("Please Enter Your Surname"); } } else { MessageBox.Show("Please Enter Your Name"); } }