File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes retrieving autonumber field from MSAccess Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "retrieving autonumber field from MSAccess" Watch "retrieving autonumber field from MSAccess" New topic
Author

retrieving autonumber field from MSAccess

neha arora
Greenhorn

Joined: May 03, 2009
Posts: 2
hello all..

i have a table in MSaccess with the one of the fields as-
SSNO (AutoNumber)

i want to retrieve this value as a new row gets inserted into the table.
i have tried last_insert_id() -> it gives "Driver Does not support this function"

Please help me..
Thank You...


Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 29219
    
134

Neha,
Welcome to JavaRanch!

Not all databases (or drivers) support retrieving the last record. MS Access is known for not supporting everything. Maybe you could write a query to get this number yourself?


[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
ujjwal soni
Ranch Hand

Joined: Mar 28, 2007
Posts: 403
Hi,

You can get that by firing select max(yourautonumberfieldname) from yourtable query.


Cheers!!!
Ujjwal B Soni <baroda, gujarat, india> <+919909981973>
"Helping hands are better than praying lips......"
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2476
    
    7

ujjwal soni wrote:Hi,

You can get that by firing select max(yourautonumberfieldname) from yourtable query.
This might not give you your latest id. It will only guarantee to give you the highest value of ourautonumberfieldname available in the table. And it might result in a table lock.


OCUP UML fundamental and ITIL foundation
youtube channel
neha arora
Greenhorn

Joined: May 03, 2009
Posts: 2
hey.. thank you all..

i have finally used
count=0;
while(rs.next())
count++;

and again moved rs.first() by making rs as ResultSet.TYPE_SCROLL_SENSITIVE
it worked fine...
steve souza
Ranch Hand

Joined: Jun 26, 2002
Posts: 852
I suspect that your code will not work if someone else inserts in between the original insert and when your code finishes its loop. It looks like the @@identity variable exists in access per the following from microsofts site. I don't use access so you should look into this further.

After an insert a query like 'select @@identity' will return the last identity used by that connection. So even if another user makes an insert it won't affect the return variable. Plus it will be much faster than the other approach.

However, Microsoft Access 2000 or later does support the @@IDENTITY property to retrieve the value of an Autonumber field after an INSERT. Using the RowUpdated event, you can determine if an INSERT has occurred, retrieve the latest @@IDENTITY value, and place that in the identity column of the local table in the DataSet.


http://www.jamonapi.com/ - a fast, free open source performance tuning api.
JavaRanch Performance FAQ
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: retrieving autonumber field from MSAccess
 
Similar Threads
Can some one help me with Entity Beans
getting the last inserted value
How to INSERT with an autoincrement column
Autonumber Primary Keys in MS-Access
Syntax error for Access Database