Solved

UPDATE Table Set .... Where ??

Posted on 2012-03-15
15
299 Views
Last Modified: 2012-03-26
I have the following update in coldfusion.

My CMTEE_pref will not be updated because the where statement is not correct.


            <CFIF #FORM.CMTEE_PREF# NEQ "">
                        UPDATE cvitool
                        SET
                        CMTEE_PREF = #FORM.CMTEE_PREF#
                        WHERE users.user_id=#FORM.user_id#
                  </CFIF>


Could you please advice how i can adjust that according to my cfquery.

FROM   users INNER JOIN cvitool ON users.id_number  = cast(cvitool.id as INT)
WHERE users.user_id = <cfqueryparam value="#client.user_id#" cfsqltype="cf_sql_integer">.
0
Comment
Question by:LelloLello
  • 7
  • 7
15 Comments
 
LVL 16

Assisted Solution

by:Gurpreet Singh Randhawa
Gurpreet Singh Randhawa earned 20 total points
ID: 37728269
i think you are doing it wrong, are u trying to use the update inside the join, ia m not sure what are exactly trying, can u please explain more and explain inbetter way or show a live link if possible
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37729200
For sql server the JOIN syntax  is something like this.  I added cfqueryparam because it's very dangerous to omit it with db's like sql server.


UPDATE  c
SET         c.CMTEE_PREF = <cfqueryparam value="#FORM.CMTEE_PREF#"
                                               cfsqltype="cf_sql_varchar">
FROM     users u INNER JOIN cvitool c ON u.id_number  = cast(c.id as INT)
WHERE    u.user_id = <cfqueryparam value="#client.user_id#" cfsqltype="cf_sql_integer">

 <CFIF #FORM.CMTEE_PREF# NEQ "">

Also to ignore all white space entries use trim()

         <cfif len(trim(FORM.CMTEE_PREF))>
                  the value is not empty. do the UPDATE
         </cfif>
0
 

Author Comment

by:LelloLello
ID: 37729858
Hello Agx,

thank you for your reply.  
I've add the following

 <CFIF #FORM.CMTEE_PREF# NEQ "">
                  UPDATE  cvitool
SET         c.CMTEE_PREF = <cfqueryparam value="#FORM.CMTEE_PREF#"
                                               cfsqltype="cf_sql_varchar">
FROM     users u INNER JOIN cvitool c ON u.id_number  = cast(c.id as INT)
WHERE    u.user_id = <cfqueryparam value="#client.user_id#" cfsqltype="cf_sql_integer">
                  </CFIF>

But the update of the field CMTEE_PREF is not updating that field in the database.

could you please advice.
0
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.

 
LVL 52

Expert Comment

by:_agx_
ID: 37729934
Is your real code missing the cfquery tags? It won't do anything without those ;)  Also, you're using VARCHAR for one of the values.  Aren't they both integers? Using the correct type is important. See my example above.

If your real code DOES have cfquery tags, possibilities why it isn't working are:

1) it's not running the UPDATE code at all. Add an else condition to debug what's happening

     <cfif len(trim(FORM.CMTEE_PREF))>
          running update query.
          <cfquery ...>
                  Your update code here
          </cfquery>
     <cfelse>
           it's empty. do nothing
      </cfif>
2. If it IS running the update, but not changing the value then it means the database didn't find a record matching your criteria. So you need to find out why. Add a result attribute to the query and dump it to see the generated sql

          <cfquery result="yourResult" ...>
                  Your update code here
          </cfquery>

          <cfdump var="#yourResult#">
0
 

Author Comment

by:LelloLello
ID: 37729954
Here is my code.  Please open it in dreamweaver... Do i'm missing anything ?

First name and last name are updating correctly...

only the CMTEE_PREF is not being update. thank you very much for your advice.
update-code.txt
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37730020
It's very hard to tell what's going on. But I would strongly recommend redesigning your form and query. The way it's designed now it runs a separate update for each field. That's extremely inefficient. It's also confusing and difficult to debug.  It's better to update all fields in a single query.


<CFIF #FORM.LoginName# NEQ "">


Why are the fields empty to start with? Normally edit forms populate all form fields with the old values - so you don't have use all that <CFIF not empty logic>.  You just update all fields

            UPDATE users
            SET         Login = <cfqueryparam value="#FORM.LoginName#" cfsqltype="cf_sql_varchar">
                            ,  ... rest of fields

As to why it's not updating, see my previous response where I explained the 2 mostly causes and how to debug them.
0
 

Author Comment

by:LelloLello
ID: 37730273
okay i wil do the test. thanks.
0
 

Author Comment

by:LelloLello
ID: 37730692
it didn't work :(
0
 

Author Comment

by:LelloLello
ID: 37730699
it's not running the UPDATE code only at at users fields (loginname, firstname, lastname, password, into the users table. nothing on the dbo.cvitool.
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 480 total points
ID: 37730733
Well I mentioned several tests, like adding debugging code and dumping the "result" attribute. What were the results when you tried them?  

Btw, since the query is so complex you should probably test the code in a separate query first.
         CFMTEE_PREF value is <cfoutput>#FORM.CMTEE_PREF#</cfoutput><br>

         <cfif len(trim(FORM.CMTEE_PREF))>
              running update query ...
             <cfquery result="yourResult" .... other settings ...>
               UPDATE  c
               SET         c.CMTEE_PREF = <cfqueryparam value="#FORM.CMTEE_PREF#"
                                               cfsqltype="cf_sql_varchar">
               FROM     users u INNER JOIN cvitool c ON u.id_number  = cast(c.id as INT)
               WHERE    u.user_id = <cfqueryparam value="#client.user_id#" cfsqltype="cf_sql_integer">
              </cfquery>
              <cfdump var="#yourResult#">
         <cfelse>
                  empty. do nothing>
         </cfif>

         <cfif len(trim(FORM.CMTEE_PREF))>
0
 

Author Comment

by:LelloLello
ID: 37730778
Please review it like this you mean.

<CFQUERY NAME="UpdateInfo" datasource="Aies" username="#application.username#" password="#application.password#">

      <CFIF #FORM.user_id# NEQ "" OR #FORM.CMTEE_PREF# NEQ "">      

<CFIF #FORM.CMTEE_PREF# NEQ "">
                  UPDATE  c
SET         c.CMTEE_PREF = <cfqueryparam value="#FORM.CMTEE_PREF#" cfsqltype="cf_sql_varchar">
FROM     users u INNER JOIN cvitool c ON u.id_number  = cast(c.id as INT)
WHERE    u.user_id = <cfqueryparam value="#client.user_id#" cfsqltype="cf_sql_integer">
                  </CFIF>
      </CFIF>
        </CFQUERY>
  <cfdump var="#yourResult#">
         <cfelse>
                  empty. do nothing>
         </cfif>
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37730876
No try it exactly like in my previous response.  You want to test the query on a page by itself, without all the other stuff.  Just fill in the cfquery datasource name and password here:

      <cfquery result="yourResult" .... other settings ...>  <=== here
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37731214
>  <cfif len(trim(FORM.CMTEE_PREF))>

Ignore the very last line, its' a copy paste error
0
 

Author Comment

by:LelloLello
ID: 37734074
ok here what i have now.

  <cfif len(trim(FORM.CMTEE_PREF))>
              running update query ...
             <cfquery result="yourResult" datasource="Actuaries" username="#application.username#" password="#application.password#">
               UPDATE  c
               SET         c.CMTEE_PREF = <cfqueryparam value="#FORM.CMTEE_PREF#"
                                               cfsqltype="cf_sql_varchar">
               FROM     users u INNER JOIN cvitool c ON u.id_number  = cast(c.id as INT)
               WHERE    u.user_id = <cfqueryparam value="#client.user_id#" cfsqltype="cf_sql_integer">
              </cfquery>
              <cfdump var="#yourResult#">
         <cfelse>
                  empty. do nothing>
         </cfif>

         <cfif len(trim(FORM.CMTEE_PREF))>
           

I got that error.

Just in time compilation error<P> An unknown attribute '<b>result</b>' has been encountered at document position (96:23) to (96:28) while processing tag CFQUERY. This tag can only take the following attributes: <ul><li>BLOCKFACTOR<li>CACHEDAFTER<li>CACHEDWITHIN<li>CONNECTSTRING<li>DATASOURCE<li>DBNAME<li>DBPOOL<li>DBSERVER<li>DBTYPE<li>DEBUG<li>MAXROWS<li>NAME<li>PASSWORD<li>PROVIDER<li>PROVIDERDSN<li>SQL<li>TIMEOUT<li>USERNAME</ul><p>The last successfully parsed CFML construct was a CFQUERY tag occupying document position (96:14) to (96:21).<p>The specific sequence of files included or processed is:<code><br><strong>d:\users\actca\html\members\toolkit\update_volunteer_info_ee.cfm      </strong></code><P><P>
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37739209
"result" was added in CF8 so it won't work under older versions like CF7.  If you're running an older version you'll have to enable debugging in the CF admin or do it the old fashioned way.  Copy the SQL and cfoutput it before the actual query

ie
<cfoutput><strong>DEBUG</strong> this is the sql generated<br>
 UPDATE  c
 SET         c.CMTEE_PREF = "#FORM.CMTEE_PREF#"
FROM     users u INNER JOIN cvitool c ON u.id_number  = cast(c.id as INT)
WHERE    u.user_id = #client.user_id#
</cfoutput>

 <cfquery ...>the actual UPDATE here </cfoutput>
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to display Coldfusion alert message box icons 8 623
paging 3 46
ajaxSubmit is giving me an error 1 47
ColdFusion not showing binary json data in request 4 30
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 …
Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them (http://www.randhawaworld.com/) . I did keep the main js functions but made sever…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

809 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