Solved

Query about query of queries

Posted on 2011-09-07
8
210 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
8 Comments
 
LVL 39

Accepted Solution

by:
gdemaria earned 250 total points
ID: 36498230

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
ID: 36498246
I got it. brb
0
 
LVL 52

Expert Comment

by:_agx_
ID: 36498255
(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
Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

 
LVL 3

Author Comment

by:Eric Bourland
ID: 36498494
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
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 250 total points
ID: 36498588
>> 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
ID: 36498617
>>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
ID: 36498739
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
ID: 36498755
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PROBLEM: How to add your own buttons to the bottom toolbar with paging info ( result count ). While creating a cfgrid, I ran into an issue where I wanted to embed my own custom buttons where the default ones ( insert / delete / etc… ) are for aes…
This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

740 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