>
> I have a insert statment. I'm inserting a new record
> in the table. The table has a primary key (empID)
> Here is the code
>
> <cftransaction action=""> > <cfquery name="addNewEmp" datasource="db">
> INSERT INTO ASSETS
> (
> empName,
> empDesk,
> empAsset
> )
>
> VALUES
>
> (
> 'blog',
> '1,
> 'ast10'
> )
> </cfquery>
> </cftransaction>
>
> I want to retreive the primary key (empID) value
> immediately after this statement. I tried this:
> <cfoutput>#addNewEmp.empID#</cfoutput> but get an error msg
> "Element empID is undefined in addNewEmp
You need to look into scope_identity() (if you're using SQL Server 2000)
or @@identity (for SQL Server 7)
Set nocount on
Insert into table (fields)
Values (values)
Select scope_identity() as myID
Set nocount on
This will "return" the newly added identity field
The problem with @@identity is that it returns the "last" identity
inserted, so if you have triggers on your table which add to another
table, then you will end up with the wrong identity field
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
