This topic has come up again and I thought I would show how to do this.
1. This example will only work with MS SQL Server. You are basically running an INSERT statemenet and following it up with a SELECT statement that queries for the @@Identity.
<cfquery name="qInsertUser" datasource="myDSN">
SET NOCOUNT ON
INSERT INTO USERS (username, email)
VALUES (
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#usersname#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#email#">
)
SELECT @@Identity AS newID
SET NOCOUNT OFF
</cfquery>
<cfset newID = qInsertUser.newID>
2. This example will work with any type of database, including MS Access.
<cflock name="insertrecord" timeout="10">
<cfquery name="qInsertUser" datasource="myDSN">
INSERT INTO USERS (username, email)
VALUES (
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#usersname#">,
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#email#">
)
</cfquery>
<cfquery name="qGetNewID" datasource="myDSN">
SELECT MAX(RecordID) AS NewID FROM USERS
</cfquery>
</cflock>
<cfset newID = qGetNewID.NewID>
Recent Comments