For Darkman 1

programmher · Jul 12, 2000

(Darkman, I have been unsuccessful in my attempts to update records in my database.  If you have time, please peruse my code and tell me where my errors lie.  (Disregard the quotation marks at the beginning and end of this code.): “FORM ONE:<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <html> <head> <title>Input</title> </head> <body><table width="100%" border="0" cellspacing="2" cellpadding="2" background="Cnrclogo.gif" bgcolor="Blue"> <tr>      <td><IMG alt="" border=0 height=95 src="Cnrclogo.gif" width=103></td>     <td style="color: Navy;">    <td style="color: Navy;"></td> </tr> </table> </head> <h1 align="CENTER" style="font-size: large; font-family: sans-serif; color: Navy;" title="EXPENSES">Input</h1> <marquee title="" height="3%" width="747" style="font-family: Times New Roman; font-size: small; color: Navy;" scrollamount="5" border="1"> COMPLY! Resistance is FUTILE...     </marquee> <cfform action="TestFormTwo.cfm" method="POST">  <th>Name:</th>       <td><cfinput Type = "text" name = "Name" maxlength="25" required="yes" message="Last Name is a required field!" Size = "25"></td>     <th>City:</th>     <td><cfinput Type = "Text" name = "City" maxlength="20" required="yes" message="City is a required field!" Size = "20"></td>  </tr> <tr>     <th>State:</th>     <td><cfinput Type = "text" name = "State" maxlength="2" required="yes" message="State is a required field!" Size = "2"></td>     <th>ZIP:</th>     <td><cfinput Type = "text" name = "ZipCode" maxlength="10" required="yes" message=" Zip Code is a required field!" Size = "10"></td> </tr>  </table> <Input type = "submit" VALUE="Submit">     <Input type = "reset" VALUE="Clear Form">     <td align="middle"> </A></td> </cfform> </body> </html> (This form works.) FORM TWO <cfinsert datasource="OPE" tablename="MyTestTable"> <cfquery name="EnteredRecord" datasource="OPE"> SELECT     MAX (ID) AS ID FROM         dbo.MyTestTable </cfquery>   <cfquery name="EditedInfo" datasource="ope"> Select * FROM MyTestTable   Where ID = #EnteredRecord.ID# </cfquery> <html> <head><title>Information</title></head> <h2 align="CENTER">Review/EditInformation</h2> <h3>This is what you entered:</h3> <cfoutput query="EditedInfo">  <input type="hidden" name="ID" value='#ID#'> h3 align="CENTER" style="color: Navy;">Below is a review of the entered data:</h3> <table width="100%" border="1" cellspacing="2" cellpadding="2" align="CENTER" valign="MIDDLE"> <tr> <th>Name</th>     <th>#Name#</th>     <th>City</th>     <td>#City#</td>     <th>ST</th>     <td>#State#</td>     <th>ZipCode</th>     <td>#ZipCode#</td> <tr> </tr> </table> <h3 style="font-size: medium; text-align: center; color: Navy;">THANK YOU - YOUR REQUEST WILL BE PROCESSED!!!</h3> <div align="center"> <td><a href=TestFormThree.cfm?ID=#ID#>[EDIT]</a></td> </cfoutput> </form> </html> </div> (This form works.) FORM THREE <cfquery name="MoreEditedInfo" datasource="ope"> Select * FROM MyTestTable Where ID = '#ID#' </cfquery> <html> <head><title> - Edit Information</title></head> <body bgcolor="#FFFFFF"> <h2 align="CENTER">Review/Edit  Information</h2> <h3>This is what you entered:</h3> <cfoutput query="MoreEditedInfo"> <Form Action="ThisLittleFormWorks.cfm" Method=post> <tr> <table width="100%" border="1" cellspacing="2" cellpadding="2" align="CENTER" valign="MIDDLE">   <tr> Name <input type="text" name="Name" size=15 value="#Name#"> City <input type="text" name="City" size=20 value="#City#"> State <input type="text" name="ST" size=2 value="#State#"> Zip Code <input type="text" name="ZipCode" size=11 value="#ZipCode#"> <pre> <td><a href="ThisLittleFormWorks.cfm?ID=#ID#">Update Record</a></td> </pre> </form> <tr></tr>  <input type="hidden" name="ID" value="#ID#"> <pre>  </cfoutput> </form> </body> </html> (This Form Works) LAST FORM - WILL NOT UPDATE DATABASE <cfparam name="Name" default=""> <cfparam name="City" default=""> <cfparam name="State" default=""> <cfparam name="ZipCode" default=""> <cfquery datasource="ThisLittleQueryWorked"> UPDATE     MyTestTable SET    Name='#Name#',  City='#City#',  State='#State#',  ZipCode='#ZipCode#' WHERE       (ID = '#ID#') </cfquery> <input type="hidden" name="ID" value="#form.ID#">     <th>Name</th>     <td>'#Name#'</td>     <th>City</th>     <td>'#City#'</td>     <th>ST</th>     <td>'#ST#'</td>     <th>ZipCode</th>     <td>'#ZipCode#'</td>    </tr> </table>”) Thank you in advance!

DarkMan · Jul 12, 2000

FORM ONE: <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <html> <head>   <title>Input</title> </head> <body> <table width="100%" border="0" cellspacing="2" cellpadding="2" background="Cnrclogo.gif" bgcolor="Blue"> <tr><td><IMG alt="" border=0 height=95 src="Cnrclogo.gif" width=103></td></tr> </table> <h1 align="CENTER" style="font-size: large; font-family: sans-serif; color: Navy;" title="EXPENSES">Input</h1> <marquee title="" height="3%" width="747" style="font-family: Times New Roman; font-size: small; color: Navy;" scrollamount="5" border="1">                COMPLY!  Resistance is FUTILE...      </marquee> <cfform action="TestFormTwo.cfm" method="POST"> <table width="100%" border="0" cellspacing="2" cellpadding="2" background="Cnrclogo.gif" bgcolor="Blue"> <tr>   <th>Name:</th>     <td><cfinput Type = "text" name = "Name" maxlength="25" required="yes" message="Last Name is a required field!" Size = "25"></td>   <th>City:</th>   <td><cfinput Type = "Text" name = "City" maxlength="20" required="yes" message="City is a required field!" Size = "20"></td> </tr> <tr>   <th>State:</th>   <td><cfinput Type = "text" name = "State" maxlength="2" required="yes" message="State is a required field!" Size = "2"></td>   <th>ZIP:</th>   <td><cfinput Type = "text" name = "ZipCode" maxlength="10" required="yes" message=" Zip Code is a required field!" Size = "10"></td> </tr> </table> <Input type = "submit" VALUE="Submit"><Input type = "reset" VALUE="Clear Form"> </cfform> </body> </html> There were a few stray tags, so I cleaned it up a bit... FORM TWO  <cftransaction>   <cfquery name="Insert" datasource="OPE">   insert into MyTestTable (Name,City,State,ZipCode)   values ('#Name#','#City#','#State#','#ZipCode#')   </cfquery>   <cfquery name="EnteredRecord" datasource="OPE">   SELECT MAX(ID) AS ID   FROM dbo.MyTestTable   </cfquery>   <cfquery name="EditedInfo" datasource="ope">   Select * FROM MyTestTable   Where ID = #EnteredRecord.ID#   </cfquery> </cftransaction> <html> <head><title>Information</title></head> <body> <h2 align="CENTER">Review/EditInformation</h2> <h3>This is what you entered:</h3> <cfoutput query="EditedInfo"> <h3 align="CENTER" style="color: Navy;">Below is a review of the entered data:</h3> <table width="100%" border="1" cellspacing="2" cellpadding="2" align="CENTER" valign="MIDDLE"> <tr>   <th>Name</th><td>#Name#</td>   <th>City</th><td>#City#</td>   <th>ST</th><td>#State#</td>   <th>ZipCode</th><td>#ZipCode#</td> </tr> </table> <h3 style="font-size: medium; text-align: center; color: Navy;">THANK YOU - YOUR REQUEST WILL BE PROCESSED!!!</h3> <div align="center"> <a href=TestFormThree.cfm?ID=#ID#>[EDIT]</a> </div> </cfoutput> </form> </body> </html> Again, a few stray tags..  Also, I put your queries in a CFTRANSACTION so they will all run one after another.  If you don't do this, it's possible another process will insert to this table before your select statements... FORM THREE <cfquery name="MoreEditedInfo" datasource="ope"> Select * FROM MyTestTable Where ID = #ID# </cfquery> <html> <head><title> - Edit Information</title></head> <body bgcolor="#FFFFFF"> <h2 align="CENTER">Review/Edit  Information</h2> <h3>This is what you entered:</h3> <cfoutput query="MoreEditedInfo"> <Form Action="ThisLittleFormWorks.cfm" Method=post> <input type="hidden" name="ID" value="#ID#"> <table width="100%" border="1" cellspacing="2" cellpadding="2" align="CENTER" valign="MIDDLE">   <tr>   <th>Name</th><td><input type="text" name="Name" size=15 value="#Name#"></td>   <th>City</th><td><input type="text" name="City" size=20 value="#City#"></td> </tr> <tr>   <th>State</th><td><input type="text" name="ST" size=2 value="#State#"></td>   <th>Zip Code</th><td><input type="text" name="ZipCode" size=11 value="#ZipCode#"></td> </tr> </table> <input type="submit" value="Update Record"> </form> </cfoutput> </body> </html> The biggest problem I saw was that your link at the bottom should have been a form submit button.  The link won't pass the changes you made to the form.... LAST FORM <cfparam name="Name" default=""> <cfparam name="City" default=""> <cfparam name="State" default=""> <cfparam name="ZipCode" default=""> <cfquery datasource="ThisLittleQueryWorked"> UPDATE MyTestTable SET  Name='#Name#',  City='#City#',  State='#State#',  ZipCode='#ZipCode#' WHERE ID = #ID# </cfquery> <html> <head> <title>Record Updated</title> </head> <body> <table> <tr>   <th>Name</th>   <td>#Name#</td>   <th>City</th>   <td>#City#</td> </tr> <tr>   <th>ST</th>   <td>#ST#</td>   <th>ZipCode</th>   <td>#ZipCode#</td> </tr> </table> </body> </html> There was no real problems here, except that I took the single quotes off the ID in the update statement.  (Single quotes should not be used with numerical values...) This should work okay, if not let me know and I'll see what I can do...

programmher · Jul 13, 2000

Darkman, Many, many, MANY thanks!!!  My pages now include an update functionality that works!!!  You TOTALLY rule!   Words REALLY can't express my gratitude; especially after spending many hours working on this, following examples, and not having much success. You should write a CF developer's manual!

DarkMan · Jul 13, 2000

Glad to have helped..

curiosity · Mar 20, 2001

In your last page which is doing the updates give the parameters a form. prefix i.e
<cfparam name="form.name" default="">

and when updating use #form.name#

While you can get away without using prefixs such as url. and form. it doen't always work the right way. Its better to be more explicit.
I'm guessing all the fields in the database are just being updated to blank.

I didn't fully read the code so if this is way off the problem sorry.

Also is the id field a number/autonumber. If so don't use single quotes in the where clause.

curiosity · Mar 20, 2001

Sorry missed the solution already there. Duh.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

For Darkman 1

programmher

Programmer

DarkMan

Programmer

programmher

Programmer

DarkMan

Programmer

curiosity

Programmer

curiosity

Programmer

Similar threads

Part and Inventory Search

Sponsor