7 Replies Latest reply on Jan 19, 2007 10:01 PM by shearak

    cfmail querying database not working

    shearak
      The code below is not working and I am not getting any errors. Any suggestions.


      <cfparam name="notifyDate" default="#DateFormat(Now(), "short")#">

      <CFQUERY name="getNotifications" datasource="AEDWEB">
      SELECT u.strEmail,
      u.strFirstName,
      u.strLastName,
      cc.strCCNoteEmailBody
      FROM umsUsers u,
      ctsConferenceCalls cc
      WHERE cc.dtsCCNotificationDate = '#notifyDate#'
      AND u.intCCNotify = 1
      AND u.intMemberstatus = 1
      </CFQUERY>

      <CFOUTPUT query="getNotifications">
      <CFMAIL TO="#strEmail#" FROM="notification@ccicom.com" SUBJECT="Conference Call Reminder" SERVER="mail.ccicom.com" port="25">
      #strCCNoteEmailBody#
      </CFMAIL>
      </CFOUTPUT>
        • 1. cfmail querying database not working
          rmorgan Level 1
          Is it possible that your
          WHERE cc.dtsCCNotificationDate = '#notifyDate#'
          AND u.intCCNotify = 1
          AND u.intMemberstatus = 1
          statements do not exist? What if you take your 'AND' statements out?

          Also, you do not need cfoutput statements with cfmail.
          • 2. cfmail querying database not working
            shearak Level 1
            I just can a query with the WHERE, AND and I get data return. I also modified the code by taking the cfoutput statement out. I still get no mail. I also check in CF admin and the mail server is running. Any other sugguestions please.


            <cfparam name="notifyDate" default="#DateFormat(Now(), "short")#">

            <CFQUERY name="getNotifications" datasource="AEDWEB">
            SELECT u.strEmail,
            u.strFirstName,
            u.strLastName,
            cc.strCCNoteEmailBody
            FROM umsUsers u,
            ctsConferenceCalls cc
            WHERE cc.dtsCCNotificationDate = '#notifyDate#'
            AND u.intCCNotify = 1
            AND u.intMemberstatus = 1
            </CFQUERY>

            <CFMAIL TO="#strEmail#" FROM="notification@ccicom.com" SUBJECT="Conference Call Reminder" SERVER="mail.ccicom.com" port="25">
            #strCCNoteEmailBody#
            </CFMAIL>
            • 3. Re: cfmail querying database not working
              rmorgan Level 1
              Have you checked your logs in cfadmin?

              Try hardcoding the TO.

              Some email servers are set that email can only be sent from a valid email account. Is notification@ccicom.com a real email account on that server.
              • 4. Re: cfmail querying database not working
                shearak Level 1
                OK , I tried everything still no luck. How would you rewrite this to make it work with any database (query)?

                <cfparam name="notifyDate" default="#DateFormat(Now(), "short")#">

                <CFQUERY name="getNotifications" datasource="AEDWEB">
                SELECT u.strEmail,
                u.strFirstName,
                u.strLastName,
                cc.strCCNoteEmailBody
                FROM umsUsers u,
                ctsConferenceCalls cc
                WHERE cc.dtsCCNotificationDate = '#notifyDate#'
                AND u.intCCNotify = 1
                AND u.intMemberstatus = 1
                </CFQUERY>


                <CFMAIL query="getNotifications" TO="#strEmail#" FROM="notification@icom.com" SUBJECT="Conference Call Reminder" SERVER="mail.icom.com" port="25">
                #strCCNoteEmailBody#
                </CFMAIL>


                Thanks again
                • 5. cfmail querying database not working
                  Dan Bracuk Level 5
                  try taking the table aliases (aliii?) out of the select clause of your query. It might not make a difference, but it's worth a shot.
                  Also consider taking away all the attibutes of your cfmail tag except query, to, from and subject.


                  By the way, how many records does your query return?
                  • 6. Re: cfmail querying database not working
                    shearak Level 1
                    The query returns no records. How do I output dates in SQL? I even typed in a real date and no return. The cfparm is not working here. Please advise.

                    <cfparam name="notifyDate" default="#DateFormat(Now(), "MM/DD/YYYY")#">
                    <cfquery name="Recordset1" datasource="AEDWEB">
                    SELECT u.strEmail,
                    u.strFirstName,
                    u.strLastName,
                    cc.strCCNoteEmailBody
                    FROM umsUsers u,
                    ctsConferenceCalls cc
                    WHERE cc.dtsCCNotificationDate = #notifyDate#
                    AND u.intCCNotify = 1
                    AND u.intMemberstatus = 1
                    </cfquery>


                    • 7. Re: cfmail querying database not working
                      shearak Level 1
                      Thanks everyone for your feed. I finally figured it out. Below is the query that made the cfmail work.

                      <cfquery name="getNotifications" datasource="AEDWEB">
                      SELECT u.strEmail,
                      u.strFirstName,
                      u.strLastName,
                      cc.strCCNoteEmailBody
                      FROM umsUsers u,
                      ctsConferenceCalls cc
                      WHERE cc.dtsCCNotificationDate = #CreateODBCDate(Now())#
                      AND u.intCCNotify = 1
                      AND u.intMemberstatus = 1
                      </cfquery>