c# UPDATE USING INNER JOIN TAbles

i have a table  with account number and balance. another table with customer name.

when person withdrawal a number, the database needs to update with the new balance, but its not doing it.    it does read the balance of the customer, and check if the input value is greater than balance value, but it wont update it when it value is less the balance.
public static void Wdrwl(string name)
      {

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

          cmdSelect.CommandText = ("Select [Account.Balance] from [Account] INNER JOIN [Customer] ON Customer.ID=Account.ID WHERE Customer.FirstName = '"+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)
          {
              
              bal = Convert.ToInt32(reader[0]);
          }
          reader.Close();
           
          if((Convert.ToInt32( mny)) <= bal)
            {

                      // Add 'name' etc.
                       cmd.CommandText = ("UPDATE [Account] SET Account.Balance ='(Account.Balance -@b) INNER JOIN [Customer] ON Account.ID=Cstomer.ID' WHERE Customer.FirstName= '"+UsName+"'");
                      cmd.Parameters.AddWithValue("@b", mny);
                      
                     int rowsAffected=  cmd.ExecuteNonQuery();
                     cmd.Connection.Close();

                     if (rowsAffected > 0)
                     {

                        // PrintReceipt.PrntRcp(UsName, mny);
                         Console.Read();
                     }      
             }
             else
             {
                 Console.WriteLine("Insufficient Funds");
             
                 Wdrwl(UsName);
             }

Open in new window

Ajs135Asked:
Who is Participating?
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Ok, start over, then try this:
cmd.CommandText = "UPDATE Customer INNER JOIN Account ON Customer.ID = Account.ID SET Account.Balance = [Account].[Balance]-@b WHERE (([Customer].[FirstName]=@pFirstName))";
cmd.Parameters.AddWithValue("@b", mny);
cmd.Parameters.AddWithValue("@pFirstName", UsName);

Open in new window

0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Hi, please replace this lines:
cmd.CommandText = ("UPDATE [Account] SET Account.Balance ='(Account.Balance -@b) INNER JOIN [Customer] ON Account.ID=Cstomer.ID' WHERE Customer.FirstName= '"+UsName+"'");
cmd.Parameters.AddWithValue("@b", mny);

Open in new window


With this lines:
cmd.CommandText = "UPDATE [Account] SET Account.Balance = Account.Balance - @b FROM dbo.Account INNER JOIN [Customer] ON Account.ID = Customer.ID WHERE Customer.FirstName = @pFirstName";
cmd.Parameters.AddWithValue("@b", mny);
cmd.Parameters.AddWithValue("@pFirstName", UsName);

Open in new window


Hope this help.
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
To make sure, use this lines instead:
cmd.CommandText = "UPDATE [Account] SET Account.Balance = Account.Balance - @b FROM Account INNER JOIN [Customer] ON Account.ID = Customer.ID WHERE Customer.FirstName = @pFirstName";
cmd.Parameters.AddWithValue("@b", mny);
cmd.Parameters.AddWithValue("@pFirstName", UsName);

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Ajs135Author Commented:
i get an error

Syntax error (missing operator) in query expression 'Account.Balance - @b FROM Account INNER JOIN [Customer] ON Account.ID = Customer.ID'.


so i put the single quotes  , it goes through, but still wont update it,
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
mmm, please try again with this:
cmd.CommandText = "UPDATE [Account] SET Balance = (Balance - @b) FROM Account INNER JOIN [Customer] ON Account.ID = Customer.ID WHERE Customer.FirstName = @pFirstName";
cmd.Parameters.AddWithValue("@b", mny);
cmd.Parameters.AddWithValue("@pFirstName", UsName);

Open in new window


Or this:
cmd.CommandText = "UPDATE [Account] SET Balance = (Balance - ?) FROM Account INNER JOIN [Customer] ON Account.ID = Customer.ID WHERE Customer.FirstName = ?";
cmd.Parameters.AddWithValue("@b", mny);
cmd.Parameters.AddWithValue("@pFirstName", UsName);

Open in new window

0
 
Ajs135Author Commented:
nope still getting same  syntax error.
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Sure are you using Sql Server?
0
 
Ajs135Author Commented:
no using access, so i guess quotes are different?
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
For test only, replace the lines with:
cmd.CommandText = "UPDATE [Account] SET Balance = Balance - 0 FROM Account INNER JOIN [Customer] ON Account.ID = Customer.ID WHERE Customer.FirstName = 'HELLO'";

Open in new window

Dont include:
cmd.Parameters.AddWithValue("@b", mny);
cmd.Parameters.AddWithValue("@pFirstName", UsName);

Open in new window


It run fine?
0
 
Ajs135Author Commented:
oh i thuoght i put it in access forum, no wonder
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Oh buddy, that is, your question zone is: MS SQL Server... so im helping you with sql server.. let me see again...
0
 
Ajs135Author Commented:
yea there we go, now it working great. Thank you very much.
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Glad to help buddy, and make use of parameters for all yours commands, that is very easy with .net  ok, avoid parameter concatenation. like this "asd = '" + myValue + "'"
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.

All Courses

From novice to tech pro — start learning today.