CODEWITHSUNDEEP
sqljdbcms-access-2007
user1304731
user1304731

Reputation: 25

JDBC selecting the Max value from an Access table

I got error "Column not found" any time i run the following code even though the column exist in my table. Am using access database, Appealing for help please

public class Trial1 {
public static void main (String[]args){

            try{
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");  
                String url = "jdbc:odbc:SENSOR";
                String user = "";
                String pass = "";
                Connection con = DriverManager.getConnection(url,user,pass);
                Statement stmt  = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);                          //stmt代表資料庫連接成功


                ResultSet rs = stmt.executeQuery("select MAX(LevelNum) from NList");
                 if (rs.next()){

                    int w = rs.getInt("LevelNum");
                   int x= 3;                             

                double i = Math.pow(2, (w-x))-1;
                System.out.printf("i is  %f",i);}


                stmt.close();
                con.close();

            }catch(Exception e)
            {
                System.out.println("Error" + e);
            }

}

}

Upvotes: 2

Views: 8140

Answers (3)

iamfnizami
iamfnizami

Reputation: 183

If you execute your query it will return max value available in table or null if no record exists with column name MAX(LevelNum) instead of LevelNum.

And in your code you are getting returned value using statement

rs.getInt("LevelNum")

whereas you should use MAX(LevelNum) or use aliasing like-

SELECT MAX(LevelNum) AS MAX_NUM FROM NList

rs.getInt("MAX_NUM")

Upvotes: 0

Ravi
Ravi

Reputation: 31397

Try this...

class Trial1 {
public static void main (String[]args){

            try{
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");  
                String url = "jdbc:odbc:SENSOR";
                String user = "";
                String pass = "";
                Connection con = DriverManager.getConnection(url,user,pass);
                Statement stmt  = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);                          


                ResultSet rs = stmt.executeQuery("select MAX(LevelNum) as LEVELNUM from NList");
                 if (rs.next()){

                    int w = rs.getInt("LEVELNUM");
                   int x= 3;                             

                double i = Math.pow(2, (w-x))-1;
                System.out.printf("i is  %f",i);}


                stmt.close();
                con.close();

            }catch(Exception e)
            {
                System.out.println("Error" + e);
            }

}

Upvotes: 0

Roger Rowland
Roger Rowland

Reputation: 26259

Assuming the error is when you get the result rather than when you execute the query, you probably need something like this instead

// ...
ResultSet rs = stmt.executeQuery("select MAX(LevelNum) as maxLevel from NList");
if (rs.next())
{
    int w = rs.getInt("maxLevel");

    // ... etc.
}

Upvotes: 3

Related Questions