cfif statement not working it seems

[fdgsogc]

I have a cfloop using a query and within it I have a cfif statement. If the condition is not met, I want to ignore the code, hit the </cfloop> end tag and look at the next record. Before I hit the </cfloop> end tag, I set a value in the current record and all related records (all with the same email address) to indicate it has been processed. Then my CFIF statement at the top looks to see if the current record is marked processed. If not, it proceeds, else it should ignore all code inside the CFIF statement.

PROBLEM: The CFIF statement is not operating as I'd hoped and it is processing all 11 records from my query in the CFLOOP. I am suspecting that the query in the CFLOOP is not refreshing with the latest data in my isProcessed (emailsent) field.

QUESTION: Does a CFLOOP cache the record set of a query and so if an outside query from another application were to change the data in the recordset of my query in the CFLOOP while the loop was running, the CFLOOP would not see that change?

Here is my code:

			<!-- Send email to each unique dealercontact established in BoatTypes_Imported_temp table -->	
			<cfquery name="selecteddealers" datasource="#db#">
			select *
			from BoatTypes_Imported_temp
			</cfquery>
		
			<cfloop query="selecteddealers">
				
				<!-- there will be dups now !!!!!!! -->
				<cfquery name="createleadasset" datasource="#db#">
				Insert Into Lead_To_OEM_Product (LeadID,ProductLineID)
				VALUES ('#lastleadid#','#prodline#')
				</cfquery>

				<!-- Prevents email being sent more than once -->
				<cfif emailsent is 1>
				<cfoutput>#boattype# - #prodline# - #salescontactid# - #emailsent#<br></cfoutput>
		
					<!--Reset the date the sales person received a new lead to now-->
					<cfquery name="updatecontactdate" datasource="#db#">
					Update Dealer_Contact
					Set
					DateLastLeadSent = '#dateformat(NOW(), "mm/dd/yyyy")# #TimeFormat(NOW(), "medium")#'
					WHERE ContactID = '#salescontactid#'
					</cfquery>
					
					<!--End Dealer Look up-->
				
					<!--Get the contact information of the OEM -->
					<cfquery name="getoemcontact" datasource="#db#" >
					Select *
					FROM VIEW_Emailtext_OEMcontact
					WHERE ProductLineID = '#selecteddealers.prodline#' AND isemailcontact = '1'
					</cfquery>
					
					<cfquery name="getdealerinfo" datasource="#db#" >
					Select DealerName, dlrcity, dlrstate, dlrzip, dlrtelephone, contactid
					FROM VIEW_Dlr_DlrContact
					WHERE contactid = '#salescontactid#'
					</cfquery>
					
					
					<!--Get the header file associated with the product line ID-->
					<cfquery name="getheader" datasource="#db#">
					SELECT headername
					FROM OEM_Product_Line
					Where ProductLineID = '#selecteddealers.prodline#'
					</cfquery>
					
					
					<!--Get the email and page text from the database-->
					<cfquery name="getmessages" datasource="#db#" >
					Select *
					FROM App_PageText
					WHERE pagename = '#url.pagename#' 
					</cfquery>
					
					<cfquery name="getdlrmessage" datasource="#db#">
					Select *
					FROM App_EmailText
					WHERE emailname = 'dealercontactemail' AND OEMID = '#selecteddealers.prodline#'
					</cfquery>
					
					<cfquery name="getleadmessage" datasource="#db#" >
					Select *
					FROM App_EmailText
					WHERE emailname = 'leadtodealer' AND OEMID = '#selecteddealers.prodline#'
					</cfquery>
			
					<!-- Links DealerContact to Lead --><!-- must add URLKEY later -->
					<cfquery name="insertdealercontactid" datasource="#db#">
					SET NOCOUNT ON
					insert
					into lead_mapping
					(leadid, dlr_contact_id)
					values
					('#lastleadid#', '#selecteddealers.salescontactid#')
					SELECT SCOPE_IDENTITY() AS [lastdealerleadid];
					SET NOCOUNT OFF
					</cfquery>
		
					<cfoutput><cfset lastdealerleadid = "#insertdealercontactid.lastdealerleadid#"></cfoutput>
					
					<cfquery name="getURLkey" datasource="#db#">
					select urlkey
					from lead_mapping
					where adminid = '#lastdealerleadid#'
					</cfquery>
					
					<!--Email lead to dealer-->
					<cfmail to="francesco@digiuseppe.ca" from="#getoemcontact.OEM_EMAIL#" subject="New Lead for #salescontactemail#" type="html">
					<table width="600" border="0" cellspacing="0" cellpadding="0">
					  <tr>
						<td width="600" align="right" bgcolor="##122F69"><img src="#appurl##getheader.headername#"></td>
					  </tr>
					  <tr>
					  <td width="600"><br>
					<br>
					Hello #DealerContactFName#,<br>
					<br>
					A new lead has been posted for your review. Please <a href="#appurl#tracking.cfm?id=#getURLkey.urlkey#&salesid=#salescontactid#">click here</a> to get the contact information.<br>
					<br>
					#getleadmessage.emailtext#
					#getoemcontact.OEM_FNAME# #getoemcontact.OEM_LNAME#<br>
					#getoemcontact.OEM_NAME#<br>
					#getoemcontact.OEM_ADDRESS#<br>
					#getoemcontact.OEM_CITY#, #getoemcontact.OEM_STATE# #getoemcontact.OEM_ZIP#<br>
					#getoemcontact.OEM_PHONE#<br>
					#getoemcontact.OEM_EMAIL#
					</cfmail>
					
					<!-- Sets email sent to 1 so don't repeat emails to same contact -->
					<cfquery name="setemailsent" datasource="#db#">
					update BoatTypes_Imported_temp
					set emailsent = '2'
					where salescontactid = '#salescontactid#'
					</cfquery>
					<cfset ifcount = ifcount +1>
					<cfset salescontacts = salescontacts & selecteddealers.salescontactid & ", ">
				<cfelse>
					stopped!<cfabort>
				</cfif><!-- Prevents email being sent more than once -->

 

[okiiyama]

I beleive I've been having a similar problem with CF and SQL Server. First the query I'm pulling the records from is a bit complex, and so I have been seeing some duplicates, and because of the possibility of duplicates I created an if statement to (attempt) to stop duplicate emails from being sent and new DB records from being created. Essentially before hitting the cfmail tag and inserting a new record, I would check the table to make sure the record had not yet been inserted. But that cfif statement wasn't working. I've now started to use a list that I'm appending to with each iteration of the cfloop and checking against that instead of using the cfif statement.

Doesn't answer any questions, but this is working quite a bit better.

 

[safaritek]

Well, to start with :

try <cfif selecteddealers.emailsent EQ "1">

Based on your code, your DB column for "emailsent" is a string, and some DBs may return a funky value for a string when you are checking numeric in the condition. Also, if you do not define the scope or query the value is being checked from, it can get confused as to which scope it is checking, especially when surrounded by numerous other queries, etc.


Second, in the code where you change the "emailsent" column so they don't get the email again, it is setting it to "2" ... isn't it "1" for already received, and "0" for not? (i.e. shouldn't you be using a bit (yes/no) column?)


Skimming your code, it would seem it should work if teh proper scopes are added everywhere to ensure the code doesn;t lose track of the information it is processing.