Search code examples
sql-servercoldfusionrailo

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


Solution

  • 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#" />