08-25-2003, 06:41 AM
I am having trouble creating a recordset from the last recordset in a database. Can some one share how would you write an sql statement that would retreive the last record inserted. Here is the specs.
Database name: dbdog
fields ID, fname, lname,address
Note : "ID" is an autonumber.
depends on the db-format.
For MySQL, check out this sticky http://www.codingforums.com/showthread.php?s=&threadid=22879
08-28-2003, 05:30 PM
As you said autonumber you may be dealing with MS Access (my sympathy) in which case afaik there is no built in way to get last insert id, so
SELECT TOP 1 ID FROM owner ORDER BY ID DESC
is probably the best you will get and in a light load environment this will work 99.xx% of the time.
MS SQL, MySQL, PostGreSQL etc. all have built in methods for it.
That's not really bulletproof in a multiuser enviroment. It would be safer to add some sort of condition, where you check on one of the just inserted values. (the username of the one that made the insert, the registratipntim --> must first be 'frozen' in a variable, etc )like
SELECT TOP 1 ID FROM owner WHERE reguser='theuser' ORDER BY ID DESC
also only run this if you checkt that the record indeed was inserted