> 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?
...
> 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).
You can either use stored procedures to perform inserts and retrieve
identity values, or attach insert triggers to your table. Here's an example
of an insert stored procedure:
CREATE PROC insItem
@Item_Name varchar(30),
@Item_Desc varchar(255)
AS
INSERT INTO Item
(Item_Name,
Item_Desc)
VALUES
(@Item_Name,
@Item_Desc)
SELECT ID = @@IDENTITY
You could then reference the ID field in the resulting recordset.
Alternatively, you could have an insert trigger on the table. Here's an
example:
CREATE TRIGGER insItem
ON Item
FOR INSERT
AS
SELECT Item_ID FROM INSERTED
Then, if you run an insert from a CFQUERY, you'd get a recordset back with
the new Item_ID value.
If you have a choice, stored procedures are more efficient than triggers.
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
voice: (202) 797-5496
fax: (202) 797-5444
------------------------------------------------------------------------------
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.
