//Add Expense
private void button4_Click(object sender, EventArgs e)
{
//define variables
string particular, date;
double amount;
//validation
DateTimePicker today = new DateTimePicker();
today.Value = DateTime.Today.Date;
string ErrorMsg;
var val = Income.validation(textBox1.Text, textBox2.Text, out ErrorMsg);
if (!val)
{
MessageBox.Show(ErrorMsg, "Entry Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else if (dateTimePicker1.Value.Date > today.Value)
{
MessageBox.Show("Entered Date falls after " + today.Value.ToString("dd-MM-yyyy") + "", "Date Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
particular = textBox1.Text;
amount = double.Parse(textBox2.Text);
date = dateTimePicker1.Value.Date.ToString("yyyy-MM-dd");
//oracle connection
OracleConnection con = new OracleConnection(connectionString);
//CHECK NO. OF ENTRIES
OracleDataAdapter no_of_entries
= new OracleDataAdapter("SELECT EXPENSEID FROM EXPENSE", con);
DataTable detail_count = new DataTable();
no_of_entries.Fill(detail_count);
int expenseidmax = detail_count.Rows.Count;
if (expenseidmax != 0)
{
for (int i = 0; i < detail_count.Rows.Count; i++)
{
int temp = int.Parse(detail_count.Rows[i][0].ToString());
if (expenseidmax <= temp)
{
expenseidmax = temp;
}
}
}
expenseidmax++;
con.Close();
// checking for redundant entries
string redundant = "SELECT EXPENSEID FROM EXPENSE" +
" WHERE EXPENSEPARTICULAR = '" + particular + "' AND " +
"EXPENSEAMOUNT = " + amount + " AND " +
"EXPENSEDATE = DATE'" + date + "' AND " +
"USERID = " + userid + " ";
con.Open();
OracleDataAdapter adt = new OracleDataAdapter(redundant, con);
DataTable dt = new DataTable();
adt.Fill(dt);
con.Close();
if (dt.Rows.Count != 0)
{
MessageBox.Show("Entry aldready exist", "Duplicate not allowed", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
else
{
string sql = "INSERT INTO EXPENSE(EXPENSEID,EXPENSEPARTICULAR,EXPENSEAMOUNT,EXPENSEDATE,USERID) " +
"VALUES(" + expenseidmax + ",'" + particular + "'," + amount + ",DATE'" + date + "'," + userid + ")";
try
{
con.Open();
OracleCommand cmd = new OracleCommand(sql, con);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
MessageBox.Show(" Expense of \n" + textBox1.Text + " : " + textBox2.Text + " Added");
}
catch (Exception)
{
MessageBox.Show("Try Again");
}
}
}
}
//Delete Expense
private void button8_Click(object sender, EventArgs e)
{
//define variables
string particular, date;
double amount;
//Validation
DateTimePicker today = new DateTimePicker();
today.Value = DateTime.Today.Date;
string ErrorMsg;
var val = Income.validation(textBox1.Text, textBox2.Text, out ErrorMsg);
if (!val)
{
MessageBox.Show(ErrorMsg, "Entry Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else if (dateTimePicker1.Value.Date > today.Value)
{
MessageBox.Show("Entered Date falls after " + today.Value.ToString("dd-MM-yyyy") + "", "Date Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
particular = textBox1.Text;
amount = double.Parse(textBox2.Text);
date = dateTimePicker1.Value.Date.ToString("yyyy-MM-dd");
//oracle connection
OracleConnection con = new OracleConnection(connectionString);
string sql1 = "SELECT EXPENSEID FROM EXPENSE WHERE USERID=" + userid + "" +
" AND EXPENSEPARTICULAR = '" + particular + "' AND EXPENSEDATE = DATE'" + date + "'" +
" AND EXPENSEAMOUNT = " + amount + "";
string Sql(int expenseId)
{
return("DELETE FROM EXPENSE WHERE EXPENSEID = " + expenseId + "");
}
con.Open();
OracleDataAdapter adapter = new OracleDataAdapter(sql1, con);
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
con.Close();
try
{
if (dataTable.Rows.Count == 1)
{
int expenseId = int.Parse(dataTable.Rows[0][0].ToString());
DialogResult result = MessageBox.Show("Do you want to delete the entry?", "Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation);
if (result == DialogResult.Yes)
{
con.Open();
OracleCommand cmd = new OracleCommand(Sql(expenseId), con);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
cmd.Dispose();
con.Close();
MessageBox.Show("" + particular + " deleted.");
}
else
{
MessageBox.Show("Entry not deleted");
}
}
else
{
MessageBox.Show("Entry does not exist");
}
}
catch (Exception)
{
MessageBox.Show("Try Again");
}
}
}