CODEWITHSUNDEEP
sql-servercoldfusionrailo
user1431633
user1431633

Reputation: 658

result not working in cfquery using railo

I'm trying to get the value of the ID just inserted. NOTE: the column I'm trying to get is datatype uniqueidentifier created with default value newid()

The below code is what I'm trying to run:

<cfquery datasource="#ds#" result="newReplyID">
    INSERT INTO nlforums_Reply(AnswerID, ReplyValue, UserID, CreateDateTime)
    VALUES( <cfqueryparam value="#form.originalMessageID#" cfsqltype="CF_SQL_VARCHAR">,
            <cfqueryparam value="#form.replyValue#" cfsqltype="CF_SQL_LONGVARCHAR">,
            <cfqueryparam value="#form.userID#" cfsqltype="CF_SQL_VARCHAR">,
            <cfqueryparam value="#form.posted#" cfsqltype="CF_SQL_DATE">
           )
</cfquery>
<cfdump var="#newReplyID['IDENTITYCOL']#" />

The error I get: key [IDENTITYCOL] doesn't exist

Here's what happens if I dump #newReplyID#

enter image description here

Upvotes: 2

Views: 265

Answers (3)

dan
dan

Reputation: 11

I think that if you dig a little deeper you might find that IDENTITYCOL has been deprecated and is no longer available from SQL Server 2012 forward.

This solution still works nicely though: INSERT INTO table(col1, col2, etc) OUTPUT inserted.IDcolumnName VALUES(1,2,etc)

Upvotes: 1

Phillip Senn
Phillip Senn

Reputation: 47615

Have you tried using queryExecute instead?

Upvotes: 0

user1431633
user1431633

Reputation: 658

Here's what works. The result attribute doesn't work on a UUID.

<cfquery datasource="#ds#" name="newReplyID">
    INSERT INTO nlforums_Reply(AnswerID, ReplyValue, UserID, CreateDateTime)
    OUTPUT inserted.ReplyID 
    VALUES( <cfqueryparam value="#form.originalMessageID#" cfsqltype="CF_SQL_VARCHAR">,
            <cfqueryparam value="#form.replyValue#" cfsqltype="CF_SQL_LONGVARCHAR">,
            <cfqueryparam value="#form.userID#" cfsqltype="CF_SQL_VARCHAR">,
            <cfqueryparam value="#form.posted#" cfsqltype="CF_SQL_DATE">
           )
</cfquery>
<cfdump var="#newReplyID#" />

Upvotes: 4

Related Questions