Solved

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

Posted on 2012-03-16
8
317 Views
Last Modified: 2012-03-19
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
Comment
Question by:earwig75
  • 3
  • 3
  • 2
8 Comments
 
LVL 28

Accepted Solution

by:
Pravin Asar earned 250 total points
Comment Utility
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
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 250 total points
Comment Utility
> <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
 

Author Comment

by:earwig75
Comment Utility
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
 

Author Closing Comment

by:earwig75
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 52

Expert Comment

by:_agx_
Comment Utility
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
 

Author Comment

by:earwig75
Comment Utility
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
 
LVL 28

Expert Comment

by:Pravin Asar
Comment Utility
Good to know your problem is solved.
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

This article  is about submitting  form through  ColdFusion.Ajax.submitForm to the action page and send a response back in JSON format which later can be decoded using ColdFusion.JSON.decode. By this way you can avoid the usual page refresh for subm…
The technique is by far very Simple! How we can export the ColdFusion query results to DOC file?  Well before writing this I researched a lot in Internet but did not found a good Answer anyways!  So i thought now i should share my small snippet w…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now