Updating forms 2

[programmher]

What is the best way to update a form with several fields when the user may only need to update one or two of these fields?<br><br>I have been unsuccessful using an embedded update SQL query.<br><br>The update form appears with all the forms updatable after an &quot;update&quot; button has been selected.<br><br>

 

[DarkMan]

Try this:<br><FONT FACE=monospace><b><br>&lt;cfquery name=&quot;update&quot; datasource=&quot;mydb&quot;&gt;<br>update mytable<br>set<br>field0=field0<br>&lt;cfif len(form.field1)&gt;, field1=#form.field1#&lt;/cfif&gt;<br>&lt;cfif len(form.field2)&gt;, field1=#form.field2#&lt;/cfif&gt;<br>&lt;cfif len(form.field3)&gt;, field1=#form.field3#&lt;/cfif&gt;<br>&lt;cfif len(form.field4)&gt;, field1=#form.field4#&lt;/cfif&gt;<br>where id=#form.id#<br>&lt;/cfquery&gt;<br></b></font><br>You need the field0 so the other commas will make sense.&nbsp;&nbsp;Try picking a column that you know will not need to be updated with the cfif's.&nbsp;&nbsp;If there are any columns that will need to be updated regardless, put them first and you can eliminate the &quot;field0=field0&quot; part...<br><br>Hope this helps,<br><br>DM

 

[programmher]

Darkman,<br><br>Thanks for the information.&nbsp;&nbsp;I tried the above and several variations, but kept getting various parameter resolution error messages.<br><br>Here is a snippet of what I want to do:<br><br><br>&lt;cfquery name=&quot;Update&quot; datasource=&quot;Expenses&quot;&gt;<br>UPDATE&nbsp;&nbsp;&nbsp;Expenditures<br>SET <br>LASTNAME = '#LASTNAME#',<br>FIRSTNAME = '#FIRSTNAME#',<br>MI = '#MI#',<br>HOMEADDRESS = '#HOMEADDRESS#', <br>ADDRESSTWO = '#ADDRESSTWO#',<br>CITY = '#CITY#',<br>ST = '#ST#',<br>ZIPCODE = '#ZIPCODE#',<br>DATE ='#DATE#', <br>ACCOUNTTYPE = '#ACCOUNTTYPE#',<br>ACCOUNTNUMBER = '#ACCOUNTNUMBER#',<br>EXPENSE1 = '#EXPENSE1#',<br>&nbsp;AMOUNTDUE1 = '#AMOUNTDUE1#',<br>REMARKS = '#REMARKS#',<br>APPLICANTNAME = '#APPLICANTNAME#'<br>&nbsp;Where SSN = #SSN#<br>&lt;/cfquery&gt;<br><br>&lt;cfoutput&gt;&nbsp;&nbsp;<br><br><br>I want the user to be able to edit a mistyped name or SSN while keeping the other information the same.&nbsp;&nbsp;Can you tell from the above where I'm erring?<br><br>Thanks again!

 

[DarkMan]

From this CFQUERY, I would do this:<br><FONT FACE=monospace><b><br>&lt;cfquery name=&quot;Update&quot; datasource=&quot;Expenses&quot;&gt;<br>UPDATE Expenditures<br>SET <br>SSN=#ssn#<br>&lt;cfif len(LASTNAME)&gt;,LASTNAME = '#LASTNAME#'&lt;/cfif&gt;<br>&lt;cfif len(FIRSTNAME)&gt;,FIRSTNAME = '#FIRSTNAME#'&lt;/cfif&gt;<br>&lt;cfif len(MI)&gt;,MI = '#MI#'&lt;/cfif&gt;<br>&lt;cfif len(HOMEADDRESS)&gt;,HOMEADDRESS = '#HOMEADDRESS#'&lt;/cfif&gt; <br>&lt;cfif len(ADDRESSTWO)&gt;,ADDRESSTWO = '#ADDRESSTWO#'&lt;/cfif&gt;<br>&lt;cfif len(CITY)&gt;,CITY = '#CITY#'&lt;/cfif&gt;<br>&lt;cfif len(ST)&gt;,ST = '#ST#'&lt;/cfif&gt;<br>&lt;cfif len(ZIPCODE)&gt;,ZIPCODE = '#ZIPCODE#'&lt;/cfif&gt;<br>&lt;cfif len(DATE)&gt;,DATE ='#DATE#'&lt;/cfif&gt;<br>&lt;cfif len(ACCOUNTTYPE)&gt;,ACCOUNTTYPE = '#ACCOUNTTYPE#'&lt;/cfif&gt;<br>&lt;cfif len(ACCOUNTNUMBER)&gt;,ACCOUNTNUMBER = '#ACCOUNTNUMBER#'&lt;/cfif&gt;<br>&lt;cfif len(EXPENSE1)&gt;,EXPENSE1 = '#EXPENSE1#'&lt;/cfif&gt;<br>&lt;cfif len(AMOUNTDUE1)&gt;,AMOUNTDUE1 = '#AMOUNTDUE1#'&lt;/cfif&gt;<br>&lt;cfif len(REMARKS)&gt;,REMARKS = '#REMARKS#'&lt;/cfif&gt;<br>&lt;cfif len(APPLICANTNAME)&gt;,APPLICANTNAME = '#APPLICANTNAME#'&lt;/cfif&gt;<br>&nbsp;Where SSN = #SSN1#<br>&lt;/cfquery&gt;<br></b></font><br>Notice the SSN1 variable.&nbsp;&nbsp;Pass this as a hidden form field so if they change the #ssn# it will update properly.&nbsp;&nbsp;You should really have a unique (autonumbering) ID field in your table for each record, since it is POSSIBLE, however unlikely that you could end up with customers with the same SSN or possibly someone mistypes their SSN to be the same as someone elses.&nbsp;&nbsp;The autonumber will eliminate this possibility.&nbsp;&nbsp;If you use this, just pass the ID as a hidden field and and change the Where clause to &quot;Where ID=#ID#&quot;..<br><br>The SSN will be updated no matter what, but that won't matter since if they are not changing it, it'll just update to the same value it was.

 

[programmher]

Darkman,<br><br>Thanks for the above information.&nbsp;&nbsp;Actually, I do have an autonumber field.&nbsp;&nbsp;I was passing the SSN as my hidden field because I was passing it on my previous forms.<br><br>I will follow your example and notify you of my results.<br><br>Thanks again!&nbsp;&nbsp;

 

[programmher]

Darkman,<br><br>I keep getting the following error message:<br><br><br>ColdFusion was unable to determine the value of the parameter. This problem is very likely due to the fact that either: <br><br>You have misspelled the parameter name, or <br>You have not specified a QUERY attribute for a CFOUTPUT, CFMAIL, or CFTABLE tag.<br><br>Surely, I don't have to pass all the variables as hidden to resolve this error and pass all my variables, do I?<br><br><br>

 

[DarkMan]

Nope.&nbsp;&nbsp;Put some CFPARAM tags at the top and give each a default of &quot;&quot; like..]<br><FONT FACE=monospace><b><br>&lt;cfparam name=&quot;LASTNAME&quot; default=&quot;&quot;&gt;<br>&lt;cfparam name=&quot;FIRSTNAME&quot; default=&quot;&quot;&gt;<br></b></font>...and so on....<br><br>This will initialize the variables to null and keep any value they already have....

 

[programmher]

Darkman,<br><br>Thank you SO much for your assistance!&nbsp;&nbsp;I really appreciate it.&nbsp;&nbsp;I'm still having difficulty getting my edited fields to update to the database; but I think it's due to programmer error.<br><br>Out of curiosity, couldn't I have acccomplished this update using CFUPDATE?&nbsp;&nbsp;Would this have been easier?&nbsp;&nbsp;

 

[DarkMan]

Probably, but I personally have had several problems with CFUPDATE and CFINSERT, and I prefer to use CFQUERY or CFSTOREDPROC for all my dealings with databases... <br><br>It's mostly a matter of personal preference here, I think...

 

[programmher]

Darkman,<br><br>I am still unable to update edited information to my database.&nbsp;&nbsp;If you have time, would you mind reviewing my code and showing me my error(s)?

 

[DarkMan]

Take a look at my response to the code you posted in the &quot;For DarkMan&quot; thread...