If you're going to put two SQL statements in the same CFQUERY tag, you
also have to suppress the returned messages from the database, or the
second SQL Statement will throw an error. SET NOCOUNT ON will
supress the returned messages. so your CFQUERY looks something like
this:
<cfquery name="myquery" datasource="#request.dsn#">
SET NOCOUNT ON
INSERT INTO tablename (field1,field2)
VALUES (
<cfqueryparam value="#trim(form.field1)#" cfsqltype="cf_sql_varchar"/>,
<cfqueryparam value="#trim(form.field2)#" cfsqltype="cf_sql_varchar"/>)
SELECT SCOPE_IDENTITY() AS newpkey
SET NOCOUNT OFF
</cfquery>
Another valid way to get the identity in SQLServer is
SELECT IDENT_CURRENT('tablename ') which returns the latest identity
in the table name in question. That also prevents getting the
identity of the wrong table in the event of the query being used as
part of a trigger which affects multiple tables.
Cheers
Mike Kear
Windsor, NSW, Australia
Certified Advanced ColdFusion Developer
AFP Webworks
http://afpwebworks.com
ColdFusion, PHP, ASP, ASP.NET hosting from AUD$15/month
On 4/5/06, S. Isaac Dealey <[EMAIL PROTECTED]> wrote:
> > Tony wrote:
> >> does that retrieve the newest UUID Primary Key generated?
> >> right now, i have a dateAdded field that i sort by, to
> >> get the newest one.
>
> > That's not real reliable. What happens if two records are
> > added in the
> > same second? :)
>
> > Yes, it returns the last identity inserted, and inside a
> > transaction, I
> > think it's reliable.
>
> Minor clarification: when using scope_identity() with a single cfquery
> tag, the cftransaction tags aren't necessary to make this reliable.
> Scope_identity() returns specifically the identity last inserted in
> the current batch statement. Which means that it can't be used (or at
> least not reliably) if you use two separate cfquery tag pairs,
> however, if there's only one cfquery tag pair with both the insert and
> select statements in it, then that obviates the need for the
> cftransaction (and I believe it will also improve performance in
> comparison to using the cftransaction around either one or two
> queries). I don't think I've actually tried using scope_identity()
> with two cfqueries, so I don't know if it would produce an error or
> just produce bad or unreliable data. I suspect it would produce an
> error.
>
>
> s. isaac dealey 434.293.6201
> new epoch : isn't it time for a change?
>
> add features without fixtures with
> the onTap open source framework
>
> http://www.fusiontap.com
> http://coldfusion.sys-con.com/author/4806Dealey.htm
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:236952
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
