Unable to update record from form

[Eric_Dahlenburg]

Hey Folks,

This has been driving me crazy. You click on the submit button and the page refreshes, but no new user is added to the database.

I went and commented out all the IF/Then statements in the following code and I get the error message that FULL_NAME is undefined in FORMS:

<!--- Start of Entry Form Info --->
<cfset CurrentPage=GetFileFromPath(GetTemplatePath())>

<cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ "REGISTRATION">
  <cfquery datasource="ilmf_parts_dev">
  INSERT INTO REGISTRATION (FULL_NAME, USER_GROUP)
VALUES (
  <cfif IsDefined("FORM.FULL_NAME") AND #FORM.FULL_NAME# NEQ "">
    <cfqueryparam value="#FORM.FULL_NAME#" cfsqltype="VARCHAR2" maxlength="20">
     <cfelse>
    ''
  </cfif>
  , <cfqueryparam value="#FORM.USER_GROUP#" cfsqltype="VARCHAR2" maxlength="20">
     <cfif IsDefined("FORM.USER_GROUP") AND #FORM.USER_GROUP# NEQ "">
     <cfqueryparam value="FORM.USER_GROUP">
     <cfelse>
    ''
  </cfif>
    )
  </cfquery>
  <cflocation url="Admin.cfm">
</cfif>

If you look at the entire code (see attached text file), FORMs does define the two form elements that I am trying to write to the database. It looks as if Coldfusion does not recognize the two elements during the "IsDefined" section of the IF/THEN section, so the page refreshes without anything being written to the database.

Any help would be appreciated !!

Eric

[ilssac]

One of the first things to do in a situation like this.  See exactly what the browser is sending to the web server.  Because before ColdFusion can "recognize" a form field, the browser has to send it.

The easiest way to do this is at the top of the form action logic do this:

<cfdump var="#form#">
<cfabort>

Then review the data that is sumitted from a form submit.  Confirm that it confroms to expectations.

[Dan_Bracuk]

Once you follow Ian's suggestion and figure out what data you have, make sure the cfsqltype you use in cfqueryparam is something from cold fusion, not something from oracle.

[Eric_Dahlenburg]

It looks like the Form is sending the correct data, see screen shot:

http://spacecoasthomes.net/dump.jpg

As for the cfsqltype, I deleted it and tested again, still have a problem. Here is the entire code if anyone wants to have a look,

Thanks Eric:

<cfset _emp="">            <!--- Clear variable _emp --->
<cfset _emp="#CGI.Auth_User#"> <!--- Current User equal to variable _emp --->

<cfquery name="q_emp" datasource="person_admin" blockfactor="1">
SELECT PERSON_PLURAL.EMPLID, PERSON_PLURAL.SEARCH_NT_ID, PERSON_PLURAL.FULL_NAME,
PERSON_PLURAL.DEPTID
FROM APPL_WEB.PERSON_PLURAL
WHERE PERSON_PLURAL.SEARCH_NT_ID = <cfqueryparam VALUE="#variables._emp#">
</cfquery> <!--- Lookup current user in Person Plural Table --->

<cfquery name="role" datasource="ilmf_parts_dev">
SELECT * FROM REGISTRATION
WHERE FULL_NAME = '#q_EMP.FULL_NAME#'
</cfquery>

<CFIF role.User_Group NEQ "Admin">
<!--- If User Group does not contain "Admin", then next line --->
    <cflocation
url="gilmore.cfm"
addtoken="false"
/>
 
</CFIF>
<!--- If valid User Group Data, Then next line --->

<cfif role.User_Group EQ ""> <!--- Is role.User_Group Empty?? "No Data"=true  Next line if true--->
    </strong><span class="style1">You are Logged-In as: <strong><cfoutput>#q_EMP.FULL_NAME#</strong></cfoutput> and have <strong>READ ONLY </strong> Privilages. </span>
   
<cfelse>
   
    <p align="left" class="style1">You are logged on as: <strong><cfoutput>"#q_EMP.FULL_NAME#"</strong>  with<strong> #role.User_Group# </strong>Privileges</cfoutput></p>
</cfif><p>

<!--- Start of Entry Form Info --->
<cfset CurrentPage=GetFileFromPath(GetTemplatePath())>

<cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ "REGISTRATION">
  <cfquery datasource="ilmf_parts_dev">
  INSERT INTO REGISTRATION (FULL_NAME, USER_GROUP)
VALUES (
  <cfif IsDefined("FORM.FULL_NAME") AND #FORM.FULL_NAME# NEQ "">
    <cfqueryparam value="#FORM.FULL_NAME#">
     <cfelse>
    ''
  </cfif>
  , <cfqueryparam value="#FORM.USER_GROUP#">
     <cfif IsDefined("FORM.USER_GROUP") AND #FORM.USER_GROUP# NEQ "">
     <cfqueryparam value="FORM.USER_GROUP">
     <cfelse>
    ''
  </cfif>
    )
  </cfquery>
  <cflocation url="Admin.cfm">
</cfif>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Registration Page</title>

<script type="text/JavaScript">
function MM_findObj(n, d) { //v4.01
  var p,i,x;  if(!d) d=document; if((p=n.indexOf("?"))>0&&parent.frames.length) {
    d=parent.frames[n.substring(p+1)].document; n=n.substring(0,p);}
  if(!(x=d)&&d.all) x=d.all; for (i=0;!x&&i<d.forms.length;i++) x=d.forms;
  for(i=0;!x&&d.layers&&i<d.layers.length;i++) x=MM_findObj(n,d.layers.document);
  if(!x && d.getElementById) x=d.getElementById(n); return x;
}

function MM_validateForm() { //v4.0
  var i,p,q,nm,test,num,min,max,errors='',args=MM_validateForm.arguments;
  for (i=0; i<(args.length-2); i+=3) { test=args[i+2]; val=MM_findObj(args);
    if (val) { nm=val.name; if ((val=val.value)!="") {
      if (test.indexOf('isEmail')!=-1) { p=val.indexOf('@');
        if (p<1 || p==(val.length-1)) errors+='- '+nm+' must contain an e-mail address.\n';
      } else if (test!='R') { num = parseFloat(val);
        if (isNaN(val)) errors+='- '+nm+' must contain a number.\n';
        if (test.indexOf('inRange') != -1) { p=test.indexOf(':');
          min=test.substring(8,p); max=test.substring(p+1);
          if (num<min || max<num) errors+='- '+nm+' must contain a number between '+min+' and '+max+'.\n';
    } } } else if (test.charAt(0) == 'R') errors += '- '+nm+' is required.\n'; }
  } if (errors) alert('The following error(s) occurred:\n'+errors);
  document.MM_returnValue = (errors == '');
}
</script>

<style type="text/css">
<!--
.style1 {font-family: Arial, Helvetica, sans-serif}
.style2 {font-weight: bold}
.style3 {font-weight: bold; font-family: Arial, Helvetica, sans-serif; }
-->
</style>
</head>

<body>

   
<table width="600" border="1" align="center" cellpadding="3" cellspacing="1" bgcolor="#DBDBDB">
  <tr>
    <th scope="col"><h3 class="style1">Manage Users </h3></th>
    <th scope="col"><h3><span class="style1">Manage Inventory </span></h3></th>
    <th scope="col"><h3><span class="style1">Manage Warranty </span></h3></th>
    <th scope="col"><h3><span class="style1">Links </span></h3></th>
  </tr>
  <tr>
    <td><div align="center"><a href="Registration.cfm" class="style1">Register Users</a></div></td>
    <td><div align="center"><a href="Inventory_Dump.cfm" class="style1">Inventory Dump </a></div></td>
    <td><div align="center"><a href="Warranty_Dump.cfm" class="style1">Warranty  Dump</a></div></td>
    <td><div align="center"><a href="gilmore.cfm" class="style1">Serialized Parts Home</a></div></td>
  </tr>
  <tr>
    <td><div align="center"><a href="User_Dump.cfm" class="style1">Registration Dump </a></div></td>
    <td><div align="center"><a href="Issue_Dump.cfm" class="style1">Issued Parts Dump</a></div></td>
    <td><div align="center"><a href="Warranty.cfm" class="style1">Enter Warranty Information</a></div></td>
     <td><div align="center"><a href="Index.cfm" class="style1">Portal Home</a></div></td>
  </tr>
</table><br/><br/>
<br/>
<h1 align="center"><span class="style2">
  </span><span class="style1">Register New Users and Privileges</span>
  </h3>
</h1>

<form id="REGISTRATION" name="REGISTRATION" method="POST" action="<cfoutput>#CurrentPage#</cfoutput>">
  <table width="603" height="104" border="1" align="center" cellpadding="3" cellspacing="1" bgcolor="D9FFFF">
    <tr>
      <td width="279"><p class="style1">NT Name: Last First Middle initial</p>
        <p class="style1">Example: Doe John J</p></td>
      <td width="303"><label>
     
     
        <input name="FULL_NAME" type="text" id="FULL_NAME" size="40" />
      </label></td>
    </tr>
    <tr>
      <td><span class="style1">Privileges (Admin or User)</span></td>
      <td><label>
     
     
      <select name="USER_GROUP" id="USER_GROUP">
        <option value="User">User</option>
        <option value="Admin">Admin</option>
        <cfoutput query="role">
          </cfoutput>
      </select>
     
      </label></td>
    </tr>
   
    <tr>
      <td> </td>
      <td><label>
        <input name="Submit" type="submit" onClick="MM_validateForm('FULL_NAME','','R','USER_GROUP','','R');return document.MM_returnValue" value="Submit" />
      </label></td>
    </tr>
  </table>
  <p> </p>
  <cfdump var="#form#">
<cfabort>
</form>

  </body>
</html>

[ilssac]

P.S.

 <cfif IsDefined("FORM.FULL_NAME") AND #FORM.FULL_NAME# NEQ "">
...
 <cfif IsDefined("FORM.USER_GROUP") AND #FORM.USER_GROUP# NEQ "">

You are overusing the pound|hash|# characters.  Usually this does not matter, but maybe here it is causing a problem.  I would rewrite those lines as.

 <cfif IsDefined("FORM.FULL_NAME") AND FORM.FULL_NAME NEQ "">
...
 <cfif IsDefined("FORM.USER_GROUP") AND FORM.USER_GROUP NEQ "">

OR better yet

 <cfif structKeyExists(form,"FULL_NAME") AND FORM.FULL_NAME NEQ "">
...
 <cfif structKeyExists(form,"USER_GROUP") AND FORM.USER_GROUP NEQ "">

Generally you only need to put the # character around varables and functions when you want to render their values into the output stream.  You don't usually need to use them as part of CFML tags or functions.

[Eric_Dahlenburg]

Well, here is where I am at...

It looks like the code does not recognize FORM.FULL_NAME or FORM.USER_GROUP. I commented out the Starting and ending CFIF tags to get rid of the MM_InsertRecord problem and have added text to the else case for both columns being added to prove this (will cause a record of FULL_NAME/ Admin.

So there are two problems:

1. I can't get the form elements to write into the update query

2. The 2nd column (User_Group) get a column not allowed here  error (no, its not a primary key or has any coulmn constraints.

Here is the latest code and the error that I get:

<!--- <cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ "REGISTRATION"> --->
  <cfquery datasource="ilmf_parts_dev">
  INSERT INTO REGISTRATION (FULL_NAME, USER_GROUP)
VALUES (
  <cfif IsDefined("FORM.FULL_NAME") AND FORM.FULL_NAME NEQ "">
    <cfqueryparam value="#FORM.FULL_NAME#">
     <cfelse>
    "FULL_NAME"
  </cfif>
  , <cfif IsDefined("FORM.USER_GROUP") AND FORM.USER_GROUP NEQ "">
     <cfqueryparam value="#FORM.USER_GROUP#">
     <cfelse>
    "Admin"
  </cfif>
    )
  </cfquery>
  <cflocation url="Admin.cfm">
<!--- </cfif> --->

Error  Executing Database Query.
[Macromedia][Oracle JDBC  Driver][Oracle]ORA-00984: column not allowed here
The  error occurred in E:\papps\fl\ilmf_parts_dev\Registration.cfm: line  51
49 :   , <cfif IsDefined("FORM.USER_GROUP") AND #FORM.USER_GROUP# NEQ ""> 50 :      <cfqueryparam value="#FORM.USER_GROUP#"> 51 :      <cfelse> 52 :     "Admin" 53 :   </cfif>
SQLSTATE   HY000 SQL    INSERT INTO  REGISTRATION (FULL_NAME, USER_GROUP) VALUES ( "FULL_NAME" , "Admin" ) VENDORERRORCODE   984 DATASOURCE   ilmf_parts_dev

Thanks for all the help !  (Time for Dinner...)

Eric

[Dan_Bracuk]

Use single quotes, not double.

[ilssac]

Your form has the fields: "full_name", "submit" and "user_group" as well as the standard 'fieldnames'.

Your form processing logic is wrapped in an if statement from a Dreamweaver wizzard that looks for the existance of a field named "MM_InsertRecord" and that this field as the value of "Registration".  If it is false it will not process the SQL statement.

<cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ "REGISTRATION"> 

So it looks like your code is doing excatly what it has been told to do.  Don't process the insert if the field "MM_InsertRecord" does not exist.