There are a couple of ways, some more preferred than others.
Option 1 (Best for Beginners and Learning, can sometimes give erroneous
results)
Perform the insert into the database. My assumption is that you are using an
Identity field, like the Autonumber field in Access or Identity in MS SQL
Server. This gives every record a UNIQUE ID.
Now immediately run a query that gets the MAX (a SQL Aggregate Function) of
the Identity field.
You should enclose this in a simple CFTRANSACTION tag.
So,
<cftransaction>
<cfquery name="insert">
INSERT INTO table (field)
VALUES (form.values)
</cfquery>
<cfquery name="get_new_id">
SELECT MAX(ID) as MaxID
FROM table
</cfquery>
</cftransaction>
<cfoutput>
#get_new_id.MaxID#
</cfoutput>
That will do it for the easier way and is good for learning.
Option 2 (More inline with larger applications and advance SQL Server - uses
stored procedure. You'll need to read the docs on this one for additional
details that I will not detail here. I have seen no books with good examples
(anyone?). (This example is using MS SQL Server)
Here is the Stored Procedure:*******************************
CREATE PROC sp_insertApplicant
@FirstName varchar(50),
@MiddleName varchar(50),
@LastName varchar(50)
AS
INSERT INTO tblApplicants
(FirstName,
MiddleName,
LastName)
VALUES
(@FirstName,
@MiddleName,
@LastName)
SELECT ApplicantID = @@IDENTITY
Here is the code in the CFM:************************************
<cfstoredproc datasource="#application.dsn#" procedure="sp_insertApplicant"
returncode="Yes" debug>
<!--- CFPROCRESULT tag. This get's the results and returns them to you for
processing --->
<cfprocresult name = rs1>
<!--- CFPROCPARAM tags. This does the work of sticking the data into the
table. --->
<cfprocparam
type="IN"
cfsqltype=cf_sql_varchar
value="#form.FirstName#"
dbvarname=@firstname>
<cfprocparam
type="IN"
cfsqltype=cf_sql_varchar
value="#form.MiddleName#"
dbvarname=@middlename>
<cfprocparam
type="IN"
cfsqltype=cf_sql_varchar
value="#form.LastName#"
dbvarname=@lastname>
</cfstoredproc>
Now you get a result set in the form of a variable called RS1. So, in this
instance we returned the new applicantID, or RS1.ApplicantID. You can use
RS1.RecordCount to see how many records were returned (one in this case) and
RS1.ColumnList for a list of Columns returned (again, one, ApplicantID).
We can use the result, the RS1.ApplicantID, to retrieve the record we just
created.
<cfquery name="get_new_record">
SELECT *
FROM tblApplicants
WHERE ApplicantID = #RS1.ApplicantID#
</cfquery>
If I missed anything or am in error, it was the Martians who made me do it.
I waive all responsibility for my actions (seems to be going around these
days).
This is my favorite and seems pretty fast. Feel free to contact me off list.
Gary McNeel, Jr.
Project Manager - DAC-Net, Research & Graduate Studies
Rice University - Houston
[Lovett Hall] 713-348-6266 (Primary)
[DAC] 713-348-5184
[M] 713-962-0885
[H] 713-723-9240
"The politicians don't just want your money. They want your soul. They want
you to be worn down by taxes until you are dependent and helpless. When you
subsidize poverty and failure, you get more of both."
-James Dale Davidson
> -----Original Message-----
> From: Shawn Mirza [mailto:[EMAIL PROTECTED]]
> Sent: Friday, June 30, 2000 11:59 AM
> To: [EMAIL PROTECTED]
> Subject: RE: return value of Identity Column
>
>
> I know its an @@id when looking it up in a stored proc
>
> -----Original Message-----
> From: Jason Zimmerman [mailto:[EMAIL PROTECTED]]
> Sent: Friday, June 30, 2000 12:35 PM
> To: Cf-Talk (E-mail)
> Subject: return value of Identity Column
>
>
> Is there a way to return the value of an Identity Column after an insert
> into a SQL db?
>
> Thanks,
> -Jason
> ------------------------------------------------------------------
> ----------
> --
> Archives: http://www.eGroups.com/list/cf-talk
> To Unsubscribe visit
> http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
> send a message to [EMAIL PROTECTED] with 'unsubscribe' in
> the body.
>
> ------------------------------------------------------------------
> ------------
> Archives: http://www.eGroups.com/list/cf-talk
> To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
------------------------------------------------------------------------------
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
