Solved

Query about query of queries

Posted on 2011-09-07
8
214 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
Stressed Out?

Watch some penguins on the livecam!

 
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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Hi, I will be creating today a basic tutorial on how we can create a Mail Custom Function and use it where ever we want. The main advantage about creating a custom function is that we can accommodate a range of arguments to pass to the Function and …
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 …
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

724 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