[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 836
  • Last Modified:

Best way to get a new user id from cfinsert?

I am building an app where a new user adds a set of information on a "first" page, more info on a second page, etc.  I need to get the new user ID between the first and second page so I can update on the rest...

So, what is the most efficient way to get the user ID from the cf8 server?

i know I can run a query for first and last name, etc, but I was wondering if CF has some sort of built in function to handle this.
2 Solutions
Right after your insert you can run this

    <cfquery name="findnewuser" datasource="#client.dsn#">
            select  max(User_ID) as id
            from tbale_name

Or you can do what you said about querying the first and last name but there could be a duplicate.

I do not know of any built in function
What's your database?  You can now get that value from most databases if you're on CF 8.  Not sure it'd work with CFInsert, use CFQuery instead.  The query returns a structure of information that includes the Identify of an inserted row.


SQL Server only. The ID of an inserted row.

Oracle only. The ID of an inserted row. This is not the primary key of the row, although you can retrieve rows based on this ID.                

Sybase only. The ID of an inserted row.                

Informix only. The ID of an inserted row.                

MySQL only. The ID of an inserted row. MySQL 3 does not support this feature.

e.g. if you were using SQL Server:

<cfquery name="insert" datasource="dsn" result="myResult">
	INTO yourTable (Name, Address, Age)
	VALUES ('#Form.Name#', '#Form.Address#', #Form.Age#)
Identity of inserted column: #myResult.IDENTITYCOL#

Open in new window

Short-Order-CoderAuthor Commented:
so for MySql it would be #myResult.GENERATED_KEY# and whatever I set as the primary key will be returned?

That is so cool.
Yes, go with Duncan's suggestion and use a regular INSERT with cfquery.  CFINSERT does not have that capability.
> <cfquery name="findnewuser" datasource="#client.dsn#">
>            select  max(User_ID) as id
>            from tbale_name
> </cfquery>

(For any future readers), that is not recommended as it it may return the same/wrong ID to multiple threads

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now