Results 1 to 9 of 9
  1. #1
    MS-POWER is offline Member
    Join Date
    Oct 2016
    Posts
    24
    Rep Power
    0

    Default help in set condition where

    Hi dear I could not able to set the condition for hours ...


    I want to add condition where Client_Number = ? and read the client number from JtextField when the Jframe loaded

    I tried many ways but all not succeeded.

    thanks

    Java Code:
    
     public void pointsOfLoyaltyByClient(){
    
      try {
                
       String sql = "SELECT   sum(Service_Points_Earned),sum(PointsUsed) FROM clientLoyalty  ";
    
      pst = conn.prepareStatement(sql);
      rs = pst.executeQuery();
                
      double addA = rs.getDouble("sum(Service_Points_Earned)");
      double addB = rs.getDouble("sum(PointsUsed)");
                 
       double balance = addA - addB;
                 
      FieldLoyaltyPoints.setText(Double.toString(balance));
    
      } catch (SQLException  e) {
         JOptionPane.showMessageDialog(null, e);
      } 
            finally {
                try {
                    rs.close();
                    pst.close();
                } catch (Exception e) {
                }
            }   
        }

  2. #2
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    13,541
    Rep Power
    26

    Default Re: help in set condition where

    Have you tried adding the WHERE clause into that SQL statement?

    If so, can you show us the code with that in, including any errors that it has caused.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  3. #3
    MS-POWER is offline Member
    Join Date
    Oct 2016
    Posts
    24
    Rep Power
    0

    Default Re: help in set condition where

    Quote Originally Posted by Tolls View Post
    Have you tried adding the WHERE clause into that SQL statement?

    If so, can you show us the code with that in, including any errors that it has caused.

    there is No error but zero result ,may be I did not write the condition in correct way.

    I tried to add :

    Java Code:
    String id = JTextFieldClientID.getText();
                
     String sql = "SELECT   sum(Service_Points_Earned),sum(PointsUsed) FROM clientLoyalty WHERE Client_Number = '"+ id +"'  ";
    
                pst = conn.prepareStatement(sql);
                rs = pst.executeQuery();
                
                 double addA = rs.getDouble("sum(Service_Points_Earned)");
                 double addB = rs.getDouble("sum(PointsUsed)");
                 
                 double balance = addA - addB;

    but its show the result 0.0

    if I remove condition it will show correct calculation but for all Clients .



    I tried this one also and showing 0.0 result :

    Java Code:
    String anyname = JTextFieldClientID.getText();
                
                String sql = "SELECT   sum(Service_Points_Earned),sum(PointsUsed) FROM clientLoyalty WHERE Client_Number = ?  ";
    
                pst = conn.prepareStatement(sql);
                pst.setString(1, anyname);
                rs = pst.executeQuery();
                
                 double addA = rs.getDouble("sum(Service_Points_Earned)");
                 double addB = rs.getDouble("sum(PointsUsed)");
                 
                 double balance = addA - addB;

  4. #4
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    13,541
    Rep Power
    26

    Default Re: help in set condition where

    And when you run that exact query directly against the database using whatever db tool you have, what result do you get?

    By the way, you ought to alias your calculated columns:
    Java Code:
    SELECT sum(Service_Points_Earned) AS sum_spe, sum(PointsUsed) AS sum_pu ...
    Something like that.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  5. #5
    MS-POWER is offline Member
    Join Date
    Oct 2016
    Posts
    24
    Rep Power
    0

    Default Re: help in set condition where

    Quote Originally Posted by Tolls View Post
    And when you run that exact query directly against the database using whatever db tool you have, what result do you get?

    By the way, you ought to alias your calculated columns:
    Java Code:
    SELECT sum(Service_Points_Earned) AS sum_spe, sum(PointsUsed) AS sum_pu ...
    Something like that.
    yes directly I'm getting correct result .

  6. #6
    SurfMan's Avatar
    SurfMan is offline Godlike
    Join Date
    Nov 2012
    Location
    The Netherlands
    Posts
    1,991
    Rep Power
    8

    Default Re: help in set condition where

    Remember to call next() on a ResultSet before you use it. If next() returns true, there is a curent record, so you should do this:
    Java Code:
    pst = conn.prepareStatement(sql);
    pst.setString(1, anyname);
    rs = pst.executeQuery();
    
    if( rs.next()) {             
      double addA = rs.getDouble("sum(Service_Points_Earned)");
      double addB = rs.getDouble("sum(PointsUsed)");
      double balance = addA - addB;
    }

    You can also refer to column numbers, but will give you some extra work when moving columnames around in the query, and using that with * is not a good idea at all. Still, for queries like this it's useful:
    Java Code:
    double addA = rs.getDouble(1);
    double addB = rs.getDouble(2);
    "It's not fixed until you stop calling the problem weird and you understand what was wrong." - gimbal2 2013

  7. #7
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    13,541
    Rep Power
    26

    Default Re: help in set condition where

    Didn't spot the lack of a call to next(), but I would have expected an exception in that case.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  8. #8
    SurfMan's Avatar
    SurfMan is offline Godlike
    Join Date
    Nov 2012
    Location
    The Netherlands
    Posts
    1,991
    Rep Power
    8

    Default Re: help in set condition where

    Quote Originally Posted by Tolls View Post
    Didn't spot the lack of a call to next(), but I would have expected an exception in that case.
    So you expected a full blown problem statement, SSCCE, screenshot, errormessage and stacktrace? How naive! ;) The fact that OP didn't show us an Exception, doesn't mean there is one....
    "It's not fixed until you stop calling the problem weird and you understand what was wrong." - gimbal2 2013

  9. #9
    MS-POWER is offline Member
    Join Date
    Oct 2016
    Posts
    24
    Rep Power
    0

    Default Re: help in set condition where

    thanks for you all , this one solved by removing pointsOfLoyaltyByClient(); from constructor and add it to ComboBox selection statement :


    if (jComboBoxPaymentTypes.getSelectedItem().equals("L oyalty"))
    {
    pointsOfLoyaltyByClient();
    }

    .................................................. .........................................


    Java Code:
     public void pointsOfLoyaltyByClient(){
            
            try {
                
                String id = JTextFieldClientID.getText() ;
                
                String sql = "SELECT   sum(Service_Points_Earned) AS sum_spe ,sum(PointsUsed) AS sum_spe2 FROM clientLoyalty WHERE Client_Number = '" + id + "' ";
                pst = conn.prepareStatement(sql);
                rs = pst.executeQuery();
                
                if( rs.next()) { 
                 double addA = rs.getDouble("sum_spe");
                 double addB = rs.getDouble("sum_spe2");
                 
                 double balance = addA - addB;
                 
                FieldLoyaltyPoints.setText(Double.toString(balance));
                }
                    
               } catch (SQLException | NumberFormatException e) {
                JOptionPane.showMessageDialog(null, e);
               } 
            finally {
                try {
                    rs.close();
                    pst.close();
                } catch (Exception e) {
                }
            }   
        }
    Last edited by Tolls; 11-11-2016 at 12:04 PM.

Similar Threads

  1. condition
    By Kareem Mesbah in forum New To Java
    Replies: 5
    Last Post: 11-15-2012, 07:25 PM
  2. How to use class in if condition
    By raj.mscking@gmail.com in forum New To Java
    Replies: 8
    Last Post: 03-08-2012, 03:53 PM
  3. Condition ignored.
    By Pojahn_M in forum New To Java
    Replies: 8
    Last Post: 12-09-2011, 02:21 AM
  4. IF-Condition in a String
    By lenaz in forum Advanced Java
    Replies: 1
    Last Post: 07-18-2009, 12:07 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •