troubleshooting Question

(revised for clarity) Survey form; help with setting an initial value for a newly created Unique ID?

Avatar of Eric Bourland
Eric BourlandFlag for United States of America asked on
ColdFusion Language
45 Comments1 Solution347 ViewsLast Modified:
Hi. I have a simple survey form here:

The form will not process data. I've been troubleshooting and tinkering with this form for a couple of days (and got some very useful help from _agx_).

It's almost finished -- but I have a problem with my action page: data_insert.cfm

In that page, I need to set an initial value for the unique ID in my database table, which is column SurveyResponseID. (This column is also PK.)

If you submit the form, you will see this error:

 Element IDENT is undefined in SURVEYRESPONSEID.
The error occurred in C:\websites\\cep\consumersurvey\data_insert.cfm: line 73

71 :  
72 : <!--- set value from newly created SurveyResponseID --->
73 : <input type="hidden" name="SurveyResponseID" value="#SurveyResponseID.ident#">
74 : <!--- set rest of values from submitted form --->
75 : <input type="hidden" name="affiliation" value="#FORM.affiliation#">

So, I thought that I defined element IDENT with this statement in the action page, in query "CEP_Survey_Responses":

SELECT scope_identity() AS ident

... but apparently ColdFusion does not accept my definition.

What am I missing here? I'm close to finishing this application, I am just stuck on this one part.

Also, I'm very grateful for any advice you have to streamline or optimize the application. If anyone has time.

Thanks as always.

<!--- details and notes about this application --->
Task: set up a simple survey for for the Center on Education Policy (CEP).
Web page should validate XHTML.
DB: MS SQL Server 2005
Two tables:
CEP_Survey_Affiliation -- lists the 24 kinds of affiliation that survey respondees can select
CEP_Survey_Responses -- contains survey responses; lists variables for survey responses
ODBC Interpreter: ColdFusion 8
action page: data_insert.cfm
<!--- /details and notes --->
 <!--- this query, Get_CEP_Survey_Affiliation, is used to output the CEP Survey Affiliation options in form, below --->
<cfquery datasource="ebwebwork" dbname="ebwebwork" name="Get_CEP_Survey_Affiliation">
SELECT affiliationID, affiliation
FROM CEP_Survey_Affiliation
  <!--- include header --->
<cfinclude template="/cep/cep_header.cfm" />
 <!--- After the user submits the Form, she sees the information that she sent --->
<cfif IsDefined('SurveyResponseID')>
  <!--- set default values for variables in table CEP_Survey_Responses --->
  <cfparam name="SurveyResponseID" default="" />
  <cfparam name="affiliation" default="" />
  <cfparam name="AwareCEPAnnualReportsStateTests" default="" />
  <cfparam name="informationcontainedreportsuseful" default="" />
  <cfparam name="howuseddatacontainedreports" default="" />
  <cfparam name="adviceimprovereports" default="" />
  <cfparam name="awareCEPotherwork" default="" />
  <cfparam name="whatworkawareof" default="" />
  <cfparam name="howimproveworkintheseareas" default="" />
  <cfparam name="DateCreated" default="" />
<!--- Display "success" message to user; display the information that the user submitted in the form --->
  <p>Thank you for taking the time to fill out the survey. We value your advice and have carefully noted your responses. Here is the information you sent:</p>
<p><strong>SurveyResponseID:</strong> #SurveyResponseID#</p>
<cfif affiliation IS NOT ""><p><strong>Affiliation:</strong> #affiliation#</p></cfif>
<cfif AwareCEPAnnualReportsStateTests IS NOT ""><p><strong>Are you aware of CEP's annual reports on state tests?</strong> #AwareCEPAnnualReportsStateTests#</p></cfif>
<cfif informationcontainedreportsuseful IS NOT ""><p><strong>Have you found the information contained in the reports useful?</strong> #informationcontainedreportsuseful#</p></cfif>
<cfif howuseddatacontainedreports IS NOT ""><p><strong>How have you used the information or data contained in the reports?</strong> #howuseddatacontainedreports#</p></cfif>
<cfif adviceimprovereports IS NOT ""><p><strong>Do you have any advice to improve the reports to make them more useful to you or your organization?</strong> #adviceimprovereports#</p></cfif>
<cfif awareCEPotherwork IS NOT ""><p><strong>Are you aware of CEP's other work?</strong> #awareCEPotherwork#</p></cfif>
<cfif whatworkawareof IS NOT ""><p><strong>What work are you aware of?</strong> #whatworkawareof#</p></cfif>
<cfif howimproveworkintheseareas IS NOT ""><p><strong>What can we do to improve our work in these other areas?</strong> #howimproveworkintheseareas#</p></cfif>
<p><strong>Date Submitted:</strong> #DateCreated#</p>
  <!--- If user has not submitted the form, then SurveyResponseID is not defined, and we should display the form to user... ---> 
 <!--- This is a form to populate the CEP Consumer Survey, November 2009, table CEP_Survey_Responses --->
<cfform action="data_insert.cfm" enctype="multipart/form-data">
 <!--- title and introductory text --->
<h1>CEP Consumer Survey, 2009</h1>
<p>Since 2007, the Center on Education Policy has collected and analyzed student test data from all 50 states and has published our findings in a series of reports showing overall trends in test scores and trends in achievement gaps between different groups of students. All of these reports, along with test score data from all the states, appear on CEP's Web site. Thank you for taking the time to fill out the survey. We value your advice.</p>
<h2 class="align-center">Affiliation (indicate all that apply):</h2>
 <!--- output the CEP Survey Affiliation options, from table CEP_Survey_Affiliation --->
<cfoutput query="Get_CEP_Survey_Affiliation">
<div class="left_checkbox">
<cfinput type="checkbox" name="affiliation" id="affiliation#currentRow#" value="#affiliationID#" tabindex="#affiliationID#" />
<div class="right_checkbox">
<div class="clear-both"></div>
<p>Other affiliation? (Enter here) <cfinput type="text" size="30" name="affiliation" value="" tabindex="25"  /></p>
    <div class="clear-both">&nbsp;</div>
    <h2 class="align-center">Are you aware of CEP's annual reports on state tests?</h2>
    <p>Since 2007, CEP has issued annual reports on analyzing state testing data.  These reports include <em>Answering the Question that Matters Most: Has Student Achievement Increased Since No Child Left Behind?</em>; <em>Has Student Achievement Increased Since 2002? State Test Scores Trends Through 2006-07</em>; and the <em>State Test Score Trends Through 2007-08</em> series. <strong>Are you aware of CEP's annual reports on state tests?</strong></p>
<p><cfinput type="radio" name="AwareCEPAnnualReportsStateTests" id="AwareCEPAnnualReportsStateTests1" value="Yes" tabindex="25" /> Yes</p>
<p><cfinput type="radio" name="AwareCEPAnnualReportsStateTests" id="AwareCEPAnnualReportsStateTests2" value="No" tabindex="26" /> No</p>
<h2 class="align-center">If you answered yes:</h2>
         <h3>a. Have you found the information contained in the reports useful?</h3>
<p><cfinput type="radio" name="informationcontainedreportsuseful" id="informationcontainedreportsuseful1" value="Yes" tabindex="27" /> Yes</p>
<p><cfinput type="radio" name="informationcontainedreportsuseful" id="informationcontainedreportsuseful2" value="No" tabindex="28" /> No</p>
<h3>b. How have you used the information or data contained in the reports?</h3>
<textarea rows="5" cols="40" name="howuseddatacontainedreports" tabindex="29"></textarea>
<h3>c. Do you have any advice to improve the reports to make them more useful to you or your organization?</h3>
<textarea  rows="5" cols="40" name="adviceimprovereports" tabindex="30"></textarea>
<h2 class="align-center">Are you aware of CEP's other work?</h2>
	<h3>Are you aware of CEP's other work, such as our studies of high school exit exams, studies of the No Child Left Behind Act, research on school restructuring, or other work?</h3>
<p><cfinput type="radio" name="awareCEPotherwork" id="awareCEPotherwork1" value="Yes" tabindex="31"> Yes</p>
<p><cfinput type="radio" name="awareCEPotherwork" id="awareCEPotherwork2" value="No" tabindex="32"> No</p>
	<h2 class="align-center">If you answered yes:</h2>
<h3>a. What work are you aware of?</h3>
<textarea rows="5" cols="40" name="whatworkawareof" tabindex="33"></textarea>
        <h3>b. What can we do to improve our work in these other areas?</h3>
<textarea rows="5" cols="40" name="howimproveworkintheseareas" tabindex="34"></textarea>
			<p class="align-center"><cfinput type="submit" name="submit" value="Send Survey" tabindex="35" /></p>
      <!--- close CFFORM --->
 <!--- close cfif IsDefined('SurveyResponseID') --->
<!--- include footer --->
<cfinclude template="/cep/cep_footer.cfm" />
action page (data_insert.cfm):
<cfif isDefined('FORM.AwareCEPAnnualReportsStateTests')>
<!--- radio input has been checked and is present in post data --->
<cfset AwareCEPAnnualReportsStateTests = 1>
<cfset AwareCEPAnnualReportsStateTests = 0>
<!--- radio input not checked and not present --->
<cfif isDefined('FORM.informationcontainedreportsuseful')>
<!--- radio input has been checked and is present in post data --->
<cfset informationcontainedreportsuseful = 1>
<cfset informationcontainedreportsuseful = 0>
<!--- radio input not checked and not present --->
<cfif isDefined('FORM.awareCEPotherwork')>
<!--- radio input has been checked and is present in post data --->
<cfset awareCEPotherwork = 1>
<cfset awareCEPotherwork = 0>
<!--- radio input not checked and not present --->
<!--- Insert values into table CEP_Survey_Responses columns --->
<cfquery datasource="ebwebwork" dbname="ebwebwork" name="CEP_Survey_Responses">
	INSERT INTO CEP_Survey_Responses (
		<cfqueryparam cfsqltype="cf_sql_varchar" value="#affiliation#">,
		<cfqueryparam cfsqltype="cf_sql_varchar" value="#AwareCEPAnnualReportsStateTests#">,
		<cfqueryparam cfsqltype="cf_sql_varchar" value="#informationcontainedreportsuseful#">,
		<cfqueryparam cfsqltype="cf_sql_varchar" value="#howuseddatacontainedreports#">,
		<cfqueryparam cfsqltype="cf_sql_varchar" value="#adviceimprovereports#">,
		<cfqueryparam cfsqltype="cf_sql_varchar" value="#awareCEPotherwork#">,
		<cfqueryparam cfsqltype="cf_sql_varchar" value="#whatworkawareof#">,
		<cfqueryparam cfsqltype="cf_sql_varchar" value="#howimproveworkintheseareas#">,
		<cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">);
	SELECT scope_identity() AS ident
<!--- pass the survey responses back to the previous page --->
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
	<title>CEP Consumer Survey Responses Sent</title>
<!--- post inputs from FORM --->
<form name="form" method="post" action="index.cfm">
<!--- set value from newly created SurveyResponseID --->
<input type="hidden" name="SurveyResponseID" value="#SurveyResponseID.ident#">
<!--- set rest of values from submitted form --->
<input type="hidden" name="affiliation" value="#FORM.affiliation#">
<cfif isdefined("form.AwareCEPAnnualReportsStateTests")>
<input type="hidden" name="AwareCEPAnnualReportsStateTests" value="#FORM.AwareCEPAnnualReportsStateTests#">
<input type="hidden" name="AwareCEPAnnualReportsStateTests" value="0">
<cfif isdefined("form.informationcontainedreportsuseful")>
<input type="hidden" name="informationcontainedreportsuseful" value="#FORM.informationcontainedreportsuseful#">
<input type="hidden" name="informationcontainedreportsuseful" value="0">
<input type="hidden" name="howuseddatacontainedreports" value="#FORM.howuseddatacontainedreports#">
<input type="hidden" name="adviceimprovereports" value="#FORM.adviceimprovereports#">
<cfif isdefined("form.awareCEPotherwork")>
<input type="hidden" name="awareCEPotherwork" value="#FORM.awareCEPotherwork#">
<input type="hidden" name="awareCEPotherwork" value="0">
<input type="hidden" name="whatworkawareof" value="#FORM.whatworkawareof#">
<input type="hidden" name="howimproveworkintheseareas" value="#FORM.howimproveworkintheseareas#">
<input type="hidden" name="DateCreated" value="#DateFormat(now(), "mm/dd/yyyy")#">
Join our community to see this answer!
Unlock 1 Answer and 45 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 45 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros