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);
}
}
