File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC 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 EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
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
internet detective

Joined: May 26, 2003
Posts: 29237

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);

[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
I agree. Here's the link:
subject: Getting Largest Row Number
Similar Threads
How to get a row count
Problems Inserting Info. into Access DB
Trouble Displaying all the data
Selection from Database - Try/catch error
HELP! Connection to MS Access DB Trouble