JDBC Access Database Problem

[ivanyeates]

Im getting an:

ERROR: findSuppliersCandy(): Column not found

error in my program.
i have two related tables. One supplier table and one candy table for different types of candy.
the method below is trying to retrieve all the candy entries related to a specific supplier by passing a supplier id as a parameter

below is the code - any help would be appreciated

thanks

public List<Candy> findSuppliersCandy(int supplierId) throws DaoException {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Candy> candylist = new ArrayList<Candy>();
        try {
            con = getConnection();
            String query = "SELECT * FROM SUPPLIERS WHERE SUPPLIERID = ?";
            ps = con.prepareStatement(query);
            ps.setInt(1, supplierId);

            rs = ps.executeQuery();
            if (rs.next()) {
                supplierId = rs.getInt("SUPPLIERID");
                String name = rs.getString("NAME")

           query = "SELECT * FROM CANDY WHERE SUPPLIERID = ?";
                
                ps = con.prepareStatement(query);
                ps.setInt(1, supplierId);
                ResultSet rs2 = ps.executeQuery();
                if (rs2.next()) {

                    int candyId = rs.getInt("CANDYID");
                    String candyname = rs.getString("CANDYNAME");
                    String candyprice = rs.getString("CANDYPRICE");
                    
System.out.println(candyId + "\t" + candyname + "\t" + candyprice);
                    Candy candy = new Candy(candyId, candyname, candyprice);
                    candylist.add(candy);
                    rs2.close();
                    rs2 = null;
                }
            }
        } catch (SQLException e ) {
            throw new DaoException("findSuppliersCandy(): " + e.getMessage());
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (ps != null) {
                    ps.close();
                }
                if (con != null) {
                    freeConnection(con);
                }
            } catch (SQLException e) {
                throw new DaoException("findSuppliersCandy(): " + e.getMessage());
            }
        }
        return candylist;
    }

[veejo]

Check the Database:
SUPPLIERID,NAME attributes lies in SUPPLIERS table and SUPPLIERID,CANDYID,CANDYNAME,CANDYPRICE attributes lies in CANDY table..
It is better to use e.printStackTrace() instead of e.getMessage(). It'll help u to find the exact line of error.

[ivanyeates]

Check the Database:
SUPPLIERID,NAME attributes lies in SUPPLIERS table and SUPPLIERID,CANDYID,CANDYNAME,CANDYPRICE attributes lies in CANDY table..
It is better to use e.printStackTrace() instead of e.getMessage(). It'll help u to find the exact line of error.

Yeah, table names and structure are correct.
I tried changing the sqlexception to print stack trace but i am just getting errors.
If the tables are fine and the columns are there, what else would cause this column not found error??

[veejo]

column not found is purely a sql exception.. no other possibily of wrong logic.

String candyprice = rs.getString("CANDYPRICE");
change this line to
int candyprice = rs.getInt("CANDYPRICE");
'if the CANDYPRICE attribute type is int or number'. if this is the case u need to change the class structure of Candy and so on..

check the datasource, username, password wheather u have connected to the correct table.. it's rare case..

still u got problem, reply with error msg of printStackTrace(),table structure and getConnection() module