Win a copy of JDBC Workbook this week in the JDBC and Relational Databases forum
or A Day in Code in the A Day in Code forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Jeanne Boyarsky
  • Junilu Lacar
  • Henry Wong
Sheriffs:
  • Ron McLeod
  • Devaka Cooray
  • Tim Cooke
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Frits Walraven
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Piet Souris
  • salvin francis
  • fred rosenberger

retrieving autonumber field from MSAccess

 
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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...


 
author & internet detective
Posts: 39986
806
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Ranch Hand
Posts: 405
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

You can get that by firing select max(yourautonumberfieldname) from yourtable query.
 
Bartender
Posts: 2658
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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.
 
neha arora
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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...
 
Ranch Hand
Posts: 862
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.

 
I was born with webbed fish toes. This tiny ad is my only friend:
ScroogeXHTML 9.0 - RTF to HTML5 and XHTML converter
https://coderanch.com/t/731949/ScroogeXHTML-RTF-HTML-XHTML-converter
    Bookmark Topic Watch Topic
  • New Topic