But use scope_identity() instead of @@identity, otherwise you'll have the same race condition you have now.
> My mistake... > <cfquery> > insert into mytable([columns]) > values([values]) > select @@identity as user_id > </cfquery> > All in one query, not two like I posted. > -----Original Message----- > From: Jacob [mailto:[EMAIL PROTECTED] > Sent: Thursday, March 09, 2006 2:13 PM > To: CF-Talk > Subject: RE: SQL-generated primary keys > After the command for inserting the new user, use the > following: > select @@identity > from MyTable > <cftransaction> > <cfquery> > What ever your SQL is to insert the new user > </cfquery> > <cfquery> > select @@identity > from MyTable > </cfquery> > </cftransaction> > -----Original Message----- > From: Chris Mueller [mailto:[EMAIL PROTECTED] > Sent: Thursday, March 09, 2006 1:35 PM > To: CF-Talk > Subject: SQL-generated primary keys > Here's a question I've been wrestling wtih. > Suppose that there is an HTML form to create a new user. > When the form > is submitted, that user's information is added to the > 'user' table. > The table uses an auto-increment integer for its primary > key/user id, > so I'd like to grab that value for the newly created user. > I've been using queries like this one: "SELECT > MAX(user_id) FROM > users", but I worry that if two users are created at the > same time, > then my query might give me the user id of the wrong user. > Is there a better way to do this? Or maybe I shouldn't > even worry about > this? > Thanks. > Chris > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:234963 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54