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
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
Instead of ExecuteSQL how about AddNew of the recordset and getting the value of the ID after an update.
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
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
ASKER
That method is definitely not working, I always get the first record back. Is this
because I am using single row fetching?
Thanks
LittlePerson
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.GetLastM odifiedBoo kmark());
directly after the Update. If your provider supports bookmarks maybe that or similar will help.
With DAO I use
RS.SetBookmark(RS.GetLastM
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
www.codeproject.com/database/caaadoclass1.asp
hi,
write a query with max function like this..
select max(ID) from <<table>>
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::dynase t, "select max(ID) from <<table>>" );
if(!rs.IsEof())
{
rs.GetFieldValue((short)0, strId);
AfxMessageBox(strId);
}
i hope above code will help u..
sreenu.
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::dynase
if(!rs.IsEof())
{
rs.GetFieldValue((short)0,
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
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).
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
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.
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.
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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.
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
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
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.
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.