com.microsoft.sqlserver.jdbc.SQLServerException: The value is not set for the parameter number 1

Question

I have a problem with line pstmt.setLong(1, id);. I get an error that the value is not set for the parameter number 1. If I use the String SQL without the question mark, it works. Also, when I use ARM the PreparedStatement and ResultSet are not automatically closed so I have to close them, and finally doesn't seem to work either

@Override  
public Company getCompany(long id) {  
    Connection con = ConnectionPool.getInstance().getConnection();  
    String sql = "SELECT * FROM Company WHERE ID=?";  
    //String sql = "SELECT * FROM Company WHERE ID=" + id;  
    Company company = new Company();  
    try (  
        PreparedStatement pstmt = con.prepareStatement(sql);  
        ResultSet rs = pstmt.executeQuery();)  
        {  
        pstmt.setLong(1, id);  
        if (rs.next()) { 
            company.setId(rs.getLong(1));  
            company.setCompName(rs.getString(2)); 
            company.setPassword(rs.getString(3));  
            company.setEmail(rs.getString(4)); 
        } else {  
            System.out.println("Company with ID: " + id + " could not be found\n"); 
        }  
        pstmt.close();  
        rs.close();  
    } catch (SQLException e) {  
        CouponSystemException ex = new CouponSystemException("Company with ID: " + id + " could not be retrieved\n", e);  
        System.out.println(ex.getMessage());  
        System.out.println(e);  
    }  
    ConnectionPool.getInstance().returnConnection(con);  
    return company;  
}

Solution 1

Set the parameter before executing the query. Also, You don't need to close Statement and result sets defined in try-with-resource statements as they'll be closed automatically when you leave the try scope.

try(PreparedStatement pstmt = con.prepareStatement(sql)) {
    pstmt.setLong(1, id);
    try(ResultSet rs = pstmt.executeQuery()) {
        // do stuff
    }
}

Solution 2

You need to set the PreparedStatement's parameters before executing it. Also note that this you're using the try-with-resource syntax you shouldn't close the resources yourself:

try (PreparedStatement pstmt = con.prepareStatement(sql)) {
    pstmt.setLong(1, id);
    try (ResultSet rs = pstmt.executeQuery()) {
        if (rs.next()) { 
            company.setId(rs.getLong(1));  
            company.setCompName(rs.getString(2)); 
            company.setPassword(rs.getString(3));  
            company.setEmail(rs.getString(4)); 
        } else {  
            System.out.println("Company with ID: " + id + " could not be found\n"); 
        }  
    }
} catch (SQLException e) {  
    CouponSystemException ex = new CouponSystemException("Company with ID: " + id + " could not be retrieved\n", e);  
    System.out.println(ex.getMessage());  
    System.out.println(e);  
}