Query about query of queries

Hi. I am trying to query a query. The first query has name UpdateRecord. After UpdateRecord runs, I want to query its result. When I do so, I get this error:

Error Executing Database Query.
<br><b>Query Of Queries runtime error.</b><br> Table named UpdateRecord was not found in memory. The name is misspelled or the table is not defined.


I thought that specifying dbtype="query" told ColdFusion to query a query, not a data column. The documentation at http://help.adobe.com/en_US/ColdFusion/9.0/Developing/WSc3ff6d0ea77859461172e0811cbec0e4fd-7ff0.html is not giving me a simple answer, though it is good reading. What am I missing?

Thank you as always.

Eric

  <cfquery name="UpdateRecord" datasource="#application.datasource#">
	  UPDATE CareplannersMembers
	  SET prefix = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.prefix,5))#">,
      firstname = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.firstname,50))#">,
      middleinit = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.middleinit,5))#">,
      lastname = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.lastname,50))#">,
      suffix = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.suffix,5))#">,
      company = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.company,100))#">,
      primaryphone = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.primaryphone,20))#">,
      UserEmail = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.UserEmail,100))#">,
      website = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.website,255))#">,
      street1 = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.street1,100))#">,
      city1 = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.city1,50))#">,
      state1 = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.state1,50))#">,
      zip1 = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.zip1,15))#">,
      country1 = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.country1,50))#">,
      phone1 = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.phone1,20))#">,
      fax1 = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.fax1,20))#">,
      street2 = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.street2,100))#">,
      city2 = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.city2,50))#">,
      state2 = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.state2,50))#">,
      zip2 = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.zip2,15))#">,
      country2 = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.country2,50))#">,
      phone2 = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.phone2,20))#">,
      fax2 = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.fax2,20))#">,
      practice = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.practice,50))#">,
      info = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.info,50))#">,
      verified = <cfqueryparam cfsqltype="cf_sql_bit" value="#Trim(form.verified)#">,
      sign_up = <cfqueryparam cfsqltype="cf_sql_bit" value="#Trim(form.sign_up)#">,
      DateModified = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">

	  WHERE CareplannersUUID = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.CareplannersUUID,36))#">
			</cfquery>

<!--- query UpdateRecord --->
<!--- get updated UserEmail value from query UpdateRecord --->
<!--- resulting value will be used later in mailing list signup --->
<cfquery name="GetUpdatedEmail" dbtype="query">
SELECT UserEmail
FROM UpdateRecord
</cfquery>

Open in new window

LVL 3
Eric BourlandAsked:
Who is Participating?
 
gdemariaConnect With a Mentor Commented:

The first cfquery has to be a SELECT statement, you have an update statement so there is no record set to query..
0
 
Eric BourlandAuthor Commented:
I got it. brb
0
 
_agx_Commented:
(no points...)

Also, why do you need to query to get the email address? The value is already available in a form variable

   UserEmail = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.UserEmail,100))#">,
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Eric BourlandAuthor Commented:
Here is the scene I envision:
1) user logs in, edits record, changes email address (UserEmail)
2) that means the email address must be changed in the mailing list subscription roster
3) I can do that in ColdFusion (using CFMAIL), but I need to get the new email address

My plan in the edit page:

1)  query UpdateRecord runs; assume UserEmail has changed
2)  need new value of UserEmail, so QoQ UpdateRecord
3)  use that new UserEmail value when I send command (via CFMAIL) to SmarterMail to update the user's email address

I am probably making this more complicated than it needs to be, but this code seems to work:
<!--- query UpdateRecord --->
 <cfquery name="UpdateRecord" datasource="#application.datasource#">
 UPDATE CareplannersMembers
 SET (values ....)
 WHERE CareplannersUUID = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(Left(form.CareplannersUUID,36))#"> </cfquery>

<!--- maybe I am making this more complicated than it needs to be --->

<!--- GetUpdatedEmail --->
<!--- get updated UserEmail value, AFTER query UpdateRecord has updated table CareplannersMembers --->
<cfquery name="GetUpdatedEmail" datasource="#application.datasource#">
SELECT CareplannersUUID,UserEmail
FROM CareplannersMembers
WHERE CareplannersUUID = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.CareplannersUUID#">
</cfquery>

<!--- QoQ queryGetUpdatedEmail --->
<!--- resulting UserEmail value will be used in mailing list signup --->
<cfquery name="queryGetUpdatedEmail" dbtype="query">
SELECT UserEmail
FROM GetUpdatedEmail
</cfquery>

<!--- sign up for, or unsubscribe from, lcpforum mailing list--->
<!--- did user sign up for lcpforum@careplanners.net? --->
<cfif sign_up IS 1 OR sign_up IS True>
   <cfmail  
      server="mail.careplanners.net"  
      from="#queryGetUpdatedEmail.UserEmail#" 
      to="(server)" 
      subject="subscribe lcpforum"  
	  username = "(username)"
      password = "pass"
      SpoolEnable="Yes"></cfmail>
</cfif>

<!--- did user unsubscribe from lcpforum@careplanners.net? --->
<cfif sign_up IS 0 OR sign_up IS False>
   <cfmail
      server="mail.careplanners.net"
      from="#GetUpdatedEmail.UserEmail#"
      to="(server)"
      subject="unsubscribe lcpforum"
	  username = "(username)"
      password = "pass"
      SpoolEnable="Yes"></cfmail>
</cfif>

Open in new window

0
 
_agx_Connect With a Mentor Commented:
>> 2)  need new value of UserEmail, so QoQ UpdateRecord

But don't you already have that value? Isn't it just the form field you're using in the UPDATE
       ie #form.UserEmail#

If that's correct you don't need to run another query. Just use the form variable.
0
 
Eric BourlandAuthor Commented:
>>But don't you already have that value? Isn't it just the form field you're using in the UPDATE
       ie #form.UserEmail#
>>>If that's correct you don't need to run another query. Just use the form variable.

I was wondering exactly that. I was thinking I needed to get the new UserEmail value from query UpdateRecord using QoQ.

I will try it the simpler way.
0
 
Eric BourlandAuthor Commented:
This is working now. I did not need a QoQ. But now I know more about QoQ. =) Thank you _agx_ and gdemaria.
0
 
Eric BourlandAuthor Commented:
Also, there was an error in my CFMAIL command to SmarterMail. This is wrong:

   <cfmail  
      server="mail.careplanners.net"  
      from="#queryGetUpdatedEmail.UserEmail#" 
      to="(server)" 
      subject="subscribe lcpforum"  
      username = "(username)"
      password = "pass"
      SpoolEnable="Yes"></cfmail>

Open in new window


SmarterMail wants the Subject blank, and the command in the body of the mail. This is correct:
   <cfmail  
      server="mail.careplanners.net"  
      from="#queryGetUpdatedEmail.UserEmail#" 
      to="(server)" 
      subject=""  
      username = "(username)"
      password = "pass"
      SpoolEnable="Yes">subscribe lcpforum</cfmail>

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.