Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 331
  • Last Modified:

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>
0
earwig75
Asked:
earwig75
  • 3
  • 3
  • 2
2 Solutions
 
Pravin AsarCommented:
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_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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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 AsarCommented:
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now