获取我刚刚插入的记录的主键 [英] Getting the primary key of the records I just inserted

问题描述

这变得有些棘手。我想输出刚插入的记录的主键。我正在插入多个记录：

This is becoming kind of tricky. I want to output the primary key of the record I just inserted. I am doing an insert of multiple records:

我的插入语句如下所示：

My insert statement looks something like this:

<cfquery name="imprtFiles" datasource="#mydsn#" result="#result#">
    <cfoutput query="myFileList">
        INSERT INTO myTablename (mycolumn) VALUES ('#valuegohere#');
    </cfoutput>
</cfquery>

<cfset newID = result.IdentityCol>
<cfoutput>#newID#</cfoutput>

这会引发CF错误：

元素IDENTITYCOL在结果中未定义。

"Element IDENTITYCOL is undefined in RESULT."

因此，我认为希望有另一个获取我刚刚插入的记录的PK的方法。有什么想法吗？

So I'm thinking that there is hopefully another way to get PK of the record I just inserted. Any thoughts?

这是我根据示例使用的代码：

Here is the code I used according to the example:

<cftransaction>
    <cfquery name="importFiles" datasource="#dsn#" result="result">
        <cfoutput query="myFileList">
        INSERT INTO tbl_logfiles (originalFile, originalFileSize) VALUES ('#name#', '#length#');
        </cfoutput>
    </cfquery>

    <cfquery name="getID" datasource="#dsn#">
         select Max(fileID) as NewID from tbl_logfiles;
    </cfquery>
</cftransaction>

<cfset newID = getID.NewID>
<cfoutput> #newID# </cfoutput>

我从 #newID＃获得的输出是280，这是我数据库表中当前最高的fileID。

The output I get from #newID# is 280, which is the highest fileID in my database table at this time. Weird.

我要获取的是我导入的最后任何 N 条记录。我希望有一种方法可以基于 cfquery.result 从 cfoutput 标记以某种方式输出它。 / p>

What I am trying to get is the last whatever N records I imported. I was hoping there was a way I could output it somehow from the cfoutput tag based on the cfquery.result.

推荐答案

在Coldfusion中有多种实现方法-在此处查看： http://tutorial480.easycfm.com/

There are various ways to do it in coldfusion - check it out here: http://tutorial480.easycfm.com/

编辑：

尝试此方法，它将最终生效：

Try this method, it will definitly work:

<cftransaction>
<cfquery name="imprtFiles" datasource="#mydsn#" result="#result#">
<cfoutput query="myFileList">
INSERT INTO myTablename (mycolumn) VALUES ('#valuegohere#');
</cfoutput>
</cfquery>
<cfquery name="getID" datasource="#mydsn#" result="#result#">
select Max(id) as NewID from myTablename;
</cfquery>
</cftransaction>
<cfset newID = getID.NewID>
<cfoutput>
#newID#
</cfoutput>

根据评论进行编辑：

是， 当然。您在cfquery标记内有一个cfoutput查询，因此您要逐行插入，并且每次都在创建新的ID。如果需要每个ID，则需要将整个内容包装到cfoutput中：

Yes, for sure. You have a cfoutput query within the cfquery tag, so you are inserting row after row, and you are creating a new ID everytime. If you need the IDs for each one, then you need to wrap the whole thing in your cfoutput:

<cftransaction>
<cfoutput query="myFileList">
<cfquery name="imprtFiles" datasource="#mydsn#" result="#result#">
INSERT INTO myTablename (mycolumn) VALUES ('#valuegohere#');
</cfquery>
<cfquery name="getID" datasource="#mydsn#" result="#result#">
select Max(id) as NewID from myTablename;
</cfquery>
<cfset newID = getID.NewID>
#newID#
</cfoutput>
</cftransaction>

这篇关于获取我刚刚插入的记录的主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！