Skip to main content
A
Anonymous
December 5, 2010
Answered

Problem when trying to update database

  • December 5, 2010
  • 1 reply
  • 1726 views

The problem I'm having is that whenever i try to change a certain piece of information about the client it always changes it to a totally different number that doesn't make sense.  in the database. For example a clients island id is 3 and i want to change it to 12, the number that shows in the database after i hit update is 40430. I am at a loss and have no clue why it's doing such a thing. Here is the edit file to make the changes accordingly.

=========== Edit file ============

<!--- Insert or update? --->
<cfset EditMode=IsDefined("FORM.BusinessID")>

<cfif EditMode>

<!--- Update movie --->
<cfupdate datasource="wyi2" tablename="Business">
<cfset action="updated">
<cfelse>
<!--- Add movie --->
<cfinsert datasource="wyi2" tablename="Business">
<cfset action="added">
</cfif>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<!--- Feedback --->
<cfoutput>
  <h1>Client #FORM.BusinessName# #action#</h1><br/>
  <cfif IsDefined('FORM.Events') AND FORM.Events EQ 1>
  <a href="add_event.cfm">Now add the event</a>
  </cfif>
</cfoutput>

</body>
</html>

    This topic has been closed for replies.
    Correct answer Owainnorth

    <!--- Insert or update? --->
    <cfset EditMode=IsDefined("FORM.BusinessID")>

    <cfif EditMode>
    <!--- Update movie --->

    <cfquery datasource="wyi2">
    UPDATE Business
    SET BusinessName='#Trim(FORM.BusinessName)#',
          Address='#Trim(FORM.Address)#',
         IslandID='#FORM.IslandID#',
         Phone='#FORM.Phone#',
         WebID='#Trim(FORM.WebID)#',
         Events='FORM.Events'
    WHERE BusinessID=#FORM.BusinessID#
    </cfquery>


    <cfset action="updated">
    <cfelse>
    <!--- Add movie --->
    <cfinsert datasource="wyi2" tablename="Business">
    <cfset action="added">
    </cfif>

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>Untitled Document</title>
    </head>

    <body>
    <!--- Feedback --->
    <cfoutput>
      <h1>Client #FORM.BusinessName# #action#</h1><br/>
      <cfif IsDefined('FORM.Events') AND FORM.Events EQ 1>
      <a href="add_event.cfm">Now add the event</a>
      </cfif>
    </cfoutput>

    </body>
    </html>

    I should also say that everytime i enter a new client into the database the islandid enters correctly, but not the update. Also thx to everyone for the replies.


    So you're saying that if you do a CFDUMP and CFABORT just before your update query then Form.IslandID is, for example, 4; but that it actually writes a 5-digit number to the database? Because that's simply nonsensical. Are you absolutely sure you haven't named two form fields identically? If you do that, CF will concatenate their values with commas which CFQuery will almost certainly just put straight into the database as it assumes it's what you want.

    Oh, and read up on CFQueryparam.

    1 reply

    D
    Community Expert
    Dave WattsCommunity Expert
    Community Expert
    December 5, 2010

    You might want to simply write regular SQL INSERT and UPDATE statements instead of using CFINSERT and CFUPDATE. That said, without seeing the form and the table columns and their datatypes, I can't answer your question.

    Dave Watts, CTO, Fig Leaf Software

    http://www.figleaf.com/

    http://training.figleaf.com/

    Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on

    GSA Schedule, and provides the highest caliber vendor-authorized

    instruction at our training centers, online, or onsite.

    Read this before you post:

    http://forums.adobe.com/thread/607238

    Dave Watts, Eidolon LLC
    A
    Anonymous
    December 5, 2010

    Here's the form.

    <cfparam name="URL.BusniessID" default="">
    <cfparam name="URL.IslandID" default="">
    <cfset EditMode=IsDefined('URL.BusinessID')>

    <!--- Edit Mode Start --->
    <cfif EditMode>

    <cfquery name="qEdit" datasource="wyi2">
    SELECT *
    FROM Business
    WHERE BusinessID = <cfqueryparam value="#URL.BusinessID#" cfsqltype="cf_sql_numeric">
    </cfquery>


    <!--- Get Variables --->
    <cfset BusinessName=Trim(qEdit.BusinessName)>
    <cfset Address=Trim(qEdit.Address)>
    <cfset IslandID=Trim(qEdit.IslandID)>
    <cfset Phone=Trim(qEdit.Phone)>
    <cfset WebID=Trim(qEdit.WebID)>

    <!--- Form text --->
    <cfset FormTitle="Update Client Form">
    <cfset ButtonText="Update Client Info">

    <cfelse>

    <cfset BusinessName="">
    <cfset Address="">
    <cfset IslandID="">
    <cfset Phone="">
    <cfset WebID="">

    <!--- Form text --->
    <cfset FormTitle="New Client Form">
    <cfset ButtonText="Add New Client">

    </cfif>

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>Edit Client Form</title>
    <link href="../css/wyi.css" rel="stylesheet" type="text/css" />
    <link href="../css/wyi2.css" rel="stylesheet" type="text/css" />
    <script type="text/javascript" src="js/window1.js"></script>
    </head>

    <body>

    <cfform action="edit_process.cfm" method="post" name="Business">

    <cfif EditMode>
    <!--- Embed primary key as a hidden field --->
    <cfoutput>
    <input type="hidden" name="IslandID" value="#qEdit.IslandID#">
    </cfoutput>
    </cfif>

        <table border="0" align="center" cellpadding="3" cellspacing="0">
          <tr bgcolor="#FFFFFF">
            <td colspan="2" align="center">
              <font size="+3">
                <strong>
                  <cfoutput>
                    #FormTitle#
                </cfoutput>
                </strong>
              </font>
            </td>
          </tr>
          <tr bgcolor="#FBFBFB">
            <td>Business Name</td>
            <td>
            <cfif IsDefined('URL.BusinessID')>
            <cfoutput>
              <input type="hidden" name="BusinessID" value="#qEdit.BusinessID#" />
            </cfoutput>
            </cfif>
              <cfinput type="text"
                         name="BusinessName"
                       class="textfield"
                       value="#BusinessName#">
            </td>
          </tr>
          <tr>
            <td bgcolor="#FFFFFF">Address</td>
            <td bgcolor="#FFFFFF">
              <cfinput type="text"
                         name="Address"
                       class="textfield"
                       value="#Address#">
            </td>
          </tr>
          <tr bgcolor="#FBFBFB">
            <td>Island ID</td>
            <td>
              <cfinput type="text" name="IslandID" mask="99" value="#VARIABLES.IslandID#" size="5" />
            <a href="#">
              <img src="images/IslandID_List.jpg" border="0" width="135" height="17" align="absmiddle" onclick="MM_openBrWindow('island_list.cfm','','status=yes,scrollbars=yes,resizable=yes,width=250,height=425')" />
            </a>
            </td>
          </tr>
          <tr>
            <td bgcolor="#FFFFFF">Phone</td>
            <td bgcolor="#FFFFFF">
              <cfinput type="text"
                         name="Phone"
                       mask="(999) 999-9999"
                       class="textfield"
                       value="#Phone#">
            </td>
          </tr>
          <tr bgcolor="#FBFBFB">
            <td>WebID</td>
            <td>
              <cfinput type="text"
                         name="WebID"
                       class="textfield"
                       value="#WebID#">
            </td>
          </tr>
          <tr>
            <td bgcolor="#FFFFFF">Events</td>
            <td bgcolor="#FFFFFF">
               <input name="Events" type="radio" id="Events_0" value="1" <cfif IsDefined('URL.Events') AND URL.Events EQ 1>checked="checked"</cfif> />
                   Yes<br />
                <input name="Events" type="radio" id="Events_1" value="0" <cfif IsDefined('URL.Events') AND URL.Events EQ 0>checked="checked"</cfif> />
                   No
            </td>
          </tr>

          <tr>
            <td valign="top" bgcolor="#FBFBFB"> </td>
            <td bgcolor="#FBFBFB"> 
             
            </td>
          </tr>
          <tr bgcolor="#FBFBFB">
            <td> </td>
            <td>
            <cfoutput>
              <input type="submit" class="button2" value="#ButtonText#">
            </cfoutput>
            </td>
          </tr>
          <tr bgcolor="#FFFFFF">
            <td> </td>
            <td> </td>
          </tr>
        </table>
    </cfform>
    </body>
    </html>

    Owainnorth
    Owainnorth
    Inspiring
    December 5, 2010

    +1 for Dave's suggestion, write yourself some proper SQL statements using CFQUERY, you have far greater control over what's going on.

    Incidentally, there should be no need to go around trimming every field you've pulled from a database, just make sure it's trimmed once when it goes *in*. All you're doing there is causing yourself more work.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices