Solved

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

Posted on 2012-03-16
8
327 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 29

Accepted Solution

by:
Pravin Asar earned 250 total points
ID: 37729643
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
ID: 37729725
> <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
ID: 37730199
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
Containers & Docker to Create a Powerful Team

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

 

Author Closing Comment

by:earwig75
ID: 37730204
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
 
LVL 52

Expert Comment

by:_agx_
ID: 37730241
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
ID: 37732462
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 29

Expert Comment

by:Pravin Asar
ID: 37733867
Good to know your problem is solved.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 37740508
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today, I was working on some optimization and spam-stopping techniques when I encountered Ben Nadel's post to reduce spam feature using Math (http://www.bennadel.com/blog/197-How-I-Stop-Spammers-On-My-ColdFusion-Blog.htm). While this method is not o…
CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Suggested Courses

636 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