Solved

Get Last Inserted ID using CRecordset Class

Posted on 2003-11-28
18
1,373 Views
Last Modified: 2013-11-20
Hi

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?

SELECT LAST_INSERT_ID() As ID

I have worked around it for short term with

rs.Requery()
rs.MoveLast()

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

LittlePerson
0
Comment
Question by:LittlePerson
  • 8
  • 4
  • 4
  • +1
18 Comments
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 9837876
Instead of ExecuteSQL how about AddNew of the recordset and getting the value of the ID after an update.
0
 

Author Comment

by:LittlePerson
ID: 9837890
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!

Thanks
0
 

Author Comment

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

Thanks

LittlePerson
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 9838289
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
RS.SetBookmark(RS.GetLastModifiedBookmark());
directly after the Update.  If your provider supports bookmarks maybe that or similar will help.
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 9838366
If you are interested in ADO then the following link is very useful

www.codeproject.com/database/caaadoclass1.asp
0
 
LVL 2

Expert Comment

by:sreenu_v78
ID: 9842063
hi,

write a query with max function like this..


select max(ID) from <<table>>

0
 
LVL 2

Expert Comment

by:sreenu_v78
ID: 9842074
here i'm givinging example

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

CDatabase data;
data.OpenEx("<<DSN=DSNName>>");
CRecordset rs(&data);

CString strId;
rs.Open(CRecordset::dynaset, "select max(ID) from <<table>>" );
if(!rs.IsEof())
{
      rs.GetFieldValue((short)0,strId);
       AfxMessageBox(strId);
}

i hope above code will help u..
sreenu.
0
 
LVL 1

Expert Comment

by:DAnderson
ID: 9846239
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 user.name = '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

Danderson

0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 9848579
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).
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 1

Expert Comment

by:DAnderson
ID: 9854923
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

Danderson
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 9856188
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.
0
 

Author Comment

by:LittlePerson
ID: 9856978
Hi

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

SELECT LAST_INSERT_ID() As ID

or

SELECT MAX(ID) FROM CLIENT As mID

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.

Thanks

LittlePerson
0
 
LVL 1

Accepted Solution

by:
DAnderson earned 50 total points
ID: 9864036
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.
   Danderson
0
 

Author Comment

by:LittlePerson
ID: 9866002
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.

Thanks

LittlePerson
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 9866041
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
http://www.experts-exchange.com/Databases/MS_Access/Q_20814851.html#9865142
http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=acc&Number=318343


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.
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 9866046
sorry, misspelling
WHERE (autofield=0) as filter
0
 
LVL 1

Expert Comment

by:DAnderson
ID: 9871909
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

Danderson
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 9872749
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.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction: Displaying information on the statusbar.   Continuing from the third article about sudoku.   Open the project in visual studio. Status bar – let’s display the timestamp there.  We need to get the timestamp from the document s…
Introduction: Hints for the grid button.  Nested classes, templated collections.  Squash that darned bug! Continuing from the sixth article about sudoku.   Open the project in visual studio. First we will finish with the SUD_SETVALUE messa…
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now