What method to use to get MAX of an AutoNumber field?

Is there a method to get the maximum of an AutoNumber field?

Page 1 of 1

2 Replies - 1138 Views - Last Post: 13 October 2009 - 08:45 AM Rate Topic: -----

#1 leeward  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 11-October 09

What method to use to get MAX of an AutoNumber field?

Posted 13 October 2009 - 07:29 AM

Hi

I'm using Java, JDBC and SQL to access a MS Access database, and all my SQL statements work fine, but I'm having trouble with finding an int.

I have to find the biggest number in an AutoNumber field of all records, not just the selection.
Thankfully this field is the primary key, if that helps.
Is there a method to get this value as an int?
And would it be a method of the Statement class?

The method I have is:

	public void enterHS(String sqlStmt) //enters new high score
		throws SQLException
	{
		Statement stmt = conn.createStatement();
		stmt.executeUpdate(sqlStmt); //the SQL statement is ("INSERT INTO Scores(WinnerName,TotalMoves) VALUES ('"  +w+  "','"  +m+  "')") which works fine.
			//The field Scores.GameID is the primary key AutoNumber I'm looking for.
		int thisGame = stmt.getMaxRows(); //this is the problematic line
		stmt.close();
	}



But this doesn't work so well if some records have been deleted. :huh:
On top of that it always ends up being 0 for some reason.
What am I doing wrong?

I can't find anything better than the getMaxRows() method in the API.
What else do you suggest?

Thanks

Is This A Good Question/Topic? 0
  • +

Replies To: What method to use to get MAX of an AutoNumber field?

#2 macosxnerd101  Icon User is online

  • Self-Trained Economist
  • member icon




Reputation: 10563
  • View blog
  • Posts: 39,087
  • Joined: 27-December 08

Re: What method to use to get MAX of an AutoNumber field?

Posted 13 October 2009 - 08:07 AM

You might want to consider using a ResultSet object to store the results of your querey. It tokenizes each cell, allowing you to access each individual token as one of a few datatypes.

For more information on result set:
http://java.sun.com/.../ResultSet.html
Was This Post Helpful? 1
  • +
  • -

#3 leeward  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 11-October 09

Re: What method to use to get MAX of an AutoNumber field?

Posted 13 October 2009 - 08:45 AM

Yes, I did something like that, but instead just sorted them descending to get the biggest first.

public int getLatestNum()
		throws SQLException
	{
		int k = 0;
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery("SELECT Scores.GameID FROM Scores ORDER BY Scores.GameID DESC");
		rs.next();
		k = rs.getInt(1);
		
		stmt.close();
		return k;
	}



Thank you for your help!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1