RE: Avoiding using the MAX function

[Olive, Christopher M Mr USACHPPM]

yes, if you call a stored procedure to insert the record, and reference the
@@IDENTITY property after the SP call.

Chris Olive
DOHRS Website Administrator
[EMAIL PROTECTED]


-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 24, 2000 12:45 PM
To: Cf-Talk
Subject: Avoiding using the MAX function


Hi Everyone,

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

Thanks,

Eron

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