Solved

UPDATE Table Set .... Where ??

Posted on 2012-03-15
15
294 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 15

Assisted Solution

by:myselfrandhawa
myselfrandhawa earned 20 total points
Comment Utility
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_
Comment Utility
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
Comment Utility
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
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
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
Comment Utility
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_
Comment Utility
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
Comment Utility
okay i wil do the test. thanks.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:LelloLello
Comment Utility
it didn't work :(
0
 

Author Comment

by:LelloLello
Comment Utility
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
Comment Utility
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
Comment Utility
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_
Comment Utility
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_
Comment Utility
>  <cfif len(trim(FORM.CMTEE_PREF))>

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

Author Comment

by:LelloLello
Comment Utility
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_
Comment Utility
"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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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 …
Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

744 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

12 Experts available now in Live!

Get 1:1 Help Now