reset or clear cfquery

Forum|Forum|15 years ago
February 3, 2011
2 replies
2182 views

I am working with some old cold fusion code (see below for details) and there is a process where it does a cfquery from a database which has the name of 'vRecipients'.

Later in the code it does a cfmail using the vRecripients query to send emails. Recently we had an issue where in a loop it used the same email. Is there a way where for each loop I can clear the cfquery?

Sample code:

<cfif param_Queried EQ 0>
<CFQUERY datasource="#dsn#" name="vRecipients" username="#cf_username#" password="#cf_password#">
SELECT EmailAddress FROM table WHERE field = '#param_ClientID#'
</CFQUERY>
</cfif>

<CFMAIL query="vRecipients" to="#EmailAddress#" from="email@domain.com" subject="Subject" TYPE="HTML">
Body of email goes here
</CFMAIL>

server.ColdFusion.ProductLevel = Professional
server.ColdFusion.ProductName = ColdFusion Server
server.ColdFusion.ProductVersion = 5, 0, 0, 0
server.OS.AdditionalInformation = Service Pack 4
server.OS.BuildNumber = 2195
server.OS.Name = Windows NT
server.OS.Version = 5.0

This topic has been closed for replies.

B

Brian Meloche

Participating Frequently

That sounds like more of a SQL issue than a issue, to filter out

duplicate records. Try this instead:

SELECT DISTINCT EmailAddress FROM table WHERE field = '#param_ClientID#'

J

JR__Bob__Dobbs

Inspiring

I don't understand the question. What does "clear the query" mean?

Does the same email addresses occur multiple times in vRecipients and you only want to execute the CFMAIL tag once for each distinct email address?

L

lleemonAuthor

Participating Frequently

Here is kind of the sudo overview:

1) Look in a holding folder for files to upload:

-file_x_A100.pdf

-file_z_B100.pdf

2) Loop through each file

3) based on what the 2nd parameter is in the file name is how it it is handled. (ie: x and z)

4) I then call the code that I listed originally and based on this value we query a user table which will grab a certain email address to send to

<cfif param_Queried EQ 0>
<CFQUERY datasource="#dsn#" name="vRecipients" username="#cf_username#" password="#cf_password#">
SELECT EmailAddress FROM table WHERE field = '#param_ClientID#'
</CFQUERY>
</cfif>

<cfif param_Queried EQ 1>
<CFQUERY datasource="#dsn#" name="vRecipients" username="#cf_username#" password="#cf_password#">
SELECT EmailAddress FROM table WHERE field = '#param_ClientID#'
</CFQUERY>
</cfif>

<CFMAIL query="vRecipients" to="#EmailAddress#" from="email@domain.com" subject="Subject" TYPE="HTML">
Body of email goes here
</CFMAIL>

So what appears to have happened is the first loop ran fine, found a email then sent. The next loop something happening and the CFQUERY was rerun so it used the last loops vRecipients value and sent an email to that person instead of someone else. So, we need a way for each entry into #4 section we want to clear the CFQUERY so if it doesn't run it won't use last loops data.

J

JR__Bob__Dobbs

Inspiring

Based on the statement " it used the last loops vRecipients value " it sounds like there may be some logic/flow control in the code that needs revision. Is vRecipients in a shared scope that persists after the CFMAIL is executed? What would occur if the value of param_Queried was neither "1" nor "0"?

Rather than use two different query objects with the same name you might consider re-factoring your code so that a query object is passed to a CFFUNCTION that handles mail. This might make it easier to troubleshoot. In step 4 you would place a call to a mailing function within the CFIF statements.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded