CODEWITHSUNDEEP
coldfusion
Bob Smith
Bob Smith

Reputation: 11

Coldfusion: Can't reference var from query result set

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.

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++; 
}

Upvotes: 1

Views: 455

Answers (1)

bittersweetryan
bittersweetryan

Reputation: 3443

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

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>

Upvotes: 3

Related Questions