c# Using The Select Command To display Info From The database

im trying to create a receipt that shows the transaction that the user did. in this case its a withdrawal transaction.  

the withdrawal transaction works fine, the problem when its time to display the transaction.

when i try to display the new balance that the user has after the withdrawal, it will still display the balance before the withdrawal was made and not the new balance.
public static void Wdrwl(string name)
      {

             string UsName = name;
          
          OleDbCommand cmd = DbUtils.GetCommand();
          cmd.CommandType = CommandType.Text;
             
          OleDbCommand cmdSelect = DbUtils.GetCommand();
          cmdSelect.CommandType = CommandType.Text;

          cmdSelect.CommandText = ("Select [Balance] from [Atm_Customers] WHERE UserName = '"+UsName+"'");

          OleDbDataReader reader = cmdSelect.ExecuteReader();
                 

          //Console.WriteLine(UsName);
          Console.Write("how much to withdrawal: ");
          string mny = Console.ReadLine();
          
          Convert.ToInt32(mny);

          if(reader.Read() == true)
          {
            if((Convert.ToInt32( mny)) <= Convert.ToInt32(reader[0]))
            {

                      // Add 'name' etc.
                       cmd.CommandText = ("UPDATE [Atm_Customers] SET [Balance] =([Balance] -@b) WHERE UserName= '"+UsName+"'");
                      cmd.Parameters.AddWithValue("@b", mny);
          
                      cmd.ExecuteNonQuery();
                     
                      PrintReceipt.PrntRcp(UsName, mny); //print receipt
                      Console.Read();
                      
             }
             else
             {
                 Console.WriteLine("Insufficient Funds");
                 Console.Read();
                 Wdrwl(UsName);
             }
          }

//
 public static void PrntRcp ( string Usn, string with )
       {
           string User = Usn;
           string withdrwl = with;
           Console.WriteLine(withdrwl); //testing
           Console.WriteLine(User); // testing
           OleDbCommand cmd = DbUtils.GetCommand();
           cmd.CommandType = CommandType.Text;

           cmd.CommandText = ("Select  FirstName,LastName,Balance from Atm_Customers WHERE UserName = '" + User + "'");

           OleDbDataReader reader = cmd.ExecuteReader();

           string fnn = string.Empty;
           while (reader.Read())
           {
               fnn = reader["FirstName"].ToString() +" "+ reader["LastName"].ToString();
              Console.WriteLine("balance: " + reader["Balance"].ToString());
               
               Console.WriteLine(fnn);
               Console.Read();
           }



       }

Open in new window

Ajs135Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SameerJagdaleCommented:
what i could think of is - you might be eating up any exceptions, as i cannot see and try..catch..block.
are you sure the update statement works fine. have you checked if the rows returned after the update is only one and not multiple?

For this - cmdSelect.CommandText = ("Select [Balance] from [Atm_Customers] WHERE UserName = '"+UsName+"'");

          OleDbDataReader reader = cmdSelect.ExecuteReader();
you can actually use cmdSelect.ExecuteScalar() instead of reader.
0
Ajs135Author Commented:
the database is updating fine,  it just when it comes to displaying on screen.

theres 4000,  i withdrawal 500,    on the database shows  3500 left.    but on the screen it showing 4000

thats the issue im having
0
mkobrinCommented:
Are you saying that the Console.Writeline is showing a balance of 4000, or are you calling this function from a form via a button, and the form's data is not being updated?

If you are calling it from a form, and you need the form to update then both of your functions should be returning an object that you can use to update the form.

Please clarify this in order for us to help you further.
0
MathiyazhaganCommented:
I am suspecting that transaction is not commited  while you are printing receipt. close the connection once you executed your query. I have refactored your code . give a try ...
public static void Wdrwl(string name)
      {

        string UsName = name;
        int bal=0;  
        Console.Write("how much to withdrawal: ");
        string mny = Console.ReadLine();
         
          OleDbCommand cmdSelect = DbUtils.GetCommand();
          cmdSelect.CommandType = CommandType.Text;
          cmdSelect.CommandText = ("Select [Balance] from [Atm_Customers] WHERE UserName = '"+UsName+"'");

          OleDbDataReader reader = cmdSelect.ExecuteReader();
                 
          if(reader.Read() == true)
          {
              bal = Convert.ToInt32(reader[0]))
                
          } 
          reader.Close();
         
          if((Convert.ToInt32( mny)) <=bal)
            {
		 OleDbCommand cmd = DbUtils.GetCommand();
	         cmd.CommandType = CommandType.Text;
                      // Add 'name' etc.
                 cmd.CommandText = ("UPDATE [Atm_Customers] SET [Balance] =([Balance] -@b) WHERE UserName= '"+UsName+"'");
                 cmd.Parameters.AddWithValue("@b",Convert.ToInt32( mny));
                  int rowsAffected = cmd.ExecuteNonQuery(); //returns no. of rows affected
                   //close connection here
                  if (rowsAffected  >0) //successful transaction
                  {
                	         PrintReceipt.PrntRcp(UsName, mny); //print receipt
	                 Console.Read();
		  }
                     
             }
             else
             {
                 Console.WriteLine("Insufficient Funds");
                 Console.Read();
                 Wdrwl(UsName);//calling recursive ???
             }
          }

//
 public static void PrntRcp ( string Usn, string with )
       {
           string User = Usn;
           string withdrwl = with;
           Console.WriteLine(withdrwl); //testing
           Console.WriteLine(User); // testing
           OleDbCommand cmd = DbUtils.GetCommand();
           cmd.CommandType = CommandType.Text;

           cmd.CommandText = ("Select  FirstName,LastName,Balance from Atm_Customers WHERE UserName = '" + User + "'");

           OleDbDataReader reader = cmd.ExecuteReader();

           string fnn = string.Empty;
           while (reader.Read())
           {
              fnn = reader["FirstName"].ToString() +" "+ reader["LastName"].ToString();
              Console.WriteLine("balance: " + reader["Balance"].ToString());
              Console.WriteLine(fnn);
              Console.Read();
           }
       }

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ajs135Author Commented:
yea that it , i wasnt closing the connection , after executing the query.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.