Retrieving ID of Last Inserted Table

[JimCu]

Hi, I've done a search of the forums and although I've found similar questions
none of them seemed to answer my question.

I'm currently writing an application for my business which will create
invoices for me. I have set up 2 MsSql tables, one called tbl_invoices and
another called tbl_invoice_items. The idea is that I create an invoice in
tbl_invoices and then start adding line items to it on the next page. The
invoice_ID field is auto generated by MsSq. Is there a way of retreiving the id
assigned by the database so I can write the correct invoice_ID to the
tbl_invoice_items table. Or am I approaching this from the wrong direction.

I envisaged the flow of the application like so.

1)Enter Data for Invoice in form eg customer name, date etc
2)Form Data written to database
3)Forwards to page with form fields for entering line items
4)Write invoice items to database and return to 3 until complete.

I appreciate your help.

Jim

[kabbi~thkek]

Hi,

Do not no if this is something you can use:

<cfquery name="id" datasource="">
SELECT LAST_INSERT_ID() AS highest_id
</cfquery>

<cfset vardev_id = id.highest_id>
<cflocation url="page_2.cfm?invoice_ID=#vardev_id#">

then on page two make a query (WHERE invoice_ID = #URL.invoice_ID# or
something) And insert the values you want to insert into the new table.

Hope this was usefull

Bye, Kabbi

[Dan Bracuk]

wrap two queries inside cftransaction and cflock tags.
Query 1 - insert your record
Query 2 - select the max(id) and have a great big where clause.

[JimCu]

Thanks, I'll give them both a go and see how it goes. Should work okay for a
limited number of users, it's shame that an insert query doesn't return the
primary key as a value. Perhaps we'll see that in 7.1?

[PaulH *TMM*]

JimCu wrote:

> I'm currently writing an application for my business which will create
> invoices for me. I have set up 2 MsSql tables, one called tbl_invoices and

well for starters don't use anything that uses a select MAX(), that will
eventually become a bottleneck. if by "MsSql" you mean ms sql server & by "auto
generated" you mean a column w/an IDENTITY characteristic, then use the native
sql server stuff in you're cfquery:

SET NOCOUNT ON
INSERT INVOICE(....
SELECT SCOPE_IDENTITY() AS invoiceID
SET NOCOUNT OFF

if you're sure there are no triggers that act on tables w/IDENTITY columns then
you could also use SELECT invoiceID=@@IDENTITY instead of the SCOPE_IDENTITY()
function. in either case the cfquery resultset will contain an "invoiceID" column.

[ptoretti]

What Paul said - Make sure that the record you get back when going for the
maxID is the same one you wrote. If you have alot of transactions, you might
get the wrong one. Using some method other than MaxID is better.