OR in SQL Server:
<cfquery>
BEGIN Transaction
SET NOCOUNT = ON
INSERT
INTO blah()
Values ()
SELECT @@Identity AS ThisID
COMMIT Transaction
</cfquery>
@@Identity is the value of the last inserted ID field in the
transaction.
SET NOCOUNT = ON will allow you to get the record set out of the query.
I use GUIDs and all Stored Procs with my current stuff, so I create the
GUID and insert it, then Return it, works much better that way.
> -----Original Message-----
> From: Ian Skinner [mailto:[EMAIL PROTECTED]
> Sent: Thursday, March 27, 2003 10:53 AM
> To: CF-Community
> Subject: RE: Referencing my last record
>
> I'm going to have to remember this one, it looks much better then my
> clunky
> solution.
>
> <cflock ....>
> <cfquery>
> Insert
> Into aTable ()
> Values ()
> </cfquery>
>
> <cfquery name="newID">
> Select MAX(id)
> From aTable
> </cfquery>
> </cflock>
>
>
> --------------
> Ian Skinner
> Web Programmer
> BloodSource
> Sacramento, CA
>
>
> -----Original Message-----
> From: Candace Cottrell [mailto:[EMAIL PROTECTED]
> Sent: Friday, March 21, 2003 5:46 AM
> To: CF-Community
> Subject: RE: Referencing my last record
>
>
> Very cool. I added that as a snippet. I will be using this again for
> sure.
>
> Much olbiged :)
>
>
>
> Candace K. Cottrell, Web Developer
> The Children's Medical Center
> One Children's Plaza
> Dayton, OH 45404
> 937-641-4293
> http://www.childrensdayton.org
>
>
> [EMAIL PROTECTED]
>
> >>> [EMAIL PROTECTED] 3/21/2003 8:35:10 AM >>>
> Try
>
> <CFQUERY NAME="qryFoo" DATASOURCE="bar">
> SET NOCOUNT ON
>
> INSERT INTO ()
> VALUES ()
>
> SELECT id = Scope_Identity()
>
> SET NOCOUNT OFF
> </CFQUERY>
> <CFOUTPUT>#qryFoo.ID#</CFOUTPUT>
>
>
>
> HTH
>
>
>
> -----Original Message-----
> From: Candace Cottrell [mailto:[EMAIL PROTECTED]
> Sent: Friday, March 21, 2003 13:31
> To: CF-Community
> Subject: RE: Referencing my last record
>
>
> Thanks Tim...
> I am using SQL Server.
>
> Now how do I reference MyID outside the cfquery?
>
> This isn't working..
>
> <cfquery name="InsertType" datasource="resDB">
> Insert Into TYPE
> (
> TypeName,
> TypeDesc
> )
> Values
> (
> '#form.TypeName#',
> '#form.TypeDesc#'
> )
>
> select @@identity as myID
> from type
> </cfquery>
>
> <cfset lastID = InsertType.myID>
>
> <cfquery name="getLast" datasource="resDB">
> SELECT TYPE.typeID, TYPE.typeName, TYPE.typeDesc FROM TYPE
> Where TypeID = #lastID#
> </cfquery>
>
> It's saying Element MYID is undefined in INSERTTYPE. Thanks for all
> your
> help :)
>
>
> Candace K. Cottrell, Web Developer
> The Children's Medical Center
> One Children's Plaza
> Dayton, OH 45404
> 937-641-4293
> http://www.childrensdayton.org
>
>
> [EMAIL PROTECTED]
>
>
>
> >>> [EMAIL PROTECTED] 3/20/2003 3:28:13 PM >>>
> Is it SQL Server?
>
> use @@identity to get the max id in SQL Server so something like:
>
> <cfquery name="InsertType" datasource="resDB">
> Insert Into TYPE
> (
> TypeName,
> TypeDesc
> )
> Values
> (
> '#form.TypeName#',
> '#form.TypeDesc#'
> )
>
> select @@identity as myID
> from type
> </cfquery>
>
> -----Original Message-----
> From: Candace Cottrell [mailto:[EMAIL PROTECTED]
> Sent: Thursday, March 20, 2003 3:01 PM
> To: CF-Community
> Subject: Referencing my last record
>
>
> Is there any way to reference the ID of the last record I added?
>
> <cfquery name="InsertType" datasource="resDB">
> Insert Into TYPE
> (TypeName, TypeDesc)
> Values ('#form.TypeName#', '#form.TypeDesc#')
> </cfquery>
>
> Something like:
>
> <cfset lastID = InsertType.TypeID>
>
> TypeID is an identity field in MSSQL.
>
>
> Candace K. Cottrell, Web Developer
> The Children's Medical Center
> One Children's Plaza
> Dayton, OH 45404
> 937-641-4293
> http://www.childrensdayton.org
>
>
> [EMAIL PROTECTED]
>
>
>
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=5
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=5
Get the mailserver that powers this list at http://www.coolfusion.com
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.5
