asked on

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

Carlos Villegas

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.

Carlos Villegas

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

Ajs135

ASKER

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,

Carlos Villegas

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

Ajs135

ASKER

nope still getting same syntax error.

Carlos Villegas

Sure are you using Sql Server?

Ajs135

ASKER

no using access, so i guess quotes are different?

Carlos Villegas

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?

Ajs135

ASKER

oh i thuoght i put it in access forum, no wonder

Carlos Villegas

Oh buddy, that is, your question zone is: MS SQL Server... so im helping you with sql server.. let me see again...

ASKER CERTIFIED SOLUTION

Carlos Villegas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ajs135

ASKER

yea there we go, now it working great. Thank you very much.

Carlos Villegas

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 + "'"