> I'm building a tool that will track problems through out
> their lifecycle. People input data in the form fields,
> you hit submit and the fields are populated into the
> database. What I'm looking to retrieve is the auto
> generated number for that specific row that the data was
> just inserted into to return for a thank you page.
>
> "Thank you, your problem number is #problemid#" and have
> it be the problemid for the row that was just submitted.
There are several approaches you can take, depending on what database you're
using and what your general preference is.
One approach is to use a stored procedure, which would insert the record and
fetch the new primary key value. Here's an example that would work in SQL
Server:
CREATE PROCEDURE insSomething
@field1 varchar(50)
AS
INSERT INTO SomeTable
(field1)
VALUES
(@field1)
SELECT @@IDENTITY AS NewID
You could do this within an SQL batch instead of a stored procedure, if your
database drivers support batching SQL statements. I'd recommend the use of a
stored procedure if possible.
Another approach would be to handle the process from within CF:
<cftransaction>
<cfquery name="qInsSomething" ...>
INSERT INTO SomeTable
(field1)
VALUES
('#Form.field1#')
</cfquery>
<cfquery name="qGetNewID" ...>
SELECT MAX(ID) AS NewID
FROM SomeTable
</cfquery>
</cftransaction>
If you take this approach, make sure that the isolation level that you use
is adequate to deal with concurrency issues; this will vary between
databases.
Yet another approach would be to eschew autogenerated primary keys entirely,
and let CF generate unique values for use as keys. You could use the
CreateUUID function for this.
I'd recommend the stored procedure approach first, but after that, you're on
your own. Each alternative approach has its pros and cons.
> I can imagine many people have ran into this wall, any help
> would be appreciated.
When you imagine many people have run into the same problem, that should
indicate that a trip to the archives is in order. The Macromedia forums are
also a good choice for finding answers to common questions. It's ok to post
your questions here instead (or in addition), of course, but you might get a
quicker answer by searching.
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
voice: (202) 797-5496
fax: (202) 797-5444
______________________________________________________________________
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
