SQL Update Query not Working...

Report · May 22, 2009

This was working until we had to encrypt the main key. This has been replaced with an existing autonum field. When I test the T-SQL query alone on SQL Server, it works. But through the ColdFusion progam, nothing happens. Its as if the submit button reference to this is not working or is ignored.

Any ideas?

Here is the code clip were the issue may be occuring.

<cfparam name="URL.SEQNO" default="1">
<cfset CurrentPage=GetFileFromPath(GetTemplatePath())>
<cfif IsDefined("FORM.MM_UpdateRecord") AND FORM.MM_UpdateRecord EQ "formUpdateRecord">
<cfquery name="Section301MI" datasource="301dsn">

DECLARE @Deleted User NVARCHAR(128)
SET @Deleted User = #FORM.SSN#;

OPEN SYMMETRIC KEY SSN_Key_01
   DECRYPTION BY CERTIFICATE anjalitest

UPDATE dbo.TableDataRecords SET SSN=
<cfif IsDefined("FORM.SSN") AND #FORM.SSN# NEQ "">
    EncryptByKey(Key_GUID('SSN_Key_01'),@str)
      <cfelse>
      NULL
</cfif>
, verifySSN =
<cfif IsDefined("FORM.SSN") AND #FORM.SSN# NEQ "">
    EncryptByKey(Key_GUID('SSN_Key_01'),@str)
      <cfelse>
      NULL
</cfif>
, LastName=
<cfif IsDefined("FORM.LastName") AND #FORM.LastName# NEQ "">
    '#FORM.LastName#'
      <cfelse>
      NULL
</cfif>
, FirstName=
<cfif IsDefined("FORM.FirstName") AND #FORM.FirstName# NEQ "">
    '#FORM.FirstName#'
      <cfelse>
      NULL
</cfif>
, MiddleInitial=
<cfif IsDefined("FORM.MiddleInitial") AND #FORM.MiddleInitial# NEQ "">
    '#FORM.MiddleInitial#'
      <cfelse>
      NULL
</cfif>
, DOB=
<cfif IsDefined("FORM.DOB") AND #FORM.DOB# NEQ "">
    '#FORM.DOB#'
      <cfelse>
      NULL
</cfif>
, BenefitProgram=
<cfif IsDefined("FORM.BenefitProgram") AND #FORM.BenefitProgram# NEQ "">
    #FORM.BenefitProgram#
      <cfelse>
      NULL
</cfif>
, MedCessDate=
<cfif IsDefined("FORM.MedCessDate") AND #FORM.MedCessDate# NEQ "">
    '#FORM.MedCessDate#'
      <cfelse>
      NULL
</cfif>
, ProgramDecision=
<cfif IsDefined("FORM.ProgramDecision") AND #FORM.ProgramDecision# NEQ "">
    #FORM.ProgramDecision#
      <cfelse>
      NULL
</cfif>
, ProgramDecisionDate=
<cfif IsDefined("FORM.ProgramDecisionDate") AND #FORM.ProgramDecisionDate# NEQ "">
    '#FORM.ProgramDecisionDate#'
      <cfelse>
      NULL
</cfif>
, ProgramType=
<cfif IsDefined("FORM.ProgramType") AND #FORM.ProgramType# NEQ "">
    #FORM.ProgramType#
      <cfelse>
      NULL
</cfif>
, ProgAllowStartDate=
<cfif IsDefined("FORM.ProgAllowStartDate") AND #FORM.ProgAllowStartDate# NEQ "">
    '#FORM.ProgAllowStartDate#'
      <cfelse>
      NULL
</cfif>
, ProgAllowCompDate=
<cfif IsDefined("FORM.ProgAllowCompDate") AND #FORM.ProgAllowCompDate# NEQ "">
    '#FORM.ProgAllowCompDate#'
      <cfelse>
      NULL
</cfif>
, DiaryDate=
<cfif IsDefined("FORM.DiaryDate") AND #FORM.DiaryDate# NEQ "">
    '#FORM.DiaryDate#'
      <cfelse>
      NULL
</cfif>
, DenialReason=
<cfif IsDefined("FORM.DenialReason") AND #FORM.DenialReason# NEQ "">
    #FORM.DenialReason#
      <cfelse>
      NULL
</cfif>
, TermDecisionDate=
<cfif IsDefined("FORM.TermDecisionDate") AND #FORM.TermDecisionDate# NEQ "">
    '#FORM.TermDecisionDate#'
      <cfelse>
      NULL
</cfif>
, TermReason=
<cfif IsDefined("FORM.TermReason") AND #FORM.TermReason# NEQ "">
    #FORM.TermReason#
      <cfelse>
      NULL
</cfif>
, Remarks=
<cfif IsDefined("FORM.Remarks") AND #FORM.Remarks# NEQ "">
    '#FORM.Remarks#'
      <cfelse>
      NULL
</cfif>
, EncryptNatIDNum1 = NULL
, EncryptNatIDNum2 = NULL

WHERE SEQNO=#URL.SEQNO#

CLOSE SYMMETRIC KEY SSN_Key_01;

</cfquery>
<cflocation url="modifysuccess.cfm">
</cfif>

Report · May 22, 2009

Are you sure your cfif is returning true?

Report · May 22, 2009

Thank you. This is part of my concern. The value is set by pressing the Update (submit) button.

I am still learning CF's syntax. But I do not know how to display a test confirmation for this.

Report · May 22, 2009

No the value is not set by pressing the submit button... not really.

When the user presses submit the browser submits the request to the web server with the form data in post headers.

The web server gets the request and sees it is a request for a ColdFusion template, it hands the request off to the ColdFusion engine.

ColdFusion takes the post data (or maybe the web server did at least part of this) and populates the Form scope with it.

Anyways, put this at the top of the page.

<cfdump var="#form#">

This will output all the post data received from the request in the form scope. See if the data you expect to see is there.

If the data for those if statements is not there that entire page does nothing.

Report · May 26, 2009

o.k. I put the cfdump at the top of the code.

It returns struct [empty]. So nothing is being submitted.

Here is the code at the end of the form...from my experience in ASP, I do not see any issue yet.

          <td valign="middle"><textarea name="Remarks" cols="32" rows="5"><cfoutput>#rsModifySingleRecord.Remarks#</cfoutput></textarea></td>
        </tr>
        <tr valign="baseline">
          <td nowrap align="right"> </td>
          <td><input name="UpdateRecord" type="submit" id="UpdateRecord" value="Update Record"></td>
        </tr>
      </table>
      <input type="hidden" name="SEQNO" value="<cfoutput>#rsModifySingleRecord.SEQNO#</cfoutput>">
      <input type="hidden" name="MM_UpdateRecord" value="formUpdateRecord">
</form>

Report · May 26, 2009

For fun, try <cfdump var="#url#"> right after the <cfdump var="#form#"> line.

Then look at the top of the form.

Is it method=get or method=post?

method=get will populate the URL scope.

method=post will popluate the FORM scope.

If both URL and FORM is empty and|or your form is method=post, then make sure the form is submitting to the action page, not some other page.

Report · May 26, 2009

One time it worked. It returned the FORM information. But that is when it was also stopped by an error saying something else was not defined in the form.

The beginning of the form:

<form method="post" name="formUpdateRecord" action="<cfoutput>#CurrentPage#</cfoutput>">

Now, again, nothing... after submitting.

URL Information - struct [empty]

FORM Information - struct [empty]

And I have tried putting URL after FORM, I get the same results... [empty].

Report · May 26, 2009

Of course, I should have recognized the Dreamweaver default of being a self-submitting form.

So the template submits to the same page the form is on. You will need slightly different debug logic for this.

Change that to this.

</cfif>

Then view the page, fill out the form and submit it.

HTH

Ian

Report · May 26, 2009

In addition to what Ian said, having hidden form fields after your submit button instead of before has caused problems for me in the past.

Report · May 26, 2009

I usually see hidden fields at the top. I moved them up to the top of the FORM. That did not make a difference.

I re-inserted that line of code that caused an error. And, I get Form information...

FORM Information - struct

BENEFITPROGRAM1

DENIALREASON1

DIARYDATE2007-05

DOB1987-02-15

FIELDNAMESSEQNO,MM_UPDATERECORD,LASTNAME,FIRSTNAME,MIDDLEINITIAL,DOB,BENEFITPROGRAM,MEDCESSDATE,PROGRAMDECISION,PROGRAMDECISIONDATE,PROGRAMTYPE,PROGALLOWSTARTDATE,PROGALLOWCOMPDATE,DIARYDATE,DENIALREASON,TERMDECISIONDATE,TERMREASON,REMARKS,UPDATERECORD FIRSTNAMEtest3

But because of the error, the form information is not submitted to the database. I wonder, is there a way to create an error and Catch/OnError it just to possible force this to have an error and submit the information?

Report · May 26, 2009

What error do you get?

This is why I don't bother with Dreamweaver wizzard forms, more trouble then they are worth.

I would just write this page in the following form.

<cfif structKeyExists(form, "FieldNames")>
  <!--- logic to do when the form is submitted --->
   ...
</cfif>

<html>
  <head>
  ....
   <form ...>
   ...
   </form>
</html>

Report · May 26, 2009

"Element SSN is undefined in FORM"

Here may be some key components of the form.

UPDATE ......

</cfquery>

...............................

    <form method="post" name="formUpdateRecord" action="<cfoutput>#CurrentPage#</cfoutput>">
      <input type="hidden" name="SEQNO" value="<cfoutput>#rsModifySingleRecord.SEQNO#</cfoutput>">
      <input type="hidden" name="MM_UpdateRecord" value="formUpdateRecord">

      <table width="100%" align="center">
        <tr valign="baseline">
          <td width="30%" align="right" nowrap><div align="left" class="tableHeader">Last Name* </div></td>
          <td valign="middle"><input type="text" name="LastName" value="<cfoutput>#rsModifySingleRecord.LastName#</cfoutput>" size="32"></td>
        </tr>

..........................

        <tr valign="baseline">
          <td width="30%" align="right" valign="middle" nowrap><div align="left" class="tableHeader">Remarks (if any)</div></td>
          <td valign="middle"><textarea name="Remarks" cols="32" rows="5"><cfoutput>#rsModifySingleRecord.Remarks#</cfoutput></textarea></td>
        </tr>
        <tr valign="baseline">
          <td nowrap align="right"> </td>
          <td><input name="UpdateRecord" type="submit" id="UpdateRecord" value="Update Record">
        </tr>
      </table>
    </form>

Report · May 26, 2009

"Element SSN is undefined in FORM."

This is in the <CFQUERY> after the form has been submitted.

I'll also try to re-write their page into a new form.

Report · May 26, 2009

PHILIP.MCCRACKEN.III@SSA. wrote:
"Element SSN is undefined in FORM."

That is a straight forward error.

You form does not have a field named "SSN"

I did not see a field named "SSN" in either your previous dump of the form scope nor in the fragment of the form html you copied, so this looks like a legitamete error.

Either add a <input name="ssn"...> to your form or remove the logic that references form.ssn in the action section.

Report · May 26, 2009

I only get the FORM information when I get that error. But I moved the "SET @str = #FORM.SSN#;" just below the IsDefined statement and the error goes away. But so does the FORM information. It seems the IsDefined statement forces the CF to recongnize the form outside of the form.

SET @str = #FORM.SSN#;

Report · May 26, 2009

The better function to test for the elements of a structure is structKeyExists()

<cfif structKeyExists(form,"SSN") AND form.ssn NEQ "">
...
</cfif>

P.S.

Normally this does not matter, but error may be comming from the #form.ssn# part of your line. You use ## signs to tell ColdFusion to render the value of a variable for output. You normally do not use them around variables inside of functions or tags. This may have been causing ColdFusion to try and render FORM.SSN to create a dynamic comparision.

If you care to tryit this might just work the way you expect it, but I would still recomended the structKeyExists() version.

<cfif isDefined("form.ssn") AND form.ssn NEQ "">

Report · May 22, 2009

I troubleshoot if/else stuff like this

fine

look at all relevent data

</cfif>

Report · May 27, 2009

1) This is almost a clone to the Insert form that works. The main differences are It selects the matching record for editing and then Updates.

2) The CFDUMP gives me results when I put it in the logic section that responds to the Form Submit button. But not at the top of the code.

I added the CFABORT so I could see a result.

<cfdump var="#form#">

<cfquery name="Section301MI" datasource="301dsn" username="section301db" password="testdb">

DECLARE @str NVARCHAR(128)
<cfif IsDefined("FORM.SSN")>
   SET @str = #FORM.SSN#;
</cfif>

OPEN SYMMETRIC KEY SSN_Key_01
   DECRYPTION BY CERTIFICATE anjalitest

UPDATE dbo.TableDataRecords SET verifySSN=                     <<This is only displayed, but not modified. Should this even be in the update?
<cfif IsDefined("FORM.SSN") AND #FORM.SSN# NEQ "">
    EncryptByKey(Key_GUID('SSN_Key_01'),@str)
      <cfelse>
      NULL
</cfif>
, [LastName]=
<cfif IsDefined("FORM.LastName") AND #FORM.LastName# NEQ "">
    '#FORM.LastName#'
      <cfelse>
      NULL
</cfif>
, [FirstName]=
<cfif IsDefined("FORM.FirstName") AND #FORM.FirstName# NEQ "">
    '#FORM.FirstName#'
      <cfelse>
      NULL
</cfif>
, MiddleInitial=
<cfif IsDefined("FORM.MiddleInitial") AND #FORM.MiddleInitial# NEQ "">
    '#FORM.MiddleInitial#'
      <cfelse>
      NULL
</cfif>
, DOB=
<cfif IsDefined("FORM.DOB") AND #FORM.DOB# NEQ "">
    '#FORM.DOB#'
      <cfelse>
      NULL
</cfif>
, BenefitProgram=
<cfif IsDefined("FORM.BenefitProgram") AND #FORM.BenefitProgram# NEQ "">
    #FORM.BenefitProgram#
      <cfelse>
      NULL
</cfif>
, MedCessDate=
<cfif IsDefined("FORM.MedCessDate") AND #FORM.MedCessDate# NEQ "">
    '#FORM.MedCessDate#'
      <cfelse>
      NULL
</cfif>
, ProgramDecision=
<cfif IsDefined("FORM.ProgramDecision") AND #FORM.ProgramDecision# NEQ "">
    #FORM.ProgramDecision#
      <cfelse>
      NULL
</cfif>
, ProgramDecisionDate=
<cfif IsDefined("FORM.ProgramDecisionDate") AND #FORM.ProgramDecisionDate# NEQ "">
    '#FORM.ProgramDecisionDate#'
      <cfelse>
      NULL
</cfif>
, ProgramType=
<cfif IsDefined("FORM.ProgramType") AND #FORM.ProgramType# NEQ "">
    #FORM.ProgramType#
      <cfelse>
      NULL
</cfif>
, ProgAllowStartDate=
<cfif IsDefined("FORM.ProgAllowStartDate") AND #FORM.ProgAllowStartDate# NEQ "">
    '#FORM.ProgAllowStartDate#'
      <cfelse>
      NULL
</cfif>
, ProgAllowCompDate=
<cfif IsDefined("FORM.ProgAllowCompDate") AND #FORM.ProgAllowCompDate# NEQ "">
    '#FORM.ProgAllowCompDate#'
      <cfelse>
      NULL
</cfif>
, DiaryDate=
<cfif IsDefined("FORM.DiaryDate") AND #FORM.DiaryDate# NEQ "">
    '#FORM.DiaryDate#'
      <cfelse>
      NULL
</cfif>
, DenialReason=
<cfif IsDefined("FORM.DenialReason") AND #FORM.DenialReason# NEQ "">
    #FORM.DenialReason#
      <cfelse>
      NULL
</cfif>
, TermDecisionDate=
<cfif IsDefined("FORM.TermDecisionDate") AND #FORM.TermDecisionDate# NEQ "">
    '#FORM.TermDecisionDate#'
      <cfelse>
      NULL
</cfif>
, TermReason=
<cfif IsDefined("FORM.TermReason") AND #FORM.TermReason# NEQ "">
    #FORM.TermReason#
      <cfelse>
      NULL
</cfif>
, Remarks=
<cfif IsDefined("FORM.Remarks") AND #FORM.Remarks# NEQ "">
    '#FORM.Remarks#'
      <cfelse>
      NULL
</cfif>
, EncryptNatIDNum1 = NULL
, EncryptNatIDNum2 = NULL

WHERE SEQNO=#URL.SEQNO#

   CLOSE SYMMETRIC KEY SSN_Key_01;
</cfquery>
<cfabort>
<cflocation url="modifysuccess.cfm">
</cfif>

Report · May 28, 2009

After using CFOUTPUT to debug the SQL UPDATE statement. I found that the WHERE value was wrong.

In the first pass the #URL.SEQNO# inputs the key value into the SELECT statement. When the submit is pushed. The #URL.SEQNO# value is defaulted to '1'.

I changed it to be #FORM.SEQNO#.

Thanks for all your assistance.