CFTRY w CFTRANSACTION - code not working

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

LVL 25
James RodgersWeb Applications DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott BennettManager TechnologyCommented:
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

0
Scott BennettManager TechnologyCommented:
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
James RodgersWeb Applications DeveloperAuthor Commented:
that code makes more sense...

thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.