Solved

Max value + MS Access

Posted on 2007-03-20
7
824 Views
Last Modified: 2008-02-26
How to retrieve the maximum value from the primary key?

I am trying to create a function that gets the maximum value from an MS Access database record.  I am creating a small jsp application to work with MS Access.  I am using a prepared statement something like this; "Select Max(table.id) from table".  I cannot seem to figure out how to get the result value.

Any help is greatly appreciated, thanks all.
0
Comment
Question by:OliviaRedhorse
  • 3
  • 3
7 Comments
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 100 total points
ID: 18758193
if you are using VBA

dim maxID
maxID=Dmax("ID", "NameOFTable")

in a query , your statement is correct
Select Max(table.id) from table
0
 
LVL 39

Assisted Solution

by:stevbe
stevbe earned 400 total points
ID: 18758221
I forget the names of the object types but I think you need to create a jdbc connection and open a dataset based on the SQL you posted above. Then you get the value by looking at the value of the collection of fields returned. You might be better off getting this question cross posted into a web TA
0
 

Author Comment

by:OliviaRedhorse
ID: 18758259
I keep getting the error; Invalid descriptor index

Yes, I do have a database connection.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 39

Assisted Solution

by:stevbe
stevbe earned 400 total points
ID: 18758355
SELECT Max([CustID]) AS Expr1 FROM tblCust;

where are you getting the error? In executing the pull of data or in trying to get the value out of the object returned? Do you have to *fill* and object after you describe it? It have been 4 years since I did anything with java/jsp I just donlt remeber the details.
0
 

Author Comment

by:OliviaRedhorse
ID: 18759440
This is what I am working with right now;

String sql = "Select Max(table.id) from table";
            PreparedStatement pstmt = con.prepareStatement(sql);
            ResultSet rset = pstmt.executeQuery();
     
            DBean dbean = new DBean();
            dbean.setNumber("What is suppose to go here?");
     
            pstmt.close();
            pstmt.close();

I am trying to get the value from the database and send it to a bean I created.  Then I can retrieve it later.
0
 
LVL 39

Accepted Solution

by:
stevbe earned 400 total points
ID: 18762846
String sql = "SELECT Max(table.id) AS MaxID FROM table";
PreparedStatement pstmt = con.prepareStatement(sql);
ResultSet rset = pstmt.executeQuery();    
rset.next();  
DBean dbean = new DBean();
dbean.setNumber(rs.getLong("MaxID");

Steve
0
 

Author Comment

by:OliviaRedhorse
ID: 18961754
Sorry for the late reply, I do have this working. Thanks
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now