UPDATE Table Set .... Where ??

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>

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.

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#">

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

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.

okay i wil do the test. thanks.

it didn't work :(

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.

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>

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

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

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

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>

"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>