If the user clicks "submit", the form submits to itself again with a hidden
parameter that launches this:
<cfif (parameterExists(mySecretParameter)>
<cftransaction isolation="read_uncommitted" action=""> < -- lots of querying and stuff here -- >
<cfif finalResult eq "INVALID ENTRY">
<cftransaction action = ""> <cfelse>
<cftransaction action = ""
<cfquery datasource="#application.dsname#" name="query1">
UPDATE RequestGroup
SET Status=#newStatus#
WHERE RequestGroupID=#RequestGroupID#
</cfquery>
<cfquery datasource="#application.dsname#"
name="query2">
UPDATE Request
SET Status=#newStatus#
WHERE RequestGroupID=#fRequestGroupID#
</cfquery>
<cfstoredproc datasource="#application.dsname#"
procedure="cf_mySPCall" returncode="yes">
<cfprocparam type = "IN" CFSQLType =
"CF_SQL_INTEGER" value="#RequestGroupID#" dbVarName = "@RequestGroupID">
<cfprocresult
name="insertintoMyTable">
</cfstoredproc>
</cftransction>
</cfif>
Now, query1 and query2 commit just fine, but the stored procedure won't
commit even though I can see it kick off in SQL Profiler:
SQL:BatchStarting IF @@TRANCOUNT > 0 COMMIT TRAN
SQL:BatchStarting SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SQL:BatchStarting UPDATE RequestGroup
SET Status=1
WHERE RequestGroupID=7865
SQL:BatchStarting
UPDATE Request
SET Status=1
WHERE RequestGroupID=7865
RPC:Starting exec cf_mySPCall 7865
SQL:BatchStarting IF @@TRANCOUNT > 0 COMMIT TRAN
SQL:BatchStarting SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SQL:BatchStarting set implicit_transactions off
The SP updates a third table with data from the other two tables.
The form is re-displayed on the screen, and everything looks okay to the
user, but the SP didn't commit any data. However, if I resubmit the form
as-is or with changes, the SP DOES commit.
What gives? Is the data from the updates not available when the SP starts?
I have also tried the SP outside the <cftransaction> tag, with no luck.
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
