File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Getting Largest Row Number Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Java Interview Guide this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Getting Largest Row Number" Watch "Getting Largest Row Number" New topic

Getting Largest Row Number

Kassi Hill

Joined: Sep 29, 2003
Posts: 24
I need to write an SQL statement to get the Largest number out of an Access database table. I know it sounds simple but when I try "SELECT MAX (Question.qID) FROM Question"; this does not work, it says "Column Count not Found". If I run the query in Access then it works but I need to be able to get this number in JAVA so that I can then add one to it for a new id number. When I try just listing the id numbers in the table it works but when I add the MAX to the query I get an error. Thank you for your help!!
I have been trying something like:
private int getNewQID(){
int qID = 0;
String sql3 = "SELECT Max(Question.qID) FROM Question";
rs = dataAccess.runSelectQuery(sql3);
while ({
qID = (rs.getInt("qID"));
qID = qID +1;
}catch(Exception sqle) {
System.err.println("SQL NOT Executed: " + sqle);
return qID;
Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33134

The column name you are looking for probably idn't called qID. I don't know what name Access uses as a default for aggregate functions, but it's probably not something you want to code into your application. You have two options for solving the problem:
1) Explicitly name the column in your select statement. "SELECT MAX (Question.qID) as "qID" FROM Question"
2) Use the column number: rs.getInt(1);

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
I agree. Here's the link:
subject: Getting Largest Row Number
jQuery in Action, 3rd edition