CFTransaction and looping through multiple inserts

[Thane Sherrington]

I'm entering a series of answers into a table, so I have a loop like this:

<cfloop index="QuestionID" list="#Form.SurveyQuestionIDList#">
        <!--- Get the answer. --->
        <CFSET NumericAnswer=Form["Answer"&QuestionID]>
        <!--- Run the query. --->
        <cfquery datasource="#Datasource#" name="InsertSurveyAnswers">
                INSERT INTO     SurveyAnswers(QuestionID, SurveySubjectID, 
NumericAnswer,          SurveyDate)
                VALUES          (#QuestionID#, #Form.SubjectID#, 
#NumericAnswer#,                #Form.DateSurveyEntered#)
        </cfquery>
</CFLOOP>

If I want to use CFTransaction, would I set it up like this?

<CFSET CommitWrite=True>
<cftransaction action=begin>
        <cftry>
        <cfloop index="QuestionID" list="#Form.SurveyQuestionIDList#">
                <!--- Get the answer. --->
                <CFSET NumericAnswer=Form["Answer"&QuestionID]>
                <!--- Run the query. --->
                <cfquery datasource="#Datasource#" name="InsertSurveyAnswers">
                        INSERT INTO     SurveyAnswers(QuestionID, SurveySubjectID,     
                 NumericAnswer, 
SurveyDate)
                        VALUES          (#QuestionID#, #Form.SubjectID#, 
#NumericAnswer#,                        #Form.DateSurveyEntered#)
                </cfquery>
        </CFLOOP>
        <cfcatch type='database'>
            <cfset CommitWrite=FALSE>
        </cfcatch>
   </cftry>

<cfif CommitWrite is 'TRUE'>
     <cftransaction action='Commit'>
   <cfelse>
     <cftransaction action='Rollback'>
   </cfif>

</cftransaction>

Or like this?

<CFSET CommitWrite=True>
<cftransaction action=begin>
        <cfloop index="QuestionID" list="#Form.SurveyQuestionIDList#">
        <!--- Get the answer. --->
        <CFSET NumericAnswer=Form["Answer"&QuestionID]>
        <!--- Run the query. --->
        <cftry>
                <cfquery datasource="#Datasource#" name="InsertSurveyAnswers">
                        INSERT INTO     SurveyAnswers(QuestionID, SurveySubjectID,     
                 NumericAnswer, 
SurveyDate)
                        VALUES          (#QuestionID#, #Form.SubjectID#, 
#NumericAnswer#,                        #Form.DateSurveyEntered#)
                </cfquery>
        <cfcatch type='database'>
            <cfset CommitWrite=FALSE>
        </cfcatch>
          </cftry>
        </CFLOOP>


<cfif CommitWrite is 'TRUE'>
     <cftransaction action='Commit'>
   <cfelse>
     <cftransaction action='Rollback'>
   </cfif>

</cftransaction>

Or can CFTransaction be used in this situation?

T

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
This list and all House of Fusion resources hosted by CFHosting.com. The place for 
dependable ColdFusion Hosting.

                                Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4