JDBC / MS ACCESS 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;
    }

[Zhivago]

Amazingly enough, it appears as if you are referencing a column that doesn't exist. I trust you understand what this means.

Check to make sure columns all columns you reference, such as 'CANDYID', 'CANDYPRICE', 'SUPPLIERID', etc.. all exist with no typos.

[jcrox]

I don't really use MS Access but with MySQL the first thing I usually do is open up SQL Browser, copy and paste the SQL statement into it and run it to make sure that I'm getting back the data that I'm expecting as well as check the column names to make sure that I haven't screwed anything up there.

If I don't find my error there I'll usually add the following to my catch statement: System.out.println(e.printStackTrace);
and figure out which column it is that is not being found and investigate from there.

[Tuork]

Originally Posted by Zhivago

Amazingly enough, it appears as if you are referencing a column that doesn't exist. I trust you understand what this means.

Check to make sure columns all columns you reference, such as 'CANDYID', 'CANDYPRICE', 'SUPPLIERID', etc.. all exist with no typos.

What he said.