UPDATE Table Set .... Where ??

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">.
LelloLelloAsked:
Who is Participating?
 
_agx_Connect With a Mentor Commented:
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
 
Gurpreet Singh RandhawaConnect With a Mentor Web DeveloperCommented:
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
 
_agx_Commented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LelloLelloAuthor Commented:
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
 
_agx_Commented:
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
 
LelloLelloAuthor Commented:
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
 
_agx_Commented:
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
 
LelloLelloAuthor Commented:
okay i wil do the test. thanks.
0
 
LelloLelloAuthor Commented:
it didn't work :(
0
 
LelloLelloAuthor Commented:
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
 
LelloLelloAuthor Commented:
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
 
_agx_Commented:
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
 
_agx_Commented:
>  <cfif len(trim(FORM.CMTEE_PREF))>

Ignore the very last line, its' a copy paste error
0
 
LelloLelloAuthor Commented:
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
 
_agx_Commented:
"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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.