• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 324
  • Last Modified:

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

0
Ajs135
Asked:
Ajs135
1 Solution
 
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
 
Ajs135Author Commented:
yea that it , i wasnt closing the connection , after executing the query.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now