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

x
  • 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.
0
Short-Order-Coder
Asked:
Short-Order-Coder
2 Solutions
 
RickEpnetCommented:
Right after your insert you can run this

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

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
0
 
duncancummingCommented:
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.

http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Tags_p-q_17.html

result_name.IDENTITYCOL            
SQL Server only. The ID of an inserted row.

result_name.ROWID            
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.                

result_name.SYB_IDENTITY            
Sybase only. The ID of an inserted row.                

result_name.SERIAL_COL            
Informix only. The ID of an inserted row.                

result_name.GENERATED_KEY            
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">
	INSERT
	INTO yourTable (Name, Address, Age)
	VALUES ('#Form.Name#', '#Form.Address#', #Form.Age#)
</cfquery>
 
Identity of inserted column: #myResult.IDENTITYCOL#

Open in new window

0
 
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.
0
 
_agx_Commented:
Yes, go with Duncan's suggestion and use a regular INSERT with cfquery.  CFINSERT does not have that capability.
0
 
_agx_Commented:
> <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
0

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