We help IT Professionals succeed at work.

CFTRY w CFTRANSACTION - code not working

James Rodgers
on
the below code ois directly from cf help files, when i tried to implement it i get an error about teh last block of a cftry must bre a cfcatch, but thsi code sample has a cftransaction, is there a different way to implement this?
<cftry>
<cfset commitIt = "Yes">
<cftransaction action = "begin">
  <cfquery name = 'makeNewCourse' dataSource = 'cfsnippets'>
  INSERT INTO Courses
    (Course_Num, Descript)
  VALUES
    ('#myNumber#', '#myDescription#')
  </cfquery>
  
  <cfcatch type = "DATABASE">
    <cftransaction action = "rollback"/>
    <cfset commitIt = "No">
  </cfcatch>
  
  <cfif commitIt>
    <cftransaction action = "commit"/>
  <cfelse>
    <cfset commitIt = "Yes">
  </cfif>
  <cfquery name = 'insertNewCourseToList' dataSource = 'cfsnippets'>
  INSERT INTO CourseList
    (CorNumber, CorDesc, Dept_ID,
    CorName, CorLevel, LastUpdate)
  VALUES
    ('#myNumber#', '#myDescription#', '#myDepartment#',
    '#myDescription#', '#myCorLevel#', #Now()#)
  </cfquery>
  
  <cfcatch type = "DATABASE">
    <cftransaction action = "rollback"/>
    <cfset commitIt = "No">
  </cfcatch>
  
  <cfif commitIt>
    <cftransaction action = "commit"/>
  <cfelse>
    <cfset commitIt = "Yes">
  </cfif>
</cftransaction>
</cftry>

Open in new window

Comment
Watch Question

Scott BennettManager Technology

Commented:
The example is not the right way to do it. You should put your ctry's around the queries and then use cfcatch's to determine if the queries failed. if a query fails then you should roll back the transaction. Also I am not sure if you would want that first commit or not, In this situation I would only use one commit after both queries have run successfully but to keep the example as close to the original as possible, here it is:
<cfset commitIt = "Yes">
<cftransaction action = "begin">
  <cftry>
  <cfquery name = 'makeNewCourse' dataSource = 'cfsnippets'>
  INSERT INTO Courses
    (Course_Num, Descript)
  VALUES
    ('#myNumber#', '#myDescription#')
  </cfquery>
  
  <cfcatch type = "DATABASE">
    <cftransaction action = "rollback"/>
    <cfset commitIt = "No">
  </cfcatch>
  </cftry>
  
  <cfif commitIt>
    <cftransaction action = "commit"/>
  <cfelse>
    <cfset commitIt = "Yes">
  </cfif>
  
  <cftry>
  <cfquery name = 'insertNewCourseToList' dataSource = 'cfsnippets'>
  INSERT INTO CourseList
    (CorNumber, CorDesc, Dept_ID,
    CorName, CorLevel, LastUpdate)
  VALUES
    ('#myNumber#', '#myDescription#', '#myDepartment#',
    '#myDescription#', '#myCorLevel#', #Now()#)
  </cfquery>
  
  <cfcatch type = "DATABASE">
    <cftransaction action = "rollback"/>
    <cfset commitIt = "No">
  </cfcatch>
  </cftry>
  
  <cfif commitIt>
    <cftransaction action = "commit"/>
  <cfelse>
    <cfset commitIt = "Yes">
  </cfif>
</cftransaction>

Open in new window

Manager Technology
Commented:
Personally I would probably do it more like:
<cfset commitIt = "Yes">
<cftransaction action = "begin">
  <cftry>
  <cfquery name = 'makeNewCourse' dataSource = 'cfsnippets'>
  INSERT INTO Courses
    (Course_Num, Descript)
  VALUES
    ('#myNumber#', '#myDescription#')
  </cfquery>
 
  <cfquery name = 'insertNewCourseToList' dataSource = 'cfsnippets'>
  INSERT INTO CourseList
    (CorNumber, CorDesc, Dept_ID,
    CorName, CorLevel, LastUpdate)
  VALUES
    ('#myNumber#', '#myDescription#', '#myDepartment#',
    '#myDescription#', '#myCorLevel#', #Now()#)
  </cfquery>
  
  <cfcatch type = "DATABASE">
    <cftransaction action = "rollback"/>
    <cfset commitIt = "No">
  </cfcatch>
  </cftry>
  
  <cfif commitIt>
    <cftransaction action = "commit"/>
  </cfif>
</cftransaction>

Open in new window

James RodgersWeb Applications Developer
CERTIFIED EXPERT

Author

Commented:
that code makes more sense...

thanks

Explore More ContentExplore courses, solutions, and other research materials related to this topic.