Thanks. Unfortunately the group must have been lagged so your response came
10 hours after I started pulling my hair out!! :)
But after some intense research through archives of this newsgroup I passed
by someone else who had mentioned the trigger method. That seemed to do the
job. Being new to SQL server, I can see where triggers become really
useful.
Thanks for your response.
[EMAIL PROTECTED]
BTW: Chrissy, one of your flash designers, highly recommended one of your
classes that I'm trying to get a budget approved for. I'll have to check
your web site, but I'm hoping that you maybe have Florida listed on an
upcoming stop.
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Thursday, April 06, 2000 1:19 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: RE: Retrieving Unique ID in from sql table.
> I come from a Microsoft Access background and now they've
> thrown me into SQL with no training. What I am trying to
> do is the following:
>
> <cftransaction>
> <cfquery Insert> - In access I have an autonumber column
> that would get
> updated here automatically.
> <cfquery select> - Here I would go through and selected max(id) as
> lastrecordadded
> </cftransaction>
>
> Now I could use this record to write to some other tables and keep a
> relationship.
>
> But with SQL, it seems that there is no autonumber so when I
> do my first insert, I have a field with type "uniqueid" and
> when I write to that table I pass NewId().
SQL Server has an autonumber data type, but it's called "identity".
> Now how do I retrieve that information? Max() does not want
> to work in my sql routine.
>
> I'm really stuck, and this should seem pretty easy to someone
> who knows SQL.
MAX should work fine in your SQL statement, but there are more efficient
ways to do this than using MAX and CFTRANSACTION. You might instead write an
SQL stored procedure that returns the identity column value for the inserted
record, or a trigger that does the same thing. Here are two examples to get
you started:
1. stored procedure
CREATE PROC sp_insNewRec
@field1 varchar(30),
@field2 varchar(30)
AS
INSERT INTO
mytable
(field1,
field2)
VALUES
(@field1,
@field2)
SELECT
ID = @@IDENTITY
2. insert trigger
CREATE TRIGGER insnewrec ON mytable FOR INSERT AS
SELECT myidfield FROM INSERTED
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.
------------------------------------------------------------------------------
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.
