none
how to deposit amount in database? RRS feed

  • Question

  • Hi All!

     i am trying to make a small ATM application in ado.net using visual studio 2010 and back end database connectivity with microsoft access 2007. i have form containing buttons of deposit and withdraw..i just want to add the value which i enter in the textBox to the value which is already exist in database..i want to store the previous value of database in a variable..but i couldn't do this..plz help me out that how can i do this..which query shoul i use and how???


    MadihaMalik

    Monday, July 23, 2012 4:22 PM

Answers

  • Hi Madiha,

    Welcome to the MSDN Forum.

    Please try this code:

                try
                {
                    OleDbConnection con = new OleDbConnection("provider=microsoft.ACE.oledb.12.0; data source= " + Application.StartupPath + "/atmdb.accdb");
                    con.Open();
    
    //select Account_no. from comboBox whose Balanceis to be modified
     
                    OleDbCommand cmd = new OleDbCommand("select * from atmtable where Account_no=" + comboBox1.SelectedItem + "   ", con);
                    OleDbDataReader dr = cmd.ExecuteReader();
                    if (dr.Read())
                    {
                        OleDbCommand cmd2 = new OleDbCommand("update atmtable set Balance= Balance + " + textBox3.Text + " where Account_no="+comboBox1.SelectedItem+"  ", con);
                        cmd2.ExecuteNonQuery();
                        //textBox3.Text = ""+dr.GetValue(2);                 
                    }
                    else
     
                    {
                        MessageBox.Show("No Item selected from Combo Box ");
                        
                    }
                }
                catch (Exception e1)
                {
                    MessageBox.Show("Error:" + e1.Message);
                }

    I hope this will be helpful.

    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by MadihaMalik1 Tuesday, July 24, 2012 10:27 AM
    Tuesday, July 24, 2012 9:55 AM
    Moderator
  • Always think of security even in test programs.  You need to use parameters in your SQL statement to avoid risk of SQL injection. 

    If your balance column is a numeric data type, you only need two statements to perform the update and the update can be done without wrapping multiple statements in a transaction.

    UPDATE atmtable SET balance = balance + ? where account_no = ?
    SELECT balance from atmtable where account_no = ?

    The first statement does the update, and the second gets the new value.  On the other hand if you need to do data conversion, you need a transaction to prevent another update from occuring between the time you read the value and the time you update.

    BEGIN TRANSACTION
    SELECT balance from atmtable where account_no = ?
    /* (do your conversion and addition) */
    UPDATE atmtable SET balance = ? where account_no = ?
    COMMIT

    Also, if you're going to get values by index: i.e. dr.GetValue(2) you should include explicit column names in your SELECT statement as I've done above.  Otherwise somebody changing the order of columns in the database will break your program.  If you must use SELECT *, then ask for data values by name instead of index.


    This signature unintentionally left blank.


    • Edited by Nick F. _ Tuesday, July 24, 2012 10:10 AM
    • Marked as answer by MadihaMalik1 Tuesday, July 24, 2012 10:29 AM
    Tuesday, July 24, 2012 10:09 AM
  • Hi all!

    thanx alot to all of you.specially thanx to MIKE FENg..the code is working awsomely...:)

    also thanx to NICK F. your answer was so helpful...:)

    i also run the following code its also working well. i wana share it with you..but i think the code given by MIKE FENG is preferable..

    Thanx..:)

    try
                {
                    OleDbConnection con = new OleDbConnection("provider=microsoft.ACE.oledb.12.0; data source= " + Application.StartupPath + "/atmdb.accdb");
                    con.Open();
                    OleDbCommand cmd = new OleDbCommand("select * from atmtable where Account_no=" + comboBox1.SelectedItem + "   ", con);
                    OleDbDataReader dr = cmd.ExecuteReader();
                    int a, b, c;
                    a = Convert.ToInt32(textBox2.Text);
                    b = Convert.ToInt32(textBox3.Text);
                    c = a + b;
                    textBox3.Text = "" + c;
                    if (dr.Read())
                    {                    
                        OleDbCommand cmd2 = new OleDbCommand("update atmtable set Balance=" + textBox3.Text + " where Account_no=" + comboBox1.SelectedItem + "  ", con);
                        cmd2.ExecuteNonQuery();
                        textBox3.Text = "" + dr.GetValue(2);
                        MessageBox.Show("Value Updated");
                        textBox1.Clear();
                        textBox2.Clear();
                        textBox3.Clear();
                        textBox4.Clear();
                        comboBox1.Text = "";
                    }
                    else
                    {
                        MessageBox.Show("No Item selected from Combo Box ");
    
                    }
                }
                catch (Exception e1)
                {
                    MessageBox.Show("Error:" + e1.Message);
                }



    MadihaMalik

    • Marked as answer by MadihaMalik1 Tuesday, July 24, 2012 12:05 PM
    Tuesday, July 24, 2012 12:04 PM

All replies

  • There are lots of ways of going about this.  Essentially, you need to query the database to get the value to display in the form, and you'll need to update the database when you change the value.  There are numerous ways of making happen with this less code, or in a more automatic kind of way such that your code matches your data nicely, particularly in the realm of entities and data binding.

    I would start with the Entity Framework.  Do you have a particular approach that you have in mind?

    Monday, July 23, 2012 4:52 PM
  • What columns does your table contain? If the table name is account, it containes account_number & balance columns, then when the button is clicked you can do it as below,

    string connString = "";
    OledbConnection conn = new OledbConnection(connString);
    OledbCommand command = conn.CreateCommand();
    command.CommandType = CommandType.Text;
    command.CommandText = "select balance from account where account_nbr = 12345";
    var reader = command.ExecuteReader();
    double curBalance = Convert.ToDouble(reader[0]);
    double newBalance = curBalance + double.Parse(textBox1.Text);
    command.CommandText = "Update account set balance = "+ newBalance + " where account_number = 12345";
    command.ExecuteNonQuery();
    conn.Close();
    

    I hope this helps.


    Please mark this post as answer if it solved your problem. Happy Programming!

    Monday, July 23, 2012 4:52 PM
  • I have written this code yet on deposit amount button..my database name is "atmdb.accdb", the table name is "atmtable". the table contains the columns "Account_no","Customer_name","Balance" i want to update the balance and add the value entered in textbox to the previous value in database..

    try { OleDbConnection con = new OleDbConnection("provider=microsoft.ACE.oledb.12.0; data source= " + Application.StartupPath + "/atmdb.accdb"); con.Open();

    //select Account_no. from comboBox whose Balanceis to be modified OleDbCommand cmd = new OleDbCommand("select * from atmtable where Account_no=" + comboBox1.SelectedItem + " ", con); OleDbDataReader dr = cmd.ExecuteReader(); if (dr.Read()) { OleDbCommand cmd2 = new OleDbCommand("update atmtable set Balance=" + textBox3.Text + " where Account_no="+comboBox1.SelectedItem+" ", con); cmd2.ExecuteNonQuery(); textBox3.Text = ""+dr.GetValue(2); } else { MessageBox.Show("No Item selected from Combo Box "); } } catch (Exception e1) { MessageBox.Show("Error:" + e1.Message); }


    this the code plz make it helpful for me if anyone can....what query should i use in cmd2 command??

    Monday, July 23, 2012 5:12 PM
  • Hi Madiha,

    Welcome to the MSDN Forum.

    Please try this code:

                try
                {
                    OleDbConnection con = new OleDbConnection("provider=microsoft.ACE.oledb.12.0; data source= " + Application.StartupPath + "/atmdb.accdb");
                    con.Open();
    
    //select Account_no. from comboBox whose Balanceis to be modified
     
                    OleDbCommand cmd = new OleDbCommand("select * from atmtable where Account_no=" + comboBox1.SelectedItem + "   ", con);
                    OleDbDataReader dr = cmd.ExecuteReader();
                    if (dr.Read())
                    {
                        OleDbCommand cmd2 = new OleDbCommand("update atmtable set Balance= Balance + " + textBox3.Text + " where Account_no="+comboBox1.SelectedItem+"  ", con);
                        cmd2.ExecuteNonQuery();
                        //textBox3.Text = ""+dr.GetValue(2);                 
                    }
                    else
     
                    {
                        MessageBox.Show("No Item selected from Combo Box ");
                        
                    }
                }
                catch (Exception e1)
                {
                    MessageBox.Show("Error:" + e1.Message);
                }

    I hope this will be helpful.

    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by MadihaMalik1 Tuesday, July 24, 2012 10:27 AM
    Tuesday, July 24, 2012 9:55 AM
    Moderator
  • Always think of security even in test programs.  You need to use parameters in your SQL statement to avoid risk of SQL injection. 

    If your balance column is a numeric data type, you only need two statements to perform the update and the update can be done without wrapping multiple statements in a transaction.

    UPDATE atmtable SET balance = balance + ? where account_no = ?
    SELECT balance from atmtable where account_no = ?

    The first statement does the update, and the second gets the new value.  On the other hand if you need to do data conversion, you need a transaction to prevent another update from occuring between the time you read the value and the time you update.

    BEGIN TRANSACTION
    SELECT balance from atmtable where account_no = ?
    /* (do your conversion and addition) */
    UPDATE atmtable SET balance = ? where account_no = ?
    COMMIT

    Also, if you're going to get values by index: i.e. dr.GetValue(2) you should include explicit column names in your SELECT statement as I've done above.  Otherwise somebody changing the order of columns in the database will break your program.  If you must use SELECT *, then ask for data values by name instead of index.


    This signature unintentionally left blank.


    • Edited by Nick F. _ Tuesday, July 24, 2012 10:10 AM
    • Marked as answer by MadihaMalik1 Tuesday, July 24, 2012 10:29 AM
    Tuesday, July 24, 2012 10:09 AM
  • Hi all!

    thanx alot to all of you.specially thanx to MIKE FENg..the code is working awsomely...:)

    also thanx to NICK F. your answer was so helpful...:)

    i also run the following code its also working well. i wana share it with you..but i think the code given by MIKE FENG is preferable..

    Thanx..:)

    try
                {
                    OleDbConnection con = new OleDbConnection("provider=microsoft.ACE.oledb.12.0; data source= " + Application.StartupPath + "/atmdb.accdb");
                    con.Open();
                    OleDbCommand cmd = new OleDbCommand("select * from atmtable where Account_no=" + comboBox1.SelectedItem + "   ", con);
                    OleDbDataReader dr = cmd.ExecuteReader();
                    int a, b, c;
                    a = Convert.ToInt32(textBox2.Text);
                    b = Convert.ToInt32(textBox3.Text);
                    c = a + b;
                    textBox3.Text = "" + c;
                    if (dr.Read())
                    {                    
                        OleDbCommand cmd2 = new OleDbCommand("update atmtable set Balance=" + textBox3.Text + " where Account_no=" + comboBox1.SelectedItem + "  ", con);
                        cmd2.ExecuteNonQuery();
                        textBox3.Text = "" + dr.GetValue(2);
                        MessageBox.Show("Value Updated");
                        textBox1.Clear();
                        textBox2.Clear();
                        textBox3.Clear();
                        textBox4.Clear();
                        comboBox1.Text = "";
                    }
                    else
                    {
                        MessageBox.Show("No Item selected from Combo Box ");
    
                    }
                }
                catch (Exception e1)
                {
                    MessageBox.Show("Error:" + e1.Message);
                }



    MadihaMalik

    • Marked as answer by MadihaMalik1 Tuesday, July 24, 2012 12:05 PM
    Tuesday, July 24, 2012 12:04 PM