Best way to get a new user id from cfinsert?

Posted on 2009-04-16
Last Modified: 2013-12-24
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.
Question by:Short-Order-Coder
    LVL 14

    Expert Comment

    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
    LVL 16

    Accepted Solution

    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


    Author Comment

    so for MySql it would be #myResult.GENERATED_KEY# and whatever I set as the primary key will be returned?

    That is so cool.
    LVL 51

    Expert Comment

    Yes, go with Duncan's suggestion and use a regular INSERT with cfquery.  CFINSERT does not have that capability.
    LVL 51

    Assisted Solution

    > <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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
    Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL ( several years ago, it seemed like now was a good time to updat…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now