Return last INSERTED record in the database

Just a shot in the dark, but you could use cftransaction, wrap all your cfqueries in a cftransaction and it should on treat all the querires as a single transaction.

I use bean and Transfer Objects for most of my data manip... but here is an example of what I do:
=================================================================
      <cffunction name="create" access="public" returntype="void" output="false" hint="CRUD Method.">      
            <cfargument name="usersBean" type="users" required="true" hint="I am the users object from which to create a record." />/>

            <cfset var usersInsert = 0 />
            <cfset var usersmaxcols = 0 />

            <cftransaction>

            <cfquery name="usersInsert" datasource="#variables.dsn#">
                  INSERT INTO users
                        (
                        username, password, firstlogin, lastlogin, logins, alias, userTypeID, personnelID, active
                        )
                  VALUES (
                        <cfqueryparam cfsqltype="cf_sql_VARCHAR" value="#arguments.usersBean.getusername()#" maxlength="#arguments.usersBean.getBeanConfigValue('username').maxlength#" null="#arguments.users.isNullusername()#" />
                        , <cfqueryparam cfsqltype="cf_sql_VARCHAR" value="#Hex(trim(arguments.usersBean.getpassword()))#" maxlength="#arguments.usersBean.getBeanConfigValue('password').maxlength#" null="#arguments.users.isNullpassword()#" />
                        , <cfqueryparam cfsqltype="cf_sql_TIMESTAMP" value="#arguments.usersBean.getfirstlogin()#" maxlength="#arguments.usersBean.getBeanConfigValue('firstlogin').maxlength#" null="#arguments.users.isNullfirstlogin()#" />
                        , <cfqueryparam cfsqltype="cf_sql_TIMESTAMP" value="#arguments.usersBean.getlastlogin()#" maxlength="#arguments.usersBean.getBeanConfigValue('lastlogin').maxlength#" null="#arguments.users.isNulllastlogin()#" />
                        , <cfqueryparam cfsqltype="cf_sql_INTEGER" value="#arguments.usersBean.getlogins()#" maxlength="#arguments.usersBean.getBeanConfigValue('logins').maxlength#" null="#arguments.users.isNulllogins()#" />
                        , <cfqueryparam cfsqltype="cf_sql_VARCHAR" value="#arguments.usersBean.getalias()#" maxlength="#arguments.usersBean.getBeanConfigValue('alias').maxlength#" null="#arguments.users.isNullalias()#" />
                        , <cfqueryparam cfsqltype="cf_sql_INTEGER" value="#arguments.usersBean.getuserTypeID()#" maxlength="#arguments.usersBean.getBeanConfigValue('userTypeID').maxlength#" null="#arguments.users.isNulluserTypeID()#" />
                        , <cfqueryparam cfsqltype="cf_sql_INTEGER" value="#arguments.usersBean.getpersonnelID()#" maxlength="#arguments.usersBean.getBeanConfigValue('personnelID').maxlength#" null="#arguments.users.isNullpersonnelID()#" />
                        , <cfqueryparam cfsqltype="cf_sql_CHAR" value="#arguments.usersBean.getactive()#" maxlength="#arguments.usersBean.getBeanConfigValue('active').maxlength#" null="#arguments.users.isNullactive()#" />
                        
                        )
            </cfquery>
            
            <cfquery name="usersmaxcols" datasource="#variables.dsn#">
                  select
                        max(userID) as maxuserID
                  from
                        users
            </cfquery>

            <cfset arguments.usersBean.setuserID(usersmaxcols.maxuserID, false, false)/>
            

            </cftransaction>

      </cffunction>
==============================================================
I use a cftransaction to wrap the insert and fetch and then set the userID of the bean with the results of the fetch. You could just as easily have a return and return the ID.

for SQL server you should use @@identity

<cftransaction>
       <cfquery datasource="#dbsrc#" name="insert_record">
               INSERT INTO tbl_users(username, fullName)
                                    values('testuser', 'Test User')
SELECT @@identity AS newUserId

        </cfquery>
      </cftransaction>

<cfoutput>
#insert_record.newUserId#
</cfoutput>

sorry all - didnt read the q properly.

Couple of things, First off cftransaction is great but your DB has to support transactions and if you are using the free version of MySQL I don't believe it supports transactions so cftransaction doesn't do much for you. Second @@identtity works but it returns the last insert identity. Even inside of cftransaction its much safer to use SCOPE_IDENTITY()  there is even another one where you specifify the entity name but I can't recall it at this time.

The best Way to know your ID is to make it yourself. Use createUUID() sure its 35 characters but in the end if you want a sure fire way that will work accross as many different DBs as you can think of then createUUID() is the best solution.

-cfMav

So,
is there anyway to know if my database supports transactions? Like, if i try <cftransaction>, how will i know that it was able to lock the database? will it throw and error or just run them as two consecutive queries?

- travis
http://www.iqdevs.net/

Take a look at this article while I investigate this a little more. Also what version of MySQL do you have?

http://www.samspublishing.com/articles/article.asp?p=23412&seqNum=3&rl=1

I will get you an example using MySQL in a few hours when I get back to the office.

did u try this query,  by the way

Select LAST_INSERT_ID()

did it work