"insert into" db only working sometimes, not consistently

[MtnGirl]

I would really appreciate it if someone could look at my code below and try to help me out. I have an application that is suppose to take info submitted in a form, update it to one table in the database and then pass variables via URL to another page where three pieces of info should be inserted into a different table in the database. My problem is that sometimes this whole process works perfectly and other times the info is not inserted into the second table. The update part of the process ALWAYS seems to work, but the insert part is very inconsistent. I'm stumped as to what may be causing the problem. I'll include my code below. I'd be very grateful for any help or suggestions you could offer.

thanks,
gwenn

This is the code that's doing the update and then you can see where the user gets redirected to ApplyOnline.cfm in the cflocation tag.
<!--- Update Resume Data --->
<CFIF Not CompareNoCase(form.action,'Update') OR Not CompareNoCase(form.action,'Save')>
<cfquery name=&quot;UpdateResume&quot; datasource=&quot;#application.dsn#&quot; dbtype=&quot;ODBC&quot;>
Update resumes
Set ractive = 1,
rbriefdes = '#form.rbriefdes#',
resumefile = '#variables.resumefile#',
rname = '#form.rname#',
typeid = #URL.typeid#,
rEmpType = '#form.remptype#',
rDriversLic = '#form.rdriverslic#',
rSSNum = '#form.rssNum#',
rAlienNum = '#form.ralienNum#',
rHighSchool = '#form.rHighSchool#',
rHSGradeComp = '#form.rHSGradeComp#',
rHSEquivDip = '#form.rHSEquivDip#',
rHSAddress = '#form.rHSAddress#',
rEmpPrefTitle = '#form.remppreftitle#',
rEmpDateAvail = '#form.rempDateAvail#',
rdismissed = #form.rdismissed#,
rCitizen = #form.rCitizen#,
rConvicted = #form.rconvicted#,
rDischarge = #form.rdischarge#,
rForcesActive = #form.rforcesactive#,
rTyping = #form.rtyping#,
rContactEmplr = #form.rcontactemplr#,
rHSGraduated = #form.rHSGraduated#,
rAffirmation = #form.raffirmation#,
rcontactins = '#form.rContactIns#',
rhowheard = '#form.rhowheard#',
rtypingtext = '#form.rtypingtext#',
rvisatype = '#form.rvisatype#',
rintentdate = '#form.rintentdate#',
rnydismissed = #form.rnydismissed#,
rnyguilty = #form.rnyguilty#,
rResign = #form.rResign#,
rResignText = '#form.rResignText#',
rDeniedten = #form.rDeniedten#,
rDeniedtenText = '#form.rDeniedtenText#',
rDismissedText = '#form.rDismissedText#',
rMilBranch = '#form.rMilBranch#',
rMilDates = '#form.rMilDates#',
rMilRank = '#form.rMilRank#',
rMilDischarge = '#form.rMilDischarge#'
Where resumeid = #URL.resid#
</cfquery>

<CFSET workingID = #URL.resid#>

<!--- Set Note --->
<CFSET note = 'Resume has been #form.action#'>

<!--- Remove all dead resumes --->
<cfquery name=&quot;DeadRes&quot; datasource=&quot;#application.dsn#&quot; dbtype=&quot;ODBC&quot;>
SELECT ResumeID From Resumes
WHERE rActive = 0
AND rName IS Null
AND rdate < #CreateODBCDate(DateAdd('H',-1,Now()))#
</cfquery>
<CFSET ResIDS = 0>
<CFIF Len(DeadRes.ResumeID)>
<CFSET ResIDs = #valuelist(DeadRes.ResumeID)#>
</CFIF>
<CFINCLUDE template=&quot;removeresumes.cfm&quot;>

<cfif not isDefined('URL.action')>
<!--- Check for Applying Online --->
<CFIF isDefined('URL.ADID')>
<cflocation url=&quot;ApplyOnline.cfm?AdID=#URL.ADID#&ResumeID=#URL.resid#&quot; addtoken=&quot;No&quot;>
<cfabort>
<CFELSE>
<cflocation url=&quot;resumes.cfm?viewOK=1&MyResumes=1&quot; addtoken=&quot;No&quot;>
<cfabort>
</CFIF>
</CFIF>


<!--- Remove Resume --->
<CFELSEIF Not CompareNoCase(form.action,'Remove')>

<!--- Remove Resume File--->
<CFIF Len(form.currentresumefile)>
<cffile action=&quot;DELETE&quot; file=&quot;#thepath##form.currentresumefile#&quot;>
</CFIF>

<CFSET ResIDs = #URL.resID#>
<CFINCLUDE template=&quot;removeresumes.cfm&quot;>


<!--- Set workingid = 0 --->
<CFSET workingid=0>

<!--- Sete Note --->
<CFSET note = 'Resume has been Removed'>

<!---Relocate --->
<cflocation url=&quot;resumes.cfm?viewOK=1&MyResumes=1&quot; addtoken=&quot;No&quot;>

</CFIF>
<!---Relocate --->



And then here's the ApplyOnline.cfm file with the INSERT Into that only works sometimes...
<!--- DID REQUIRED VARIABLES GET PASSED from MHCRP_App, et al.--->
<CFIF isDefined('URL.AdID') AND isDefined('URL.ResumeID')>
<!--- Initialize --->
<CFIF isDefined('URL.AdID') AND isDefined('URL.ResumeID')>
<cfparam name=&quot;variables.adid&quot; default=&quot;#URL.AdID#&quot;>
<cfparam name=&quot;variables.Resumeid&quot; default=&quot;#URL.ResumeID#&quot;>
<CFELSE>
<cfparam name=&quot;variables.adid&quot; default=&quot;0&quot;>
<cfparam name=&quot;variables.Resumeid&quot; default=&quot;0&quot;>
</CFIF>

<!--- Store Transaction in Applicants --->
<CFQUERY NAME=&quot;addApplicant&quot; DATASOURCE=&quot;newaen1&quot;>
INSERT INTO Applicants (AdID, ResumeID, MemID)
Values(#variables.adid#, #variables.Resumeid#, #session.memberid#)
</CFQUERY>

<!--- Get data for Emailing --->
<!--- Get Job --->
<CFQUERY NAME=&quot;MyAd&quot; DATASOURCE=&quot;newaen1&quot;>
SELECT *
FROM jobview
WHERE adid = #variables.adid#
</CFQUERY>

<!--- Get Resume --->
<CFQUERY NAME=&quot;Myresume&quot; DATASOURCE=&quot;newaen1&quot;>
SELECT *
FROM resumeview
WHERE MemID = #session.memberid#
AND ResumeID = #variables.resumeID#
</CFQUERY>

<!--- Send Email --->
<CFINCLUDE Template=&quot;AppEmailSend.cfm&quot;>

<!--- Show Thank You --->
<CFINCLUDE Template=&quot;AppThankyou.cfm&quot;>


<!--- End Applicant processing --->
<CFELSE>
</CFIF>

 

[webmigit]

I'd suggest doing a CFLOCK on it for a timeout of 15 seconds.. Maybe simultaneous requests are tripping it.. Macromedia posts one article saying that it is not a good idea to cflock queries but only because then the lock waits for the query to be done.. This may however be your only solution..

Just CFLOCK it all...

HOPE THIS HELPS,
Tony Hicks

 

[MtnGirl]

If the update of the Resume table happens on one file (act_Resume.cfm) and the insert into the Applicants table happens on ApplyOnline.cfm, I can't CFLOCK both things together, can I? Would I just enclose the part on the ApplyOnline.cfm file since that seems to be the problem?

Sorry if this is a dumb question. I'm fairly new at this and inherited this project which was written by someone else.

Thanks again!!
Gwenn

 

[Silvano]

the CFLOCK is a must whenever application or session variable is being accessed like webmigit suggests here; to avoid having multiple locks in the template, you can use it only once at the top of the template and scope the variable localy:

<cflock timeout=&quot;30&quot; throwontimeout=&quot;Yes&quot; name=&quot;#application.ApplicationName#&quot;type=&quot;readonly&quot;>
<cfscript>
variables.dbn = application.dbn;
</cfscript>
</cflock>

so the application.dbn is accessible throughout the template by using variables.dbn and not having to use CFLOCK again;

second, in your query &quot;addApplicant&quot;:

INSERT INTO Applicants (AdID, ResumeID, MemID)
Values(#variables.adid#, #variables.Resumeid#, #session.memberid#)

I don't know exactly how your database look like, but consider the following example:

<cftransaction>
<cfquery name=&quot;addApplicant&quot;
datasource=&quot;#variables.dbn#&quot; dbtype=&quot;ODBC&quot;>
INSERT INTO Applicants (ResumeID, MemID)
VALUES (#variables.Resumeid#, #session.memberid#);
</cfquery>

<cfquery name=&quot;getNewAddApplicantID&quot;
datasource=&quot;#variables.dbn#&quot; dbtype=&quot;ODBC&quot;>
SELECT MAX(AdID) AS newAdIDID FROM Applicants;
</cfquery>
</cftransaction>

assumption: AdID field is primary key field and it's data type is Auto Number

this is recommended way to insert new data, generate the new ID, and extract the new ID to work with it Sylvano
dsylvano@hotmail.com

 

[MtnGirl]

Actually, there's another field in the Applicants table which is the primary key and is an autonumber (AppID). The AdID field is not unique. So could I do this?

<cftransaction>
<cfquery name=&quot;addApplicant&quot;
datasource=&quot;#variables.dbn#&quot; dbtype=&quot;ODBC&quot;>
INSERT INTO Applicants (ResumeID, MemID)
VALUES (#variables.AdID#, #variables.Resumeid#, #session.memberid#);
</cfquery>

<cfquery name=&quot;getNewAddApplicantID&quot;
datasource=&quot;#variables.dbn#&quot; dbtype=&quot;ODBC&quot;>
SELECT MAX(AppID) AS newAppIDID FROM Applicants;
</cfquery>
</cftransaction>

Thanks for your help!!
Gwenn

 

[Silvano]

if that is the case, this might be the cause of all your problems. you must have a field in the table to uniquely identify each and every record in it.

how do you determine the value of variables.AdID?
how do you know that the new value does not exist in the Applicants table already?

I think that the best way is to let the database create unique ID and you retrieve it in the getNewAddApplicantID query; to do that just open the database and set the Data Type for the AdID field to AutoNumber Sylvano
dsylvano@hotmail.com