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

Hi Eric:

Two things. I had did a liittle modification inyour code:

here are they

i separated the select statement and put in in a different query after the insert statement so it should carry a relevant value which you do the insert.

i am unable to test it so i assume that can solve the purpose you are trying to do.

Check and post back if something goes wrong.

<cfquery datasource="ebwebwork" dbname="ebwebwork">
	INSERT INTO CEP_Survey_Responses (
		affiliation,
		AwareCEPAnnualReportsStateTests,
		informationcontainedreportsuseful,
		howuseddatacontainedreports,
		adviceimprovereports,
		awareCEPotherwork,
		whatworkawareof,
		howimproveworkintheseareas,
		DateCreated)
	VALUES(
		<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()#">)
</cfquery>
<cfquery datasource="ebwebwork" dbname="ebwebwork" name="CEP_Survey_Responses">
SELECT scope_identity() AS ident from 
CEP_Survey_Responses
</cfquery>
 
<input type="hidden" name="SurveyResponseID" value="#CEP_Survey_Responses.ident#">
 
See how it goes

Select allOpen in new window

myselfrandhawa,

Thank you for this!

OK, I tried out your code in my action file: data_insert.cfm. We got rid of the error. However, it looks like the form does not fully process, and the form submission gets stuck on the action page and never posts the output of the form. Take a look here:

http://ebwebwork.com/cep/consumersurvey/

Question: does that first query need a query name?

<cfquery datasource="ebwebwork" dbname="ebwebwork">

If you have time, please let me know what you think?

Thanks again. I always appreciate your help. Hope you are well.

Eric

action file: data_insert.cfm:
 
 
<cfif isDefined('FORM.AwareCEPAnnualReportsStateTests')>
<!--- radio input has been checked and is present in post data --->
<cfset AwareCEPAnnualReportsStateTests = 1>
<cfelse>
<cfset AwareCEPAnnualReportsStateTests = 0>
<!--- radio input not checked and not present --->
</cfif>
 
<cfif isDefined('FORM.informationcontainedreportsuseful')>
<!--- radio input has been checked and is present in post data --->
<cfset informationcontainedreportsuseful = 1>
<cfelse>
<cfset informationcontainedreportsuseful = 0>
<!--- radio input not checked and not present --->
</cfif>
 
 
<cfif isDefined('FORM.awareCEPotherwork')>
<!--- radio input has been checked and is present in post data --->
<cfset awareCEPotherwork = 1>
<cfelse>
<cfset awareCEPotherwork = 0>
<!--- radio input not checked and not present --->
</cfif>
 
 
<!--- Insert values into table CEP_Survey_Responses columns --->
<cfquery datasource="ebwebwork" dbname="ebwebwork">
	INSERT INTO CEP_Survey_Responses (
		affiliation,
		AwareCEPAnnualReportsStateTests,
		informationcontainedreportsuseful,
		howuseddatacontainedreports,
		adviceimprovereports,
		awareCEPotherwork,
		whatworkawareof,
		howimproveworkintheseareas,
		DateCreated)
	VALUES(
		<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()#">)
</cfquery>
 
 
<cfquery datasource="ebwebwork" dbname="ebwebwork" name="CEP_Survey_Responses">
SELECT scope_identity() AS ident
FROM CEP_Survey_Responses
</cfquery>
 
 
 
		  
<!--- pass the survey responses back to the previous page --->
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
 
<html>
<head>
	<title>Your Consumer Survey Response was sent to CEP</title>
</head>
 
<body>
 
 
 
<!--- post inputs from FORM --->
  
 <cfoutput>
 
<form name="form" method="post" action="index.cfm">
 
<!--- set value from newly created SurveyResponseID --->
<input type="hidden" name="SurveyResponseID" value="#CEP_Survey_Responses.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#">
<cfelse>
<input type="hidden" name="AwareCEPAnnualReportsStateTests" value="0">
</cfif>
 
 
 
<cfif isdefined("form.informationcontainedreportsuseful")>
<input type="hidden" name="informationcontainedreportsuseful" value="#FORM.informationcontainedreportsuseful#">
<cfelse>
<input type="hidden" name="informationcontainedreportsuseful" value="0">
</cfif>
 
 
<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#">
<cfelse>
<input type="hidden" name="awareCEPotherwork" value="0">
</cfif>
 
 
 
 
<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")#">
 
</form>
 </cfoutput>
 
 
 
 
 
 
</body>
</html>

Select allOpen in new window

@ Eric What i noticed is that you post the data to index.cfm page.

and you have defined the insert query in the same page. This is confusing

can u tell me what exactly is happening

I'll try to explain. I've used this code before, to create simple Forms in ColdFusion. I am not sure why the code is not working now. I've been very careful to copy the code exactly. Obviously I am missing something.

There are two files, index.cfm (the form page) and data_insert.cfm (the action page).

Yes indeed, I do post the data to the index.cfm page. That has always worked for me.

If you have a better way to do it, I'm very glad to learn it. Though I like the idea of posting from the index page, back to the index page -- it just seems "cleaner".

Does that explanation help at all?

Thank you again. I really appreciate your time.

Eric

@Eric,

If you are on CF8, I would recommend using the "result" attribute to get the identity value instead.

     <cfquery datasource="ebwebwork" dbname="ebwebwork" name="CEP_Survey_Responses"
               result="yourResultStructureName">
              INSERT INTO .....
     </cfquery>

You can then grab the new identity value from the result structure:

    <cfoutput>
          New Identify value = #yourResultStructureName.IDENTITYCOL#
     </cfoutput>


Also, I would recommend _against_ storing all of the selected #affiliation# values in the same record like this: "18,1,6,18".  Storing data that way makes it really hard to work with.  For example, if you wanted to find all of the responses that selected "State legislator" or "School board member", you couldn't easily do that if you're storing the id's in a single record.

Instead, they should be stored in a separate table. So you can easily query them:

Affiliations:  - Distinct affiliation types
AffiliationID (Unique PK), other columns

SurveyResponses  - Distinct responses
Columns:  SurveyResponseID (Unique PK), other columns ....

SurveyResponsesAffiliation -  One or more Affiliations selected for each response
SurveyResponseID, AffiliationID

Example:  If response #1 selected the first three checkboxes, you would have 3 records in the SurveyResponsesAffiliation  table

SurveyResponseID, AffiliationID
1, 1 (Governor's education advisor)
1, 2 (State legislator)
1, 3 (State legislative staff)

> I would recommend using the "result" attribute to get the identity value instead.

ie ... instead of using "SELECT scope_identity() AS ident" after the INSERT statement.

_agx_,

This makes sense. I am on CF 8. I'm going to work on this and get back to you in a while. Thank you again.

Eric

@Eric,

It would probably look something like the untested code below.  

<!--- Use "0" or some other INVALID value to represent no boxes selected ---->
<cfparam name="form.affiliation" default="0">
 
<!--- Use transaction to prevent phantom records --->
<cftransaction>
 
      <!--- create the response record ...--->
      <cfquery name="CEP_Survey_Responses" result="yourResultStructureName" ...>
           INSERT INTO TheResponseTable (....) VALUES (.....)
      </cfquery>
 
      <!--- then add the selected affiliations under the new Identity value ...--->
      <!--- this will insert 1 record for each affiliation selected ...--->
      <cfquery ... >
         INSERT INTO SurveyResponsesAffiliation ( SurveyResponseID, AffiliationID )
         SELECT #yourResultStructureName.IDENTITYCOL# AS SurveyResponseID
		AffiliationID
	 FROM   CEP_Survey_Affiliation
	 WHERE  AffilationID IN 
		(
		   <cfqueryparam value="#form.Affiliation#" cfsqltype="cf_sql_integer" list="true">
		)
      </cfquery
</cftransaction>

Select allOpen in new window

Still working on this. I've got three tables in MS SQL Server 2005 -- see below. I believe I need to set up relationships among these tables but am not sure how to do that in MS SQL Server 2005: the PKs of the first two tables are Foreign Keys in the third -- correct? Am exploring relationships in the SQL Server Management Studio Express interface. This is just an update on my progress. More in a while....

My three tables:

table: CEP_Survey_Affiliation
columns:
affiliationID (PK, datatype int)
affiliation (datatype nvarchar50)


table: CEP_Survey_Responses
columns:
SurveyResponseID (PK, datatype int)
AwareCEPAnnualReportsStateTests (datatype nvarchar(50))
informationcontainedreportsuseful (datatype nvarchar(50))
howuseddatacontainedreports (datatype nvarchar(MAX))
adviceimprovereports (datatype nvarchar(MAX))
awareCEPotherwork (datatype nvarchar(50))
whatworkawareof (datatype nvarchar(MAX))
howimproveworkintheseareas (datatype nvarchar(MAX))
DateCreated (datatype nvarchar(50))


table: CEP_SurveyResponsesAffiliation
columns:
SurveyResponseID (datatype int)
affiliationID (datatype int)

Sorry, column DateCreated has (datatype DateTime)

=)

> the PKs of the first two tables are Foreign Keys in the third -- correct?

Yes.  Technically, you do not HAVE TO set up relationships to identify these columns as Foreign Keys. ie The query will still work if you don't. They just wont' restrict the column values to valid survey and affiliationID's. But it is a good idea from a db/documentation point of view.

http://technet.microsoft.com/en-us/library/ms175464.aspx

I rarely use the wizards. I prefer using sql to create the constraints.  Assuming the two columns are already primary keys. Something like

CREATE TABLE CEP_SurveyResponsesAffiliation
(
SurveyResponseID INT  CONSTRAINT FK_Survey_SurveyResponseID
          FOREIGN KEY REFERENCES CEP_Survey_Responses (SurveyResponseID),
AffiliationID INT CONSTRAINT FK_Survey_AffiliationID
         FOREIGN KEY REFERENCES CEP_Survey_Affiliation (AffiliationID)
)

That is cool. Working on this.

_agx_

Hmm. I've been looking at data_insert.cfm for a while. Does table CEP_Survey_Responses need a column "affiliation"? As long as table CEP_Survey_Affiliation has column "affiliation", and table CEP_SurveyResponsesAffiliation relates table CEP_Survey_Affiliation to table CEP_Survey_Responses, does table CEP_Survey_Responses need a column "affiliation"?

And I am not sure what to do with the query 2 in data_insert.cfm. Did I set that up correctly?

I'm continuing to look at these tables and this code. Thanks for any advice.

Eric

Tables:
 
table: CEP_Survey_Affiliation
columns:
affiliationID (PK, datatype int)
affiliation (datatype nvarchar50)
 
 
table: CEP_Survey_Responses
columns:
SurveyResponseID (PK, datatype int)
AwareCEPAnnualReportsStateTests (datatype nvarchar(50))
informationcontainedreportsuseful (datatype nvarchar(50))
howuseddatacontainedreports (datatype nvarchar(MAX))
adviceimprovereports (datatype nvarchar(MAX))
awareCEPotherwork (datatype nvarchar(50))
whatworkawareof (datatype nvarchar(MAX))
howimproveworkintheseareas (datatype nvarchar(MAX))
DateCreated (datatype nvarchar(50))
 
 
table: CEP_SurveyResponsesAffiliation
columns:
SurveyResponseID (datatype int)
affiliationID (datatype int)
 
 
 
date_insert.cfm:
 
<cfif isDefined('FORM.AwareCEPAnnualReportsStateTests')>
<!--- radio input has been checked and is present in post data --->
<cfset AwareCEPAnnualReportsStateTests = 1>
<cfelse>
<cfset AwareCEPAnnualReportsStateTests = 0>
<!--- radio input not checked and not present --->
</cfif>
 
<cfif isDefined('FORM.informationcontainedreportsuseful')>
<!--- radio input has been checked and is present in post data --->
<cfset informationcontainedreportsuseful = 1>
<cfelse>
<cfset informationcontainedreportsuseful = 0>
<!--- radio input not checked and not present --->
</cfif>
 
 
<cfif isDefined('FORM.awareCEPotherwork')>
<!--- radio input has been checked and is present in post data --->
<cfset awareCEPotherwork = 1>
<cfelse>
<cfset awareCEPotherwork = 0>
<!--- radio input not checked and not present --->
</cfif>
 
 
<!--- Use "0" or some other INVALID value to represent no boxes selected ---->
<cfparam name="form.affiliation" default="0" />
 
 
<!--- Use transaction to prevent phantom records --->
<cftransaction>
 
 <!--- query 1 --->
 
      <!--- create the CEP_Survey_Responses record --->
      <cfquery name="CEP_Survey_Responses" result="ResultStructureName">
         	INSERT INTO CEP_Survey_Responses (
		affiliation,
		AwareCEPAnnualReportsStateTests,
		informationcontainedreportsuseful,
		howuseddatacontainedreports,
		adviceimprovereports,
		awareCEPotherwork,
		whatworkawareof,
		howimproveworkintheseareas,
		DateCreated)
        
        	VALUES(
		<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()#">)
      </cfquery>
 
 <!--- query 2 --->
 
      <!--- then add the selected affiliations under the new Identity value ...--->
      <!--- this will insert 1 record for each affiliation selected ...--->
      <cfquery name="CEP_Survey_Affiliation" result="ResultStructureName" >
         INSERT INTO SurveyResponsesAffiliation ( SurveyResponseID, AffiliationID )
         SELECT #ResultStructureName.IDENTITYCOL# AS SurveyResponseID
		AffiliationID
	 FROM   CEP_Survey_Affiliation
	 WHERE  AffilationID IN 
		(
		   <cfqueryparam value="#form.Affiliation#" cfsqltype="cf_sql_integer" list="true" />
		)
      </cfquery
</cftransaction>
 
 
 
<cfquery datasource="ebwebwork" dbname="ebwebwork" name="CEP_Survey_Responses">
SELECT scope_identity() AS ident
FROM CEP_Survey_Responses
</cfquery>
 
 
 
		  
<!--- pass the survey responses back to the previous page --->
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
 
<html>
<head>
	<title>Your Consumer Survey Response was sent to CEP</title>
</head>
 
<body>
 
 
 
<!--- post inputs from FORM --->
  
 <cfoutput>
 
<form name="form" method="post" action="index.cfm">
 
<!--- set value from newly created SurveyResponseID --->
<input type="hidden" name="SurveyResponseID" value="#CEP_Survey_Responses.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#">
<cfelse>
<input type="hidden" name="AwareCEPAnnualReportsStateTests" value="0">
</cfif>
 
 
 
<cfif isdefined("form.informationcontainedreportsuseful")>
<input type="hidden" name="informationcontainedreportsuseful" value="#FORM.informationcontainedreportsuseful#">
<cfelse>
<input type="hidden" name="informationcontainedreportsuseful" value="0">
</cfif>
 
 
<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#">
<cfelse>
<input type="hidden" name="awareCEPotherwork" value="0">
</cfif>
 
 
 
 
<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")#">
 
</form>
 </cfoutput>
 
 
 
 
 
 
</body>
</html>

Select allOpen in new window

> Does table CEP_Survey_Responses need a column "affiliation"

   No. You're right. It doesn't need an "affiliation" column because they already stored in the new
   table: "CEP_SurveyResponsesAffiliation".  

> And I am not sure what to do with the query 2 in data_insert.cfm. Did I set that up correctly?

      Yes, though I think you're missing a closing ">" on the </cfquery> tag.

      Keep in mind the second query is just a shortcut way of writing multiple INSERTS.  You could just
      loop through the list of id's in #form.affiliation# and do a separate INSERT for each affiliate id like this:

      INSERT INTO SurveyResponsesAffiliation (SurveyResponseID, AffiliationID ) VALUES (4, 18)
      INSERT INTO SurveyResponsesAffiliation (SurveyResponseID, AffiliationID ) VALUES (4, 19)
      INSERT INTO SurveyResponsesAffiliation (SurveyResponseID, AffiliationID ) VALUES (4, 20)
      INSERT INTO SurveyResponsesAffiliation (SurveyResponseID, AffiliationID ) VALUES (4, 22)
 
      But instead a shorter way is to use an INSERT INTO/SELECT.   The SELECT statement returns all
      of the id's in your #form.affiliation# list and the INSERT INTO inserts them directly into your second table.  

      Try it without the INSERT INTO first ... just so you can see what's happening and get a feel for how it works.
      Just don't forget to add it back, or the affiliations won't be saved ;-)

       <!---- This is what will be added to the  CEP_Survey_Affiliation table ---->
       <cfquery ...>
         SELECT #ResultStructureName.IDENTITYCOL# AS SurveyResponseID
                  AffiliationID
       FROM   CEP_Survey_Affiliation
       WHERE  AffilationID IN
            (
               <cfqueryparam value="#form.Affiliation#" cfsqltype="cf_sql_integer" list="true" />
            )
      </cfquery



> <cfquery datasource="ebwebwork" dbname="ebwebwork" name="CEP_Survey_Responses">
>  SELECT scope_identity() AS ident

        BTW: you do not need that query anymore.  You already have the new identity value in
        the result structure

_agx_, still working on this. I had to switch over to another task for a while. I'll respond soon with my latest work. Thank you again. Hope you are well.

eric

_agx_

I've been working with data_insert.cfm for a while. I got rid of column "affiliation" from table CEP_Survey_Responses.

I understand that I don't need to relate the tables in MS SQL Server ... for now, I will hold back on doing that unless you think I should proceed.

I set up my second query which populates table SurveyResponsesAffiliation. I'm getting an error and I think the problem is this SQL statement: the WHERE clause looks unfinished?

       WHERE  AffilationID IN
     
     Should the be:
     
     WHERE  AffilationID IN SurveyResponseID
     
Also, we have queries that populate tables CEP_Survey_Responses and CEP_SurveyResponsesAffiliation .... but do we need a query to populate table CEP_Survey_Affiliation as well?

I really appreciate your time. I've been looking at this code for a long time to figure it out on my own. as much as possible.

Eric


Three tables:

table: CEP_Survey_Affiliation
columns:
affiliationID (PK, datatype int)
affiliation (datatype nvarchar50)


table: CEP_Survey_Responses
columns:
SurveyResponseID (PK, datatype int)
AwareCEPAnnualReportsStateTests (datatype nvarchar(50))
informationcontainedreportsuseful (datatype nvarchar(50))
howuseddatacontainedreports (datatype nvarchar(MAX))
adviceimprovereports (datatype nvarchar(MAX))
awareCEPotherwork (datatype nvarchar(50))
whatworkawareof (datatype nvarchar(MAX))
howimproveworkintheseareas (datatype nvarchar(MAX))
DateCreated (datatype nvarchar(50))


table: CEP_SurveyResponsesAffiliation
columns:
SurveyResponseID (datatype int)
affiliationID (datatype int)

 
data_insert.cfm:
 
<cfif isDefined('FORM.AwareCEPAnnualReportsStateTests')>
<!--- radio input has been checked and is present in post data --->
<cfset AwareCEPAnnualReportsStateTests = 1>
<cfelse>
<cfset AwareCEPAnnualReportsStateTests = 0>
<!--- radio input not checked and not present --->
</cfif>
 
<cfif isDefined('FORM.informationcontainedreportsuseful')>
<!--- radio input has been checked and is present in post data --->
<cfset informationcontainedreportsuseful = 1>
<cfelse>
<cfset informationcontainedreportsuseful = 0>
<!--- radio input not checked and not present --->
</cfif>
 
 
<cfif isDefined('FORM.awareCEPotherwork')>
<!--- radio input has been checked and is present in post data --->
<cfset awareCEPotherwork = 1>
<cfelse>
<cfset awareCEPotherwork = 0>
<!--- radio input not checked and not present --->
</cfif>
 
 
<!--- Use "0" or some other INVALID value to represent no boxes selected ---->
<cfparam name="form.affiliation" default="0" />
 
 
<!--- Use transaction to prevent phantom records --->
<cftransaction>
 
      <!--- create the CEP_Survey_Responses record --->
      <cfquery name="CEP_Survey_Responses" result="ResultStructureName" datasource="ebwebwork">
         	INSERT INTO CEP_Survey_Responses (
		AwareCEPAnnualReportsStateTests,
		informationcontainedreportsuseful,
		howuseddatacontainedreports,
		adviceimprovereports,
		awareCEPotherwork,
		whatworkawareof,
		howimproveworkintheseareas,
		DateCreated)
        
        	VALUES(
		<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()#">)
      </cfquery>
 
      <!--- then add the selected affiliations under the new Identity value ...--->
      <!--- this will insert 1 record for each affiliation selected ...--->
      <cfquery name="Survey_Responses_Affiliation" result="ResultStructureName" datasource="ebwebwork">
         INSERT INTO CEP_SurveyResponsesAffiliation ( SurveyResponseID, AffiliationID )
         SELECT #ResultStructureName.IDENTITYCOL# AS SurveyResponseID
		AffiliationID
	 FROM   CEP_Survey_Affiliation
	 WHERE  AffilationID IN 
		(
		   <cfqueryparam value="#form.Affiliation#" cfsqltype="cf_sql_integer" list="true" />
		)
      </cfquery>
</cftransaction>
 
 
 
 
		  
<!--- pass the survey responses back to the previous page --->
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
 
<html>
<head>
	<title>Your Consumer Survey Response was sent to CEP</title>
</head>
 
<body>
 
 
 
<!--- post inputs from FORM --->
  
 <cfoutput>
 
<form name="form" method="post" action="index.cfm">
 
<!--- set value from newly created SurveyResponseID --->
<input type="hidden" name="SurveyResponseID" value="#CEP_Survey_Responses.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#">
<cfelse>
<input type="hidden" name="AwareCEPAnnualReportsStateTests" value="0">
</cfif>
 
 
 
<cfif isdefined("form.informationcontainedreportsuseful")>
<input type="hidden" name="informationcontainedreportsuseful" value="#FORM.informationcontainedreportsuseful#">
<cfelse>
<input type="hidden" name="informationcontainedreportsuseful" value="0">
</cfif>
 
 
<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#">
<cfelse>
<input type="hidden" name="awareCEPotherwork" value="0">
</cfif>
 
 
 
 
<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")#">
 
</form>
 </cfoutput>
 
 
</body>
</html>

Select allOpen in new window

No problem.  

No, the statement should be  WHERE  AffilationID IN ( ... list of id's from form field ...).  Can you post the error message and also dump the value of the form field, right before that statement?

<cfdump var="#form.Affiliation#">

Got it. OK, I will take a stab at this.

WHERE  AffilationID IN

I understand that you are using the IN operator to constrain query results to the affiliations. So my syntax should be:

       WHERE  AffilationID IN
           ('AwareCEPAnnualReportsStateTests',
            'informationcontainedreportsuseful',
            'howuseddatacontainedreports',
            'adviceimprovereports',
            'awareCEPotherwork',
            'whatworkawareof',
            'howimproveworkintheseareas')

I got this syntax from reviewing the IN operator in my Forta SQL book. Both Forta and you are making sense. =)

When I use this syntax in data_insert.cfm, I get this error when I process the form:

 Invalid data '' for CFSQLTYPE CF_SQL_INTEGER.
 
The error occurred in C:\websites\ebwebwork.com\cep\consumersurvey\data_insert.cfm: line 73

71 :      
72 :             (
73 :                <cfqueryparam value="#form.Affiliation#" cfsqltype="cf_sql_integer" list="true" />
74 :             )
75 :       </cfquery>

Next, per your note, I added:

<cfdump var="#form.Affiliation#">

right before the WHERE statement, like this:

      <cfquery name="Survey_Responses_Affiliation" result="ResultStructureName" datasource="ebwebwork">
         INSERT INTO SurveyResponsesAffiliation ( SurveyResponseID, AffiliationID )
         SELECT #ResultStructureName.IDENTITYCOL# AS SurveyResponseID
            AffiliationID
       FROM   CEP_Survey_Affiliation

<cfdump var="#form.Affiliation#">

       WHERE  AffilationID IN  ('AwareCEPAnnualReportsStateTests',
            'informationcontainedreportsuseful',
            'howuseddatacontainedreports',
            'adviceimprovereports',
            'awareCEPotherwork',
            'whatworkawareof',
            'howimproveworkintheseareas')
     
            (
               <cfqueryparam value="#form.Affiliation#" cfsqltype="cf_sql_integer" list="true" />
            )
      </cfquery>

... but I still get the same error as above. I get the feeling that CF does not like this syntax:

<cfqueryparam value="#form.Affiliation#" cfsqltype="cf_sql_integer" list="true" />

Though that syntax looks familiar to me, and looks OK. So that is curious.

I'm also wondering, in data_insert.cfm, we have queries that populate tables CEP_Survey_Responses and CEP_SurveyResponsesAffiliation .... but do we need a query to populate table CEP_Survey_Affiliation as well?

Thanks again for your time.

Eric

data_insert.cfm
 
<cfif isDefined('FORM.AwareCEPAnnualReportsStateTests')>
<!--- radio input has been checked and is present in post data --->
<cfset AwareCEPAnnualReportsStateTests = 1>
<cfelse>
<cfset AwareCEPAnnualReportsStateTests = 0>
<!--- radio input not checked and not present --->
</cfif>
 
<cfif isDefined('FORM.informationcontainedreportsuseful')>
<!--- radio input has been checked and is present in post data --->
<cfset informationcontainedreportsuseful = 1>
<cfelse>
<cfset informationcontainedreportsuseful = 0>
<!--- radio input not checked and not present --->
</cfif>
 
 
<cfif isDefined('FORM.awareCEPotherwork')>
<!--- radio input has been checked and is present in post data --->
<cfset awareCEPotherwork = 1>
<cfelse>
<cfset awareCEPotherwork = 0>
<!--- radio input not checked and not present --->
</cfif>
 
 
<!--- Use "0" or some other INVALID value to represent no boxes selected ---->
<cfparam name="form.affiliation" default="0" />
 
 
<!--- Use transaction to prevent phantom records --->
<cftransaction>
 
      <!--- create the CEP_Survey_Responses record --->
      <cfquery name="CEP_Survey_Responses" result="ResultStructureName" datasource="ebwebwork">
         	INSERT INTO CEP_Survey_Responses (
		AwareCEPAnnualReportsStateTests,
		informationcontainedreportsuseful,
		howuseddatacontainedreports,
		adviceimprovereports,
		awareCEPotherwork,
		whatworkawareof,
		howimproveworkintheseareas,
		DateCreated)
        
        	VALUES(
		<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()#">)
      </cfquery>
 
      <!--- then add the selected affiliations under the new Identity value ...--->
      <!--- this will insert 1 record for each affiliation selected ...--->
      <cfquery name="Survey_Responses_Affiliation" result="ResultStructureName" datasource="ebwebwork">
         INSERT INTO SurveyResponsesAffiliation ( SurveyResponseID, AffiliationID )
         SELECT #ResultStructureName.IDENTITYCOL# AS SurveyResponseID
		AffiliationID
	 FROM   CEP_Survey_Affiliation
<cfdump var="#form.Affiliation#">
	 WHERE  AffilationID IN  ('AwareCEPAnnualReportsStateTests',
		'informationcontainedreportsuseful',
		'howuseddatacontainedreports',
		'adviceimprovereports',
		'awareCEPotherwork',
		'whatworkawareof',
		'howimproveworkintheseareas')
     
		(
		   <cfqueryparam value="#form.Affiliation#" cfsqltype="cf_sql_integer" list="true" />
		)
      </cfquery>
</cftransaction>
 
 
 
 
		  
<!--- pass the survey responses back to the previous page --->
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
 
<html>
<head>
	<title>Your Consumer Survey Response was sent to CEP</title>
</head>
 
<body>
 
 
 
<!--- post inputs from FORM --->
  
 <cfoutput>
 
<form name="form" method="post" action="index.cfm">
 
<!--- set value from newly created SurveyResponseID --->
<input type="hidden" name="SurveyResponseID" value="#CEP_Survey_Responses.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#">
<cfelse>
<input type="hidden" name="AwareCEPAnnualReportsStateTests" value="0">
</cfif>
 
 
 
<cfif isdefined("form.informationcontainedreportsuseful")>
<input type="hidden" name="informationcontainedreportsuseful" value="#FORM.informationcontainedreportsuseful#">
<cfelse>
<input type="hidden" name="informationcontainedreportsuseful" value="0">
</cfif>
 
 
<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#">
<cfelse>
<input type="hidden" name="awareCEPotherwork" value="0">
</cfif>
 
 
 
 
<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")#">
 
</form>
 </cfoutput>
 
 
 
 
 
 
</body>
</html>

Select allOpen in new window

Eric:

I think you should revise this staement. Di u think u are doing it right way, i think not

SELECT #ResultStructureName.IDENTITYCOL# AS SurveyResponseID
		AffiliationID
	 FROM   CEP_Survey_Affiliation
<cfdump var="#form.Affiliation#">
	 WHERE  AffilationID IN  ('AwareCEPAnnualReportsStateTests',
		'informationcontainedreportsuseful',
		'howuseddatacontainedreports',
		'adviceimprovereports',
		'awareCEPotherwork',
		'whatworkawareof',
		'howimproveworkintheseareas')
     
		(
		   <cfqueryparam value="#form.Affiliation#" cfsqltype="cf_sql_integer" list="true" />
		)

Select allOpen in new window

myselfrandhawa,

Hmmm. Well, there's obviously a problem with the statement since it's causing an error. I'm thinking about it. =) I will see what else I can come up with.

Thanks as always.

Eric

>   WHERE  AffilationID IN  ('AwareCEPAnnualReportsStateTests',
>            'informationcontainedreportsuseful',
> ....     'howimproveworkintheseareas')
>     
>      </cfquery>

Also, that part should not be in the query. Notice, how my query only contains this:

          FROM     CEP_Survey_Affiliation
          WHERE  AffilationID IN
            (
               <cfqueryparam value="#form.Affiliation#" cfsqltype="cf_sql_integer" list="true" />
            )

I see. OK, I am working on this.

@Eric,

BTW - The INSERT INTO/SELECT ... WHERE IN (...) syntax takes a bit of getting used to at first.  But once you get it down, you'll like it.  

Conceptually, a multiple insert loop like this is easier to understand.  That's probably why most people use it.

<cfloop list="#listOfNumericIDs#" index="currentIDValue">
     <cfquery>
          INSERT INTO SomeTable ( SomeNumericIDColumn )
          VALUES ( #currentIDValue#  )
     </cfquery>
</cfloop>


But the INSERT INTO ... method you are using now is far more efficient.

OK, making progress. I've been thinking about this problem and I do understand that my previous query code was redundant. I like the   (
               <cfqueryparam value="#form.Affiliation#" cfsqltype="cf_sql_integer" list="true" />
            )
           
            which means I can omit listing the variables / column names ... that's elegant.
           
In index.cfm, I commented out

 <!--- <cfinput type="checkbox" name="affiliation" id="affiliation#currentRow#" value="#affiliationID#" tabindex="#affiliationID#" /> --->
 
 and replaced it with:
 
 <cfinput type="checkbox" name="affiliation" value="#affiliationID#" tabindex="#affiliationID#">


I understand that the query expects an integer, and value="#affiliationID#" supplies the integer as expected.

Rats. I am still getting the error:

 Invalid data '' for CFSQLTYPE CF_SQL_INTEGER.
 
The error occurred in C:\websites\ebwebwork.com\cep\consumersurvey\data_insert.cfm: line 68

66 :        WHERE  AffilationID IN
67 :             (
68 :                <cfqueryparam value="#form.Affiliation#" cfsqltype="cf_sql_integer" list="true" />
69 :             )
70 :       </cfquery>

I understand that the problem is, I have not supplied an integer, as required by cfsqltype="cf_sql_integer".

Hmmm. What have I missed? I have failed to give an integer to CF. =) thanks for any advice.

I repeat, below, all of my code and the three tables. Thank you again.

Eric

Three tables:
 
table: CEP_Survey_Affiliation
columns:
affiliationID (PK, datatype int)
affiliation (datatype nvarchar50)
 
 
table: CEP_Survey_Responses
columns:
SurveyResponseID (PK, datatype int)
AwareCEPAnnualReportsStateTests (datatype nvarchar(50))
informationcontainedreportsuseful (datatype nvarchar(50))
howuseddatacontainedreports (datatype nvarchar(MAX))
adviceimprovereports (datatype nvarchar(MAX))
awareCEPotherwork (datatype nvarchar(50))
whatworkawareof (datatype nvarchar(MAX))
howimproveworkintheseareas (datatype nvarchar(MAX))
DateCreated (datatype nvarchar(50))
 
 
table: CEP_SurveyResponsesAffiliation
columns:
SurveyResponseID (datatype int)
affiliationID (datatype int)
 
 
 
 
index.cfm:
<!--- 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
 
URL: http://ebwebwork.com/cep/consumersurvey/
 
--->
 
<!--- /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
</cfquery>
 
  
  
  <!--- 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>
  
<cfoutput>
<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>
  
</cfoutput>
 
 
  
  <!--- If user has not submitted the form, then SurveyResponseID is not defined, and we should display the form to user... ---> 
 
<cfelse>
 
 
 
 <!--- 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" value="#affiliationID#" tabindex="#affiliationID#">
 
 <!--- <cfinput type="checkbox" name="affiliation" id="affiliation#currentRow#" value="#affiliationID#" tabindex="#affiliationID#" /> --->
 
 
</div> 
                
<div class="right_checkbox">
#affiliation#
</div> 
 
 
<div class="clear-both"></div>
 
</cfoutput>
 
 
 
<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 --->
</cfform>
 
 
 <!--- close cfif IsDefined('SurveyResponseID') --->
 
</cfif> 
 
 
<!--- include footer --->
<cfinclude template="/cep/cep_footer.cfm" />
 
 
 
 
 
data_insert.cfm:
 
 
 
<cfif isDefined('FORM.AwareCEPAnnualReportsStateTests')>
<!--- radio input has been checked and is present in post data --->
<cfset AwareCEPAnnualReportsStateTests = 1>
<cfelse>
<cfset AwareCEPAnnualReportsStateTests = 0>
<!--- radio input not checked and not present --->
</cfif>
 
<cfif isDefined('FORM.informationcontainedreportsuseful')>
<!--- radio input has been checked and is present in post data --->
<cfset informationcontainedreportsuseful = 1>
<cfelse>
<cfset informationcontainedreportsuseful = 0>
<!--- radio input not checked and not present --->
</cfif>
 
 
<cfif isDefined('FORM.awareCEPotherwork')>
<!--- radio input has been checked and is present in post data --->
<cfset awareCEPotherwork = 1>
<cfelse>
<cfset awareCEPotherwork = 0>
<!--- radio input not checked and not present --->
</cfif>
 
 
<!--- Use "0" or some other INVALID value to represent no boxes selected ---->
<cfparam name="form.affiliation" default="0" />
 
 
<!--- Use transaction to prevent phantom records --->
<cftransaction>
 
      <!--- create the CEP_Survey_Responses record --->
      <cfquery name="CEP_Survey_Responses" result="ResultStructureName" datasource="ebwebwork">
         	INSERT INTO CEP_Survey_Responses (
		AwareCEPAnnualReportsStateTests,
		informationcontainedreportsuseful,
		howuseddatacontainedreports,
		adviceimprovereports,
		awareCEPotherwork,
		whatworkawareof,
		howimproveworkintheseareas,
		DateCreated)
        
        	VALUES(
		<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()#">)
      </cfquery>
 
      <!--- then add the selected affiliations under the new Identity value ...--->
      <!--- this will insert 1 record for each affiliation selected ...--->
      <cfquery name="Survey_Responses_Affiliation" result="ResultStructureName" datasource="ebwebwork">
       INSERT INTO CEP_SurveyResponsesAffiliation ( SurveyResponseID, AffiliationID )
       SELECT #ResultStructureName.IDENTITYCOL# AS SurveyResponseID,
                    AffiliationID
       FROM   CEP_Survey_Affiliation
       WHERE  AffilationID IN
            (
               <cfqueryparam value="#form.Affiliation#" cfsqltype="cf_sql_integer" list="true" />
            )
      </cfquery>
</cftransaction>
 
 
 
 
		  
<!--- pass the survey responses back to the previous page --->
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
 
<html>
<head>
	<title>Your Consumer Survey Response was sent to CEP</title>
</head>
 
<body>
 
 
 
<!--- post inputs from FORM --->
  
 <cfoutput>
 
<form name="form" method="post" action="index.cfm">
 
<!--- set value from newly created SurveyResponseID --->
<input type="hidden" name="SurveyResponseID" value="#CEP_Survey_Responses.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#">
<cfelse>
<input type="hidden" name="AwareCEPAnnualReportsStateTests" value="0">
</cfif>
 
 
 
<cfif isdefined("form.informationcontainedreportsuseful")>
<input type="hidden" name="informationcontainedreportsuseful" value="#FORM.informationcontainedreportsuseful#">
<cfelse>
<input type="hidden" name="informationcontainedreportsuseful" value="0">
</cfif>
 
 
<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#">
<cfelse>
<input type="hidden" name="awareCEPotherwork" value="0">
</cfif>
 
 
 
 
<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")#">
 
</form>
 </cfoutput>
 
 
 
 
 
 
</body>
</html>

Select allOpen in new window

Well, it looks okay to me. But the only way to figure out why cfquery is complaining is to dump the FORM fields on the action page to see what it _is_ receiving ;-)


Do a dump before both of the queries. What is the value of  form.affiliation?


<cfparam name="form.affiliation" default="0" />
<cfdump var="#FORM#">                                       <!---- add the dump here. before the cfquery's ... --->
 .....

> <p>Other affiliation? (Enter here) <cfinput type="text" size="30" name="affiliation" value="" tabindex="25"  /></p>

Hmm.. I just noticed your form contains another text field also named "affiliation".  That will cause problems because the values of the checkboxes and this text field will merge together when the form is submitted.  

You need to give the field a different name.  

   Other affiliation? (Enter here) <cfinput type="text" size="30" name="otherAffiliation" value=""> ....

Then decide how to store it in your tables.  Since it's a single value, you could add it to your main survey table:

CEP_Survey_Responses
OtherAffiliation varchar(100)

I saw this too and was thinking about it.

Still working on your last comment.

_agx_,

I believe I have corrected the Other Affiliation problem. I added, to table CEP_Survey_Responses, this column:

OtherAffiliation varchar(50)

(50 should be enough.)

I added, before the two queries, this code:

<cfparam name="form.affiliation" default="0" />
<cfdump var="#FORM#">  

... and I get an interesting result (see below). I believe my index.cfm and data_insert.cfm are not yet correct. This error:

Invalid column name 'AffilationID'.

strikes me as weird b/c I definitely do have a column 'AffilationID' in table CEP_Survey_Affiliation.

Is this cfdump information useful to us? Thanks again.

Eric

struct
ADVICEIMPROVEREPORTS       [empty string]
AFFILIATION       0
FIELDNAMES       OTHERAFFILIATION,HOWUSEDDATACONTAINEDREPORTS,ADVICEIMPROVEREPORTS,WHATWORKAWAREOF,HOWIMPROVEWORKINTHESEAREAS,SUBMIT
HOWIMPROVEWORKINTHESEAREAS       [empty string]
HOWUSEDDATACONTAINEDREPORTS       [empty string]
OTHERAFFILIATION       [empty string]
SUBMIT       Send Survey
WHATWORKAWAREOF       [empty string]

The web site you are accessing has experienced an unexpected error.
Please contact the website administrator.

The following information is meant for the website developer for debugging purposes.
Error Occurred While Processing Request
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Invalid column name 'AffilationID'.
 
The error occurred in C:\websites\ebwebwork.com\cep\consumersurvey\data_insert.cfm: line 72

70 :        WHERE  AffilationID IN
71 :             (
72 :                <cfqueryparam value="#form.Affiliation#" cfsqltype="cf_sql_integer" list="true" />
73 :             )
74 :       </cfquery>

data_insert.cfm
 
 
 
<cfif isDefined('FORM.AwareCEPAnnualReportsStateTests')>
<!--- radio input has been checked and is present in post data --->
<cfset AwareCEPAnnualReportsStateTests = 1>
<cfelse>
<cfset AwareCEPAnnualReportsStateTests = 0>
<!--- radio input not checked and not present --->
</cfif>
 
<cfif isDefined('FORM.informationcontainedreportsuseful')>
<!--- radio input has been checked and is present in post data --->
<cfset informationcontainedreportsuseful = 1>
<cfelse>
<cfset informationcontainedreportsuseful = 0>
<!--- radio input not checked and not present --->
</cfif>
 
 
<cfif isDefined('FORM.awareCEPotherwork')>
<!--- radio input has been checked and is present in post data --->
<cfset awareCEPotherwork = 1>
<cfelse>
<cfset awareCEPotherwork = 0>
<!--- radio input not checked and not present --->
</cfif>
 
 
<!--- Use "0" or some other INVALID value to represent no boxes selected ---->
<cfparam name="form.affiliation" default="0" />
 
 
<!--- Use transaction to prevent phantom records --->
<cftransaction>
 
 
<cfparam name="form.affiliation" default="0" />
<cfdump var="#FORM#">   
 
      <!--- create the CEP_Survey_Responses record --->
      <cfquery name="CEP_Survey_Responses" result="ResultStructureName" datasource="ebwebwork">
         	INSERT INTO CEP_Survey_Responses (
		AwareCEPAnnualReportsStateTests,
		informationcontainedreportsuseful,
		howuseddatacontainedreports,
		adviceimprovereports,
		awareCEPotherwork,
		whatworkawareof,
		howimproveworkintheseareas,
		DateCreated)
        
        	VALUES(
		<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()#">)
      </cfquery>
 
      <!--- then add the selected affiliations under the new Identity value ...--->
      <!--- this will insert 1 record for each affiliation selected ...--->
      <cfquery name="Survey_Responses_Affiliation" result="ResultStructureName" datasource="ebwebwork">
       INSERT INTO CEP_SurveyResponsesAffiliation ( SurveyResponseID, AffiliationID )
       SELECT #ResultStructureName.IDENTITYCOL# AS SurveyResponseID,
                    AffiliationID
       FROM   CEP_Survey_Affiliation
       WHERE  AffilationID IN
            (
               <cfqueryparam value="#form.Affiliation#" cfsqltype="cf_sql_integer" list="true" />
            )
      </cfquery>
</cftransaction>
 
 
 
 
		  
<!--- pass the survey responses back to the previous page --->
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
 
<html>
<head>
	<title>Your Consumer Survey Response was sent to CEP</title>
</head>
 
<body>
 
 
 
<!--- post inputs from FORM --->
  
 <cfoutput>
 
<form name="form" method="post" action="index.cfm">
 
<!--- set value from newly created SurveyResponseID --->
<input type="hidden" name="SurveyResponseID" value="#CEP_Survey_Responses.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#">
<cfelse>
<input type="hidden" name="AwareCEPAnnualReportsStateTests" value="0">
</cfif>
 
 
 
<cfif isdefined("form.informationcontainedreportsuseful")>
<input type="hidden" name="informationcontainedreportsuseful" value="#FORM.informationcontainedreportsuseful#">
<cfelse>
<input type="hidden" name="informationcontainedreportsuseful" value="0">
</cfif>
 
 
<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#">
<cfelse>
<input type="hidden" name="awareCEPotherwork" value="0">
</cfif>
 
 
 
 
<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")#">
 
</form>
 </cfoutput>
 
 
 
 
 
 
</body>
</html>

Select allOpen in new window

> AffilationID

Oops. That's a typo on my part.  The actual column in your table is probably "AffiliationID"

Of course. I see.

Sorry :)  The last few responses I was more focused on "concept" than copy and paste.  So some typos slipped into my adhoc typing..

Progress. Fixing that typo fixed the error.

Um, I'm usually pretty good at seeing typos. Used to be an editor. Anyway, I feel sheepish. =)

My next task is to correctly define the variable "SurveyResponseID". Currently, variable is defined in "ident":

<input type="hidden" name="SurveyResponseID" value="#CEP_Survey_Responses.ident#">

But CF objects to this, and gives me the following error:

 Element IDENT is undefined in CEP_SURVEY_RESPONSES.
 
The error occurred in C:\websites\ebwebwork.com\cep\consumersurvey\data_insert.cfm: line 100

98 :  
99 : <!--- set value from newly created SurveyResponseID --->
100 : <input type="hidden" name="SurveyResponseID" value="#CEP_Survey_Responses.ident#">
101 :
102 : <!--- set rest of values from submitted form --->

I am reading back through everything you have told me, above -- I think I need to define variable in IDENTITYCOL rather than ident. I will try that.

Thank you again for your help. Hope you are well.

Eric

data_insert.cfm
 
(I notice that <cfparam name="form.affiliation" default="0" /> occurs twice -- I don't think I need it twice?)
 
<cfif isDefined('FORM.AwareCEPAnnualReportsStateTests')>
<!--- radio input has been checked and is present in post data --->
<cfset AwareCEPAnnualReportsStateTests = 1>
<cfelse>
<cfset AwareCEPAnnualReportsStateTests = 0>
<!--- radio input not checked and not present --->
</cfif>
 
<cfif isDefined('FORM.informationcontainedreportsuseful')>
<!--- radio input has been checked and is present in post data --->
<cfset informationcontainedreportsuseful = 1>
<cfelse>
<cfset informationcontainedreportsuseful = 0>
<!--- radio input not checked and not present --->
</cfif>
 
 
<cfif isDefined('FORM.awareCEPotherwork')>
<!--- radio input has been checked and is present in post data --->
<cfset awareCEPotherwork = 1>
<cfelse>
<cfset awareCEPotherwork = 0>
<!--- radio input not checked and not present --->
</cfif>
 
 
<!--- Use "0" or some other INVALID value to represent no boxes selected ---->
<cfparam name="form.affiliation" default="0" />
 
 
<!--- Use transaction to prevent phantom records --->
<cftransaction>
 
 
<cfparam name="form.affiliation" default="0" />
 
 
<cfdump var="#FORM#">   
 
      <!--- create the CEP_Survey_Responses record --->
      <cfquery name="CEP_Survey_Responses" result="ResultStructureName" datasource="ebwebwork">
         	INSERT INTO CEP_Survey_Responses (
		AwareCEPAnnualReportsStateTests,
		informationcontainedreportsuseful,
		howuseddatacontainedreports,
		adviceimprovereports,
		awareCEPotherwork,
		whatworkawareof,
		howimproveworkintheseareas,
		DateCreated)
        
        	VALUES(
		<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()#">)
      </cfquery>
 
      <!--- then add the selected affiliations under the new Identity value ...--->
      <!--- this will insert 1 record for each affiliation selected ...--->
      <cfquery name="Survey_Responses_Affiliation" result="ResultStructureName" datasource="ebwebwork">
       INSERT INTO CEP_SurveyResponsesAffiliation ( SurveyResponseID, AffiliationID )
       SELECT #ResultStructureName.IDENTITYCOL# AS SurveyResponseID,
                    AffiliationID
       FROM   CEP_Survey_Affiliation
       WHERE  AffiliationID IN
            (
               <cfqueryparam value="#form.Affiliation#" cfsqltype="cf_sql_integer" list="true" />
            )
      </cfquery>
</cftransaction>
 
 
 
 
		  
<!--- pass the survey responses back to the previous page --->
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
 
<html>
<head>
	<title>Your Consumer Survey Response was sent to CEP</title>
</head>
 
<body>
 
 
 
<!--- post inputs from FORM --->
  
 <cfoutput>
 
<form name="form" method="post" action="index.cfm">
 
<!--- set value from newly created SurveyResponseID --->
<input type="hidden" name="SurveyResponseID" value="#CEP_Survey_Responses.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#">
<cfelse>
<input type="hidden" name="AwareCEPAnnualReportsStateTests" value="0">
</cfif>
 
 
 
<cfif isdefined("form.informationcontainedreportsuseful")>
<input type="hidden" name="informationcontainedreportsuseful" value="#FORM.informationcontainedreportsuseful#">
<cfelse>
<input type="hidden" name="informationcontainedreportsuseful" value="0">
</cfif>
 
 
<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#">
<cfelse>
<input type="hidden" name="awareCEPotherwork" value="0">
</cfif>
 
 
 
 
<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")#">
 
</form>
 </cfoutput>
 
 
 
 
 
 
</body>
</html>

Select allOpen in new window

> Um, I'm usually pretty good at seeing typos. Used to be an editor. Anyway, I feel sheepish. =)

As it's my typo, I'm the one that should feel sheepish ;-)

> I think I need to define variable in IDENTITYCOL rather than ident.

Yep.  Since you removed the original query that defined "ident", you just need to replace it with the result structure:

        #ResultStructureName.IDENTITYCOL#

You'll probably need to make a few changes to the confirmation page as well. So you can display the affiliation names, not id's.

> <cfqueryparam cfsqltype="cf_sql_varchar" value="#AwareCEPAnnualReportsStateTests#">,

Also I was curious about the data types of some of the fields.  Isn't form.AwareCEPAnnualReportsStateTests a radio button yes/no? In which case wouldn't it be better stored as a BIT, rather than VARCHAR?  Then you could just make the button values 1 or 0 and use the #YesNoFormat(form.fieldName)# function when you need to display a user friendly value.

Sorry to get back to this task so late. I've was called away to work on other tasks.

>>>Also I was curious about the data types of some of the fields.  Isn't form.AwareCEPAnnualReportsStateTests a radio button yes/no? In which case wouldn't it be better stored as a BIT, rather than VARCHAR?  Then you could just make the button values 1 or 0 and use the #YesNoFormat(form.fieldName)# function when you need to display a user friendly value.

This is true and I was having a similar thought. I'm concerned about the output that the end user will see -- the output of this application needs to be very human readable -- but I will cross that bridge later. =)

I will work on this and get back to you soon. thank you!

Eric

_agx_,

I changed three of the datatypes to "bit" as appropriate, and changed the corresponding radio values to 1 for yes or 0 for no. That does make more sense.

For now I have commented out: <cfdump var="#FORM#"> b/c I don't think we presently need it.

I changed
<!--- set value from newly created SurveyResponseID --->
<input type="hidden" name="SurveyResponseID" value="#CEP_Survey_Responses.ident#">

to

<!--- set value from newly created SurveyResponseID --->
<input type="hidden" name="SurveyResponseID" value="#ResultStructureName.IDENTITYCOL#">

I still get an Undefined error:

 Element IDENTITYCOL is undefined in RESULTSTRUCTURENAME.
 
The error occurred in C:\websites\ebwebwork.com\cep\consumersurvey\data_insert.cfm: line 102

100 :  
101 : <!--- set value from newly created SurveyResponseID --->
102 : <input type="hidden" name="SurveyResponseID" value="#ResultStructureName.IDENTITYCOL#">
103 :
104 : <!--- set rest of values from submitted form --->


Also, I'm not sure what you mean here:

You'll probably need to make a few changes to the confirmation page as well. So you can display the affiliation names, not id's.

<cfqueryparam cfsqltype="cf_sql_varchar" value="#AwareCEPAnnualReportsStateTests#">

Do you mean, in data_insert.cfm, I should replace

<cfif isdefined("form.AwareCEPAnnualReportsStateTests")>
<input type="hidden" name="AwareCEPAnnualReportsStateTests" value="#FORM.AwareCEPAnnualReportsStateTests#">
<cfelse>
<input type="hidden" name="AwareCEPAnnualReportsStateTests" value="0">
</cfif>

with:

<cfqueryparam cfsqltype="cf_sql_varchar" value="#AwareCEPAnnualReportsStateTests#">

?

Thank you as always for your time. Hope you are well.

I repeat, below, current code and the three tables.

Eric


Three tables:

table 1: CEP_SurveyResponsesAffiliation
columns:
SurveyResponseID (datatype int)
affiliationID (datatype int)

table 2: CEP_Survey_Responses
columns:
SurveyResponseID (PK, datatype int)
AwareCEPAnnualReportsStateTests (datatype bit)
informationcontainedreportsuseful (datatype bit)
howuseddatacontainedreports (datatype nvarchar(MAX))
adviceimprovereports (datatype nvarchar(MAX))
awareCEPotherwork (datatype bit)
whatworkawareof (datatype nvarchar(MAX))
howimproveworkintheseareas (datatype nvarchar(MAX))
DateCreated (datatype datetime)

table 3: CEP_Survey_Affiliation
columns:
affiliationID (PK, datatype int)
affiliation (datatype nvarchar(50))

data_insert.cfm:
 
<cfif isDefined('FORM.AwareCEPAnnualReportsStateTests')>
<!--- radio input has been checked and is present in post data --->
<cfset AwareCEPAnnualReportsStateTests = 1>
<cfelse>
<cfset AwareCEPAnnualReportsStateTests = 0>
<!--- radio input not checked and not present --->
</cfif>
 
<cfif isDefined('FORM.informationcontainedreportsuseful')>
<!--- radio input has been checked and is present in post data --->
<cfset informationcontainedreportsuseful = 1>
<cfelse>
<cfset informationcontainedreportsuseful = 0>
<!--- radio input not checked and not present --->
</cfif>
 
 
<cfif isDefined('FORM.awareCEPotherwork')>
<!--- radio input has been checked and is present in post data --->
<cfset awareCEPotherwork = 1>
<cfelse>
<cfset awareCEPotherwork = 0>
<!--- radio input not checked and not present --->
</cfif>
 
 
<!--- Use "0" or some other INVALID value to represent no boxes selected ---->
<cfparam name="form.affiliation" default="0" />
 
 
<!--- Use transaction to prevent phantom records --->
<cftransaction>
 
 
<cfparam name="form.affiliation" default="0" />
 
 
<!--- <cfdump var="#FORM#">  ---> 
 
      <!--- create the CEP_Survey_Responses record --->
      <cfquery name="CEP_Survey_Responses" result="ResultStructureName" datasource="ebwebwork">
         	INSERT INTO CEP_Survey_Responses (
		AwareCEPAnnualReportsStateTests,
		informationcontainedreportsuseful,
		howuseddatacontainedreports,
		adviceimprovereports,
		awareCEPotherwork,
		whatworkawareof,
		howimproveworkintheseareas,
		DateCreated)
        
        	VALUES(
		<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()#">)
      </cfquery>
 
      <!--- then add the selected affiliations under the new Identity value ...--->
      <!--- this will insert 1 record for each affiliation selected ...--->
      <cfquery name="Survey_Responses_Affiliation" result="ResultStructureName" datasource="ebwebwork">
       INSERT INTO CEP_SurveyResponsesAffiliation ( SurveyResponseID, AffiliationID )
       SELECT #ResultStructureName.IDENTITYCOL# AS SurveyResponseID,
                    AffiliationID
       FROM   CEP_Survey_Affiliation
       WHERE  AffiliationID IN
            (
               <cfqueryparam value="#form.Affiliation#" cfsqltype="cf_sql_integer" list="true" />
            )
      </cfquery>
</cftransaction>
 
 
 
 
		  
<!--- pass the survey responses back to the previous page --->
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
 
<html>
<head>
	<title>Your Consumer Survey Response was sent to CEP</title>
</head>
 
<body>
 
 
 
<!--- post inputs from FORM --->
  
 <cfoutput>
 
<form name="form" method="post" action="index.cfm">
 
<!--- set value from newly created SurveyResponseID --->
<input type="hidden" name="SurveyResponseID" value="#ResultStructureName.IDENTITYCOL#">
 
<!--- 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#">
<cfelse>
<input type="hidden" name="AwareCEPAnnualReportsStateTests" value="0">
</cfif>
 
 
 
<cfif isdefined("form.informationcontainedreportsuseful")>
<input type="hidden" name="informationcontainedreportsuseful" value="#FORM.informationcontainedreportsuseful#">
<cfelse>
<input type="hidden" name="informationcontainedreportsuseful" value="0">
</cfif>
 
 
<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#">
<cfelse>
<input type="hidden" name="awareCEPotherwork" value="0">
</cfif>
 
 
 
 
<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")#">
 
</form>
 </cfoutput>
 
 
 
 
 
 
</body>
</html>
 
 
 
 
index.cfm:
<!--- 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
 
Three 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
CEP_SurveyResponsesAffiliation -- relates affiliation to survey response
 
ODBC Interpreter: ColdFusion 8
 
action page: data_insert.cfm
 
URL: http://ebwebwork.com/cep/consumersurvey/ --->
 
<!--- /notes --->
 
 
 <!--- 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
</cfquery>
 
  
  
  <!--- 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="otherAffiliation" 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>
  
<cfoutput>
<p><strong>SurveyResponseID:</strong> #SurveyResponseID#</p>
 
<cfif affiliation IS NOT ""><p><strong>Affiliation:</strong> #otherAffiliation#</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>
  
</cfoutput>
 
 
  
  <!--- If user has not submitted the form, then SurveyResponseID is not defined, and we should display the form to user... ---> 
 
<cfelse>
 
 
 
 <!--- 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="otherAffiliation" value="#affiliationID#" tabindex="#affiliationID#">
 
 <!--- <cfinput type="checkbox" name="affiliation" id="affiliation#currentRow#" value="#affiliationID#" tabindex="#affiliationID#" /> --->
 
 
</div> 
                
<div class="right_checkbox">
#affiliation#
</div> 
 
 
<div class="clear-both"></div>
 
</cfoutput>
 
 
 
<p>Other affiliation? (Enter here) <cfinput type="text" size="30" name="otherAffiliation" 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="1" tabindex="25" /> Yes</p>
 
			
<p><cfinput type="radio" name="AwareCEPAnnualReportsStateTests" id="AwareCEPAnnualReportsStateTests2" value="0" 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="1" tabindex="27" /> Yes</p>
			
<p><cfinput type="radio" name="informationcontainedreportsuseful" id="informationcontainedreportsuseful2" value="0" 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="1" tabindex="31"> Yes</p>
		
<p><cfinput type="radio" name="awareCEPotherwork" id="awareCEPotherwork2" value="0" 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 --->
</cfform>
 
 
 <!--- close cfif IsDefined('SurveyResponseID') --->
 
</cfif> 
 
 
<!--- include footer --->
<cfinclude template="/cep/cep_footer.cfm" />

Select allOpen in new window

> Also, I'm not sure what you mean here:
> You'll probably need to make a few changes

Well, IIRC the original forms were predicated on the original design - where "Affiliation" was text.  So you could just display the form field values directly  (ie #form.affiliation#) and get a list of the affiliation names. Now that the checkboxes store id's I'm thinking you'll need a query to get the "user friendly" affiliation name.  BUT ..  let me review the new code in its entirety.  Just to make sure that still applies!

> Thank you as always for your time. Hope you are well.

You are always welcome! I am doing great and hope your week is going well. Of course it's almost Friday which tends to make everything better ;-)

I really appreciate the time you are taking for this. Please, take your time. Looking forward to the weekend, myself.

Thanks.  I'm interested in getting the whole thing working properly. But today has been hectic, so I probably wont' get to it until later ;-)  Happy Friday!

_agx_,

Hope your hectic Friday resolved well, and that you had a relaxing work-free weekend. (I edited video all weekend.) =) I really appreciate your help. Please take your time -- there's no hurry.

Eric

_agx_, I'm wondering if I need to define IDENTITYCOL in another scope besides RESULTSTRUCTURENAME. I'm thinking how or why I might do this. I realize I could be completely on the wrong track with that idea.

I've also been closely reading the code to see if I put in any typos. =)

Thanks again for any advice, and for your patience.

(This week has been very hectic.) Hope you are well.

Eric

@Eric,

I want to apologize for dropping off like that. I had some emergencies to deal with, so I was totally unavailable. Sorry again, to leave you hanging.  

Hope you're doing well.

_agx_,

No worries at all. In the past week I have moved my residence and office, and was called away to deal with other tasks.

This week I need to take another look at this survey application. I really appreciate the more flexible solution that you suggested (in your comment on 10/26).

I hope your emergencies worked out well.

I'm still not sure what's going on with this error:

Element IDENTITYCOL is undefined in RESULTSTRUCTURENAME.

... because I thought, in the query:

      <cfquery name="Survey_Responses_Affiliation" result="ResultStructureName" datasource="ebwebwork">
       INSERT INTO CEP_SurveyResponsesAffiliation ( SurveyResponseID, AffiliationID )
       SELECT #ResultStructureName.IDENTITYCOL# AS SurveyResponseID,
                    AffiliationID
       FROM   CEP_Survey_Affiliation
       WHERE  AffiliationID IN
            (
               <cfqueryparam value="#form.Affiliation#" cfsqltype="cf_sql_integer" list="true" />
            )
      </cfquery>

... we defined IDENTITYCOL in the scope of ResultStructureName. Maybe I am thinking about this wrongly.

Please take your time in replying .... this is not an urgent task.

As always, thank you and take care.

Eric

I'm closing this question, even though I did not discover the complete solution. I am really grateful to _agx_ for his support. I learned a great deal from my communications with _agx_ regarding this problem. I will return to this problem later. Thanks _agx_!