Inserting Data - Inserting into all fields 1

[wilcoHead]

This is a two-part question:

1. I have the following form: Note that I have a field called "RebateAccrual" that will only show if the "CoOpID" is "18".

----------------------------------------------------------
Form Code
----------------------------------------------------------
<TABLE border="0" cellspacing="2" cellpadding="2" align="center">
<TR>
<TD align="center">&nbsp;</TD>
<TD align="center">NET</TD>
<CFIF GetDealer.CoopID EQ "18"><TD align="center">REBATE ACCRUAL</TD><CFELSE></CFIF>
<TD align="center">&nbsp;</TD>
</TR>

<FORM action="GetDealersActionAdd.cfm" method="post">
<INPUT name="CoopID" type="hidden" value="CoopID">
<TR>
<CFOUTPUT>
<TD>#GetDealer.Distributor#</TD>
<TD width="50"><INPUT name="Net" type="text" style="border:1px ##CCCCCC solid" value="#GetDealer.Net#"></TD>
<CFIF GetDealer.CoopID EQ "18"><TD width="100"><INPUT name="RebateAccrual" type="text" style="border:1px ##CCCCCC solid" value="#GetDealer.RebateAccrual#"></TD><CFELSE>&nbsp;</CFIF>
<TD align="center"><INPUT name="submit" type="submit" value="submit" style="border:1px ##CCCCCC solid"></TD>
</CFOUTPUT>
</TR>
</FORM>
</TABLE>
---------------------------------------------------------
Here is the Insert Code
---------------------------------------------------------
Note that I have a <CFIF> if the "CoOpID" is "18".
---------------------------------------------------------
<CFQUERY NAME="EditCoOp" DATASOURCE="CoOp_DB">
UPDATE COOP

SET
Net = '#FORM.Net#'


<cfif FORM.CoopID EQ "18">
, RebateAccrual = '#FORM.RebateAccrual#'
<cfelse>

</cfif>

WHERE CoopID = #FORM.CoopID#
</CFQUERY>
----------------------------------------------------------
Now, what is happening is:

1. When I update a number in one CoOp Account, it changes all of the other accounts.

2. If I select the account (CoOpID) #18 and put a number in the the "RebateAccrual" field, it processes with no error but does not update nor insert any data for that field.

Thanks for your input;
WilcoHEAD

 

[krisbrixon]

First, dump the query to the string instead of run it then try to run it in Query Analyzer or Access or ?? (depends on DB). Make sure it is making valid SQL.

My guess:
# 1 is no id is being sent, so check for it.
# 2 there is an error with the SQL, so catch any errors.

<cfparam name="FORM.CoopID" default=0>
<cfparam name="FORM.RebateAccrual" default="">
<cfif FORM.CoopID GT 0>
  <cftry>

    <CFQUERY NAME="EditCoOp" DATASOURCE="CoOp_DB">
      UPDATE COOP SET         
      Net = '#FORM.Net#'
      <cfif FORM.CoopID EQ 18>
        , RebateAccrual = '#FORM.RebateAccrual#'
      </cfif>
      WHERE CoopID = #FORM.CoopID#
    </CFQUERY>

  <cfcatch type="any">
    <p>#cfcatch.message#</p>
    <p>#cfcatch.Detail#</p>
    <p>Caught an exception, type = #CFCATCH.TYPE# </p>
  </cfcatch>
  </cftry>
</cfif>

Kris Brixon
www.brixon.org

 

[wilcoHead]

Ahh, that worked - I should have put it in Debugging mode. I forgot to add a CFOutput on the hidden form field for the CoopID.

Once I did that it was cool.

Thanks for your help

Thanks for your input;
WilcoHEAD