7 Replies Latest reply on Feb 11, 2008 9:56 AM by Steve Sommers

    get last record inserted

    cybertek23
      Hi i am inserting into 2 tables. I am first inserting into client id. I then want to get that client id and insert it into the sale table.

      What is the easist way to get this.
        • 1. get last record inserted
          JR "Bob" Dobbs-qSBHQ2 Level 3
          You have a couple options depending on your database.

          1. The best, in my opinion, is to wrap this functionality into a stored procedure that will handle the multiple inserts, then run that stored procedure inside a transaction (or include the transaction inside the stored procedure).

          2. Use the result object returned by cfquery on the first insert to get the ID of the new record from result_name.IDENTITYCOL (or whatever property is set for your database). Then use the ID value in a second cfquery for the sales table.
          http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Tags_p-q_17.html#1102316

          What is your database product and version?
          • 2. Re: get last record inserted
            cybertek23 Level 1
            Unfortuantly we are using access at the moment. Can you please give me an idea of how to use this i have the following codes
            • 3. Re: get last record inserted
              Dan Bracuk Level 5
              select max(clientid) from clientdetails
              where as many conditions as you can think of are met.
              • 4. Re: get last record inserted
                JR "Bob" Dobbs-qSBHQ2 Level 3
                See attached sample.


                Note that the use of max(clientid) is not guarenteed to get you the clientid inserted in the previous statement. If changes to the database are possible you might use a string for the clientid and have coldfusion create a UUID for each client with the CreateUUID function and use that value to identify client records.

                I also recommend that you use cfqueryparam within your cfquery calls. This will prevent the surname "O'Brien" from causing errors.
                • 5. get last record inserted
                  Steve Sommers Level 4
                  quote:

                  Originally posted by: JR "Bob" Dobbs
                  ...Note that the use of max(clientid) is not guarenteed to get you the clientid inserted in the previous statement.

                  If you wrap your query with a CFTRANSACTION it will. But this might slow things down a bit on a busy site.

                  The best approach I have found (other than migrating to MSSQL or MySQL) is to add a UUID field to your table with an index. When you insert a record, create a UUID, insert it with your data and then recall the record using the UUID as the key:
                  • 6. Re: get last record inserted
                    JR "Bob" Dobbs-qSBHQ2 Level 3
                    quote:

                    Originally posted by: Steve Sommers
                    quote:

                    Originally posted by: JR "Bob" Dobbs
                    ...Note that the use of max(clientid) is not guarenteed to get you the clientid inserted in the previous statement.

                    If you wrap your query with a CFTRANSACTION it will. But this might slow things down a bit on a busy site.



                    As far as I know Access does not support transactions. Also bear in mind that the isolation level of the transaction would affect the results of the select(max) if multiple inserts occurred at the same time.
                    • 7. Re: get last record inserted
                      Steve Sommers Level 4
                      quote:

                      Originally posted by: JR "Bob" Dobbs
                      As far as I know Access does not support transactions. Also bear in mind that the isolation level of the transaction would affect the results of the select(max) if multiple inserts occurred at the same time.


                      It's been a long while since I worked with access but from what I remember there was an isolation level that worked BUT I do not recommend this route.

                      By far, my #1 recommendation would be to migrate to MSSQL or MySQL and use an identity field. Most hosting providers offer either of these two at no additional charge and both are virtually free if you host yourself. My #2 recommendation would be the uuid logic I previously posted.