Scope_identity() 返回错误'@P11'。MSSQL，Coldfusion。尝试获取上次插入的 ID

我得到一个错误与SELECT Scope_Identity()

我正在从表单中插入新的数据行。该行生成一个自动递增的ID(RBID)。

我试图立即抓住它并将它(RBID)插入另一行，因此可以在需要时引用。

<cfquery name="recbilling" datasource="#request.dsn#" result="myResult">
INSERT INTO RBilling
(CID, UserID, DateCompleted, ClientID, Title, Qty, Rate, Taxable, Description, NextBillingDate,
FrequencyUnit, FrequencyDuration, ProjectID)
VALUES (
<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#CID#">, 
<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#UserID#">, 
<cfqueryparam cfsqltype="CF_SQL_TIMESTAMP" value="#DateCompleted#">, 
<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#ClientID#">, 
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#text0#">, 
<cfqueryparam cfsqltype="CF_SQL_REAL" value="#Qty#">, 
<cfqueryparam cfsqltype="CF_SQL_REAL" value="#text2#">, 
<cfqueryparam cfsqltype="CF_SQL_BIT" value="#Taxable#">, 
<cfqueryparam cfsqltype="CF_SQL_LONGVARCHAR" value="#text1#">, 
<cfqueryparam cfsqltype="CF_SQL_TIMESTAMP" value="#NextBillingDate#">, 
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#FrequencyUnit#">, 
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#FrequencyDuration#">, 
<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#ProjectID#">)
</cfquery>
<cfquery name="getRBID" datasource="#request.dsn#">
SELECT Scope_Identity() as newID
</cfquery>
<cfset RBID = #getRBID.newID#>
</cfif>


<cfquery name="submithourssubmit" datasource="#request.dsn#">
INSERT INTO Billedfees
(CID, UserID, DateCompleted, ClientID, Title, Qty, Rate, Taxable, Description, ProjectID, RBID)
VALUES (
<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#CID#">, 
<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#UserID#">,
<cfqueryparam cfsqltype="CF_SQL_TIMESTAMP" value="#DateCompleted#">, 
<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#ClientID#">, 
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#text0#">, 
<cfqueryparam cfsqltype="CF_SQL_REAL" value="#Qty#">, 
<cfqueryparam cfsqltype="CF_SQL_REAL" value="#text2#">, 
<cfqueryparam cfsqltype="CF_SQL_BIT" value="#Taxable#">, 
<cfqueryparam cfsqltype="CF_SQL_LONGVARCHAR" value="#text1#">, 
<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#ProjectID#">
<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#RBID#">
)
</cfquery>

得到的错误是:执行数据库查询错误。[Macromedia][SQLServer JDBC Driver][SQLServer] '@P11'附近语法错误。
错误发生在第57行。

我花了一段时间才弄清楚@P11是什么，但似乎第一个查询中的FrequencyUnit或第二个插入查询中的RBID都有问题。

数据类型设置正确

提前谢谢你。

编辑:

SQL字符串(param 1) = [type='IN'， class='java.lang. lang. ']Integer'， value='15488'， sqltype='CF_SQL_INTEGER']， (param 2) = [type='IN'， class='java.lang. lang ']。Integer'， value='21066'， sqltype='CF_SQL_INTEGER']， (param 3) = [type='IN'， class='java.sql. net ']。时间戳'，value='2013-03-22 00:00:00.0'， sqltype='CF_SQL_TIMESTAMP']，(参数4)= [type='IN'， class='java.lang. 0']。Integer'， value='143933'， sqltype='CF_SQL_INTEGER']， (param 5) = [type='IN'， class='java.lang. lang ']。String'， value='Software Test 3'， sqltype='CF_SQL_VARCHAR']， (param 6) = [type='IN'， class='java.lang. lang. ']。Float'， value='1.0'， sqltype='CF_SQL_REAL']， (param 7) = [type='IN'， class='java.lang. net ']。Float'， value='33.0'， sqltype='CF_SQL_REAL']， (param 8) = [type='IN'， class='java.lang. lang. ']。Boolean'， value='false'， sqltype='CF_SQL_BIT']， (param 9) = [type='IN'， class='java.lang. lang ']。String'， value='Software Test 3'， sqltype='CF_SQL_LONGVARCHAR']， (param 10) = [type='IN'， class='java.lang. lang. ']。Integer'， value='0'， sqltype='CF_SQL_INTEGER']， (param 11) = [type='IN'， class='java.lang. lang ']。Integer'， value='153314'， sqltype='CF_SQL_INTEGER']

到目前为止我得到的是:

<cftransaction>

<cfset NextBillingDate = #DateAdd("#FrequencyUnit#", "#FrequencyDuration#", #DateCompleted#)#>
<cfquery name="recbilling" datasource="#request.dsn#" result="myResult">
INSERT INTO RBilling
(CID, UserID, DateCompleted, ClientID, Title, Qty, Rate, Taxable, Description, NextBillingDate,
FrequencyUnit, FrequencyDuration, ProjectID)
VALUES (
<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#CID#">, 
<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#UserID#">, 
<cfqueryparam cfsqltype="CF_SQL_TIMESTAMP" value="#DateCompleted#">, 
<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#ClientID#">, 
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#text0#">, 
<cfqueryparam cfsqltype="CF_SQL_REAL" value="#Qty#">, 
<cfqueryparam cfsqltype="CF_SQL_REAL" value="#text2#">, 
<cfqueryparam cfsqltype="CF_SQL_BIT" value="#Taxable#">, 
<cfqueryparam cfsqltype="CF_SQL_LONGVARCHAR" value="#text1#">, 
<cfqueryparam cfsqltype="CF_SQL_TIMESTAMP" value="#NextBillingDate#">, 
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#FrequencyUnit#">, 
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#FrequencyDuration#">, 
<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#ProjectID#">)
</cfquery>

<cfoutput>
<Cfset RBID="#myResult.identitycol#">
</cfoutput>


<cfquery name="submithourssubmit" datasource="#request.dsn#">
INSERT INTO Billedfees
(CID, UserID, DateCompleted, ClientID, Title, Qty, Rate, Taxable, Description, ProjectID, RBID)
VALUES (
<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#CID#">, 
<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#UserID#">,
<cfqueryparam cfsqltype="CF_SQL_TIMESTAMP" value="#DateCompleted#">, 
<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#ClientID#">, 
<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#text0#">, 
<cfqueryparam cfsqltype="CF_SQL_REAL" value="#Qty#">, 
<cfqueryparam cfsqltype="CF_SQL_REAL" value="#text2#">, 
<cfqueryparam cfsqltype="CF_SQL_BIT" value="#Taxable#">, 
<cfqueryparam cfsqltype="CF_SQL_LONGVARCHAR" value="#text1#">, 
<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#ProjectID#">
<cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#RBID#">
)
</cfquery>

给了我同样的错误:执行数据库查询错误。[Macromedia][SQLServer JDBC Driver][SQLServer] '@P11'附近语法错误。
错误发生在第59行。

第59行是第二个插入查询。

如果您还需要什么，请告诉我。

谢谢

你已经可以使用这个

来访问这个变量了

<cfoutput>#myResult.identitycol#</cfoutput>
<Cfset rbid = myResult.identitycol>

因为你使用的是MSSQL，无论你命名你的结果将有.identitycol属性。

我相信如果你使用mysql, oracle, access，它可能会有所不同(作为cf9)，但我认为他们在cf10中标准化了。

更新:

错误的原因是您在RBID字段前面缺少一个逗号。下面是你的代码的固定版本:

<cftransaction>
    <cfset NextBillingDate = DateAdd(FrequencyUnit, FrequencyDuration, DateCompleted)>
    <cfquery name="recbilling" datasource="#request.dsn#" result="myResult">
        INSERT INTO RBilling 
        (
          CID
          , UserID
          , DateCompleted
          , ClientID
          , Title
          , Qty
          , Rate
          , Taxable
          , Description
          , NextBillingDate
          , FrequencyUnit
          , FrequencyDuration
          , ProjectID
        )
        VALUES 
        (
          <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#CID#">
          , <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#UserID#">
          , <cfqueryparam cfsqltype="CF_SQL_TIMESTAMP" value="#DateCompleted#">
          , <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#ClientID#">
          , <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#text0#">
          , <cfqueryparam cfsqltype="CF_SQL_REAL" value="#Qty#">
          , <cfqueryparam cfsqltype="CF_SQL_REAL" value="#text2#">
          , <cfqueryparam cfsqltype="CF_SQL_BIT" value="#Taxable#">
          , <cfqueryparam cfsqltype="CF_SQL_LONGVARCHAR" value="#text1#">
          , <cfqueryparam cfsqltype="CF_SQL_TIMESTAMP" value="#NextBillingDate#">
          , <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#FrequencyUnit#">
          , <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#FrequencyDuration#">
          , <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#ProjectID#">
       )
    </cfquery>
    <cfset RBID = myResult.identitycol>
    <cfquery name="submithourssubmit" datasource="#request.dsn#">
        INSERT INTO Billedfees (
            CID
            , UserID
            , DateCompleted
            , ClientID
            , Title
            , Qty
            , Rate
            , Taxable
            , Description
            , ProjectID
            , RBID
        )
        VALUES (
            <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#CID#">
            , <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#UserID#">
            , <cfqueryparam cfsqltype="CF_SQL_TIMESTAMP" value="#DateCompleted#">
            , <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#ClientID#">
            , <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#text0#">
            , <cfqueryparam cfsqltype="CF_SQL_REAL" value="#Qty#">
            , <cfqueryparam cfsqltype="CF_SQL_REAL" value="#text2#">
            , <cfqueryparam cfsqltype="CF_SQL_BIT" value="#Taxable#">
            , <cfqueryparam cfsqltype="CF_SQL_LONGVARCHAR" value="#text1#">
            , <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#ProjectID#">
            , <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#RBID#">
        )
    </cfquery>
</cftransaction>

正如我在评论中提到的，除非您正在运行CF7或更早的版本，否则您不应该需要使用scope_identity。作为CF8，新的身份值将放在您的result属性即"myResult"。因此，新的ID在变量myResult.IDENTITYCOL中可用。

顺便说一句，如果要使用scope_identity，则必须确保在相同的数据库连接上调用它。所以要么在insert query的末尾调用它:

    <cfquery name="addRecord" ...>
          SET NOCOUNT ON;
          INSERT INTO TableName (....)
          VALUES ( ..... );
          SELECT SCOPE_IDENTITY() AS RBID;
          SET NOCOUNT OFF;
    </cfquery>
    <cfoutput> New RBID: #addRecord.RBID#</cfoutput>

…-=或=-在cftransaction中包装两个cfquery，以确保保持相同的连接。即

  <cftransaction>
    <cfquery name="addRecord" ...>
          INSERT INTO TableName (....)
          VALUES ( ..... );
    </cfquery>
    <cfquery name="getNewRecord" ...>
          SELECT SCOPE_IDENTITY() AS RBID;
    </cfquery>
 </cftransaction>
 <cfoutput> New RBID: #getNewRecord.RBID#</cfoutput>

相关内容

最新更新

热门标签：