aspose file tools*
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
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Getting Largest Row Number" Watch "Getting Largest Row Number" New topic
Author

Getting Largest Row Number

Kassi Hill
Greenhorn

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";
try{
rs = dataAccess.runSelectQuery(sql3);
while (rs.next()){
qID = (rs.getInt("qID"));
qID = qID +1;
System.out.println(qID);
}
}catch(Exception sqle) {
System.err.println("SQL NOT Executed: " + sqle);
}
return qID;
}
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30537
    
150

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: http://aspose.com/file-tools
 
subject: Getting Largest Row Number