[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1618
  • Last Modified:

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

0
Ajs135
Asked:
Ajs135
  • 8
  • 5
1 Solution
 
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
 
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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
 
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
 
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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

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