Solved

Query about query of queries

Posted on 2011-09-07
8
206 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
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
 
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Coldfusion HTTPS 2 63
Having an issue with NumberFormat 7 58
multi page pdf 4 82
REReplaceNoCase help 1 13
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…
Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

919 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

15 Experts available now in Live!

Get 1:1 Help Now