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.

[JR__Bob__Dobbs-qSBHQ2]

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?

[cybertek23]

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

[Dan_Bracuk]

select max(clientid) from clientdetails
where as many conditions as you can think of are met.

[JR__Bob__Dobbs-qSBHQ2]

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.

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

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:

[JR__Bob__Dobbs-qSBHQ2]

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.

[Steve Sommers]

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.