[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Max value + MS Access

Posted on 2007-03-20
7
Medium Priority
?
873 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 400 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 1600 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 39

Assisted Solution

by:stevbe
stevbe earned 1600 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 1600 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

656 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