There's another way with SQL Server using a trigger:
CREATE TRIGGER GetPK_MyTable ON MyTable
FOR INSERT
AS
SELECT MyTable_ID FROM INSERTED
When you do your CFQUERY and perform the SQL INSERT,
SQL server will return the inserted ID via the trigger!!!
Using the @@IDENTITY variable isn't guaranteed to be
correct under heavy load from my understanding.
Tom Nunamaker
[EMAIL PROTECTED]
At 6/30/00 12:41 PM, you wrote:
>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.
------------------------------------------------------------------------------
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.
