private void btnBackToMainMenu_Click(object sender, EventArgs e) { this.Visible = false; frmMainMenu mainMenu = new frmMainMenu(); mainMenu.Show(); }
private void btnUpdatePayer_Click(object sender, EventArgs e) { Payer payer = new Payer(); payer.FirstName = txtPayerFirstName.Text; payer.Surname = txtPayerSurname.Text; try { //Learnt how to update tables using parameterized queries from: https://rosettacode.org/wiki/Parametrized_SQL_statement#C.23 (20/11/2018) connection.Open(); OleDbCommand command = new OleDbCommand(); command.Connection = connection; command.CommandText = "UPDATE expenseTable SET FirstName = @FirstName, Surname = @Surname WHERE ID = @ID AND PayeeOrPayer = @PayeeOrPayer"; command.Parameters.Add("@FirstName", OleDbType.VarChar).Value = payer.FirstName; command.Parameters.Add("@Surname", OleDbType.VarChar).Value = payer.Surname; command.Parameters.Add("@ID", OleDbType.Integer).Value = txtPayerID.Text; command.Parameters.Add("@PayeeOrPayer", OleDbType.VarChar).Value = "Payer"; command.ExecuteNonQuery(); string message = "The Payer has been updated"; string caption = "Payer updated"; MessageBoxButtons buttons = MessageBoxButtons.OK; DialogResult result = MessageBox.Show(this, message, caption, buttons); if (result == DialogResult.OK) { this.Visible = false; frmMainMenu mainMenu = new frmMainMenu(); mainMenu.Show(); } connection.Close(); } catch (Exception ex) { MessageBox.Show("Error " + ex.Message); } }
private void btnUpdatePayee_Click(object sender, EventArgs e) { Payee payee = new Payee(); payee.FirstName = txtPayeeFirstName.Text; payee.Surname = txtPayeeSurname.Text; try { //Code referenced from: ProgrammingKnowledge YouTube C# Access playlist (20/11/2018). One of the few times I use string concatenation in SQL queries instead of parameterized queries connection.Open(); OleDbCommand command = new OleDbCommand(); command.Connection = connection; command.CommandText = "UPDATE expenseTable SET FirstName='" + txtPayeeFirstName.Text + "' ,Surname='" + txtPayeeSurname.Text + "' WHERE ID =" + txtPayeeID.Text + " AND PayeeOrPayer='Payee' "; //MessageBox.Show(command.CommandText); command.ExecuteNonQuery(); string message = "The Payee has been updated"; string caption = "Payee updated"; MessageBoxButtons buttons = MessageBoxButtons.OK; DialogResult result = MessageBox.Show(this, message, caption, buttons); if (result == DialogResult.OK) { this.Visible = false; frmMainMenu mainMenu = new frmMainMenu(); mainMenu.Show(); } connection.Close(); } catch (Exception ex) { MessageBox.Show("Error " + ex.Message); } }
private void btnLogin_Click(object sender, EventArgs e) { //Reads data from userTable Access database and verifies if it matches the input in the textboxes //Count is used in the while loop so that it checks if there is only one occurence of what input the user has entered in the textbox //Referenced from: https://www.youtube.com/watch?v=72POpCW5-zE&list=PLS1QulWo1RIZLd9EIGHZ5GnDbQJFh_9Lu&index=3 (ProgrammingKnowledge YouTube series) (10/11/2018) if ((string.IsNullOrEmpty(txtPassword.Text))) { MessageBox.Show("You must enter a password"); } if ((string.IsNullOrEmpty(txtUsername.Text))) { MessageBox.Show("You must enter a username"); } OleDbConnection connection = new OleDbConnection(); connection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\toyin\Documents\UserDatabase.accdb"; User user = new User(); user.Username = txtUsername.Text; user.Password = txtPassword.Text; OleDbCommand command = new OleDbCommand("SELECT * FROM userTable WHERE Username='******' AND Password='******'"); command.Connection = connection; connection.Open(); OleDbDataReader reader = command.ExecuteReader(); int count = 0; while (reader.Read()) { count++; } if (count == 1) { connection.Close(); connection.Dispose(); this.Visible = false; frmMainMenu mainmenu = new frmMainMenu(); mainmenu.Show(); //frmMainMenu mainmenu = new frmMainMenu(); //mainmenu.Show(); } else if (count > 1) { MessageBox.Show("There is a duplicate record of the username and the password"); } if (count == 0) { MessageBox.Show("Username and password is incorrect"); } }
private void btnAddPayee_Click(object sender, EventArgs e) { //Code referenced from: https://stackoverflow.com/questions/19275557/c-sharp-inserting-data-from-a-form-into-an-access-database (17/11/2018) OleDbConnection connection = new OleDbConnection(); connection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\toyin\Documents\ExpenseDatabase.accdb"; Payee payee = new Payee(); payee.FirstName = txtPayeeFirstName.Text; payee.Surname = txtPayeeSurname.Text; payee.AccountNumber = txtPayeeAccountNo.Text; OleDbCommand command = new OleDbCommand("INSERT INTO expenseTable (PayeeOrPayer, FirstName, Surname, AccountNumber) Values(@PayeeOrPayer, @FirstName, @Surname, @AccountNumber)"); command.Connection = connection; connection.Open(); if (connection.State == ConnectionState.Open) { command.Parameters.Add("@PayeeOrPayer", OleDbType.VarChar).Value = "Payee"; command.Parameters.Add("@FirstName", OleDbType.VarChar).Value = payee.FirstName; command.Parameters.Add("@Surname", OleDbType.VarChar).Value = payee.Surname; command.Parameters.Add("@AccountNumber", OleDbType.Integer).Value = payee.AccountNumber; try { command.ExecuteNonQuery(); string message = "The Payee has been added"; string caption = "Payee added"; MessageBoxButtons buttons = MessageBoxButtons.OK; DialogResult result = MessageBox.Show(this, message, caption, buttons); if (result == DialogResult.OK) { this.Visible = false; frmMainMenu mainMenu = new frmMainMenu(); mainMenu.Show(); } connection.Close(); } catch (OleDbException ex) { MessageBox.Show(ex.Message); connection.Close(); } } else { MessageBox.Show("Connection has failed"); } }
private void btnAddPayer_Click(object sender, EventArgs e) { OleDbConnection connection = new OleDbConnection(); connection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\toyin\Documents\ExpenseDatabase.accdb"; Payer payer = new Payer(); payer.FirstName = txtPayerFirstName.Text; payer.Surname = txtPayerSurname.Text; payer.AccountNumber = txtPayerAccountNo.Text; OleDbCommand command = new OleDbCommand("INSERT INTO expenseTable (PayeeOrPayer, FirstName, Surname, AccountNumber) Values(@PayeeOrPayer, @FirstName, @Surname, @AccountNumber)"); command.Connection = connection; connection.Open(); if (connection.State == ConnectionState.Open) { command.Parameters.Add("@PayeeOrPayer", OleDbType.VarChar).Value = "Payer"; command.Parameters.Add("@FirstName", OleDbType.VarChar).Value = payer.FirstName; command.Parameters.Add("@Surname", OleDbType.VarChar).Value = payer.Surname; command.Parameters.Add("@AccountNumber", OleDbType.Integer).Value = payer.AccountNumber; try { command.ExecuteNonQuery(); string message = "The Payer has been added"; string caption = "Payer added"; MessageBoxButtons buttons = MessageBoxButtons.OK; DialogResult result = MessageBox.Show(this, message, caption, buttons); if (result == DialogResult.OK) { this.Visible = false; frmMainMenu mainMenu = new frmMainMenu(); mainMenu.Show(); } connection.Close(); } catch (OleDbException ex) { MessageBox.Show(ex.Message); connection.Close(); } } else { MessageBox.Show("Connection has failed"); } }
private void btnUpdateExpense_Click(object sender, EventArgs e) { Expense expense = new Expense(); expense.expenseDate = expenseDatePicker.Value.Date; expense.expensePrice = txtIncomingPrice.Text; expense.referenceNo = txtIncomingReferenceNo.Text; string selected = this.cboPayeePayer.GetItemText(this.cboPayeePayer.SelectedItem); try { connection.Open(); OleDbCommand command2 = new OleDbCommand(); command2.Connection = connection; command2.CommandText = "UPDATE expenseTable SET ExpenseDate = @ExpenseDate, Price = @Price, ReferenceNumber = @ReferenceNumber, ExpenseType = @ExpenseType WHERE FirstName = @FirstName"; command2.Parameters.Add("@ExpenseDate", OleDbType.Date).Value = expenseDatePicker.Value.Date; command2.Parameters.Add("@Price", OleDbType.Currency).Value = txtIncomingPrice.Text; command2.Parameters.Add("@ReferenceNumber", OleDbType.Integer).Value = txtIncomingReferenceNo.Text; command2.Parameters.Add("@ExpenseType", OleDbType.VarChar).Value = "Incoming"; command2.Parameters.Add("@FirstName", OleDbType.VarChar).Value = selected; //MessageBox.Show(command2.CommandText); command2.ExecuteNonQuery(); string message = "The incoming expenses has been updated"; string caption = "Expenses updated"; MessageBoxButtons buttons = MessageBoxButtons.OK; DialogResult result = MessageBox.Show(this, message, caption, buttons); if (result == DialogResult.OK) { this.Visible = false; frmMainMenu mainMenu = new frmMainMenu(); mainMenu.Show(); } connection.Close(); } catch (Exception ex) { MessageBox.Show("Error " + ex.Message); } }