Solved

Query about query of queries

Posted on 2011-09-07
8
205 Views
Last Modified: 2012-06-27
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

0
Comment
Question by:Eric Bourland
  • 5
  • 2
8 Comments
 
LVL 39

Accepted Solution

by:
gdemaria earned 250 total points
Comment Utility

The first cfquery has to be a SELECT statement, you have an update statement so there is no record set to query..
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
I got it. brb
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
(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
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 250 total points
Comment Utility
>> 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
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
>>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
 
LVL 3

Author Closing Comment

by:Eric Bourland
Comment Utility
This is working now. I did not need a QoQ. But now I know more about QoQ. =) Thank you _agx_ and gdemaria.
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
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

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
This video discusses moving either the default database or any database to a new volume.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now