Link to home
Start Free TrialLog in
Avatar of daniel weissmann
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.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 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>



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\admin\si.cfm: line 78
Called from E:\www\djsauctions.com\admin\si.cfm: line 72
Called from E:\www\djsauctions.com\admin\si.cfm: line 66
Called from E:\www\djsauctions.com\admin\si.cfm: line 15
Called from E:\www\djsauctions.com\admin\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 !
Avatar of gdemaria
gdemaria
Flag of United States of America image

The error says that the TO address of CFMAIL is empty.

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#'</CFQUERY>
Avatar of daniel weissmann
daniel weissmann

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 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

<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 ---->

Open in new window

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
Let me revise that.  I did not get any invoices.
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#</cfoutput> 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...

<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>

Open in new window

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 !
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.

<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>

Open in new window


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>

Open in new window

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.
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
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.
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)
(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
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.....
looking at your other queries, it seems your database may take single quotes not double quotes?  ' ?
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
You can remove line breaks at the end of lines that can be simple paragraphs.

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. 

Open in new window


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. 

Open in new window


Unless you want a paragraph break, allow the email app to wrap the text.
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!
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

<CFOUTPUT>
 #LOT# - #scn# - #DES# - #CON# -  #NumberFormat(HIGHBID, '_$_,___,___.__')#  #return# 
 <CFSET X=X+#HIGHBID#>
</CFOUTPUT>            

Open in new window


Change to

<CFLOOP query="xxx">
 #LOT# - #scn# - #DES# - #CON# -  #NumberFormat(HIGHBID, '_$_,___,___.__')#  #return# 
 <CFSET X=X+#HIGHBID#>
</CFLOOP>            

Open in new window


replace xxx with the name of the query that handles the included variables
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.
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..
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.
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.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members 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.