Participating Frequently

Question

SQL Update Query not Working...

Forum|Forum|17 years ago
May 22, 2009
2 replies
3537 views

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 @9766605 NVARCHAR(128)
SET @9766605 = #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>

Database access

This topic has been closed for replies.

P

PHILIP_MCCRACKEN_IIIAuthor

Participating Frequently

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>

P

PHILIP_MCCRACKEN_IIIAuthor

Participating Frequently

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.

D

Dan_Bracuk

Inspiring

Are you sure your cfif is returning true?

P

PHILIP_MCCRACKEN_IIIAuthor

Participating Frequently

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.

ilssac

Inspiring

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.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded