CFMAIL 1

[mikeadmin]

Having trouble executing a auto email to owner of voided lot. Lot's get voided but when running email function I get the following error.

SET NOCOUNT ON SELECT DISTINCT EMailAddress FROM Lot, Item, Person WHERE Lot.PersonID = Person.PersonID AND Lot.LotID = Item.LotID AND ItemID IN (#ValueList(EMailAddresses.EMailAddress)#) UNION
Error Occurred While Processing Request
Error Diagnostic Information
Invalid CFQUERY syntax

The error is most likely caused by one of two things:

In your template you have mismatched <CFQUERY> and </CFQUERY> tags, or,
You have omitted the SQL= attribute of a simple <CFQUERY> tag, i.e., one that has no </CFQUERY> tag associated with it.


HERE IS THE CODE FOR THE PAGE:

<!--- get the unvoided items in the lot --->
<CFQUERY NAME=&quot;Items&quot; DATASOURCE=&quot;PDC&quot;>
SELECT ItemID FROM Item WHERE LotID = #LotID#
AND NOT EXISTS (SELECT ItemID FROM ItemVoid WHERE ItemID = Item.ItemID)
</CFQUERY>

<CFTRANSACTION>

<!--- void the items in the lot --->
<CFLOOP QUERY=&quot;Items&quot;>
<CFQUERY DATASOURCE=&quot;PDC&quot;>
SET NOCOUNT ON
INSERT INTO ItemVoid (ItemID, Reason, PersonID, Date) VALUES (#ItemID#, '#Reason#', #Cookie.CurrentPersonID#, GetDate())
</CFQUERY>
</CFLOOP>

<!--- void the lot --->
<CFQUERY DATASOURCE=&quot;PDC&quot;>
SET NOCOUNT ON
INSERT INTO Void (LotID, Reason, PersonID, Date) VALUES (#LotID#, '#Reason#', #Cookie.CurrentPersonID#, GetDate())
</CFQUERY>

</CFTRANSACTION>

<!--- determine interested people --->
<CFQUERY NAME=&quot;EMailAddresses&quot; DATASOURCE=&quot;PDC&quot;>
SET NOCOUNT ON
<!--- created the lot --->
SELECT DISTINCT EMailAddress FROM Lot, Item, Person
WHERE Lot.PersonID = Person.PersonID AND Lot.LotID = Item.LotID
AND ItemID IN (#ValueList(EMailAddresses.EMailAddress)#)
UNION
<!--- send the email --->
<CFMAIL SUBJECT=&quot;Voided Lot #LotID#&quot;
TO=&quot;#ValueList(EMailAddresses.EMailAddress)#&quot;
FROM=&quot;#PrimaryAdminEMailAddress#&quot;>
Lot #LotID# was voided on #DateFormat(LotID.Date, &quot;ddd, mmmm dd, yyyy&quot#

</CFMAIL>

 

[CFDude]

This section is not complete --

<CFQUERY NAME=&quot;EMailAddresses&quot; DATASOURCE=&quot;PDC&quot;>
SET NOCOUNT ON
<!--- created the lot --->
SELECT DISTINCT EMailAddress FROM Lot, Item, Person
WHERE Lot.PersonID = Person.PersonID AND Lot.LotID = Item.LotID
AND ItemID IN (#ValueList(EMailAddresses.EMailAddress)#)
UNION

????? You don't have your other half of the union here and no closing </cfquery> tag

<!--- send the email --->
<CFMAIL SUBJECT=&quot;Voided Lot #LotID#&quot;
TO=&quot;#ValueList(EMailAddresses.EMailAddress)#&quot;
FROM=&quot;#PrimaryAdminEMailAddress#&quot;>
Lot #LotID# was voided on #DateFormat(LotID.Date, &quot;ddd, mmmm dd, yyyy&quot#

</CFMAIL>

 

[mikeadmin]

Opps sorry here is the ending :

<CFQUERY NAME=&quot;EMailAddresses&quot; DATASOURCE=&quot;PDC&quot;>
SET NOCOUNT ON
SELECT DISTINCT EMailAddress FROM Lot, Item, Person
WHERE Lot.PersonID = Person.PersonID AND Lot.LotID = Item.LotID
AND ItemID IN (#ValueList(EMailAddresses.EMailAddress)#)
UNION
(SELECT EMailAddress FROM Person
WHERE EMailAddress LIKE '#PrimaryAdminEMailAddress#')
</CFQUERY>

 

[CFDude]

Mike,
Sorry, I am fairly new to this forum and see that you responded, but I didn't see it. Are you still having difficulty with this? Try removing the parenthesis that is around the second part of the union and see if that helps.

Sorry for the delay,
Tim P.

 

[mikeadmin]

Hi Tim,
Thanks for getting back to me. Removing the parantheses didnt work.

Mike

 

[CFDude]

Oh -- it just hit me -- in your query you have this line:

AND ItemID IN (#ValueList(EMailAddresses.EMailAddress)#)

But it is the same query (EmailAddresses) -- you can't reference the list of emailaddresses inside the same query -- you can do a couple of things -- either create a subquery or create a separate query above this and reference that.

e.g

SELECT DISTINCT EMailAddress FROM Lot, Item, Person
WHERE Lot.PersonID = Person.PersonID AND Lot.LotID = Item.LotID
AND ItemID IN (Select EmailAddress from Lot)

or

<cfquery name=&quot;qry1&quot; datasource=&quot;PDC&quot;>
Select EmailAddress from Lot
</cfquery>

<CFQUERY NAME=&quot;EMailAddresses&quot; DATASOURCE=&quot;PDC&quot;>
SET NOCOUNT ON
<!--- created the lot --->
SELECT DISTINCT EMailAddress FROM Lot, Item, Person
WHERE Lot.PersonID = Person.PersonID AND Lot.LotID = Item.LotID
AND ItemID IN (#ValueList(qry1.EMailAddress)#)

etc...

You may need to put a criteria on the subquery on what email addresses to pull.

HTH,
Tim P.

 

[mikeadmin]

Hi Tim,
I think I'm getting closer but its still not working. It appears to be sending to all email addresses so your right I do need criteria. How do I put a criteria to send a email to just the person the created the lot. And where does it go? Thanks, very new to this so thanks for your help.
BTW, I used your second suggestion and broke it into two parts.
Mike

 

[CFDude]

I'm not 100% sure how the tables are set up, but it looks like that originally you had this:

SELECT DISTINCT EMailAddress FROM Lot, Item, Person
WHERE Lot.PersonID = Person.PersonID AND Lot.LotID = Item.LotID

Can you just pass the lotID you are looking for? So it would be:

SELECT DISTINCT EMailAddress FROM Lot, Item, Person
WHERE Lot.PersonID = Person.PersonID AND Lot.LotID = Item.LotID AND Lot.LotID = #LotID#

 

[mikeadmin]

Hi Tim,
Thanks for helping me. Here is my source code:
<CFQUERY NAME=&quot;EMailAddresses&quot; DATASOURCE=&quot;PDC&quot;>
Select EmailAddress from Lot
</CFQUERY>

<CFQUERY NAME=&quot;EMailAddresses&quot; DATASOURCE=&quot;PDC&quot;>
SET NOCOUNT ON
<!--- created the lot --->
SELECT DISTINCT EMailAddress FROM Lot, Item, Person
WHERE Lot.PersonID = Person.PersonID AND Lot.LotID = Item.LotID
AND Lot.LotID = #LotID#
AND LotID IN (#ValueList(qry1.EMailAddress)#)
UNION (SELECT EMailAddress FROM Person
WHERE EMailAddress LIKE '#PrimaryAdminEMailAddress#')

I get this error when executing:
Invalid column name 'EmailAddress'

It almost worked when I took out:
<CFQUERY NAME=&quot;EMailAddresses&quot; DATASOURCE=&quot;PDC&quot;>
Select EmailAddress from Lot
</CFQUERY>
And:
AND LotID IN (#ValueList(qry1.EMailAddress)#)

When I did that it sent a email To and From the same person(the primary admin)
Hope this makes sense. What table am I looking for the right column? Thanks again for all your help!
Mike

 

[CFDude]

You have both of the querys with the same name -- rename the first query qry1 and try it from there --

<CFQUERY NAME=&quot;qry1&quot; DATASOURCE=&quot;PDC&quot;>
Select EmailAddress from Lot
</CFQUERY>

<CFQUERY NAME=&quot;EMailAddresses&quot; DATASOURCE=&quot;PDC&quot;>
SET NOCOUNT ON
<!--- created the lot --->
SELECT DISTINCT EMailAddress FROM Lot, Item, Person
WHERE Lot.PersonID = Person.PersonID AND Lot.LotID = Item.LotID
AND Lot.LotID = #LotID#
AND LotID IN (#ValueList(qry1.EMailAddress)#)
UNION (SELECT EMailAddress FROM Person
WHERE EMailAddress LIKE '#PrimaryAdminEMailAddress#')


I really am not sure which table or column you should be selecting from. I assumed from your code that you first did a query (qry1) to grab all the email addresses out and then in the second query (EmailAddresses)you are pulling the email address out based on the list that was created from the first query (qry1). Is that right? Where do you get the #PrimaryAdminEMailAddress# variable? It may be that we are doing a lot more work here than what is really needed. Your where clause (LIKE '#PrimaryAdminEMailAddress#') will only pull email addresses that are exactly the PrimaryAdminEMailAddress, so if you have that value, then the query may be unnecessary.

One other thing that I noticed in your code that may get you into trouble its this code here:

<CFLOOP QUERY=&quot;Items&quot;>
<CFQUERY DATASOURCE=&quot;PDC&quot;>
SET NOCOUNT ON
INSERT INTO ItemVoid (ItemID, Reason, PersonID, Date) VALUES (#ItemID#, '#Reason#', #Cookie.CurrentPersonID#, GetDate())
</CFQUERY>
</CFLOOP>

If you are doing a cfquery inside a query loop (cfloop query) there is a bug with cold fusion where sometimes the record does not get changed in the next iteration of the loop, so it will not cycle through the whole record set. The fix is to set a temporary variable with the value(s) of the first query and use that in your cfquery in the loop.
e.g
<CFLOOP QUERY=&quot;Items&quot;>
<cfset tmpItemID = Items.ItemID>
<cfset tmpReason = Items.Reason>
<CFQUERY DATASOURCE=&quot;PDC&quot;>
INSERT INTO ItemVoid (ItemID, Reason, PersonID, Date) VALUES (#tmpItemID#, '#tmpReason#', #Cookie.CurrentPersonID#, GetDate())
</CFQUERY>
</CFLOOP>

 

[mikeadmin]

I took out the union and just added myself in the from email section. Also I did what you said for the loop bug and it wouldnt let me void the lot so I took it out.
Here is my current source:
<CFQUERY NAME=&quot;qry1&quot; DATASOURCE=&quot;PDC&quot;>
Select EmailAddress FROM Person
</CFQUERY>
<CFQUERY NAME=&quot;EMailAddresses&quot; DATASOURCE=&quot;PDC&quot;>
SET NOCOUNT ON
<!--- created the lot --->
SELECT DISTINCT EMailAddress FROM Lot, Item, Person
WHERE Lot.PersonID = Person.PersonID AND Lot.LotID = Item.LotID
AND Lot.LotID = #LotID#
AND LotID IN (#ValueList(qry1.EMailAddress)#)
</CFQUERY>

<!--- send the email --->
<CFMAIL SUBJECT=&quot;Voided Lot #LotID#&quot;
TO=&quot;#ValueList(EmailAddresses.EMailAddress)#&quot;
FROM=&quot;#ProgrammerEMailAddress#&quot;>
Lot #LotID# was voided
-Connecticut Property Distribution Center Administration
</CFMAIL>


The new error message is :
[Microsoft][ODBC SQL Server Driver][SQL Server]The number name 'Kenn.Stephenson@po.state.ct' contains more than the maximum number of prefixes. The maximum is 3.

I dont understand because kenn is the primaryadmin but I took him out and just put myself in the from section.

 

[CFDude]

The error is coming from sql server -- are you passing an incorrect username or something to the server? (I don't think it has anything to do with the email address)

 

[tleish]

Sorry to interupt, but can you help me understand something?

What is a possible value of EmailAddress in the table Person?

... and What is a possible value of LotID from the table Lot? - tleish

 

[mikeadmin]

The username isnt kenn, its mine (mikeadmin) and I get around the system fine. Not sure how kenn's name is even getting there. He is the primaryadmin but I thought I took that out.
Any suggestions?
Thanks

 

[mikeadmin]

tleish,
the value for email is
Kenn.Stephenson@po.state.ct'

and LotID is 3 digits

 

[mikeadmin]

Also,
Lot ID data type INT and length 4
Email data is varchar and length is 255

 

[tleish]

That's what I thought. That being the case, the following item will not work in the query:

LotID IN (#ValueList(qry1.EMailAddress)#)

I believe this line should be comparing the 2 email addresses, correct? Try changing this line and using the QuotedValueList() function in place of the ValueList() function.

<CFQUERY NAME=&quot;qry1&quot; DATASOURCE=&quot;PDC&quot;>
Select EmailAddress FROM Person
</CFQUERY>
<CFQUERY NAME=&quot;EMailAddresses&quot; DATASOURCE=&quot;PDC&quot;>
SET NOCOUNT ON
[COLOR=666666]<!--- created the lot --->[/color]
SELECT DISTINCT EMailAddress FROM Lot, Item, Person
WHERE Lot.PersonID = Person.PersonID AND Lot.LotID = Item.LotID
AND Lot.LotID = #LotID#
AND Lot.EMailAddress IN (#QuotedValueList(qry1.EMailAddress)#)
</CFQUERY>

[COLOR=666666]<!--- send the email --->[/color]
<CFMAIL SUBJECT=&quot;Voided Lot #LotID#&quot;
TO=&quot;#ValueList(EmailAddresses.EMailAddress)#&quot;
FROM=&quot;#ProgrammerEMailAddress#&quot;>
Lot #LotID# was voided
-Connecticut Property Distribution Center Administration
</CFMAIL> - tleish

 

[mikeadmin]

Tleish
I tried what you said, didnt work. Here is the error:

ODBC Error Code = S0022 (Column not found)

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'EMailAddress'

 

[mikeadmin]

tleis
h,
Should I be changing the to: line in the send mail section?

 

[tleish]

I assumed there was an email address field in the Lot table.

Is there not an email address in the Lot field? Did you write this query, or did you get it from somewhere? I'm not sure I understand what you are trying to accomplish. - tleish