> At the CFUG-orama in Washington DC last week, I think Ben Forta said that
> there is a way to avoid having to use the "MAX()" SQL function to
> get the ID
> number of the item you just inserted into a database. Can anyone tell me
> how to do this?
>
> In other words, currently I'm doing something like this:
>
> <CFQUERY NAME="insert_the_record" DATASOURCE="my_table">
> INSERT INTO tablename (x,y,z)
> values ('x','y','z')
> </CFQUERY>
>
> Then, to get the autonumber'ed ID of the item I just inserted, I'll use:
>
> <CFQUERY NAME="get_the_newest_record" DATASOURCE="my_table">
> Select Max(ID) as the_newest_record
> From Tablename
> </CFQUERY>
>
> Now I'll have the ID number of the newest inserted record in
> #get_the_newest_record.newest_record#.
>
> If I understood Ben correctly, then there is a way to insert the
> record AND
> return the ID number of that inserted record all at the same time.
>
> The database in question is a SQL Server database. (Although it would be
> good to know how to do it with MS Access if its possible).
With SQL Server, you can build the whole thing in one query (2 SQL commands,
one CFQuery), and use @@identity to get the inserted identity - not sure you
can do this in Access
The cleanest way to do this and make it all-database compatible though, is
to use a CFTransaction around the 2 queryies, as Access doesn't allow 2 SQL
queries in one command
HTH
Philip Arnold
ASP Multimedia Limited
T: +44 (0)20 8680 1133
"Websites for the real world"
**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.
**********************************************************************
------------------------------------------------------------------------------
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
