Click here to Skip to main content
15,850,359 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
hey guys,

im developing an online library. in here i want to borrow a book. before borrow need to get total amount of pending loans and fines. when i execute this function it says
java.sql.SQLException: Column 'l_amt' not found
table name is correct. can anyone one tell me whats wrong with this code.

Java
@Override
    public double checkLoan(reader r) {
        //throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
        System.out.println("Inside checkLoan");
        double b_result = 0.00;
        Connection conn= null;
        PreparedStatement ptmt= null;
        ResultSet rset = null;
        try{
            conn= getConnection();             
            String queryString = "SELECT SUM(l_amt) FROM loan WHERE l_status='true' AND r_id=? ";
            ptmt = conn.prepareStatement(queryString);
            ptmt.setString(1, r.getR_id());
            System.out.println(r.getR_id());
            rset = ptmt.executeQuery();
            while (rset.next()){
                System.out.println(rset.next());
                b_result = rset.getDouble("l_amt");   
                                        
                System.out.println("-.-.-."+b_result);
            }
        }catch (Exception ex){
            ex.printStackTrace();
        }finally{
            try{
                ptmt.close();
                    conn.close();
            }catch(SQLException ex){
                ex.printStackTrace();
            }
        }return b_result;
    }

    @Override
    public double checkFine(reader r) {
        //throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
        System.out.println("Inside checkFine");
        double b_result = 0.00;
        Connection conn= null;
        PreparedStatement ptmt= null;
        ResultSet rset = null;
        try{
            conn= getConnection();             
            String queryString = "SELECT SUM(f_amt) AS sum_fine_amt FROM fine WHERE f_status='true' AND r_id=?";
            ptmt = conn.prepareStatement(queryString);
            ptmt.setString(1, r.getR_id());
            System.out.println(r.getR_id());
            rset = ptmt.executeQuery();
            while (rset.next()){
                System.out.println(rset.next());
                b_result = rset.getDouble("sum_fine_amt");           
                         
                System.out.println("-.-.-."+b_result);
            }
        }catch (Exception ex){
            ex.printStackTrace();
        }finally{
            try{
                ptmt.close();
                 conn.close();
            }catch(SQLException ex){
                ex.printStackTrace();
            }
        }return b_result;
    }

    @Override
    public void borrow(book b, reader r) {
        //throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
        System.out.println("Inside borrow");
        Connection conn=null;
        PreparedStatement psmt=null;
        try{
            conn = getConnection();
            String queryString = "INSERT INTO book_borrow(b_id,rid) VALUES(?,?)";
            psmt = conn.prepareStatement(queryString);

            psmt.setString(1,b.getB_id());            
            psmt.setString(2,r.getR_id());
            
            int result = psmt.executeUpdate();
            System.out.println("result :" + result);
        }
        catch(Exception ex){
            ex.printStackTrace();
        }
        finally{
            try {
                 psmt.close();
                 conn.close();
             } catch (SQLException ex) {
                 ex.printStackTrace();
             }
        }
    }
Posted
Comments
mali_angel 11-Sep-13 3:28am    
Thanx everybody it works!

Remove the following lines
Java
System.out.println(rset.next());
 
Share this answer
 
v2
If you want the l_amt column in your result set, then you have to include the l_amt column in your WHERE clause ; moreover, if you want to SUM, you have to add a GROUP BY clause also.
C#
String queryString = "SELECT l_amt, SUM(f_amt) AS sum_fine_amt FROM fine WHERE f_status='true' AND r_id=? GROUP BY ?";

Here you have to give a column name used for groupment (ie. replace the '?' after GROUP BY with a column name).
 
Share this answer
 
Comments
Shubhashish_Mandal 10-Sep-13 5:45am    
"SELECT SUM(l_amt) FROM loan WHERE l_status='true' AND r_id=12" , this query execute fine in my case. without adding the group by .
phil.o 10-Sep-13 5:55am    
Except you are not the OP, and his issue could not be yours...
Moreover, he has to sum on column f_amt, not l_amt. Thus the GROUP BY statement.
Shubhashish_Mandal 10-Sep-13 6:21am    
you post different query rather than OP. OP's first query is on the l_amt

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900