Get Last Inserted ID using CRecordset Class


I have auto increment fields in my database and need
to determine what the Last Inserted ID was for any new record.

As ExecuteSQL does not retrieve any records for you, is there another way
to execute the follwing and retrieve the value?


I have worked around it for short term with


but this is only good if I can ensure that no other inserts are made on the table in question.

Any suggestions??

Thanks in Advance

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AndyAinscowFreelance programmer / ConsultantCommented:
Instead of ExecuteSQL how about AddNew of the recordset and getting the value of the ID after an update.
LittlePersonAuthor Commented:
Hi Andy

I tried that but it seemed to only give me the value for the first record in the table.
I had thought this may be how it worked but maybe I missed something!

I'll have another go!

LittlePersonAuthor Commented:
That method is definitely not working, I always get the first record back. Is this
because I am using single row fetching?


CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

AndyAinscowFreelance programmer / ConsultantCommented:
I use the CDaoRecordset (for MS Access), unfortunately that doesn't go to the newly added row with an AddNew.  Using ADO it does behave that way so I thought it was worth a stab.
With DAO I use
directly after the Update.  If your provider supports bookmarks maybe that or similar will help.
AndyAinscowFreelance programmer / ConsultantCommented:
If you are interested in ADO then the following link is very useful

write a query with max function like this..

select max(ID) from <<table>>

here i'm givinging example

for retriving Data from data base use CRecordset;
#include <afxdb.h>

CDatabase data;
CRecordset rs(&data);

CString strId;
rs.Open(CRecordset::dynaset, "select max(ID) from <<table>>" );

i hope above code will help u..
In general, what you are doing will not work with relational database.
like you pointed out if multi user update the database, between inserting & fetching the last record someone else could have inserted records.

There is two solutions that I know, it depends on how the table is define:
first solution:
    if your record as another unique key that you supply, then use that key to fetch the record after an insert. let's suppose that name must be unique, but ID is an auto increment number
  insert "Bill gates"
  select * from User where = 'Bill gates'
  you will have the ID associated with bill gates

Solution 2: when you cannot use a unique field like name.
    change you table so that the field is not autoincrement, just unique.

    Have a table in the db that will give you last number then increment that last number
    all in one shot (very db specific) usually done with a procedure.
   use that number when inserting.

   for performance reason, you should have as many LastNumberTable as you have tables using them


AndyAinscowFreelance programmer / ConsultantCommented:
Hi Danderson.
I use MS Access and the CDAORecordset.  (Access is relational DB).  If the recordset you have does not 'see' additions from other users then the bookmark technique should always provide you with the record you added.  The important thing is not to requery the set.  So Littleperson requires 1) a recordset that supports bookmarks and 2) a recordset that isn't dynamic.  If the recordset doesn't support bookmarks then he needs to start at the first record and step through, searching for the largest value of his autoincrement field.  Again that requires a static set (one that doesn't respond to the additions of other users).
Hi AndyAinscow!

What you say might work with access, but in limited ways. scrolling through recordset works for small db, but as the system become bigger it does not scale well. let say you have 100000 records, scanning will take many seconds. doing that for every insertion would be a waste.

maybe with access you can circumvent that problem with a bookmark, but if he want his software to work with other db engine (SQL SERVER, Sql Anywhere, DB2, MSDE, etc...) he is better structuring the tables properly so he does have to rely on gimmicks to get his data off the ground

AndyAinscowFreelance programmer / ConsultantCommented:
Access supports bookmarking - that is the way to check if the recordset supports bookmarks and one can retrieve the last modified bookmark.  And yes it may not be supported on other engines however changing the DB is a major change to a system.

I agree with you completely that getting the table structure us by far the most important thing.  It saves so much grief later.

If the DB provider doesn't support a bookmark one can always open a second set using a where clause that returns zero records.  The insert will then result in a small set to scroll through.
LittlePersonAuthor Commented:

I am using mysql and have tried the bookmark thing but with no
success. Getting the last inserted ID should not really be this big a
problem. The problem is coming from not being able to execute an
SQL statement and get back a recordset like




I find this a little strange for a language as powerful as C++/MFC.
I usually use the first SQL statement as each connection has its own
'counter' for the last inserted ID.

Is there another API or class that can do this.


For AndyAinscow:
   access not relational, the Jet engine is an inverted list db, like dbase used to be: old technologie.
   it's like using odbc driver with Dbase: it works but it does not make Dbase more relational.
   so access can be used like a relational db AND like an inverted list db.
   When you use bookmark you use inverted list property.
Contrairy to popular belief using SQL like syntax to access a database does not make that database relational.

For little person
  Even if you could query for the lastid it would not be reliable, some else could have inserted
  stuff in the db in the meantime, so the last ID would not be yours but someone else.

  Like I said in previous post do you have another unique index in that table ?
      if so use it to retrieve Last ID
  if you do not then
   Can you change the schema of the DB ?
    if you cannot
       You are stuck. LOL.
   If you can change it
         change that field from autoincrement to long and make it an unique index
    then Find a way to get unique ID and used them when inserting the record.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LittlePersonAuthor Commented:
For D Anderson

As far as I can see in MySQL the Last inserted ID is session/connection based and is reliable! therefore
any changes/inserts made by other users will not affect the lasted inserted ID value for your connection.

I appreciate your comments and am sure I will find a suitable method from them.


AndyAinscowFreelance programmer / ConsultantCommented:
Hi DAnderson

Your comment 'access not relational'

I for one think MS Access is a relational database and others do so as well.

You ought to have a look at the following

If the recordset (irrelevant of provider) is a static cursor then it does not have the inserts of other users. Hence one can use the AddNew of a recordset to add a new record AND other records added by other users do not appear in the recordset.
So if the provider supports bookmarks one can use those to go rapidly to the added record.
If it does not support bookmarks then one has two options.
Add to the open recordset and loop through each record to find the largest value of this auto field
OR create another recordset with zero records (eg WHILE (autofield=0) as filter) and add new record to this set then move to first record and get the ID of the newly added record.
AndyAinscowFreelance programmer / ConsultantCommented:
sorry, misspelling
WHERE (autofield=0) as filter
For LittlePerson:
Your trick his highly db specific, but in general the way to get the last auto number of an insert is dependent on the schema & the db engine.

For Andy

I do not want to start a religious war but the best comment I could find was from Hans:

" Access adheres to many requirements for a relational database system, so I would say that in a practical sense, the anwer is yes. But it allows you to break the rules if you so wish."

but what you do with your bookmark & static snapshot is breaking the rules so let me restate it:
Acces can be used like a relational DB (I do it often)

but you seem to use some inverted list database feature (static cursor stuff) so your usage is far from relational.
I'm not blaming you for doing that, I use DAO most of the time with access and take advantage of those inverted list features myself.
but when I do  I don't go around claiming that I have built a relational system.

Relational versus inverted list db is and old debate, a bit like procedural versus object oriented laguage debates

From my point of view very sterile debates. It is better to understand what it means then move on to real problems

AndyAinscowFreelance programmer / ConsultantCommented:
A central point that is worth remembering is that the data store is the foundation and the user interface is the app that provides an environment to store, modify and retrieve data built upon this foundation.
How the storage works is a decision taken very early on and with a functional system a change is not to be taken lightly.  In other words if there is supplied functionality then there is a case for using it.  At a later time it may be found to have been a poor decision for that particular storage medium but then a major interface overhaul may also be due.

For my understanding a relational database is how the data is stored and organised.  A static cursor has nothing to do with that, that is a data retrieval option saying I want a recordset and I don't want to see additions to the underlying data by other users.

As you know, get 20 experts together and you have at least 20 different opinions.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
System Programming

From novice to tech pro — start learning today.