Question

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

Asked by: EricBourland

Hi. I have a simple survey form here:

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

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\ebwebwork.com\cep\consumersurvey\data_insert.cfm: line 73

71 :
72 : 
73 : <input type="hidden" name="SurveyResponseID" value="#SurveyResponseID.ident#">
74 : 
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.

Eric

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" 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" />
 
 
 
action page (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" name="CEP_Survey_Responses">
	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()#">);
	SELECT scope_identity() AS ident
</cfquery>
 
 
		  
<!--- pass the survey responses back to the previous page --->
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
 
<html>
<head>
	<title>CEP Consumer Survey Responses Sent</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="#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#">
<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>

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:
179:
180:
181:
182:
183:
184:
185:
186:
187:
188:
189:
190:
191:
192:
193:
194:
195:
196:
197:
198:
199:
200:
201:
202:
203:
204:
205:
206:
207:
208:
209:
210:
211:
212:
213:
214:
215:
216:
217:
218:
219:
220:
221:
222:
223:
224:
225:
226:
227:
228:
229:
230:
231:
232:
233:
234:
235:
236:
237:
238:
239:
240:
241:
242:
243:
244:
245:
246:
247:
248:
249:
250:
251:
252:
253:
254:
255:
256:
257:
258:
259:
260:
261:
262:
263:
264:
265:
266:
267:
268:
269:
270:
271:
272:
273:
274:
275:
276:
277:
278:
279:
280:
281:
282:
283:
284:
285:
286:
287:
288:
289:
290:
291:
292:
293:
294:
295:
296:
297:
298:
299:
300:
301:
302:
303:
304:
305:
306:
307:
308:
309:
310:
311:
312:
313:
314:
315:
316:
317:
318:
319:
320:
321:
322:
323:
324:
325:
326:
327:
328:
329:
330:
331:
332:
333:
334:
335:
336:
337:
338:
339:
340:
341:
342:
343:
344:
345:
346:
347:
348:
349:
350:
351:
352:
353:
354:
355:
356:
357:
358:
359:
360:
361:
362:
363:
364:
365:
366:
367:
368:
369:

Select all Open in new window

This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

Plus...

30 Day FREE access, no risk, no obligation
Collaborate with the world's top tech experts
Unlimited access to our exclusive solution database
Never be left without tech help again

Subscribe Now

Asked On: 2009-10-24 at 15:09:14ID24841040
Tags: ColdFusion 6; unique ID
,; ColdFusion 8
Topic: Cold Fusion Markup Language
Participating Experts: 2
Points: 500
Comments: 45

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

"The time we save is the biggest benefit of Experts Exchange to Warner Bros. 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.
"Our team likes having a resource that is more secure than just using Google and most experts using this service really know their stuff. It's nice to look here first versus using Google." Dayna Sellner, Lockheed Martin
"Anytime that I've been stumped with a problem, 9 out of 10 times Experts Exchange has either the accepted solution or an open discussion of the potential solution to the problem." Kenny Red, eBay Inc.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.

Screenshot of Experts Exchange Knowledgebase

Need individual assistance?

Our experts are ready to help.

If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.

Want to learn from the best?

Read articles from industry experts.

Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.

Working on a long term project?

Store your work and research.

Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.

Access the answers to your technology questions today.

Subscribe Now

30-day free trial. Register in 60 seconds.

What Makes Experts Exchange Unique?

Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Trusted by the world's most respected brands.

image of each brand's logo

Faithfully serving IT professionals since 1996.

Experts Exchange Logo

Try it out and discover for yourself.

Subscribe Now

30-day free trial. Register in 60 seconds.

Free Tech Articles

WARNING: 5 Reasons why you should NEVER fix a computer for free.
It is in our nature to love the puzzle. We are obsessed. The lot of us. We love puzzles. We love the challenge. We thrive on finding the answer. We hate disarray. It bothers us deep in our soul. W...
SCCM OSD Basic troubleshooting
SCCM 2007 OSD is a fantastic way to deploy operating systems, however, like most things SCCM issues can sometimes be difficult to resolve due to the sheer volume of logs to sift through and the dispe...
Migrate Small Business Server 2003 to Exchange 2010 and Windows 2008 R2
This guide is intended to provide step by step instructions on how to migrate from Small Business Server 2003 to Windows 2008 R2 with Exchange 2010. For this migration to work you will need the fo...
Create a Win7 Gadget
This article shows you how to create a simple "Gadget" -- a sort of mini-application supported by Windows 7 and Vista. Gadgets can be dropped anywhere on the desktop to provide instant information, ...
Outlook continually prompting for username and password
There have been a lot of questions recently regarding Outlook prompting for a username and password whilst using Exchange 2007. There are a few reasons why this would happen and I will try to cover t...
Backup Exchange 2010 Information Store using Windows Backup
There seems to be quite a lot of confusion around the ability to backup Exchange 2010 using the built in Windows Backup feature. This stems from the omission of this feature prior to Exchange 2007 s...

Cloud Class Webinars

Avoiding Bugs in Microsoft Access
Alison Balter takes and in-depth look at avoiding bugs in Access. In this webinar you will learn about using the immediate window to debug your applications, invoking the debugger, using breakpoints to troubleshoot, stepping through code, setting the next statement to execute, ...
Top 10 Best New Features in Visio 2010
Scott Helmers gives live demonstrations of the top 10 new features in Visio 2010. This webinar will teach you how to create compelling diagrams by adding shapes to the page with a single click, linking the shapes in a diagram to data in Excel (or SQL Server, or SharePoint), ...
IT Consultant Business Secrets Revealed
Michael Munger, Experts Exchange tech pro and IT consultant, pulls back the curtain on his very successful businesses and answers question on every IT consultant and business owner should know about. He shares secrets on what he did to solve the 5 most common problems in IT, ...
Disaster Recovery and Business Continuity
Quest CTO, Mike Billon, gives an overview of the steps involved in building a dunamic disaster recovery plan. Through case studies and an examination of software/hardware tooles for monitoring and testing, you'll gain a better understandin of where you are, where you want ...
Organize Your Visio Diagrams with Containers and Lists
Scott Helmers uses cross functional flowcharts, wireframe diagrams, data graphic legends and seating charts to teach you: how to ustilize all three new structured diagram components in Visio 2010, the best practices for organizeing shapes in previous version of Visio, how to organize ...
How to Us Objects, Properties, Events and Methods in Microsoft Access
Alison Dalter gives an in-depbth look at objects, properties, events and methods in Microsoft Access. In this webinar you will learn about using the object browser, referring to objects, working with properties and methods, working with object variables, understanding the ...

Watch More

Join the Community

Give a Little. Get a Lot.

Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.

Join the Community

Answers

by: myselfrandhawaPosted on 2009-10-25 at 00:42:11ID: 25655855

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

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:

Select all Open in new window

by: EricBourlandPosted on 2009-10-25 at 11:03:29ID: 25657476

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>

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:

Select all Open in new window

by: myselfrandhawaPosted on 2009-10-25 at 18:37:03ID: 25659319

@ 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

by: EricBourlandPosted on 2009-10-25 at 19:51:31ID: 25659477

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

by: _agx_Posted on 2009-10-26 at 10:07:23ID: 25664397

@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)

by: _agx_Posted on 2009-10-26 at 10:08:49ID: 25664416

> 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.

by: EricBourlandPosted on 2009-10-26 at 10:19:10ID: 25664512

_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

by: _agx_Posted on 2009-10-26 at 10:50:50ID: 25664817

@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>

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:

Select all Open in new window

by: EricBourlandPosted on 2009-10-26 at 14:22:05ID: 25667050

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)

by: EricBourlandPosted on 2009-10-26 at 14:24:02ID: 25667069

Sorry, column DateCreated has (datatype DateTime)

=)

by: _agx_Posted on 2009-10-26 at 14:26:41ID: 25667098

> 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.

by: _agx_Posted on 2009-10-26 at 14:40:36ID: 25667213

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)
)

by: EricBourlandPosted on 2009-10-26 at 14:49:55ID: 25667303

That is cool. Working on this.

by: EricBourlandPosted on 2009-10-26 at 16:26:46ID: 25668031

_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>

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:
179:
180:
181:
182:
183:
184:
185:
186:
187:
188:

Select all Open in new window

by: _agx_Posted on 2009-10-26 at 16:55:57ID: 25668184

> 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 ;-)


<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

by: EricBourlandPosted on 2009-10-27 at 14:21:10ID: 25677697

_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

by: EricBourlandPosted on 2009-10-27 at 19:32:58ID: 25679589

_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>

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:

Select all Open in new window

by: _agx_Posted on 2009-10-28 at 09:40:29ID: 25685202

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#">

by: EricBourlandPosted on 2009-10-28 at 10:51:41ID: 25686140

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>

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:

Select all Open in new window

by: myselfrandhawaPosted on 2009-10-28 at 11:36:53ID: 25686686

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" />
		)

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:

Select all Open in new window

by: EricBourlandPosted on 2009-10-28 at 11:47:18ID: 25686799

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

by: _agx_Posted on 2009-10-28 at 11:48:52ID: 25686814

No, I meant dump the values _before_ the query :) But that did show the problem. The checkbox values should be the AffiliationID, not the name.

<cfinput type="checkbox" name="affiliation" value="#affiliationID#">

That's why you're getting the error:

Invalid data '' for CFSQLTYPE CF_SQL_INTEGER.

The query expects AffiliationID values ie an integer . "AwareCEPAnnualReportsStateTests" is obviously not an integer, so that's why you're getting the error. Just make the checkbox value an integer, and this query should work fine:

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" />
)

Once CF translates the values into sql, the resulting statement will be something like this:


INSERT INTO SurveyResponsesAffiliation ( SurveyResponseID, AffiliationID )
SELECT 12 AS SurveyResponseID, AffiliationID
FROM CEP_Survey_Affiliation

WHERE AffilationID IN
(
1,12,8,9
)

by: _agx_Posted on 2009-10-28 at 11:51:49ID: 25686837

> 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" />
)

by: EricBourlandPosted on 2009-10-28 at 12:05:14ID: 25686970

I see. OK, I am working on this.

by: _agx_Posted on 2009-10-28 at 12:15:50ID: 25687081

@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.

by: EricBourlandPosted on 2009-10-28 at 13:31:30ID: 25687950

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



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>

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:
179:
180:
181:
182:
183:
184:
185:
186:
187:
188:
189:
190:
191:
192:
193:
194:
195:
196:
197:
198:
199:
200:
201:
202:
203:
204:
205:
206:
207:
208:
209:
210:
211:
212:
213:
214:
215:
216:
217:
218:
219:
220:
221:
222:
223:
224:
225:
226:
227:
228:
229:
230:
231:
232:
233:
234:
235:
236:
237:
238:
239:
240:
241:
242:
243:
244:
245:
246:
247:
248:
249:
250:
251:
252:
253:
254:
255:
256:
257:
258:
259:
260:
261:
262:
263:
264:
265:
266:
267:
268:
269:
270:
271:
272:
273:
274:
275:
276:
277:
278:
279:
280:
281:
282:
283:
284:
285:
286:
287:
288:
289:
290:
291:
292:
293:
294:
295:
296:
297:
298:
299:
300:
301:
302:
303:
304:
305:
306:
307:
308:
309:
310:
311:
312:
313:
314:
315:
316:
317:
318:
319:
320:
321:
322:
323:
324:
325:
326:
327:
328:
329:
330:
331:
332:
333:
334:
335:
336:
337:
338:
339:
340:
341:
342:
343:
344:
345:
346:
347:
348:
349:
350:
351:
352:
353:
354:
355:
356:
357:
358:
359:
360:
361:
362:
363:
364:
365:
366:
367:
368:
369:
370:
371:
372:
373:
374:
375:
376:
377:
378:
379:
380:
381:
382:
383:
384:
385:
386:
387:
388:
389:
390:
391:
392:
393:
394:
395:
396:
397:
398:
399:
400:
401:
402:
403:
404:
405:
406:
407:
408:
409:
410:
411:
412:
413:
414:
415:
416:
417:
418:
419:
420:
421:
422:
423:
424:
425:
426:
427:
428:

Select all Open in new window

by: _agx_Posted on 2009-10-28 at 13:43:28ID: 25688099

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#"> 
.....

by: _agx_Posted on 2009-10-28 at 14:10:49ID: 25688364

> <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)

by: EricBourlandPosted on 2009-10-28 at 14:56:58ID: 25688765

I saw this too and was thinking about it.

Still working on your last comment.

by: EricBourlandPosted on 2009-10-28 at 16:30:30ID: 25689323

_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>

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:

Select all Open in new window

by: _agx_Posted on 2009-10-28 at 18:26:53ID: 25689894

> AffilationID

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

by: EricBourlandPosted on 2009-10-28 at 20:31:04ID: 25690469

Of course. I see.

by: _agx_Posted on 2009-10-28 at 20:37:09ID: 25690491

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

by: EricBourlandPosted on 2009-10-28 at 20:50:16ID: 25690530

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 : 
100 : <input type="hidden" name="SurveyResponseID" value="#CEP_Survey_Responses.ident#">
101 :
102 : 

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>

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:

Select all Open in new window

by: _agx_Posted on 2009-10-29 at 10:43:54ID: 25696188

> 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.

by: EricBourlandPosted on 2009-10-29 at 13:42:13ID: 25698030

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

by: EricBourlandPosted on 2009-10-29 at 14:47:08ID: 25698598

_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

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

to


<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 : 
102 : <input type="hidden" name="SurveyResponseID" value="#ResultStructureName.IDENTITYCOL#">
103 :
104 : 

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" />

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:
179:
180:
181:
182:
183:
184:
185:
186:
187:
188:
189:
190:
191:
192:
193:
194:
195:
196:
197:
198:
199:
200:
201:
202:
203:
204:
205:
206:
207:
208:
209:
210:
211:
212:
213:
214:
215:
216:
217:
218:
219:
220:
221:
222:
223:
224:
225:
226:
227:
228:
229:
230:
231:
232:
233:
234:
235:
236:
237:
238:
239:
240:
241:
242:
243:
244:
245:
246:
247:
248:
249:
250:
251:
252:
253:
254:
255:
256:
257:
258:
259:
260:
261:
262:
263:
264:
265:
266:
267:
268:
269:
270:
271:
272:
273:
274:
275:
276:
277:
278:
279:
280:
281:
282:
283:
284:
285:
286:
287:
288:
289:
290:
291:
292:
293:
294:
295:
296:
297:
298:
299:
300:
301:
302:
303:
304:
305:
306:
307:
308:
309:
310:
311:
312:
313:
314:
315:
316:
317:
318:
319:
320:
321:
322:
323:
324:
325:
326:
327:
328:
329:
330:
331:
332:
333:
334:
335:
336:
337:
338:
339:
340:
341:
342:
343:
344:
345:
346:
347:
348:
349:
350:
351:
352:
353:
354:
355:
356:
357:
358:
359:
360:
361:
362:
363:
364:
365:
366:
367:
368:
369:
370:
371:
372:
373:
374:
375:
376:
377:
378:
379:
380:
381:
382:
383:
384:
385:
386:
387:
388:
389:
390:
391:
392:
393:
394:
395:
396:
397:
398:
399:

Select all Open in new window

by: _agx_Posted on 2009-10-29 at 15:20:35ID: 25698835

> 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 ;-)

by: EricBourlandPosted on 2009-10-30 at 11:34:15ID: 25705674

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

by: _agx_Posted on 2009-10-30 at 12:57:44ID: 25706293

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!

by: EricBourlandPosted on 2009-11-02 at 06:31:33ID: 25719595

_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

by: EricBourlandPosted on 2009-11-05 at 19:07:45ID: 25756312

_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

by: _agx_Posted on 2009-11-17 at 12:26:40ID: 25843751

@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.

by: EricBourlandPosted on 2009-11-17 at 18:41:37ID: 25846238

_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

by: EricBourlandPosted on 2010-02-27 at 08:55:18ID: 31645515

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_!

20120131-EE-VQP-002

3 Ways to Join

30-Day Free Trial

For individual users
Instant access to solutions
Ask your tech questions
Start your 30-day Free Trial

Business Accounts

Perfect for organizations
Multiple users
Save over 36%
Create a Business Account

Answer for Membership

Earn free access
Showcase your knowledge
No credit card required
Sign Up to Answer

The Experts

98% positive feedback on 31,087 answers since March 2000. angeliii is a Microsoft Most Valuable Professional for his work with MS SQL Server & Develoment.

He has also proven his knowledge of Visual Basic Programming, PHP Scripting and Oracle Databases.

The Experts

97% positive feedback on 10,752 answers since July 2000. lrmoore has more than 18 years experience in the networking industry.

The six-time Mircosoft MVPs specialties include firewalls, virtual private networking, and network management.

Testimonials

"...and excellent source for support... Kind of like having your very own IT dept." Electriciansnet

Testimonials

"I was apprehensive at signing up at first. However... it has already made my life as an IT administrator much easier." JaCrews

Testimonials

"WOW! You guys have great, active, and knowledgeable people on here." moore50

Business Clients

In the Press

"If you’ve got a question... Experts Exchange can supply an answer.”

In the Press

"...an invaluable aid for both IT professionals and those who require tech support."

In the Press

"where IT professionals provide quick answers on just about any topic"

Business Account Plans

Loading Advertisement...

Question

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

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

ColdFusion 6; unique ID

ColdFusion 8

Cold Fusion Markup Language

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Need individual assistance?

Our experts are ready to help.

Want to learn from the best?

Read articles from industry experts.

Working on a long term project?

Store your work and research.

What Makes Experts Exchange Unique?

Trusted by the world's most respected brands.

Faithfully serving IT professionals since 1996.

Related Solutions

Free Tech Articles

Cloud Class Webinars

Join the Community

Give a Little. Get a Lot.

Answers

3 Ways to Join

The Experts

The Experts

Testimonials

Testimonials

Testimonials

Business Clients

Business Clients

In the Press

In the Press

In the Press