[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Max value + MS Access

Posted on 2007-03-20
7
Medium Priority
?
875 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 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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
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

Independent Software Vendors: 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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

834 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