What is my ID for the record I just inserted?
A question I get quite often from visitors and members of
EasyCFM.COM is "if I insert a record, is there a way I can get the ID of the record I just
inserted, without having to connect to the database again?"
The answer of course is yes ;) first let me explain how your database structure would
look for this example (keep in mind that this is a small example and your table
will be different, all you need for this example to work properly is an IDENTITY
FIELD in your SQL server. If you need help setting up an identity field, please
post a request to the forums [I will be writing a tutorial on this at a later
time and will update this page with the link at a latter time... for now please
use the forums ;] ...) Now, lets look at the database itself,
|
FIELD TYPE |
FIELD TYPE |
NULL |
IDENTITY |
| MEMBER_ID |
INT |
NO |
1 |
|
FIRSTNAME |
VARCHAR(50) |
YES |
|
| LASTNAME |
VARCHAR(50) |
YES |
|
|
EMAIL |
varchar(250) |
YES |
|
<cfquery name="qGetUser"
datasource="MyDSN">
INSERT INTO users (firstName, lastName, email )
VALUES ( '#FORM.LastName#', '#FORM.LastName#', '#FORM.email#' );
select thisID = @@identity
</cfquery>
<cfoutput>
The ID of the item just inserted is: #qGetUser.thisID#
</cfoutput>
That more or less does it :) Let me explain
what it is you saw and what it does....
First you do an insert (as you would any other
insert) with the difference that you add a ";" at the end of it. (Notice it in
bold). The next thing you did was SELECT thisID =
@@identity.... this works similar to a regular SELECT statement... but it
basically just returns the ID of the item just inserted to the database!
Questions? Comments?
Let me know....