cftry/cfcatch and cftransaction

[AlwaysWilling]

I'm having some problems with using "cftransaction" with "cftry/cfcatch" tags. What happens is I make the insert into fail on purpose to see if the transaction would prevent the insertion of the new record. I am also using cftry/cfcatch to send myself an email when an error does occur.

But I noticed that the cftransaction does not work when I use it with cftry/cfcatch. When I enter a valid data the PK value is 1, then I make it fail (I get the email that it failed for a reason) and then I enter another valid data. The PK value should be 1 and 2 (because out of 3 attempts only 2 were valid) but instead I see 1 and 3. Why isn't it 1 and 2?

This is my code:

	<cftry>
		[b]<cftransaction>[/b]
		<cfquery name="addProgram" datasource="#DB#">
			DECLARE @TheTable_ID int
			INSERT INTO myTableRec	(	col1, 
										col2, 
										col3, 
										col4, 
									)
			VALUES					(	<cfif len(#FORM.col1#) GTE 1>#FORM.col1#<cfelse>NULL</cfif>,
										<cfif len(#FORM.col2#) GTE 1>'#FORM.col2#'<cfelse>NULL</cfif>,
										<cfif len(#FORM.col3#) GTE 1>'#FORM.col3#'<cfelse>NULL</cfif>,
										<cfif len(#FORM.col4#) GTE 1>'#FORM.col4#'<cfelse>NULL</cfif>,
									)
			SELECT @TheTable_ID = @@identity
			SELECT @TheTable_ID AS TheTable_ID
		</cfquery>	
		[b]</cftransaction>[/b]

	<cfcatch type="any">
		<cfmail from="#sendErrEmailFrom#" to="#sendErrEmailTo#" subject="Error" type="html">
			There was an error when adding/updating a program. <br/><br/>
			The query set was:<br/>
			------------------------------------------------------------------------------------------<br/>
			INSERT INTO myTableRec	(	col1, 
										col2, 
										col3, 
										col4, 
									)
			VALUES					(	<cfif len(#FORM.col1#) GTE 1>#FORM.col1#<cfelse>NULL</cfif>,
										<cfif len(#FORM.col2#) GTE 1>'#FORM.col2#'<cfelse>NULL</cfif>,
										<cfif len(#FORM.col3#) GTE 1>'#FORM.col3#'<cfelse>NULL</cfif>,
										<cfif len(#FORM.col4#) GTE 1>'#FORM.col4#'<cfelse>NULL</cfif>,
									)
			------------------------------------------------------------------------------------------<br/>
			<br/><br/>
			The following is a cfdump of all relevant info:<br/>
			<cfdump var="#cfcatch#">
		</cfmail>
	</cfcatch>
	</cftry>

 

[ecobb]

Basically, the way a cftransaction works is that it acutally does make the insert, but then it goes back and removes it when the transaction fails. So, the problem with your PK value shows that cftransaction is working correctly.


Hope This Helps!

ECAR
ECAR Technologies

http://www.ecartech.com

"My work is a game, a very serious game." - M.C. Escher

 

[AlwaysWilling]

But the same happens when I take out the cftransaction tag and only leave cftry/cfcatch tags. Does the cftry/cfcatch work the same way as cftransaction tag? In that if the insert into fails the record is removed from the database?

Is there a way to make the PK value sequencial order even in the case it fails?

Thanks!

 

[AlwaysWilling]

Anyone?