rotating email 2

[boatguy]

Once again, I'm stuck.

I have a SQL DB with a table that has 2 fields employeeemail(nvarchar 100) and Active(bit).

I want to rotate the email address that a web form gets sent to. So, all email addresses are set to inactive except the first one. Once the form submits and the data sent to the one active address, I want to set that record to inactive and the next one to active. Any recommendations? Suggestions to accomplish this are a more simple fashion?

I can handle the query to pull the email, I just don't know how to select the next record in line to activate it.

Thanks

 

[webmigit]

Also assuming there's an ID field "employeeID"

<cfquery...name="getOneActive">
  select top 2 * from emailTbl
   where active = 1
</cfquery>

<cfmail to="#getOneActive.employeeEmail[1]#" subject="Hey look...">Email Body</cfmail>

<cfif getOneActive.recordcount gt 0>
  <cfquery...>
    update emailTbl
       set active = 0
     where employeeID <= #getOneActive.employee[1]#
  </cfquery>
</cfif>

<cfif getOneActive.recordCount lt 2>
  <cfquery...>
    update emailTbl
       set active = 1
  </cfquery>
</cfif>

This should check for two active users. If it finds at least one active user, it sends to that email address.

If it finds only one or zero, it sets all emails to active that way the next time the page is hit, it will start back from the beginning.

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[boatguy]

webmigit I am eternally grateful! I was getting close tonight, but having problems when getting to the bottom of the list.

Thanks again.

 

[MorganGreylock]

It wasn't my problem, but it still deserves a star...
Good job webmigit!

MG

 

[boatguy]

I tried to give him one last night, must have clicked close too quickly. It works like a charm.