Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2012-03-16
8
Medium Priority
?
329 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 750 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 750 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
How To Reduce Deployment Times With Pre-Baked AMIs

Even if we can't include all the files in the base image, we can sometimes include some of the larger files that we would otherwise have to download, and we can also sometimes remove the most time-consuming steps. This can help a lot with reducing deployment times.

 

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

Python: Series & Data Frames With Pandas

Learn the basics of Python’s pandas library of series & data frames and how we can use these tools for data manipulation.

Question has a verified solution.

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

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…
Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

715 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