示例#1
0
        //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");
                    }
                }
            }
        }
示例#2
0
        //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");
                }
            }
        }