Skip to main content
L
lleemon
Participating Frequently
February 3, 2011
Question

reset or clear cfquery

  • February 3, 2011
  • 2 replies
  • 2181 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.

    2 replies

    B
    Brian Meloche
    Participating Frequently
    February 3, 2011

    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
    February 3, 2011

    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
    February 4, 2011

    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
    February 4, 2011

    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.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices