daniel weissmann
asked on
Attribute validation error for tag CFMAIL. The value of the attribute TO is invalid. The length of the string, 0 character(s), must be greater than or equal to 1 character(s).
The following script is producing invoices. It was working fine in 4.5, but my server has upgraded to CF 7.0. The first invoice is produced, and then I encounter the error, I have removed all addresses from the database except for the 20 or so valid ones from people who bid in the auction. I am not a CF expert, and I am freaking out a little bit.
Here is the code:
<html>
<head>
<title>SEND INVOICES</title>
</head>
<body BGCOLOR="#FFFFFF">
<CFIF ParameterExists(ID) IS "NO"><CFSET ID=0></CFIF>
<CFIF ParameterExists(PASS) IS "NO"><CFSET PASS=0></CFIF>
<CFQUERY name="validate" DATASOURCE="DJS">SELECT * FROM ADMIN WHERE ID='#ID#' AND PASS='#PASS#'</CFQUERY>
<CFIF VALIDATE.RECORDCOUNT is 0><CFLOCATION URL="ERROR.CFM?ERROR=2"></ CFIF>
<CFQUERY name="checkclose" datasource="DJS">select * from times where type='auction'</cfquery>
<CFIF #checkclose.enddate# LT #CreateODBCDateTIME(now()) #>
<CFQUERY NAME="AUCTIONINFO" DATASOURCE="DJS" MAXROWS="1">SELECT * FROM TIMES</CFQUERY>
<CFQUERY NAME="lots" DATASOURCE="DJS">SELECT * FROM lots ORDER BY lot ASC</CFQUERY>
<CFLOOP QUERY="lots">
<CFQUERY name="highbidder" datasource="DJS" maxrows="1">SELECT * FROM bids WHERE lot = #lot# ORDER BY bid DESC</CFQUERY>
<CFIF HIGHBIDDER.RECORDCOUNT IS NOT 0>
<CFQUERY NAME="HBI" DATASOURCE="DJS" MAXROWS="1">SELECT * FROM MEMBERS WHERE MEMBERID='#HIGHBIDDER.MEMB ERID#'</CF QUERY>
<CFQUERY NAME="CHECKTABLE" DATASOURCE="DJS" MAXROWS="1">SELECT * FROM WINTABLE WHERE LOT=#LOT#</CFQUERY>
<CFIF CHECKTABLE.RECORDCOUNT IS 0>
<CFQUERY NAME="PUTRECORD" DATASOURCE="DJS">INSERT INTO WINTABLE(LOT, OPEN, DES, SCN, CON, SCV, COUNTRY, BIDNUMBER, MEMBERID, HIGHBID, EMAIL, CONSIGN)
VALUES(#LOT#, #OPEN#, '#DES#', '#SCN#', '#CON#', #SCV#, '#COUNTRY#', #HIGHBIDDER.BID#, '#HIGHBIDDER.MEMBERID#', #HIGHBIDDER.AMOUNT#, '#HBI.EMAIL#', '#CONSIGN#')</CFQUERY>
<CFELSE>
<CFQUERY NAME="UPRECORD" DATASOURCE="DJS">UPDATE WINTABLE SET BIDNUMBER=#HIGHBIDDER.BID# , MEMBERID='#HIGHBIDDER.MEMB ERID#', HIGHBID=#HIGHBIDDER.AMOUNT #, EMAIL='#HBI.EMAIL#' WHERE LOT=#LOT#</CFQUERY>
</CFIF>
<CFELSE>
<CFQUERY NAME="CHECKTABLE" DATASOURCE="DJS" MAXROWS="1">SELECT * FROM WINTABLE WHERE LOT=#LOT#</CFQUERY>
<CFSET BID=0>
<CFSET AMOUNT=0>
<CFSET MEMBERID="NOBID">
<CFSET EMAIL="danny@djsstamps.com ">
<CFIF CHECKTABLE.RECORDCOUNT IS 0>
<CFQUERY NAME="PUTRECORD" DATASOURCE="DJS">INSERT INTO WINTABLE(LOT, OPEN, DES, SCN, CON, SCV, COUNTRY, BIDNUMBER, MEMBERID, HIGHBID, EMAIL, CONSIGN)
VALUES(#LOT#, #OPEN#, '#DES#', '#SCN#', '#CON#', #SCV#, '#COUNTRY#', #BID#, '#MEMBERID#', #AMOUNT#, '#EMAIL#', '#CONSIGN#')</CFQUERY>
<CFELSE>
<CFQUERY NAME="UPRECORD" DATASOURCE="DJS">UPDATE WINTABLE SET BIDNUMBER=#BID#, MEMBERID='#MEMBERID#', HIGHBID=#AMOUNT#, EMAIL='#EMAIL#' WHERE LOT=#LOT#</CFQUERY>
</CFIF>
</CFIF>
</CFLOOP>
<CFQUERY NAME="GETMEMBERS" DATASOURCE="DJS">SELECT * FROM MEMBERS ORDER BY MEMBERID ASC</CFQUERY>
<CFLOOP QUERY="GETMEMBERS">
<CFQUERY NAME="GETLOTS" DATASOURCE="DJS">SELECT * FROM WINTABLE WHERE MEMBERID='#getmembers.MEMB ERID#' ORDER BY LOT ASC</CFQUERY>
<CFQUERY NAME="MINFO" DATASOURCE="DJS">SELECT * FROM MEMBERS WHERE MEMBERID='#MEMBERID#'</CFQ UERY>
<CFIF GETLOTS.RECORDCOUNT IS NOT 0>
<cfset return="">
<cfset return=return & Chr(10)>
<CFMAIL query="getlots" TO="#MINFO.EMAIL#" FROM="danny@djsstamps.com" CC="djsstamps@gmail.com" SUBJECT="Invoice - DJS Stamps Auction ## #AUCTIONINFO.ANUMBER#" SERVER="localhost">
DJS Stamps AUCTION ## #AUCTIONINFO.ANUMBER#
POB 21516
Baltimore, MD 21282-1516
Phone Number (410) 804-8718
FAX (413) 643-4765
CONGRATULATIONS!, you are the successful bidder in
DJS Stamps Auction ## #AUCTIONINFO.ANUMBER#, #Dateformat(AUCTIONINFO.EN DDATE, 'MMMM DD, YYYY')#,
of the following auction LOTS.
We accept Visa, MasterCard, and American Express as well as checks (made out to Danny Weissmann)
in payment of these LOTS. You may email, or call (410) 804-8718
with your card ## and expiration date. You may also use the Paypal
option found on the auction homepage for a secure, online
credit card payment.
If you have a credit card on file, just email your OK to
put the balance on that card.
Please enclose a copy of this invoice with payment.
<CFSET X=0>
<CFSET HP=0>
<CFSET TAX=0>
#MINFO.NAME# (#MINFO.EMAIL#) AUCTION ID - #MINFO.MEMBERID# <CFIF MINFO.DEALER IS 'X'>MINNESOTA STAMP DEALER</CFIF>
#MINFO.ADDRESS#
#MINFO.CITY#, #MINFO.REGION# #MINFO.POSTCODE#
LOT ## - SCOTT - DESCRIPTION - PRICE #return#
__________________________ __________ __________ __________ __________ __________ __________ __________
<CFOUTPUT>
#LOT# - #scn# - #DES# - #CON# - #NumberFormat(HIGHBID, '_$_,___,___.__')# #return#
<CFSET X=X+#HIGHBID#></CFOUTPUT>
_____________________
<CFSET Y=X><CFIF Y LT 75><CFSET POST=100/100><CFSET INS=0><CFELSEIF Y GTE 75 and Y LTE 200><CFSET POST=100/100><CFSET INS=2.10><CFELSE><CFSET POST=100/100></CFIF><CFIF MINFO.REGION IS 'QQ' OR MINFO.REGION IS 'Qaryland'><CFSET TAX=Y*0.06><CFELSE><CFSET TAX=0></CFIF>
<CFIF Y GT 200 AND Y LTE 400><CFSET INS=3.35>
<CFELSEIF Y GT 400><CFSET INCR=Int(Y/100 - 3)><CFSET INS=3.35+(INCR*1.25)></CFI F>
<CFSET Z=X>
<CFIF Z LT 100><CFSET Z=X-(X/20)><CFELSEIF Z GTE 100 and Z LT 500><CFSET Z=X-(X/10)><CFELSEIF Z GTE 500><CFSET Z=X-(X/10)-(x/20)></CFIF>
<CFSET TOTAL=Z+INS+POST+TAX>
SUBTOTAL = #NumberFormat(X, '_$_,___,___.__')# #return#
<CFIF X LT 100>SUBTOTAL (Less 10%) = #NumberFormat(Z, '_$_,___,___.__')#</CFIF> #return#
<CFIF X GTE 100 and X LT 500>SUBTOTAL (Less 15%) = #NumberFormat(Z, '_$_,___,___.__')#</CFIF> #return#
<CFIF X GTE 500>SUBTOTAL (Less 20%) = #NumberFormat(Z, '_$_,___,___.__')#</CFIF> #return#
POSTAGE = #NumberFormat(POST, '_$_,___,___.__')# #return#
INSURANCE = #NumberFormat(INS, '_$_,___,___.__')# (insured privately through APS) #return#
<CFIF #TAX# IS NOT 0>SALES TAX = #NumberFormat(TAX, '_$_,___,___.__')#</CFIF> #return#
_____________________
YOUR TOTAL = #NumberFormat(TOTAL, '_$_,___,___.__')# #return#
Thanks,
Danny
PLEASE - Come Visit us at our web page
http://www.djsstamps.com
DJ'S Stamps
POB 21516
Baltimore, MD 21282-1516
Phone Number (410) 804-8718
FAX (413) 643-4765
</CFMAIL>
<CFELSE>
<CFMAIL TO="#MINFO.EMAIL#" FROM="danny@djsstamps.com" SUBJECT="Final Results- DJ'S StampS Auction ## #AUCTIONINFO.ANUMBER#" SERVER="localhost">
DJS Stamps AUCTION ## #AUCTIONINFO.ANUMBER#
POB 21516
Baltimore, MD 21282-1516
Phone Number (410) 804-8718
FAX (413) 643-4765
#MINFO.NAME# (#MINFO.EMAIL#) AUCTION ID - #MINFO.MEMBERID#
#MINFO.ADDRESS#
#MINFO.CITY#, #MINFO.REGION# #MINFO.POSTCODE#
Thank you for having shown interest in DJ's Stamps Auction ## #AUCTIONINFO.ANUMBER#,
which just closed on #Dateformat(AUCTIONINFO.EN DDATE, 'MMMM DD, YYYY')#.
If you bid in this auction but did not win, we hope that you will join us
for the next auction. We thank you for your business.
Thanks,
Danny
PLEASE - Come Visit us at our web page
http://www.djsstamps.com
DJ'S Stamps
POB 21516
Baltimore, MD 21282-1516
Phone Number (410) 804-8718
FAX (413) 643-4765
IF YOU WOULD LIKE TO BE REMOVED FROM OUR AUCTIONS, PLEASE E-MAIL US AT:
danny@djsstamps.com
</CFMAIL>
</CFIF>
</CFLOOP>
<center><h2>ALL INVOICES HAVE BEEN SENT!</h2></center><BR>
<CFOUTPUT><p align="center"><a href="admin.cfm?ID=#ID#&PA SS=#PASS#" ><big>Retu rn to Main Admin Page</big></a></p></CFOUTP UT>
<CFELSE>
<CENTER><font color="##FF0000" SIZE="5">CAN'T PERFORM THIS FUNCTION YET - AUCTION STILL OPEN</FONT></CENTER><BR>
<CFOUTPUT><p align="center"><a href="admin.cfm?ID=#ID#&PA SS=#PASS#" ><big>Retu rn to Main Admin Page</big></a></p></CFOUTP UT>
</CFIF>
</body>
</html>
Here is the error:
Attribute validation error for tag CFMAIL.
The value of the attribute TO is invalid. The length of the string, 0 character(s), must be greater than or equal to 1 character(s).
The error occurred in E:\www\djsauctions.com\adm in\si.cfm: line 78
Called from E:\www\djsauctions.com\adm in\si.cfm: line 72
Called from E:\www\djsauctions.com\adm in\si.cfm: line 66
Called from E:\www\djsauctions.com\adm in\si.cfm: line 15
Called from E:\www\djsauctions.com\adm in\si.cfm: line 1
76 :
77 :
78 : <CFMAIL query="getlots" TO="#MINFO.EMAIL#" FROM="danny@djsstamps.com" CC="djsstamps@gmail.com" SUBJECT="Invoice - DJS Stamps Auction ## #AUCTIONINFO.ANUMBER#" SERVER="localhost">
79 : DJS Stamps AUCTION ## #AUCTIONINFO.ANUMBER#
80 : POB 21516
Any hep would be greatly appreciated !
Here is the code:
<html>
<head>
<title>SEND INVOICES</title>
</head>
<body BGCOLOR="#FFFFFF">
<CFIF ParameterExists(ID) IS "NO"><CFSET ID=0></CFIF>
<CFIF ParameterExists(PASS) IS "NO"><CFSET PASS=0></CFIF>
<CFQUERY name="validate" DATASOURCE="DJS">SELECT * FROM ADMIN WHERE ID='#ID#' AND PASS='#PASS#'</CFQUERY>
<CFIF VALIDATE.RECORDCOUNT is 0><CFLOCATION URL="ERROR.CFM?ERROR=2"></
<CFQUERY name="checkclose" datasource="DJS">select * from times where type='auction'</cfquery>
<CFIF #checkclose.enddate# LT #CreateODBCDateTIME(now())
<CFQUERY NAME="AUCTIONINFO" DATASOURCE="DJS" MAXROWS="1">SELECT * FROM TIMES</CFQUERY>
<CFQUERY NAME="lots" DATASOURCE="DJS">SELECT * FROM lots ORDER BY lot ASC</CFQUERY>
<CFLOOP QUERY="lots">
<CFQUERY name="highbidder" datasource="DJS" maxrows="1">SELECT * FROM bids WHERE lot = #lot# ORDER BY bid DESC</CFQUERY>
<CFIF HIGHBIDDER.RECORDCOUNT IS NOT 0>
<CFQUERY NAME="HBI" DATASOURCE="DJS" MAXROWS="1">SELECT * FROM MEMBERS WHERE MEMBERID='#HIGHBIDDER.MEMB
<CFQUERY NAME="CHECKTABLE" DATASOURCE="DJS" MAXROWS="1">SELECT * FROM WINTABLE WHERE LOT=#LOT#</CFQUERY>
<CFIF CHECKTABLE.RECORDCOUNT IS 0>
<CFQUERY NAME="PUTRECORD" DATASOURCE="DJS">INSERT INTO WINTABLE(LOT, OPEN, DES, SCN, CON, SCV, COUNTRY, BIDNUMBER, MEMBERID, HIGHBID, EMAIL, CONSIGN)
VALUES(#LOT#, #OPEN#, '#DES#', '#SCN#', '#CON#', #SCV#, '#COUNTRY#', #HIGHBIDDER.BID#, '#HIGHBIDDER.MEMBERID#', #HIGHBIDDER.AMOUNT#, '#HBI.EMAIL#', '#CONSIGN#')</CFQUERY>
<CFELSE>
<CFQUERY NAME="UPRECORD" DATASOURCE="DJS">UPDATE WINTABLE SET BIDNUMBER=#HIGHBIDDER.BID#
</CFIF>
<CFELSE>
<CFQUERY NAME="CHECKTABLE" DATASOURCE="DJS" MAXROWS="1">SELECT * FROM WINTABLE WHERE LOT=#LOT#</CFQUERY>
<CFSET BID=0>
<CFSET AMOUNT=0>
<CFSET MEMBERID="NOBID">
<CFSET EMAIL="danny@djsstamps.com
<CFIF CHECKTABLE.RECORDCOUNT IS 0>
<CFQUERY NAME="PUTRECORD" DATASOURCE="DJS">INSERT INTO WINTABLE(LOT, OPEN, DES, SCN, CON, SCV, COUNTRY, BIDNUMBER, MEMBERID, HIGHBID, EMAIL, CONSIGN)
VALUES(#LOT#, #OPEN#, '#DES#', '#SCN#', '#CON#', #SCV#, '#COUNTRY#', #BID#, '#MEMBERID#', #AMOUNT#, '#EMAIL#', '#CONSIGN#')</CFQUERY>
<CFELSE>
<CFQUERY NAME="UPRECORD" DATASOURCE="DJS">UPDATE WINTABLE SET BIDNUMBER=#BID#, MEMBERID='#MEMBERID#', HIGHBID=#AMOUNT#, EMAIL='#EMAIL#' WHERE LOT=#LOT#</CFQUERY>
</CFIF>
</CFIF>
</CFLOOP>
<CFQUERY NAME="GETMEMBERS" DATASOURCE="DJS">SELECT * FROM MEMBERS ORDER BY MEMBERID ASC</CFQUERY>
<CFLOOP QUERY="GETMEMBERS">
<CFQUERY NAME="GETLOTS" DATASOURCE="DJS">SELECT * FROM WINTABLE WHERE MEMBERID='#getmembers.MEMB
<CFQUERY NAME="MINFO" DATASOURCE="DJS">SELECT * FROM MEMBERS WHERE MEMBERID='#MEMBERID#'</CFQ
<CFIF GETLOTS.RECORDCOUNT IS NOT 0>
<cfset return="">
<cfset return=return & Chr(10)>
<CFMAIL query="getlots" TO="#MINFO.EMAIL#" FROM="danny@djsstamps.com"
DJS Stamps AUCTION ## #AUCTIONINFO.ANUMBER#
POB 21516
Baltimore, MD 21282-1516
Phone Number (410) 804-8718
FAX (413) 643-4765
CONGRATULATIONS!, you are the successful bidder in
DJS Stamps Auction ## #AUCTIONINFO.ANUMBER#, #Dateformat(AUCTIONINFO.EN
of the following auction LOTS.
We accept Visa, MasterCard, and American Express as well as checks (made out to Danny Weissmann)
in payment of these LOTS. You may email, or call (410) 804-8718
with your card ## and expiration date. You may also use the Paypal
option found on the auction homepage for a secure, online
credit card payment.
If you have a credit card on file, just email your OK to
put the balance on that card.
Please enclose a copy of this invoice with payment.
<CFSET X=0>
<CFSET HP=0>
<CFSET TAX=0>
#MINFO.NAME# (#MINFO.EMAIL#) AUCTION ID - #MINFO.MEMBERID# <CFIF MINFO.DEALER IS 'X'>MINNESOTA STAMP DEALER</CFIF>
#MINFO.ADDRESS#
#MINFO.CITY#, #MINFO.REGION# #MINFO.POSTCODE#
LOT ## - SCOTT - DESCRIPTION - PRICE #return#
__________________________
<CFOUTPUT>
#LOT# - #scn# - #DES# - #CON# - #NumberFormat(HIGHBID, '_$_,___,___.__')# #return#
<CFSET X=X+#HIGHBID#></CFOUTPUT>
_____________________
<CFSET Y=X><CFIF Y LT 75><CFSET POST=100/100><CFSET INS=0><CFELSEIF Y GTE 75 and Y LTE 200><CFSET POST=100/100><CFSET INS=2.10><CFELSE><CFSET POST=100/100></CFIF><CFIF MINFO.REGION IS 'QQ' OR MINFO.REGION IS 'Qaryland'><CFSET TAX=Y*0.06><CFELSE><CFSET TAX=0></CFIF>
<CFIF Y GT 200 AND Y LTE 400><CFSET INS=3.35>
<CFELSEIF Y GT 400><CFSET INCR=Int(Y/100 - 3)><CFSET INS=3.35+(INCR*1.25)></CFI
<CFSET Z=X>
<CFIF Z LT 100><CFSET Z=X-(X/20)><CFELSEIF Z GTE 100 and Z LT 500><CFSET Z=X-(X/10)><CFELSEIF Z GTE 500><CFSET Z=X-(X/10)-(x/20)></CFIF>
<CFSET TOTAL=Z+INS+POST+TAX>
SUBTOTAL = #NumberFormat(X, '_$_,___,___.__')# #return#
<CFIF X LT 100>SUBTOTAL (Less 10%) = #NumberFormat(Z, '_$_,___,___.__')#</CFIF> #return#
<CFIF X GTE 100 and X LT 500>SUBTOTAL (Less 15%) = #NumberFormat(Z, '_$_,___,___.__')#</CFIF> #return#
<CFIF X GTE 500>SUBTOTAL (Less 20%) = #NumberFormat(Z, '_$_,___,___.__')#</CFIF> #return#
POSTAGE = #NumberFormat(POST, '_$_,___,___.__')# #return#
INSURANCE = #NumberFormat(INS, '_$_,___,___.__')# (insured privately through APS) #return#
<CFIF #TAX# IS NOT 0>SALES TAX = #NumberFormat(TAX, '_$_,___,___.__')#</CFIF> #return#
_____________________
YOUR TOTAL = #NumberFormat(TOTAL, '_$_,___,___.__')# #return#
Thanks,
Danny
PLEASE - Come Visit us at our web page
http://www.djsstamps.com
DJ'S Stamps
POB 21516
Baltimore, MD 21282-1516
Phone Number (410) 804-8718
FAX (413) 643-4765
</CFMAIL>
<CFELSE>
<CFMAIL TO="#MINFO.EMAIL#" FROM="danny@djsstamps.com"
DJS Stamps AUCTION ## #AUCTIONINFO.ANUMBER#
POB 21516
Baltimore, MD 21282-1516
Phone Number (410) 804-8718
FAX (413) 643-4765
#MINFO.NAME# (#MINFO.EMAIL#) AUCTION ID - #MINFO.MEMBERID#
#MINFO.ADDRESS#
#MINFO.CITY#, #MINFO.REGION# #MINFO.POSTCODE#
Thank you for having shown interest in DJ's Stamps Auction ## #AUCTIONINFO.ANUMBER#,
which just closed on #Dateformat(AUCTIONINFO.EN
If you bid in this auction but did not win, we hope that you will join us
for the next auction. We thank you for your business.
Thanks,
Danny
PLEASE - Come Visit us at our web page
http://www.djsstamps.com
DJ'S Stamps
POB 21516
Baltimore, MD 21282-1516
Phone Number (410) 804-8718
FAX (413) 643-4765
IF YOU WOULD LIKE TO BE REMOVED FROM OUR AUCTIONS, PLEASE E-MAIL US AT:
danny@djsstamps.com
</CFMAIL>
</CFIF>
</CFLOOP>
<center><h2>ALL INVOICES HAVE BEEN SENT!</h2></center><BR>
<CFOUTPUT><p align="center"><a href="admin.cfm?ID=#ID#&PA
<CFELSE>
<CENTER><font color="##FF0000" SIZE="5">CAN'T PERFORM THIS FUNCTION YET - AUCTION STILL OPEN</FONT></CENTER><BR>
<CFOUTPUT><p align="center"><a href="admin.cfm?ID=#ID#&PA
</CFIF>
</body>
</html>
Here is the error:
Attribute validation error for tag CFMAIL.
The value of the attribute TO is invalid. The length of the string, 0 character(s), must be greater than or equal to 1 character(s).
The error occurred in E:\www\djsauctions.com\adm
Called from E:\www\djsauctions.com\adm
Called from E:\www\djsauctions.com\adm
Called from E:\www\djsauctions.com\adm
Called from E:\www\djsauctions.com\adm
76 :
77 :
78 : <CFMAIL query="getlots" TO="#MINFO.EMAIL#" FROM="danny@djsstamps.com"
79 : DJS Stamps AUCTION ## #AUCTIONINFO.ANUMBER#
80 : POB 21516
Any hep would be greatly appreciated !
ASKER
Thats what is so wierd. I trimmed the member list to only valid customers (20 or so that bid), and I still get the error. Then I went in and changed all the email addresses to my email address, but I only got 1 invoice, then this error.
Is there a way to trap the error to see what is exactly causing it. I am not a CF expert, and I appreciate the help.
Is there a way to trap the error to see what is exactly causing it. I am not a CF expert, and I appreciate the help.
ASKER
Is there a syntax change from cf 4.5 to CF7 that might cause this?
Ok, it's hard to know how you want to handle the errors, whether you throw an error or just ignore it or just show a message to the screen.
In this code, I added a CFIF CFELSIF block to test the MINFO record to see if it exists, see if EMAIL is populated
You have to take the big block of code that sends the email and put it between the CFELSE and the CFENDIF of this new statement
In this code, I added a CFIF CFELSIF block to test the MINFO record to see if it exists, see if EMAIL is populated
You have to take the big block of code that sends the email and put it between the CFELSE and the CFENDIF of this new statement
<CFLOOP QUERY="GETMEMBERS">
<CFQUERY NAME="GETLOTS" DATASOURCE="DJS">SELECT * FROM WINTABLE WHERE MEMBERID='#getmembers.MEMBERID#' ORDER BY LOT ASC</CFQUERY>
<CFQUERY NAME="MINFO" DATASOURCE="DJS">SELECT * FROM MEMBERS WHERE MEMBERID='#MEMBERID#'</CFQUERY>
<cfif minfo.recordCount eq 0>
Member #MEMBERID# is not found<br>
<cfelseif len(MINFO.EMAIL)>
Member #MEMBERID# is does not have an email address...<br>
<cfelse>
<!---- Put the entire block that sends the email inside this CFIF statement ----->
<CFIF GETLOTS.RECORDCOUNT IS NOT 0>
<cfset return="">
<cfset return=return & Chr(10)>
<CFMAIL query="getlots" TO="#MINFO.EMAIL#" FROM="danny@djsstamps.com" CC="djsstamps@gmail.com" SUBJECT="Invoice - DJS Stamps Auction ## #AUCTIONINFO.ANUMBER#" SERVER="localhost">
DJS Stamps AUCTION ## #AUCTIONINFO.ANUMBER#
POB 21516
... etc ....
</CFMAIL>
.....
</CFIF>
<!---- end big block that goes inside the new CFIF statement ------>
</cfif> <!---- end if check MINFO record ---->
ASKER
I got this result:
Member #MEMBERID# is does not have an email address...
Member #MEMBERID# is does not have an email address...
Member #MEMBERID# is does not have an email address...
Member #MEMBERID# is does not have an email address...
Member #MEMBERID# is does not have an email address...
Member #MEMBERID# is does not have an email address...
Member #MEMBERID# is does not have an email address...
Member #MEMBERID# is does not have an email address...
Member #MEMBERID# is does not have an email address...
Member #MEMBERID# is does not have an email address...
Member #MEMBERID# is does not have an email address...
Member #MEMBERID# is does not have an email address...
Member #MEMBERID# is does not have an email address...
Member #MEMBERID# is does not have an email address...
Member #MEMBERID# is does not have an email address...
Member #MEMBERID# is does not have an email address...
Member #MEMBERID# is does not have an email address...
Member #MEMBERID# is does not have an email address...
Member #MEMBERID# is does not have an email address...
Member #MEMBERID# is does not have an email address...
Member #MEMBERID# is does not have an email address...
Member #MEMBERID# is does not have an email address...
Member #MEMBERID# is does not have an email address...
Member #MEMBERID# is does not have an email address...
Member #MEMBERID# is does not have an email address...
Member #MEMBERID# is does not have an email address...
The script completed, but I still only got the first invoice. All my email addresses are set at: danny@djsstamps.com
Member #MEMBERID# is does not have an email address...
Member #MEMBERID# is does not have an email address...
Member #MEMBERID# is does not have an email address...
Member #MEMBERID# is does not have an email address...
Member #MEMBERID# is does not have an email address...
Member #MEMBERID# is does not have an email address...
Member #MEMBERID# is does not have an email address...
Member #MEMBERID# is does not have an email address...
Member #MEMBERID# is does not have an email address...
Member #MEMBERID# is does not have an email address...
Member #MEMBERID# is does not have an email address...
Member #MEMBERID# is does not have an email address...
Member #MEMBERID# is does not have an email address...
Member #MEMBERID# is does not have an email address...
Member #MEMBERID# is does not have an email address...
Member #MEMBERID# is does not have an email address...
Member #MEMBERID# is does not have an email address...
Member #MEMBERID# is does not have an email address...
Member #MEMBERID# is does not have an email address...
Member #MEMBERID# is does not have an email address...
Member #MEMBERID# is does not have an email address...
Member #MEMBERID# is does not have an email address...
Member #MEMBERID# is does not have an email address...
Member #MEMBERID# is does not have an email address...
Member #MEMBERID# is does not have an email address...
Member #MEMBERID# is does not have an email address...
The script completed, but I still only got the first invoice. All my email addresses are set at: danny@djsstamps.com
ASKER
Let me revise that. I did not get any invoices.
ASKER
One more thing. I am using a MS access DB.
I know you're new to ColdFusion, in order to see the value of a variable, it must be in CFOUTPUT.
Member <cfoutput>#MEMBERID#</cfou tput> is does not have an email address...<br>
Wrapping the variable as shown above will show you which memberIDs do not have an email address.
I don't now your application, but you may want to revisit some of the logic just to make sure you've got it all right.
I don't know what table you're calling "invoices" because there is no table or query named invoices. Maybe it's getLots from the winTable?
In any case, I notice that your loop to send emails will send to EVERY member, every time you run the script, even if that member is not involved in the auction. I believe this to be true because there is no where clause on the member query "GetMembers" to filter down which members to email or not. It will email every member.
Same thing with your "lots" query. There is not where-clause that would filter the code to only run for certain lots. Every time you run this code it will update every "lots" even ones from months ago. Unless the lots table is being deleted at some point, every LOTS record will be recalculated and emailed.
In any case, if that's the way the code has always been, then maybe somehow it's right.
Here is the modified code, with my lines testing the email address inserted.
To verify the blank email issue, look in your database of members and see how many have an empty email address. Perhaps members are removing their email addresses in order to keep from getting an email on every auction? even when they are not involved...
Member <cfoutput>#MEMBERID#</cfou
Wrapping the variable as shown above will show you which memberIDs do not have an email address.
I don't now your application, but you may want to revisit some of the logic just to make sure you've got it all right.
I don't know what table you're calling "invoices" because there is no table or query named invoices. Maybe it's getLots from the winTable?
In any case, I notice that your loop to send emails will send to EVERY member, every time you run the script, even if that member is not involved in the auction. I believe this to be true because there is no where clause on the member query "GetMembers" to filter down which members to email or not. It will email every member.
Same thing with your "lots" query. There is not where-clause that would filter the code to only run for certain lots. Every time you run this code it will update every "lots" even ones from months ago. Unless the lots table is being deleted at some point, every LOTS record will be recalculated and emailed.
In any case, if that's the way the code has always been, then maybe somehow it's right.
Here is the modified code, with my lines testing the email address inserted.
To verify the blank email issue, look in your database of members and see how many have an empty email address. Perhaps members are removing their email addresses in order to keep from getting an email on every auction? even when they are not involved...
<html>
<head>
<title>SEND INVOICES</title>
</head>
<body BGCOLOR="#FFFFFF">
<CFIF ParameterExists(ID) IS "NO"><CFSET ID=0></CFIF>
<CFIF ParameterExists(PASS) IS "NO"><CFSET PASS=0></CFIF>
<CFQUERY name="validate" DATASOURCE="DJS">SELECT * FROM ADMIN WHERE ID='#ID#' AND PASS='#PASS#'</CFQUERY>
<CFIF VALIDATE.RECORDCOUNT is 0><CFLOCATION URL="ERROR.CFM?ERROR=2"></CFIF>
<CFQUERY name="checkclose" datasource="DJS">select * from times where type='auction'</cfquery>
<CFIF #checkclose.enddate# LT #CreateODBCDateTIME(now())#>
<CFQUERY NAME="AUCTIONINFO" DATASOURCE="DJS" MAXROWS="1">SELECT * FROM TIMES</CFQUERY>
<CFQUERY NAME="lots" DATASOURCE="DJS">SELECT * FROM lots ORDER BY lot ASC</CFQUERY>
<CFLOOP QUERY="lots">
<CFQUERY name="highbidder" datasource="DJS" maxrows="1">SELECT * FROM bids WHERE lot = #lot# ORDER BY bid DESC</CFQUERY>
<CFIF HIGHBIDDER.RECORDCOUNT IS NOT 0>
<CFQUERY NAME="HBI" DATASOURCE="DJS" MAXROWS="1">SELECT * FROM MEMBERS WHERE MEMBERID='#HIGHBIDDER.MEMBERID#'</CFQUERY>
<CFQUERY NAME="CHECKTABLE" DATASOURCE="DJS" MAXROWS="1">SELECT * FROM WINTABLE WHERE LOT=#LOT#</CFQUERY>
<CFIF CHECKTABLE.RECORDCOUNT IS 0>
<CFQUERY NAME="PUTRECORD" DATASOURCE="DJS">INSERT INTO WINTABLE(LOT, OPEN, DES, SCN, CON, SCV, COUNTRY, BIDNUMBER, MEMBERID, HIGHBID, EMAIL, CONSIGN)
VALUES(#LOT#, #OPEN#, '#DES#', '#SCN#', '#CON#', #SCV#, '#COUNTRY#', #HIGHBIDDER.BID#, '#HIGHBIDDER.MEMBERID#', #HIGHBIDDER.AMOUNT#, '#HBI.EMAIL#', '#CONSIGN#')</CFQUERY>
<CFELSE>
<CFQUERY NAME="UPRECORD" DATASOURCE="DJS">UPDATE WINTABLE SET BIDNUMBER=#HIGHBIDDER.BID#, MEMBERID='#HIGHBIDDER.MEMBERID#', HIGHBID=#HIGHBIDDER.AMOUNT#, EMAIL='#HBI.EMAIL#' WHERE LOT=#LOT#</CFQUERY>
</CFIF>
<CFELSE>
<CFQUERY NAME="CHECKTABLE" DATASOURCE="DJS" MAXROWS="1">SELECT * FROM WINTABLE WHERE LOT=#LOT#</CFQUERY>
<CFSET BID=0>
<CFSET AMOUNT=0>
<CFSET MEMBERID="NOBID">
<CFSET EMAIL="danny@djsstamps.com">
<CFIF CHECKTABLE.RECORDCOUNT IS 0>
<CFQUERY NAME="PUTRECORD" DATASOURCE="DJS">INSERT INTO WINTABLE(LOT, OPEN, DES, SCN, CON, SCV, COUNTRY, BIDNUMBER, MEMBERID, HIGHBID, EMAIL, CONSIGN)
VALUES(#LOT#, #OPEN#, '#DES#', '#SCN#', '#CON#', #SCV#, '#COUNTRY#', #BID#, '#MEMBERID#', #AMOUNT#, '#EMAIL#', '#CONSIGN#')</CFQUERY>
<CFELSE>
<CFQUERY NAME="UPRECORD" DATASOURCE="DJS">UPDATE WINTABLE SET BIDNUMBER=#BID#, MEMBERID='#MEMBERID#', HIGHBID=#AMOUNT#, EMAIL='#EMAIL#' WHERE LOT=#LOT#</CFQUERY>
</CFIF>
</CFIF>
</CFLOOP>
<CFQUERY NAME="GETMEMBERS" DATASOURCE="DJS">SELECT * FROM MEMBERS ORDER BY MEMBERID ASC</CFQUERY>
<CFLOOP QUERY="GETMEMBERS">
<CFQUERY NAME="GETLOTS" DATASOURCE="DJS">SELECT * FROM WINTABLE WHERE MEMBERID='#getmembers.MEMBERID#' ORDER BY LOT ASC</CFQUERY>
<CFQUERY NAME="MINFO" DATASOURCE="DJS">SELECT * FROM MEMBERS WHERE MEMBERID='#MEMBERID#'</CFQUERY>
<CFIF MINFO.RECORDCOUNT EQ 0>
Member <cfoutput>#MEMBERID#</cfoutput> is not found<br>
<CFELSEIF LEN(MINFO.EMAIL) EQ 0>
Member <cfoutput>#MEMBERID#</cfoutput> has no email address. Cannot send email.<br>
<CFELSEIF GETLOTS.RECORDCOUNT IS NOT 0>
<cfset return="">
<cfset return=return & Chr(10)>
<CFMAIL query="getlots" TO="#MINFO.EMAIL#" FROM="danny@djsstamps.com" CC="djsstamps@gmail.com" SUBJECT="Invoice - DJS Stamps Auction ## #AUCTIONINFO.ANUMBER#" SERVER="localhost">
DJS Stamps AUCTION ## #AUCTIONINFO.ANUMBER#
POB 21516
Baltimore, MD 21282-1516
Phone Number (410) 804-8718
FAX (413) 643-4765
CONGRATULATIONS!, you are the successful bidder in
DJS Stamps Auction ## #AUCTIONINFO.ANUMBER#, #Dateformat(AUCTIONINFO.ENDDATE, 'MMMM DD, YYYY')#,
of the following auction LOTS.
We accept Visa, MasterCard, and American Express as well as checks (made out to Danny Weissmann)
in payment of these LOTS. You may email, or call (410) 804-8718
with your card ## and expiration date. You may also use the Paypal
option found on the auction homepage for a secure, online
credit card payment.
If you have a credit card on file, just email your OK to
put the balance on that card.
Please enclose a copy of this invoice with payment.
<CFSET X=0>
<CFSET HP=0>
<CFSET TAX=0>
#MINFO.NAME# (#MINFO.EMAIL#) AUCTION ID - #MINFO.MEMBERID# <CFIF MINFO.DEALER IS 'X'>MINNESOTA STAMP DEALER</CFIF>
#MINFO.ADDRESS#
#MINFO.CITY#, #MINFO.REGION# #MINFO.POSTCODE#
LOT ## - SCOTT - DESCRIPTION - PRICE #return#
________________________________________________________________________________________________
<CFOUTPUT>
#LOT# - #scn# - #DES# - #CON# - #NumberFormat(HIGHBID, '_$_,___,___.__')# #return#
<CFSET X=X+#HIGHBID#></CFOUTPUT>
_____________________
<CFSET Y=X><CFIF Y LT 75><CFSET POST=100/100><CFSET INS=0><CFELSEIF Y GTE 75 and Y LTE 200><CFSET POST=100/100><CFSET INS=2.10><CFELSE><CFSET POST=100/100></CFIF><CFIF MINFO.REGION IS 'QQ' OR MINFO.REGION IS 'Qaryland'><CFSET TAX=Y*0.06><CFELSE><CFSET TAX=0></CFIF>
<CFIF Y GT 200 AND Y LTE 400><CFSET INS=3.35>
<CFELSEIF Y GT 400><CFSET INCR=Int(Y/100 - 3)><CFSET INS=3.35+(INCR*1.25)></CFIF>
<CFSET Z=X>
<CFIF Z LT 100><CFSET Z=X-(X/20)><CFELSEIF Z GTE 100 and Z LT 500><CFSET Z=X-(X/10)><CFELSEIF Z GTE 500><CFSET Z=X-(X/10)-(x/20)></CFIF>
<CFSET TOTAL=Z+INS+POST+TAX>
SUBTOTAL = #NumberFormat(X, '_$_,___,___.__')# #return#
<CFIF X LT 100>SUBTOTAL (Less 10%) = #NumberFormat(Z, '_$_,___,___.__')#</CFIF> #return#
<CFIF X GTE 100 and X LT 500>SUBTOTAL (Less 15%) = #NumberFormat(Z, '_$_,___,___.__')#</CFIF> #return#
<CFIF X GTE 500>SUBTOTAL (Less 20%) = #NumberFormat(Z, '_$_,___,___.__')#</CFIF> #return#
POSTAGE = #NumberFormat(POST, '_$_,___,___.__')# #return#
INSURANCE = #NumberFormat(INS, '_$_,___,___.__')# (insured privately through APS) #return#
<CFIF #TAX# IS NOT 0>SALES TAX = #NumberFormat(TAX, '_$_,___,___.__')#</CFIF> #return#
_____________________
YOUR TOTAL = #NumberFormat(TOTAL, '_$_,___,___.__')# #return#
Thanks,
Danny
PLEASE - Come Visit us at our web page
http://www.djsstamps.com
DJ'S Stamps
POB 21516
Baltimore, MD 21282-1516
Phone Number (410) 804-8718
FAX (413) 643-4765
</CFMAIL>
<CFELSE>
<CFMAIL TO="#MINFO.EMAIL#" FROM="danny@djsstamps.com" SUBJECT="Final Results- DJ'S StampS Auction ## #AUCTIONINFO.ANUMBER#" SERVER="localhost">
DJS Stamps AUCTION ## #AUCTIONINFO.ANUMBER#
POB 21516
Baltimore, MD 21282-1516
Phone Number (410) 804-8718
FAX (413) 643-4765
#MINFO.NAME# (#MINFO.EMAIL#) AUCTION ID - #MINFO.MEMBERID#
#MINFO.ADDRESS#
#MINFO.CITY#, #MINFO.REGION# #MINFO.POSTCODE#
Thank you for having shown interest in DJ's Stamps Auction ## #AUCTIONINFO.ANUMBER#,
which just closed on #Dateformat(AUCTIONINFO.ENDDATE, 'MMMM DD, YYYY')#.
If you bid in this auction but did not win, we hope that you will join us
for the next auction. We thank you for your business.
Thanks,
Danny
PLEASE - Come Visit us at our web page
http://www.djsstamps.com
DJ'S Stamps
POB 21516
Baltimore, MD 21282-1516
Phone Number (410) 804-8718
FAX (413) 643-4765
IF YOU WOULD LIKE TO BE REMOVED FROM OUR AUCTIONS, PLEASE E-MAIL US AT:
danny@djsstamps.com
</CFMAIL>
</CFIF>
</CFLOOP>
<center><h2>ALL INVOICES HAVE BEEN SENT!</h2></center><BR>
<CFOUTPUT><p align="center"><a href="admin.cfm?ID=#ID#&PASS=#PASS#"><big>Return to Main Admin Page</big></a></p>
</CFOUTPUT>
<CFELSE>
<CENTER><font color="##FF0000" SIZE="5">CAN'T PERFORM THIS FUNCTION YET - AUCTION STILL OPEN</FONT></CENTER><BR>
<CFOUTPUT><p align="center"><a href="admin.cfm?ID=#ID#&PASS=#PASS#"><big>Return to Main Admin Page</big></a></p>
</CFOUTPUT>
</CFIF>
</body>
</html>
ASKER
First of all - thank you for the help.
The lots table is new for each auction.
I went into the members list, and changed all the emails to my email address. There are no emails that are blank.
When I ran your script, I got the following:
Member NOBID is not found
Member NOBID is not found
Member NOBID is not found
Member NOBID is not found
Member NOBID is not found
Member NOBID is not found
Member NOBID is not found
Member NOBID is not found
Member NOBID is not found
Member NOBID is not found
Member NOBID is not found
Member NOBID is not found
Member NOBID is not found
Member NOBID is not found
Member NOBID is not found
Member NOBID is not found
Member NOBID is not found
Member NOBID is not found
Member NOBID is not found
Member NOBID is not found
Member NOBID is not found
Member NOBID is not found
Member NOBID is not found
Member NOBID is not found
Member NOBID is not found
Member NOBID is not found
I did get 1 regular invoice (instead of 20 or so).
I then added a member called NOBID.
Good news and bad news.
Good news - I got invoices.
Bad news - each invoice pulled in my NOBID member as the name instead of the correct bidders name.
So...
1. How do i get the corrected member name from the members table - column id = name.
2. Instead of adding the member name NOBID, is there a way to bypass this? NOBID show up when the wintable does not have a bidder, It uses NOBID as the member id.
Thanks so much !
The lots table is new for each auction.
I went into the members list, and changed all the emails to my email address. There are no emails that are blank.
When I ran your script, I got the following:
Member NOBID is not found
Member NOBID is not found
Member NOBID is not found
Member NOBID is not found
Member NOBID is not found
Member NOBID is not found
Member NOBID is not found
Member NOBID is not found
Member NOBID is not found
Member NOBID is not found
Member NOBID is not found
Member NOBID is not found
Member NOBID is not found
Member NOBID is not found
Member NOBID is not found
Member NOBID is not found
Member NOBID is not found
Member NOBID is not found
Member NOBID is not found
Member NOBID is not found
Member NOBID is not found
Member NOBID is not found
Member NOBID is not found
Member NOBID is not found
Member NOBID is not found
Member NOBID is not found
I did get 1 regular invoice (instead of 20 or so).
I then added a member called NOBID.
Good news and bad news.
Good news - I got invoices.
Bad news - each invoice pulled in my NOBID member as the name instead of the correct bidders name.
So...
1. How do i get the corrected member name from the members table - column id = name.
2. Instead of adding the member name NOBID, is there a way to bypass this? NOBID show up when the wintable does not have a bidder, It uses NOBID as the member id.
Thanks so much !
Here's one thing to try...
The second query does not indicate the scope on the memberID.
Notice the difference in the variable between the first and second cfquery statement.
So let's tell Coldfusion to use the memberID from the getMembers table...and see if that helps
The second query does not indicate the scope on the memberID.
Notice the difference in the variable between the first and second cfquery statement.
<CFQUERY NAME="GETLOTS" DATASOURCE="DJS">SELECT * FROM WINTABLE WHERE MEMBERID='#getmembers.MEMBERID#' ORDER BY LOT ASC</CFQUERY>
<CFQUERY NAME="MINFO" DATASOURCE="DJS">SELECT * FROM MEMBERS WHERE MEMBERID='#MEMBERID#'</CFQUERY>
So let's tell Coldfusion to use the memberID from the getMembers table...and see if that helps
<CFQUERY NAME="MINFO" DATASOURCE="DJS">SELECT * FROM MEMBERS WHERE MEMBERID='#getMembers.MEMBERID#'</CFQUERY>
ASKER
I did get invoices - Yeah.
1 issue. I got a invoice for NOBId. How can I suppress this invoice from being created since it will be pages long, and is not neeeded?
Thanks again for all the help.
1 issue. I got a invoice for NOBId. How can I suppress this invoice from being created since it will be pages long, and is not neeeded?
Thanks again for all the help.
ASKER
Also,
How do I get rid of the double spacing in the invoice? Lots of extra lines
Please enclose a copy of this invoice with payment.
EXTRA LINE HERE !!!
TEST (danny@djsstamps.com) AUCTION ID - bfoot56
POB 12345
Woodland Park, Colorado 98765
LOT # - SCOTT - DESCRIPTION - PRICE
EXTRA LINE HERE !!!
__________________________ __________ __________ __________ __________ __________ __________ __________
EXTRA LINE HERE !!!
144 - 343 - Very Fine - Mint - Never Hinged - $5.00
EXTRA LINE HERE !!!
213 - 424 - Very Fine - Mint - Never Hinged - $4.00
EXTRA LINE HERE !!!
EXTRA LINE HERE !!!
263 - 483 - Very Fine - Mint - Never Hinged - $12.00
267 - 488 - Fine/Very Fine - Mint - Never Hinged - $2.00
328 - 528 - Fine/Very Fine - Mint - Never Hinged - $9.00
338 - 534 - Fine/Very Fine (natural gum bend) - Mint - Never Hinged - $10.00
366 - 555 - Fine/Very Fine - Mint - Never Hinged - $13.00
377 - 565 - Fine/Very Fine - Mint - Never Hinged - $3.50
383 - 570 - Almost Very Fine - Mint - Never Hinged - $30.00
403 - 587 - Fine/Very Fine (gum skips) - Mint - Never Hinged - $6.00
418 - 601 - Fine/Very Fine - Mint - Never Hinged - $3.00
431 - 604 - Fine/Very Fine - Mint - Never Hinged - $0.25
847 - 3168-72 - Complete Sheet - Very Fine - Mint - Never Hinged - $10.00
1529 - C17 - Fine/Very Fine - Mint - Never Hinged - $1.50
_____________________
SUBTOTAL = $109.25
SUBTOTAL (Less 15%) = $98.33
POSTAGE = $1.00
INSURANCE = $2.10 (insured privately through APS)
_____________________
YOUR TOTAL = $101.43
Thanks,
Danny
PLEASE - Come Visit us at our web page
http://www.djsstamps.com
DJ'S Stamps
POB 21516
Baltimore, MD 21282-1516
Phone Number (410) 804-8718
FAX (413) 643-4765
How do I get rid of the double spacing in the invoice? Lots of extra lines
Please enclose a copy of this invoice with payment.
EXTRA LINE HERE !!!
TEST (danny@djsstamps.com) AUCTION ID - bfoot56
POB 12345
Woodland Park, Colorado 98765
LOT # - SCOTT - DESCRIPTION - PRICE
EXTRA LINE HERE !!!
__________________________
EXTRA LINE HERE !!!
144 - 343 - Very Fine - Mint - Never Hinged - $5.00
EXTRA LINE HERE !!!
213 - 424 - Very Fine - Mint - Never Hinged - $4.00
EXTRA LINE HERE !!!
EXTRA LINE HERE !!!
263 - 483 - Very Fine - Mint - Never Hinged - $12.00
267 - 488 - Fine/Very Fine - Mint - Never Hinged - $2.00
328 - 528 - Fine/Very Fine - Mint - Never Hinged - $9.00
338 - 534 - Fine/Very Fine (natural gum bend) - Mint - Never Hinged - $10.00
366 - 555 - Fine/Very Fine - Mint - Never Hinged - $13.00
377 - 565 - Fine/Very Fine - Mint - Never Hinged - $3.50
383 - 570 - Almost Very Fine - Mint - Never Hinged - $30.00
403 - 587 - Fine/Very Fine (gum skips) - Mint - Never Hinged - $6.00
418 - 601 - Fine/Very Fine - Mint - Never Hinged - $3.00
431 - 604 - Fine/Very Fine - Mint - Never Hinged - $0.25
847 - 3168-72 - Complete Sheet - Very Fine - Mint - Never Hinged - $10.00
1529 - C17 - Fine/Very Fine - Mint - Never Hinged - $1.50
_____________________
SUBTOTAL = $109.25
SUBTOTAL (Less 15%) = $98.33
POSTAGE = $1.00
INSURANCE = $2.10 (insured privately through APS)
_____________________
YOUR TOTAL = $101.43
Thanks,
Danny
PLEASE - Come Visit us at our web page
http://www.djsstamps.com
DJ'S Stamps
POB 21516
Baltimore, MD 21282-1516
Phone Number (410) 804-8718
FAX (413) 643-4765
I got a invoice for NOBId. How can I suppress this invoice from being created since it will be pages long, and is not neeeded?
Add a where clause to your select from members query to not select that user
where memberId != 'NOBID' .. or whatever the syntax is for that
Regarding spacing. Try to remove any extra space, you may want to push the code inside the CFMAIL up to the left margin of your page.
Add a where clause to your select from members query to not select that user
where memberId != 'NOBID' .. or whatever the syntax is for that
Regarding spacing. Try to remove any extra space, you may want to push the code inside the CFMAIL up to the left margin of your page.
ASKER
Ive tried it a few ways, and keep getting syntax errors. Ideas?
<CFQUERY NAME="GETMEMBERS" DATASOURCE="DJS">SELECT * FROM MEMBERS where [MEMBERID] !="NOBID" ORDER BY MEMBERID ASC</CFQUERY>
<CFQUERY NAME="GETMEMBERS" DATASOURCE="DJS">SELECT * FROM MEMBERS WHERE MEMBERID!="NOBID" ORDER BY MEMBERID ASC</CFQUERY>
Syntax error (missing operator) in query expression (for the second)
<CFQUERY NAME="GETMEMBERS" DATASOURCE="DJS">SELECT * FROM MEMBERS where [MEMBERID] !="NOBID" ORDER BY MEMBERID ASC</CFQUERY>
<CFQUERY NAME="GETMEMBERS" DATASOURCE="DJS">SELECT * FROM MEMBERS WHERE MEMBERID!="NOBID" ORDER BY MEMBERID ASC</CFQUERY>
Syntax error (missing operator) in query expression (for the second)
(missing operator) .. the operator is the = + / symbols. It depends on your database, perhaps not equal in your database is <> instead of !=
You can google your database to find out
You can google your database to find out
ASKER
Luckily, im getting a different error with this query:
<CFQUERY NAME="GETMEMBERS" DATASOURCE="DJS">SELECT * FROM MEMBERS WHERE MEMBERID<>"NOBID" ORDER BY MEMBERID ASC</CFQUERY>
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
I tried putting brackets [memberid]<>nobid. ... same
so, close.....
<CFQUERY NAME="GETMEMBERS" DATASOURCE="DJS">SELECT * FROM MEMBERS WHERE MEMBERID<>"NOBID" ORDER BY MEMBERID ASC</CFQUERY>
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
I tried putting brackets [memberid]<>nobid. ... same
so, close.....
looking at your other queries, it seems your database may take single quotes not double quotes? ' ?
ASKER
This worked.
Going back to the blank lines, I deleted all blank space, and even copied this over from another script. Is it possible that the new loop is adding a clank line after (or before) each lot?
Thanks
Danny
I have attached the latest script.
si-with-fixees.txt
Going back to the blank lines, I deleted all blank space, and even copied this over from another script. Is it possible that the new loop is adding a clank line after (or before) each lot?
Thanks
Danny
I have attached the latest script.
si-with-fixees.txt
You can remove line breaks at the end of lines that can be simple paragraphs.
For example this:
You should remove the line breaks at the end of each line to make this:
Unless you want a paragraph break, allow the email app to wrap the text.
For example this:
If you have a credit card on file, just email your OK to
put the balance on that card.
Please enclose a copy of this invoice with payment.
You should remove the line breaks at the end of each line to make this:
If you have a credit card on file, just email your OK to put the balance on that card. Please enclose a copy of this invoice with payment.
Unless you want a paragraph break, allow the email app to wrap the text.
ASKER
The issue wasnt in the paragraph, its in the part of the invoice where the lots are printed. That is new after all the changes. At the bottom I have included an old invoice:
New invoice:
DJS Stamps AUCTION # 490
POB 21516
Baltimore, MD 21282-1516
Phone Number (410) 804-8718
FAX (413) 643-4765
CONGRATULATIONS!, you are the successful bidder in
DJS Stamps Auction # 490, September 05, 2017,
of the following auction LOTS.
We accept Visa, MasterCard, and American Express as well as checks (Made to Danny Weissmann) in payment of these LOTS. You may email, or call (410) 804-8718
with your card # and expiration date. You may also use the Paypal option found on the auction homepage for a secure, online credit card payment.
If you have a credit card on file, just email your OK to put the balance on that card. Please enclose a copy of this invoice with payment.
EST (danny@djsstamps.com) AUCTION ID - 5506
TEST
Kansas City, Missouri 64151
LOT # - SCOTT - DESCRIPTION - PRICE
__________________________ __________ __________ __________ __________ __________ __________ __________
157 - 368 - EXTRA FINE - Mint - With a hinge remnant - $7.00
187 - 401 - Fine/Very Fine (spot) - Mint - Lightly Hinged - $8.00
273 - 493 - Fine/Very Fine - Mint - Lightly Hinged - $5.00
342 - 536 - Fine/Very Fine - Mint - With a hinge remnant - $8.00
_____________________
SUBTOTAL = $28.00
POSTAGE = $1.00
INSURANCE = $0.00 (insured privately through APS)
_____________________
YOUR TOTAL = $29.00
Thanks,
Danny
PLEASE - Come Visit us at our web page
http://www.djsstamps.com
DJ'S Stamps
POB 21516
Baltimore, MD 21282-1516
Phone Number (410) 804-8718
FAX (413) 643-4765
old invoice:
DJS Stamps AUCTION # 481
POB 21516
Baltimore, MD 21282-1516
Phone Number (410) 804-8718
FAX (413) 643-4765
CONGRATULATIONS!, you are the successful bidder in
DJS Stamps Auction # 481, May 15, 2017,
of the following auction LOTS.
We accept Visa, MasterCard, and American Express as well as checks (made out to Danny Weissmann)
in payment of these LOTS. You may email, or call (410) 804-8718
with your card # and expiration date. You may also use the Paypal
option found on the auction homepage for a secure, online
credit card payment.
If you have a credit card on file, just email your OK to
put the balance on that card.
Please enclose a copy of this invoice with payment.
TEST (danny@djsstamps.com) AUCTION ID - 120 1234 Main Street Chelsea, Michigan 48118
LOT # - SCOTT - DESCRIPTION - PRICE
__________________________ __________ __________ __________ __________ __________ __________ __________
16 - 236 - Very Fine - Mint- Never Hinged - $100.00
202 - 464 - Almost EXTRA FINE - Mint - Never Hinged - $100.00
204 - 466 - Fine/Very Fine - Mint - Never Hinged - $100.00
390 - 599a - Almost Very Fine - With a PF Certificate - Mint - Never Hinged - $140.00
966 - C1 - Fine/Very Fine - Mint - Never Hinged - $64.00
_____________________
SUBTOTAL = $504.00
SUBTOTAL (Less 15%) = $428.40
POSTAGE = $1.00
INSURANCE = $6.00 (insured privately through APS)
_____________________
YOUR TOTAL = $435.40
Thanks!
New invoice:
DJS Stamps AUCTION # 490
POB 21516
Baltimore, MD 21282-1516
Phone Number (410) 804-8718
FAX (413) 643-4765
CONGRATULATIONS!, you are the successful bidder in
DJS Stamps Auction # 490, September 05, 2017,
of the following auction LOTS.
We accept Visa, MasterCard, and American Express as well as checks (Made to Danny Weissmann) in payment of these LOTS. You may email, or call (410) 804-8718
with your card # and expiration date. You may also use the Paypal option found on the auction homepage for a secure, online credit card payment.
If you have a credit card on file, just email your OK to put the balance on that card. Please enclose a copy of this invoice with payment.
EST (danny@djsstamps.com) AUCTION ID - 5506
TEST
Kansas City, Missouri 64151
LOT # - SCOTT - DESCRIPTION - PRICE
__________________________
157 - 368 - EXTRA FINE - Mint - With a hinge remnant - $7.00
187 - 401 - Fine/Very Fine (spot) - Mint - Lightly Hinged - $8.00
273 - 493 - Fine/Very Fine - Mint - Lightly Hinged - $5.00
342 - 536 - Fine/Very Fine - Mint - With a hinge remnant - $8.00
_____________________
SUBTOTAL = $28.00
POSTAGE = $1.00
INSURANCE = $0.00 (insured privately through APS)
_____________________
YOUR TOTAL = $29.00
Thanks,
Danny
PLEASE - Come Visit us at our web page
http://www.djsstamps.com
DJ'S Stamps
POB 21516
Baltimore, MD 21282-1516
Phone Number (410) 804-8718
FAX (413) 643-4765
old invoice:
DJS Stamps AUCTION # 481
POB 21516
Baltimore, MD 21282-1516
Phone Number (410) 804-8718
FAX (413) 643-4765
CONGRATULATIONS!, you are the successful bidder in
DJS Stamps Auction # 481, May 15, 2017,
of the following auction LOTS.
We accept Visa, MasterCard, and American Express as well as checks (made out to Danny Weissmann)
in payment of these LOTS. You may email, or call (410) 804-8718
with your card # and expiration date. You may also use the Paypal
option found on the auction homepage for a secure, online
credit card payment.
If you have a credit card on file, just email your OK to
put the balance on that card.
Please enclose a copy of this invoice with payment.
TEST (danny@djsstamps.com) AUCTION ID - 120 1234 Main Street Chelsea, Michigan 48118
LOT # - SCOTT - DESCRIPTION - PRICE
__________________________
16 - 236 - Very Fine - Mint- Never Hinged - $100.00
202 - 464 - Almost EXTRA FINE - Mint - Never Hinged - $100.00
204 - 466 - Fine/Very Fine - Mint - Never Hinged - $100.00
390 - 599a - Almost Very Fine - With a PF Certificate - Mint - Never Hinged - $140.00
966 - C1 - Fine/Very Fine - Mint - Never Hinged - $64.00
_____________________
SUBTOTAL = $504.00
SUBTOTAL (Less 15%) = $428.40
POSTAGE = $1.00
INSURANCE = $6.00 (insured privately through APS)
_____________________
YOUR TOTAL = $435.40
Thanks!
I would still recommend removing line breaks from the ends of those lines. Different email apps on difference size screens will wrap the text oddly.
I believe the problem with the list is the CFOUTPUT statements. CFMAIL automatically assumes CFOUTPUT within the CFMAIL tag.
To avoid using CFOUPUT there, you should switch it to CFLOOP. The CFOUTPUT is also missing the query attributes, you need to put that in, for whichever query name you are using
Change to
replace xxx with the name of the query that handles the included variables
I believe the problem with the list is the CFOUTPUT statements. CFMAIL automatically assumes CFOUTPUT within the CFMAIL tag.
To avoid using CFOUPUT there, you should switch it to CFLOOP. The CFOUTPUT is also missing the query attributes, you need to put that in, for whichever query name you are using
<CFOUTPUT>
#LOT# - #scn# - #DES# - #CON# - #NumberFormat(HIGHBID, '_$_,___,___.__')# #return#
<CFSET X=X+#HIGHBID#>
</CFOUTPUT>
Change to
<CFLOOP query="xxx">
#LOT# - #scn# - #DES# - #CON# - #NumberFormat(HIGHBID, '_$_,___,___.__')# #return#
<CFSET X=X+#HIGHBID#>
</CFLOOP>
replace xxx with the name of the query that handles the included variables
ASKER
Changed it to:
<CFLOOP query="getlots">
#LOT# - #scn# - #DES# - #CON# - #NumberFormat(HIGHBID, '_$_,___,___.__')# #return#
<CFSET X=X+#HIGHBID#>
</CFLOOP>
Still getting a blank line between each lot.
<CFLOOP query="getlots">
#LOT# - #scn# - #DES# - #CON# - #NumberFormat(HIGHBID, '_$_,___,___.__')# #return#
<CFSET X=X+#HIGHBID#>
</CFLOOP>
Still getting a blank line between each lot.
Apparently, the variable "RETURN" is set to 'new line'
<cfset return=return & Chr(10)>
chr(10) is the ascii for new line.
You can remove #return# from the end of those lines or perhaps just make RETURN be nothing...
<cfset return= "">
A little trial and error should do it..
<cfset return=return & Chr(10)>
chr(10) is the ascii for new line.
You can remove #return# from the end of those lines or perhaps just make RETURN be nothing...
<cfset return= "">
A little trial and error should do it..
ASKER
Hi
I removed the cfloop as it was printing multiple invoices for each.
I removed the #return# from the end of the lines.
I also deleted any blank spaces at the end of each line.
Although the invoices dont look as good as it used to, the fact that they are all printing is huge for me.
If you have any other ideas to get rid of the blank lines that would be great. If not, I will live with the invoices as is.
Your help has been invaluable.
I removed the cfloop as it was printing multiple invoices for each.
I removed the #return# from the end of the lines.
I also deleted any blank spaces at the end of each line.
Although the invoices dont look as good as it used to, the fact that they are all printing is huge for me.
If you have any other ideas to get rid of the blank lines that would be great. If not, I will live with the invoices as is.
Your help has been invaluable.
You could switch to HTML email instead of text. If you do that, you will have to put <br> everywhere you want a line break, or you can use <p></p> markers or any other HTML you want including a table for your list.
Just add the type to the cfmail statement.
<cfmail ...... type="html">
Of course your recipients have to be able to accept HTML emails, almost all can.
Just add the type to the cfmail statement.
<cfmail ...... type="html">
Of course your recipients have to be able to accept HTML emails, almost all can.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Your TO address is assigned to this variables; #MINFO.EMAIL#
That variable comes from the query MINFO and the COlumn EMAIL.
So either the memberID is not found or the member does not have an email address.
You need to test for the record to be found or the email address to be populated (not empty)
<CFQUERY NAME="MINFO" DATASOURCE="DJS">SELECT * FROM MEMBERS WHERE MEMBERID='#MEMBERID#'</CFQ