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

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>
earwig75Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Pravin AsarConnect With a Mentor Principal Systems EngineerCommented:
With update, why you are not using cfqueryparam


<cfqueryparam value = "#ARGUMENTS.OfferType#" CFSQLType = "CF_SQL_VARCHAR">

<cfquery datasource="#datasource#">
  UPDATE [DB].[dbo].Tbl_Record_Master
  SET OfferType=<cfqueryparam value = "#ARGUMENTS.OfferType#" CFSQLType = "CF_SQL_VARCHAR">,
........other fields.....
 WHERE ReferenceNumber=#ARGUMENTS.ReferenceNumber#
  </cfquery>
0
 
_agx_Connect With a Mentor Commented:
> <cfoutput><OPTION>#VARIABLES.OfferType#</OPTION></cfoutput>

I think it's because of your list code. You're not giving the 1st option a "value". So it'll use the option's "text" (ie "Widget A") instead. Either remove that code or assign both the "value" and text

     <cfoutput>
     <OPTION VALUE="#someNumberID#">#VARIABLES.OfferType#</OPTION>
     </cfoutput>

       ie   <option value="1">Widget A</option>

Open in new window


Just a tip, don't test too much code at once. Test each piece individually first. For example as others said your query code is missing cfqueryparam. Also, if #OfferType# is numeric it shouldn't be in quotes.  

Assuming the two columns are of type "INTEGER" the query code should be more like this. Notice ALL values use cfqueryparam. Change the data types as needed.
    UPDATE   [DB].[dbo].Tbl_Record_Master
    SET          OfferType = <cfqueryparam value="#ARGUMENTS.OfferType#"   
                                            cfsqltype="cf_sql_integer">
    WHERE    ReferenceNumber = <cfqueryparam value="#ARGUMENTS.ReferenceNumber#"
                     cfsqltype="cf_sql_integer">

Open in new window


Test it with hard coded values first
        <cfinvoke component="yourcomponent" method="update"
                ReferenceNumber="123"
                OfferType="1" ...>

Open in new window


Once that works as you expect, test it with your form code. Be sure to dump the FORM variables at the top of the page to ensure the values are correct. If not, go back and fix your form.

    <cfdump var="#FORM#">
0
 
earwig75Author Commented:
Thank you for the help. It turns out someone created a relationship between my 2 tables incorrectly and I didn't know about it. The problem wasn't with my code after all. As far as not having the queryparam - I had other code setting default values and planned on changing the update eventually.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
earwig75Author Commented:
This turned out to be a problem with my database. There was a relationship between 2 tables created incorrectly that I did not know about.
0
 
_agx_Commented:
Btw, if you answered your own question, without help from the comments offered, don't feel obligated to accept them as an answer. Only if got something useful out of them. You can always accept your own comment as an answer OR delete the question if the solution is too specialized to be of use to anyone else.

As far as not having the queryparam - I had other code setting default values and planned on changing the update eventually.

Well defaults don't help with text fields and single select lists because they always exist. As far as waiting, my advice is - don't. Better to tighten things up while you're working on them because its easy to get busy and forget all about it. Until you get hacked and then it's too late.
0
 
earwig75Author Commented:
Thanks, I am using "isdefined" on each of them because I kept running into an unrelated problem. I'll post an example Monday, if you're bored and want to take a look that would be cool. Thanks again.
0
 
Pravin AsarPrincipal Systems EngineerCommented:
Good to know your problem is solved.
0
 
_agx_Commented:
Thanks, I am using "isdefined" on each of them because I kept running into an unrelated problem.

You probably know this already, but just to be sure .. cfqueryparam and cfparam are very different things. cfqueryparam is specifically designed for db queries. cfparam and IsDefined are not related to db queries at all.
0
All Courses

From novice to tech pro — start learning today.