troubleshooting Question

INSERT works but UPDATE does not. UPDATE enters wrong info. from drop-down into table

Avatar of earwig75
earwig75 asked on
ColdFusion Language
8 Comments2 Solutions348 ViewsLast Modified:
I am inserting values from drop-down on my form into a master table. The values are saved in a separate table. I assign each list item in the reference table a numeric value. Below is an example of what I am using (the relevent code) on my INSERT and what I am using on my UPDATE. The insert works fine but when I update an item it is inserting the word (list item) and not the numeric value. The odd thing is; if I change certain fields on the update it works OK, but others breaks it. Can someone check out my code sample below and tell me what I am doing wrong? If other code samples are needed please let me know.

Any help would be greatly appreciated.

INSERT CODE:

CFC:


 <!--- Get details for a record --->

<cffunction name="get"
             returntype="query"
             hint="Get record details">

<cfargument name="ReferenceNumber"
              type="numeric"
              required="yes"
              hint="Reference Number">

 <cfquery datasource="#datasource#"
           name="record">
SELECT     dbo.Tbl_Record_Master.ReferenceNumber, dbo.Rtbl_Record_Offer_Type.OfferType,  
           dbo.Tbl_Record_Master.OtherField, dbo.Tbl_Record_Master.OtherField2

FROM       dbo.Tbl_Record_Master LEFT OUTER JOIN

         dbo.Rtbl_Record_Offer_Type ON dbo.Tbl_Record_Master.OfferType = dbo.Rtbl_Record_Offer_Type.OfferTypeID

WHERE      dbo.Tbl_Record_Master.ReferenceNumber=#ARGUMENTS.ReferenceNumber#

  </cfquery>
  <cfreturn record>
 
 </cffunction>

<!--- Adding an entry --->

 <cffunction name="add"
             returntype="boolean"
             hint="Adding an entry">
 
  <!--- Method arguments --->
  <cfargument name="OfferType"
              type="string"
              required="no"
              hint="Offer Type">

 <cfquery datasource="#datasource#">
             
      INSERT INTO [DB].[dbo].Tbl_Record_Master(OfferType, OtherField, OtherField2)

VALUES(
            <cfqueryparam value = "#ARGUMENTS.OfferType#" CFSQLType = "CF_SQL_VARCHAR">,
            <cfqueryparam value = "#ARGUMENTS.OtherField#" CFSQLType = "CF_SQL_VARCHAR">,
            <cfqueryparam value = "#ARGUMENTS.OtherField2#" CFSQLType = "CF_SQL_VARCHAR"> )

</cfquery>
  <cfreturn true>

 </cffunction>

<!--- Get Offer Types --->

 <!--- Get service types --->
 <cffunction name="GetServiceTypes"
             returntype="query"
             hint="Get Service Types">

  <cfquery datasource="#datasource#"
           name="offertypes">
  SELECT OfferTypeID, OfferType
  FROM [DB].[dbo].Rtbl_Record_Offer_Type
  ORDER BY OfferTypeID
  </cfquery>
  <cfreturn offertypes>

 </cffunction>

FORM PAGE:

<cfinvoke component="recordcfc"
           method="get"
           ReferenceNumber="#URL.CFGRIDKEY#"
           returnvariable="record">


<cfset OfferType="">
 <cfset OtherField="">
 <cfset OtherField2="">

<!--- Get Offer types --->
 <cfinvoke component="recordcfc"
           method="GetOfferTypes"
           returnvariable="offertypes">

<!--- Add/update record form --->
<cfform action="doit.cfm">

      <SELECT NAME="OfferType">
      <cfoutput><OPTION>#VARIABLES.OfferType#</OPTION></cfoutput>
            <CFOUTPUT QUERY="OfferTypes">
                  <OPTION VALUE="#OfferTypeID#">#OfferType#</OPTION>
            </CFOUTPUT>
      </SELECT>            

...........

doit.cfm (the page that processes the form):

<cfinvoke component="recordcfc"
          method="#method#"> (add or update depending on if the reference number was passed from the form)

<cfinvokeargument name="OfferType"
                   value="#FORM.OfferType#">

</cfinvoke>

<!--- When finished go back to main page --->

<cflocation url="index.cfm">


UPDATE CODE:


CFC:


<cffunction name="update"
             returntype="boolean"
             hint="Update a record">
  <!--- Method arguments --->
  <cfargument name="ReferenceNumber"
              type="numeric"
              required="yes"
              hint="Reference Number">
  <cfargument name="OfferType"
              type="string"
              required="no"
              hint="Offer Type">

<!--- Update the entry --->

  <cfquery datasource="#datasource#">
  UPDATE [DB].[dbo].Tbl_Record_Master
  SET OfferType='#ARGUMENTS.OfferType#',........other fields.....

 WHERE ReferenceNumber=#ARGUMENTS.ReferenceNumber#
  </cfquery>
  <cfreturn true>

 </cffunction>

FORM PAGE:

 <cfinvoke component="recordcfc"
           method="get"
           ReferenceNumber="#URL.CFGRIDKEY#"
           returnvariable="record">

 <!--- Save to variables --->

<cfset OfferType=record.OfferType>
<cfset OtherField=Trim(record.OtherField)>
<cfset OtherField2=record.OtherField>

The Dropdown and rest of form are the same on this page...

doit.cfm (the page that processes the form):

<cfinvoke component="recordcfc"
          method="#method#"> (add or update depending on if the reference number was passed from the form)

<cfinvokeargument name="OfferType"
                   value="#FORM.OfferType#">

</cfinvoke>
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 8 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros