Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Online questionnaire data input problems 1

Status
Not open for further replies.
trolldude

trolldude

Technical User
Apr 29, 2004
14
GB
I have written an online feedback questionnaire that has 13 pages. Each page has some multi choice answers to questions using radio buttons and also a text input form element for comments. The radio buttons are valued to indicate the answer value 1,2,3,4,5 or 6 that correspond to the headings of each radio button column.

I have 2 problems that I am struggling with, any help would be very welcome.
I am new to Coldfusion so please bear with me and some possibly strange code.

QUESTIONS

How can I stop data being overwritten by subsequent logins using the same UserName and Recipient name?
How can I allow users to flip through or bypass pages without them causing data to be entered in the DB in the event that they want to input data in more than 1 session or the session crashes part way through?


MY CODE EXPLAINED

The user registers their full name and a UserName and Password that is entered in to users table in an MS Access DB.
The User then logs in and selects a recipient to complete the feedback questionnaire about.

The first question page of the questionnaire uses the User name (a session variable from login) and the Recipients name (a session variable from a drop down menu selection) to find the database 'AnswerId' value for the combination of User Name and Recipients name. That 'AnswerID' is then set as session variable that the rest of the pages use to find the database row to enter the answer data from each form.

The form sends the data to the next page where the code enters it into the data base.

PROBLEMS

Some users have entered the questionnaire again after completing it and this has overwritten the data they have previously entered. I assume the CFquery finds the combination of names and enters the data in that row. I need to make each login unique so that any data entered in the DB cannot be overwritten.

When a user looses a connection with the server they have to complete the questionnaire from scratch, because if they flip through the pages to the section they got to previously they overwrite data. How can I allow them to get to the page they require and still enter data into the same DB row with the data from the previously terminated session.

I do hope this lot makes sense to somebody...

Many thanks in advance if anyone can help

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Here is my code for the first question page
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

<cfset session.RecFirstName = Form.select>

<!---enter User and recipients first and Last Name in answers table using answerID feild to locate record to update --->
<cfquery Datasource="3484-oec_data" name="User_n_Rec">
INSERT INTO EgAnswer (User, Recipient)
VALUES ('#session.UserFirstName# #session.UserLastName#', '#session.recFirstName# #session.RecLastName#')
</cfquery>

<!--- query answers database for answerID to locate record row for update of answers --->
<cfquery name="GetAnswerID" datasource="3484-oec_data">
SELECT answerID
FROM EgAnswer
WHERE User = '#session.UserFirstName# #session.UserLastName#'
AND Recipient = '#session.recFirstName# #session.RecLastName#'
</cfquery>

<!---set answerID as a session variable--->
<cfset session.answerIDvalue = GetAnswerID.answerID>




<html>
<head>
<title>360 Questionnaire - Section 1</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body bgcolor="#999999">

<table width="720" border="0" align="center" cellpadding="0" cellspacing="0" bgcolor="#FFFFFF">
<!--DWLayoutTable-->
<tr>
<td height="30" colspan="3" align="center" valign="middle" bgcolor="#990000"><font color="#FFFFFF" size="4"

face="Verdana, Arial, Helvetica, sans-serif"><cfoutput>#session.QuestionnaireName#</cfoutput></font><font color="#0000CC"

size="2"><strong><font size="1" face="Verdana, Arial, Helvetica, sans-serif"><a name="topsection1"></a><font color="#FFFFFF"

size="3"> - Example Section 1 </font></font></strong></font></td>
</tr>
<tr>
<td height="19" colspan="3" valign="top" bgcolor="#999999"><!--DWLayoutEmptyCell-->&nbsp;</td>
</tr>
<tr>
<td width="29" height="32">&nbsp;</td>
<td width="669" align="left" valign="middle"><cfoutput><font size="3" face="Arial, Helvetica, sans-serif"><strong><font

color="##0000CC" size="2">#session.UserFirstName# #session.UserLastName# </font></strong></font></cfoutput><font

color="#000000" size="2"><strong><font face="Arial, Helvetica, sans-serif">giving
Feedback to</font></strong></font><font color="#0000CC" size="2"><strong><font face="Arial, Helvetica, sans-serif">
<cfoutput>#session.RecFirstName#</cfoutput></font></strong></font></td>
<td width="22">&nbsp;</td>
</tr>
<tr>
<td height="19">&nbsp;</td>
<td align="right" valign="top"><font size="1" face="Verdana, Arial, Helvetica, sans-serif"><a href="#rating_notes">click
here to scroll down to rating notes </a></font></td>
<td>&nbsp;</td>
</tr>

</table>
<table width="710" border="0" align="center" cellpadding="0" cellspacing="0" bgcolor="#FFFFFF">
<tr>
<cfquery name="GetRecordtoUpdate" datasource="3484-oec_data">
SELECT answerID, s1q1, s1q2, s1q3, s1comments FROM EgAnswer WHERE answerID
= #session.answerIDvalue#
</cfquery>
<cfoutput query="GetRecordtoUpdate">


<table width="720" border="0" align="center" cellpadding="0" cellspacing="0" bordercolor="##CCCCCC" bgcolor="##FFFFFF">
<!--DWLayoutTable-->
<form action="section5.cfm" method="Post">
<tr>
<td width="49" height="32" valign="top"><!--DWLayoutEmptyCell-->&nbsp;</td>
<td colspan="2" valign="middle"><strong><font color="##990000" size="2" face="Verdana, Arial, Helvetica,

sans-serif">Section
1 - Managing Performance</font></strong></td>
<td width="13" rowspan="4" valign="top"><!--DWLayoutEmptyCell-->&nbsp;</td>
<td width="49" align="center" valign="middle"><div align="center"><strong><font size="1" face="Verdana, Arial,

Helvetica, sans-serif">Always</font></strong></div></td>
<td width="56" align="center" valign="middle"><p align="center"><strong><font size="1" face="Verdana, Arial,

Helvetica, sans-serif">Almost<br>
Always </font></strong></p></td>
<td width="50" align="center" valign="middle"><div align="center"><strong><font size="1" face="Verdana, Arial,

Helvetica, sans-serif">Some-<br>
times </font></strong></div></td>
<td width="55" align="center" valign="middle"><div align="center"><strong><font size="1" face="Verdana, Arial,

Helvetica, sans-serif">Almost<br>
Never </font></strong></div></td>
<td width="60" align="center" valign="middle"><div align="center"><strong><font size="1" face="Verdana, Arial,

Helvetica, sans-serif">Not<br>
Relevant</font></strong></div></td>
<td width="50" align="center" valign="middle"><div align="center"><strong><font size="1" face="Verdana, Arial,

Helvetica, sans-serif">Don't<br>
Know </font></strong></div></td>
</tr>
<tr>
<td height="63" align="center" valign="top"><font color="##990000" size="4" face="Arial, Helvetica,

sans-serif"><strong>Q1</strong></font></td>
<td colspan="2" valign="top"><font size="2" face="Verdana, Arial, Helvetica, sans-serif"><strong>Goal
Setting</strong><br>
Translates university strategy into clear, meaningful goals for others</font></td>
<td align="center" valign="middle"><input type="radio" name="s1q1" value="#1#"></td>
<td align="center" valign="middle"><input type="radio" name="s1q1" value="#2#"></td>
<td align="center" valign="middle"><input type="radio" name="s1q1" value="#3#"></td>
<td align="center" valign="middle"><input type="radio" name="s1q1" value="#4#"></td>
<td align="center" valign="middle"><input type="radio" name="s1q1" value="#5#"></td>
<td align="center" valign="middle"><input type="radio" name="s1q1" value="#6#" checked></td>
</tr>
<tr>
<td height="63" align="center" valign="top"><font color="##990000" size="4" face="Arial, Helvetica,

sans-serif"><strong>Q2</strong></font></td>
<td colspan="2" valign="top"><font color="##000000" size="2" face="Verdana, Arial, Helvetica,

sans-serif"><strong>Prioritising</strong><br>
Sets priorities and schedules which recognise and balance critical
goals </font></td>
<td align="center" valign="middle"><input type="radio" name="s1q2" value="#1#"></td>
<td align="center" valign="middle"><input type="radio" name="s1q2" value="#2#"></td>
<td align="center" valign="middle"><input type="radio" name="s1q2" value="#3#"></td>
<td align="center" valign="middle"><input type="radio" name="s1q2" value="#4#"></td>
<td align="center" valign="middle"><input type="radio" name="s1q2" value="#5#"></td>
<td align="center" valign="middle"><input type="radio" name="s1q2" value="#6#" checked></td>
</tr>
<tr>
<td height="63" align="center" valign="top"><font color="##990000" size="4" face="Arial, Helvetica,

sans-serif"><strong>Q3</strong></font></td>
<td colspan="2" valign="top"><font size="2" face="Verdana, Arial, Helvetica, sans-serif"><strong>Agreeing
Key Objectives</strong><br>
Agrees &quot;key objectives&quot; that are critical to success </font></td>
<td align="center" valign="middle"><input type="radio" name="s1q3" value="#1#"></td>
<td align="center" valign="middle"><input type="radio" name="s1q3" value="#2#"></td>
<td align="center" valign="middle"><input type="radio" name="s1q3" value="#3#"></td>
<td align="center" valign="middle"><input type="radio" name="s1q3" value="#4#"></td>
<td align="center" valign="middle"><input type="radio" name="s1q3" value="#5#"></td>
<td align="center" valign="middle"><input type="radio" name="s1q3" value="#6#" checked></td>
</tr>
<tr>
<td height="19" colspan="10" valign="top"><!--DWLayoutEmptyCell-->&nbsp;</td>
</tr>
<tr>
<td height="102" colspan="2" valign="top"><blockquote>
<p><font size="3" face="Arial, Helvetica, sans-serif"><strong>Comments</strong></font></p>
</blockquote></td>
<td colspan="8" valign="top"><font size="3" face="Arial, Helvetica, sans-serif">
<textarea name="s1comments" cols="50" rows="5" id="s1comments"></textarea>
</font></td>
</tr>
<tr>
<td height="56" colspan="10" valign="top"><blockquote>
<p><font size="3" face="Arial, Helvetica, sans-serif"><br>
<strong>Click next to continue</strong></font>
<input type="submit" value="next">
</p>
</blockquote></td>
</tr>
<tr>
<td height="1"></td>
<td width="94"></td>
<td width="244"></td>
<td></td>

</tr>
</form>
</table>
</cfoutput>

</body>
</html>

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Here is the code form the top of the next page
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

<cfquery name="UpdateRecord" datasource="3484-oec_data">
UPDATE EgAnswer
SET s1q1 = #form.s1q1#,
s1q2 = #form.s1q2#,
s1q3 = #form.s1q3#,
s1comments = '#form.s1comments#'
WHERE answerID = #session.answerIDvalue#
</cfquery>


 
Sort by date Sort by votes
there are a few things you can do to solve your problems:

I would add some code after the login that decides where in the survey to take them to, if they have filled out the first 4 sections, start them at section 5.
Code: 
====setup_questionnaire.cfm======
<!--- query answers database for answerID to locate record row for update of answers --->
    <cfquery name="GetAnswerID" datasource="3484-oec_data">
        SELECT    *
        FROM     EgAnswer
        WHERE    User         = '#session.UserFirstName# #session.UserLastName#'
        AND     Recipient     = '#session.recFirstName# #session.RecLastName#'
    </cfquery>

<cfif (fields from page1) EQ ''>
<cflocation url="page1.cfm">
<cfelseif (fields from page2) EQ ''>
<cflocation url="page2.cfm">
so on and so forth....
</cfif>

then IF your query at the top of each page to only perform if there is a form submission.

Code: 
<cfif IsDefined("FORM.submit")>
<cfquery name="UpdateRecord" datasource="3484-oec_data">
           UPDATE    EgAnswer
           SET        s1q1 = #form.s1q1#,
                 s1q2 = #form.s1q2#,
                  s1q3 = #form.s1q3#,
                s1comments    = '#form.s1comments#'
           WHERE     answerID = #session.answerIDvalue#
    </cfquery>
</cfif>

so the process would be

login and set session variables- submit to survey setup page
|
|
survey page finds out where they left off and redirects
|
|
survey question page only updates previous question if there was a submit

this way, no data is overwritten and questions they have answered are bypassed
 
Upvote 0 Downvote
Many thanks for the suggestion NorthStarDA

I am having problems getting the IsDefined code to work. I have entered it as you directed using ("FORM.submit") but I cannot get the query in the cfif tags to work.

What could I be doing wrong?

Michael
 
Upvote 0 Downvote
#FORM.submit# assumes you have a form element that was passed to the page named 'Submit'. You can replace submit with the name of the button used to send the form, i usually put in a hidden form variable inside my form in case that button wasn't pressed:

<input type="hidden" name="Submit" value="TRUE">
 
Upvote 0 Downvote
Thanks again NorthStarDA

I've got that bit sorted now.

I'm thinking of another possible solution.

At present every time they login and select a Participant from the drop down menu the users name and the Participants name are entered into the database. This causes multiple enteries and any data entered is entered into the first instance found and that AnswerID used for the rest of the answers.

To get a system to work whereby they can bypass pages they have completed and enter data from pages they have not completed and get that data into one row, I need a method of checking if the User name and Participants name exists and entering or not entering the data as appropriate.

I have try various bits of code involving <cfif> tags but I haven't found any reference in the literature I have to check if what I'm doing is correct, so I could be close or I could be miles away.

Your help would agin be much appreciated.

Michael
 
Upvote 0 Downvote
if the names come from a drop list, it's pretty easy- you don't have to worry about typos creating different records
Code: 
<!--- query answers database for answerID to locate record row for update of answers --->
    <cfquery name="GetAnswerID" datasource="3484-oec_data">
        SELECT    answerID
        FROM     EgAnswer
        WHERE    User         = '#session.UserFirstName# #session.UserLastName#'
        AND     Recipient     = '#session.recFirstName# #session.RecLastName#'
    </cfquery>
	<!--- Only perform the insert if no records were found above --->
	<cfif GetAnswerID.recordcount EQ 0>
	<!---enter User and recipients first and Last Name in answers table using answerID feild to locate record to update --->
    	<cfquery Datasource="3484-oec_data" name="User_n_Rec">
        	INSERT INTO EgAnswer (User, Recipient)
        	VALUES ('#session.UserFirstName# #session.UserLastName#', '#session.recFirstName# #session.RecLastName#')
        	</cfquery>
	<cfquery name="GetAnswerID" datasource="3484-oec_data">
        	SELECT    answerID
        	FROM     EgAnswer
        	WHERE    User         = '#session.UserFirstName# #session.UserLastName#'
        	AND     Recipient     = '#session.recFirstName# #session.RecLastName#'
    	</cfquery>
	</cfif>



    
<!---set answerID as a session variable--->
<cfset session.answerIDvalue = GetAnswerID.answerID>
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

ibeenjammin
  • Locked
  • Question
FrameMaker 2015 Conditional Text Problems
Replies
0
Views
190
ibeenjammin
ibeenjammin
zamvil
  • Locked
  • Question
Invoke NET webservice(svc) with data complext
Replies
0
Views
83
zamvil
zamvil
Guthro
  • Locked
  • Question
Save site data DW8 and Win7
Replies
2
Views
72
Guthro
Guthro
LarrySteele
  • Locked
  • Question
Form variable problems with Safari 5.1.5
Replies
1
Views
74
jdhilljr
jdhilljr
lollieleaver
  • Locked
  • Question
Dreamweaver CS4 - how can I make an Access database downloadable from my site?
Replies
2
Views
87
lollieleaver
lollieleaver

Part and Inventory Search

Sponsor

Back
Top