Link to home
Start Free TrialLog in
Avatar of LittlePerson
LittlePerson

asked on

Get Last Inserted ID using CRecordset Class

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
Avatar of AndyAinscow
AndyAinscow
Flag of Switzerland image

Instead of ExecuteSQL how about AddNew of the recordset and getting the value of the ID after an update.
Avatar of LittlePerson
LittlePerson

ASKER

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
That method is definitely not working, I always get the first record back. Is this
because I am using single row fetching?

Thanks

LittlePerson
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.
If you are interested in ADO then the following link is very useful

www.codeproject.com/database/caaadoclass1.asp
hi,

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;
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.
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

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

Danderson
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.
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
ASKER CERTIFIED SOLUTION
Avatar of DAnderson
DAnderson

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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
https://www.experts-exchange.com/questions/20814851/Is-Access-a-relational-database.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.
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

Danderson
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.