Coldfusion：无法从查询结果集中引用var

问题描述

使用cfscript，尝试设置新插入问题的ID，以便我可以在答案插入中使用它来建立关系。在cfscript之外，我已经完成了一百万遍了。 setName似乎是调用此查询来创建查询名称的适当方法。

Using cfscript, trying to set the ID of the newly inserted question so that I can use it in my answer insert to build the relationship. I've done this a million times outside of cfscript. setName seems to be the proper method to call to create the query name.

我收到一个错误，即qryQuestion中不存在 theQuestionID

I'm receiving the error that "theQuestionID" does not exist in qryQuestion

i = 1;
while ( structKeyExists( form, "question" & i ) )
{

    q = new Query();
    q.setDatasource("kSurvey");
    q.setName("qryQuestion");
    q.setSQL("
              set nocount on
              insert into question (question)
              values('#form["question#i#"]#')
              select @@IDENTITY AS theQuestionID
              set NOCOUNT off
              ");
    q.execute();

    writeOutput("Question"&i&"<br>");
    j = 1;
        while ( structKeyExists( form, "question" & i & "_answer" & j) ) {

            q = new Query();
            q.setDatasource("kSurvey");
            q.setSQL("
                      insert into answer (answer,questionid)
                      values('#form["question#i#_answer#j#"]#',#qryQuestion.theQuestionID#)
                      ");
            q.execute();

            writeOutput("Answer"&j&"<br>");
            j++;
        }

i++;
}

推荐答案

还有一种更好的方法不必选择@@ identity（这本身并不是从sql server中获取它的最佳方法，使用scope_identity是在sql server中实现此目的的最佳实践方法。

Theres a better way to accomplish this without having to select @@identity (which in itself isn't the best way to get it from sql server, using scope_identity is the best practice way to do this in sql server. http://msdn.microsoft.com/en-us/library/ms190315.aspx

幸运的是，ColdFusion使其更容易：

Fortunately ColdFusion makes this even easier:

<cfscript>
    insertQuery = new query();

    insertQuery.setDatasource("datasourcename");

    insertQuery.setSql("insert into contact(firstname, lastname)
           values('ryan','anklam')");

    result = insertQuery.Execute();

    theKey = result.getPrefix().generatedkey;
 </cfscript>

这篇关于Coldfusion：无法从查询结果集中引用var的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！