| Programmer to ProgrammerTM | |||||
| |
|||||
|
|
|
||||
|
|
|
|
|
|
|
|
|
|
| |||||||||||||||||||
| The ASPToday
Article March 13, 2001 |
Previous
article - March 12, 2001 |
||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||
| ABSTRACT |
| ||||||||||||||||||||||||||||||
|
| |||||||||||||||||||||||||||||||
| Article Discussion | Rate this article | Related Links | Index Entries | ||||||||
| ARTICLE | |||||||||||
In Part 1 of this two part series, we built the administration tools needed to create our online surveys. Here is a quick recap of the client's requirements for the online survey generator:
The application was built to run on Microsoft's Internet Explorer only, as the client has standardized on Microsoft's products throughout the company. It also gave me the opportunity to implement several Cascading Style Sheet (CSS) properties that are exclusive to IE's browser.
In this article, we are going to round off the whole application by developing the following functionality:
Note: The support material code runs on SQL Server only, as there are differences between the versions of SQL on SQL Server and Microsoft Access. For example, Access does not support SQL Server's CONVERT function. However, the code can be converted fairly easily to work over an Access database. For example, instead of formatting the data in the SQL query, we could retrieve the "raw" data and format it using VBScript functions. A prime example is using VBScript's Cstr(X) function where I use Transact-SQL's CONVERT(VARCHAR, X).
Also, instead of looping through the pre-formatted recordset as follows:
<%
strOutput = ""
fldCount = 0
rowCount = 0
If Not rsSurvey.EOF Then
fldCount = rsSurvey.Fields.Count-1
Do While Not rsSurvey.EOF
rowCount = rowCount + 1
strOutput = strOutput & " <tr class="""">"
For i = 0 To fldCount
Select Case i
Case 0
strOutput = strOutput & "<td class=""tableleft"">"
Case fldCount
strOutput = strOutput & "<td class=""tableright"">"
Case Else
strOutput = strOutput & "<td class=""tablemiddle"">"
End Select
strOutput = strOutput & rsSurvey.Fields(i).Value & "</td>" & vbCrlf
Next
strOutput = strOutput & "</tr>"
rsSurvey.MoveNext
Loop
Response.Write strOutput
End If
%>
One would have to store the values in variables, format them using VBScript, and then write them out individually as the pseudo-code below shows:
<%
strOutput = ""
If Not rsSurvey.EOF Then
Do While Not rsSurvey.EOF
rowCount = rowCount + 1
strOutput = strOutput & " <tr class="""">"
strOutput = strOutput & "<td class=""tableleft"">"
strVar1 = rsSurvey.Fields(0)
strVar1 = FormatDatTime(strVar1, 2) 'Short Date format
strOutput = strOutput & & "</td>" & vbCrlf
strOutput = strOutput & "<td class=""tablemiddle"">"
strVar2 = rsSurvey.Fields(1)
strVar2 = Left(CStr(strVar2), 10)
strOutput = strOutput & & "</td>" & vbCrlf
strOutput = strOutput & "<td class=""tableright"">"
strVar3 = rsSurvey.Fields(2)
strVar3 = CInt(strVar3)
strOutput = strOutput & & "</td>" & vbCrlf
strOutput = strOutput & "</tr>"
rsSurvey.MoveNext
Loop
Response.Write strOutput
End If
%>
In part 1, we constructed the following tables:
For more details on each of the tables, refer back to the first article.
Two extra fields have been added to the Survey table. They are:
The following tables have been added:
SurveyRespondent - contains the contact details of a user responding to a non anonymous survey.
This table consists of the following fields:
SurveyResult - contains the results of a survey, stored per individual.
This table consists of the following fields:
Note: There is a file called conn.inc in the /admin/survey/include directory of the support material. This file instantiates the ADODB Connection object, and opens the connection to the database. This file is included on every page. I did this so that you, the developer, could change the connection string to connect to your database in one place only. Ordinarily, I would store the connection string in the global.asa file and reference it when needed
Below is a list of the scripts that make up the application:
I will only cover the pages that are new, or that have changed since Part 1. Please refer back to the previous article for an explanation of the other pages.
This page is the entry point into the system. From this page, we are able to add a new survey, edit an existing one, delete a survey and view the results of a survey. If you press the add new survey button you get a form like this:
which you can use to add a new survey, by adding a title and a description.
The SQL query used to return the results looks in /admin/default.asp like this:
strSQL = "SELECT SurveyName AS [Survey], SurveyAdministrator AS [Administrator], "
strSQL = strSQL & "Anonymous = CASE isAnon WHEN 0 THEN 'No' ELSE 'Yes' END, "
strSQL = strSQL & "[Email Notification] = CASE isEmail WHEN 0 THEN 'No' ELSE 'Yes' END, "
strSQL = strSQL & "'<a href=""editsurvey.asp?ID=' + CONVERT(VARCHAR, ID) + '"" & _
class=""link"">edit</a> / "
strSQL = strSQL & "<a href=""Javascript:DeleteSurvey(' + CONVERT(VARCHAR, ID) + ');"" & _
class=""link"">delete</a> / "
strSQL = strSQL & "<a href=""surveyresults.asp?ID=' + CONVERT(VARCHAR, ID) + '"" & _
class=""link"">view results</a> / "
strSQL = strSQL & "<a href=""surveydetails.asp?ID=' + CONVERT(VARCHAR, ID) + '"" & _
class=""link"">view details</a>' AS [Action] FROM Survey "
strSQL = strSQL & "ORDER BY SurveyName"
As you can see I have created all the hyperlinks in a single field called Action in the query. I have done this because I use some code to loop through all the fields and then write the output to the screen This can be seen below:
<%
strOutput = ""
fldCount = 0
rowCount = 0
If Not rsSurvey.EOF Then
fldCount = rsSurvey.Fields.Count-1
Do While Not rsSurvey.EOF
rowCount = rowCount + 1
strOutput = strOutput & " <tr class="""">"
For i = 0 To fldCount
Select Case i
Case 0
strOutput = strOutput & "<td class=""tableleft"">"
Case fldCount
strOutput = strOutput & "<td class=""tableright"">"
Case Else
strOutput = strOutput & "<td class=""tablemiddle"">"
End Select
strOutput = strOutput & rsSurvey.Fields(i).Value & "</td>" & vbCrlf
Next
strOutput = strOutput & "</tr>"
rsSurvey.MoveNext
Loop
Response.Write strOutput
End If
%>
For more information on this approach, see my other article, Some Useful Generic Functions for Formatting Data Returned from a DB.
Clicking on the add a new survey button, or the edit link on an existing survey takes the user to the editsurvey.asp page which is explained further down in this article.
Clicking on the delete link runs the following JavaScript function, asking for confirmation that the user does indeed want to delete the survey and all related information. This was added to ensure that the Corporate Communications guys were aware of the implications of wiping their 50+ question survey and to cover us.
<script language="JavaScript" type="text/javascript">
<!--
function DeleteSurvey(Survey) {
if( confirm("Confirm Delete:\n\nAre you sure you want to delete this survey?" & _
"\n\nAll the sections, questions and their choices for this section will be deleted as well.") ) {
location.href = "delsurvey.asp?SurveyID=" + Survey;
}
}
//-->
</script>
Clicking on the view details link drills the user down into the selected survey, presenting a list of sections associated with the survey, as can be seen further down in this article.
Clicking on the view results link takes the user to the surveyresults.asp page that is explained further down in the article.
This page, in conjunction with the doeditsurvey.asp page, handles the adding and editing of survey details. If a SurveyID is passed to the page, we are editing an existing survey and the submit button reflects as much, otherwise it displays Add Survey.
If the survey administrator sets the Anonymous Survey to No, the survey respondent will be required to capture their contact details.
The Email Notification Required field is there to allow the administrator to choose whether or not they want to receive an email notification that another respondent has completed the survey. The email functionality depends on the correct configuration of the SMTP server. This falls outside the scope of this article, however, I have included two links at the bottom of the article that could be of some help. If in doubt, set the Email Notification Required field to No.
For more information on this page, refer to the previous article.
This page displays the survey, as captured with the administration tools. As you can see, the survey name and its description appear at the top, followed by the contact details section that appears only when Anonymous Survey = No. Then, we output the section name and its description and finally we display the questions with their choices.
At the bottom of the survey, there are two buttons that can be seen in the above screenshot. Submit survey calls the doSurvey.asp page, which processes the completed survey and stores it in the database. This page is described in further detail later on in the article. The Reset button resets all the fields on the survey to their defaults.
The first thing that occurs on this page is that we check to see if the survey is anonymous or not, because this will affect whether we capture the respondent's details.
The code looks as follows:
strSQL = "SELECT SurveyName, IsAnon, SurveyDescription FROM Survey WHERE ID = " & SurveyID
Set rsHeader = Server.CreateObject("ADODB.Recordset")
rsHeader.Open strSQL, Conn
IsAnon = False
If Not rsHeader.EOF Then
SurveyName = rsHeader("SurveyName")
IsAnon = rsHeader("IsAnon")
SurveyDescription = rsHeader("SurveyDescription")
End If
Now that we have this stored in our Boolean variable IsAnon, we can use this in the code to determine whether or not to display the contact details fields:
<%If IsAnon Then%>
<input type="hidden" name="IsAnon" value="1">
<%Else%>
<tr>
<td colspan="3" class="sectiondesc"><b>Contact Details (These fields are required)</b></td>
</tr>
<tr>
<td class="questionbackground">Name:</td>
<td class="blankbackground"> </td>
<td class="answerbackground"><input type="text" name="Name" class="answer" size="50"></td>
</tr>
.
.
<%End If%>
If it is an anonymous survey, we create a hidden field called IsAnon that will be passed through to the processing page - this will aid us in determining whether or not we need to store the respondent's details.
Now we can move onto the "meat" of the page, the actual displaying of the survey itself. Let us start with the SQL query that will return our survey:
strSQL = "SELECT SS.ID AS [SectionID], SS.SectionName, SS.SectionOrder, " & _
"SQ.ID AS [QuestionID], SQ.QuestionOrder, SQC.ChoiceOrder, " & _
"SQC.ChoiceID, SQT.Value, SS.SectionDescription, SQ.Question, SC.ChoiceDescription " & _
"FROM Survey S " & _
"INNER JOIN SurveySection SS ON S.ID = SS.SurveyID " & _
"INNER JOIN SurveyQuestion SQ ON SS.ID = SQ.SectionID " & _
"LEFT OUTER JOIN SurveyQuestionChoice SQC ON SQ.ID = SQC.QuestionID " & _
"LEFT OUTER JOIN SurveyChoice SC ON SQC.ChoiceID = SC.ID " & _
"INNER JOIN SurveyQuestionType SQT ON SQ.QuestionTypeID = SQT.ID " & _
"WHERE S.ID = "& SurveyID & _
"ORDER BY SS.SectionOrder, SQ.QuestionOrder, SQC.ChoiceOrder"
Set rsSurvey = Server.CreateObject("ADODB.Recordset")
rsSurvey.Open strSQL, Conn
This is a fairly straightforward query. All we are doing is returning a recordset containing all the fields required to build our survey form. How we use this recordset is where it gets more interesting:
First, we initialize the variables that we will be using to track the section and question that we are on, as well as a Boolean variable that will be used to alternate the styles between odd and even rows.
<% intSection = 0 intQuestion = 0 intTemp = 0 rsSurvey.MoveFirst blnOdd = False
Next, we begin looping through our recordset. The first check is to see if we have a new section. If we do, we:
Do While Not rsSurvey.EOF
If intSection <> rsSurvey("SectionID") Then
blnOdd = False
strOutput = strOutput & "<tr class=""sectionheader""><td colspan=""3"" & _
class=""sectionheader"">" & rsSurvey("SectionName") & "</td></tr>" & vbcrlf
strTemp = rsSurvey("SectionDescription")
If Not(strTemp = "" Or IsNull(strTemp)) Then
strOutput = strOutput & "<tr class=""sectiondesc""><td colspan=""3"" & _
class=""sectiondesc"">" & strTemp & "</td></tr>" & vbcrlf
End If
intSection = rsSurvey("SectionID")
End If
Next, we store some information about the current question in variables. These values will be used to construct the names of the fields on our form, as you will see shortly.
Note: As a user mentioned to me, this application is a prime candidate for hierarchical recordsets, which can be returned using the MSDataShape provider. I have provided some links at the bottom of the article to resources on hierarchical recordsets for those who are interested.
intTemp = rsSurvey("QuestionID")
strS = rsSurvey("SectionID")
strQ = rsSurvey("QuestionID")
qType = rsSurvey("Value")
Now we check to see if it is a new question - if it is, and it is not the first question in the survey, we check to see if it is of type ddl (Drop-down list = <SELECT>). If it is a drop-down list, we close the </SELECT> tag, before closing the table cell and table row tags:
If intQuestion <> intTemp Then
blnOdd = Not(blnOdd)
If intQuestion <> 0 Then
If blnDDL Then
strOutput = strOutput & "</select>" & vbcrlf
blnDDL = False
End If
strOutput = strOutput & "</td></tr>" & vbcrlf
End If
Now we can write out the question itself. If the question is a drop-down list, we need to write out the <SELECT> tag:
If blnOdd Then
strOutput = strOutput & "<tr class=""oddrow"">"
Else
strOutput = strOutput & "<tr class=""evenrow"">"
End If
strOutput = strOutput & "<td class=""questionbackground"">" & _
rsSurvey("Question") & "</td><td class=""blankbackground""> & _
</td><td class=""answerbackground"">"
Select Case qType
Case "ddl" 'Drop-down list
strOutput = strOutput & "<select name=""" & strS & "~ddl~" & strQ & _
""" class=""answer"">" & vbcrlf
blnDDL = True
End Select
End If
As you will see below, we use a Case statement to process the different types of questions. The name of the choice field on the form is comprised of the SectionID, question type abbreviation and the QuestionID. The value of the field is the either the ChoiceID, or the text answer, depending on the question type:
strC = rsSurvey("ChoiceID")
strCD = rsSurvey("ChoiceDescription")
Select Case qType
Case "chk" 'Check box
strOutput = strOutput & "<input type=""checkbox"" name=""" & strS & "~chk~" & _
strQ & """ value="""
strOutput = strOutput & strC & """ class=""answer"">" & strCD & "<br>" & vbcrlf
Case "mlt" 'Multiple choice
strOutput = strOutput & "<input type=""radio"" name=""" & strS & "~mlt~" & _
strQ & """ value="""
strOutput = strOutput & strC & """ class=""answer"">" & strCD & "<br>" & vbcrlf
Case "tta" 'Text area
strOutput = strOutput & "<textarea name=""" & strS & "~tta~" & _
strQ & """ class=""answer"" cols=""50"" rows=""5""></textarea>" & vbcrlf
Case "txt" 'Text field
strOutput = strOutput & "<input type=""text"" name=""" & strS & "~txt~" & _
strQ & """ class=""answer"" size=""50"">" & vbcrlf
Case "ddl" 'Drop-down list
strOutput = strOutput & "<option value=""" & strC & """ class=""answer"">" & strCD & vbcrlf
End Select
Finally, we store the current record's QuestionID in our intQuestion variable before moving on to the next record so that we can check to see if the next record is either a new question or yet another choice for this question. Once we have finished looping through the recordset, we write out the entire survey that has been stored in the strOutPut variable:
intQuestion = intTemp
rsSurvey.MoveNext
Loop
Response.Write strOutput
%>
The last piece of functionality on this page worth mentioning is the JavaScript form validation that comes into play if the survey is not anonymous. If the respondent leaves any of the contact details fields empty, or enters invalid data in the Name or Telephone Number fields, they will be alerted as such, and will not be able to submit the form until the necessary corrections have been made.
This page processes the completed survey as follows:
First, we check to see if it is an anonymous survey or not. If it is not anonymous, we check to see if the respondent's contact details are in the database already. If they are, we retrieve the RespondentID. If not, we add the respondent and then retrieve the RespondentID.
Note: Ideally, we should wrap this code up in a stored procedure, and there are several other areas in the application where this would have merit. I have kept the queries in the pages though so that those of you who wish to migrate this to Microsoft Access find it easier!
Then we check to see if the respondent has already completed the survey, if they have, we redirect them to an error page, surveyerror.asp with an appropriate message:
IsAnon = Request("IsAnon")
If IsAnon = 1 Then
RespondentID = "NULL"
Else
Name = Replace(Request("Name"), "'", "''")
Surname = Replace(Request("Surname"), "'", "''")
Email = Replace(Request("Email"), "'", "''")
Telephone = Request("Telephone")
strSQL = "SELECT ID FROM SurveyRespondent WHERE Name = '" & _
Name & "' AND Surname = '" & Surname & "'"
Set rsRespondee = Server.CreateObject("ADODB.Recordset")
rsRespondee.Open strSQL, Conn
If Not rsRespondee.EOF Then
RespondentID = rsRespondee("ID")
Else
strSQL = "INSERT INTO SurveyRespondent (Name, Surname, Email, Telephone) " & _
"VALUES ('" & Name & _
"', '" & Surname & "', '" & Email & "', '" & Telephone & "')"
Conn.Execute strSQL
strSQL = "SELECT ID FROM SurveyRespondent WHERE Name='" & _
Name & "' AND Surname = '" & Surname & "'"
Set rsID = Server.CreateObject("ADODB.Recordset")
rsID.Open strSQL, Conn
If Not rsID.EOF Then
RespondentID = rsID(0)
End If
rsID.Close
Set rsID = Nothing
End If
rsRespondee.Close
Set rsRespondee = Nothing
'check to see if the user has already responded to this query
strSQL = "SELECT SurveyID, RespondentID FROM SurveyResult WHERE SurveyID = " & SurveyID & _
" AND RespondentID = " & RespondentID
Set rsSurvey = Server.CreateObject("ADODB.Recordset")
rsSurvey.Open strSQL, Conn
If Not rsSurvey.EOF Then
Response.Redirect "surveyerror.asp?" & _
"Msg=You have already completed this survey. You cannot complete it again."
Response.End
End If
rsSurvey.Close
Set rsSurvey = Nothing
End If
Here is the error screen:
Now we loop through all the form fields and write the answers into the SurveyResult table, question by question:
For Each Item In Request.Form
iPos = Instr(1, Item, "~")
iPos2 = Instr(iPos + 1, Item, "~")
If iPos > 0 And iPos2 > iPos Then
SectionID = Left(Item, iPos - 1)
QuestionID = Mid(Item, iPos2 + 1)
Answer = Trim(Replace(Request(Item), "'", "''"))
ItemType = Mid(Item, iPos + 1, iPos2 - (iPos + 1))
Select Case ItemType
Case "chk"
i = InStr(1, Answer, ",")
Do While i > 0
strSQL = "INSERT INTO SurveyResult (SurveyID, RespondentID, " & _
"SectionID, QuestionID, ChoiceID) VALUES (" & _
SurveyID & ", " & RespondentID & ", " & SectionID & _
", " & QuestionID & ", " & Left(Answer, i - 1) & ")"
Conn.Execute strSQL
Answer = Trim(Mid(Answer, i + 1))
i = InStr(1, Answer, ",")
Loop
Answer = Trim(Answer)
If Trim(Answer) <> "" Then
strSQL = "INSERT INTO SurveyResult (SurveyID, RespondentID, " & _
"SectionID, QuestionID, ChoiceID) VALUES (" & _
SurveyID & ", " & RespondentID & ", " & SectionID & _
", " & QuestionID & ", " & Answer & ")"
Conn.Execute strSQL
End If
Case "mlt", "ddl"
strSQL = "INSERT INTO SurveyResult (SurveyID, RespondentID, " & _
"SectionID, QuestionID, ChoiceID) VALUES (" & _
SurveyID & ", " & RespondentID & ", " & SectionID & _
", " & QuestionID & ", " & Answer & ")"
Conn.Execute strSQL
Case "txt", "tta"
strSQL = "INSERT INTO SurveyResult (SurveyID, RespondentID, " & _
"SectionID, QuestionID, Answer) VALUES (" & _
SurveyID & ", " & RespondentID & ", " & SectionID & _
", " & QuestionID & ", '" & Answer & "')"
Conn.Execute strSQL
End Select
End If
Next
The last step, before redirecting the respondent to the surveycomplete.asp page is to check if the survey administrator requires email notification. If it is required, we build a simple email message and send it using CDONTS:
strSQL = "SELECT SurveyName, SurveyAdminEmail, isEmail FROM Survey WHERE ID = " & SurveyID
Set rsSurvey = Conn.Execute(strSQL)
If Not rsSurvey.EOF Then
If rsSurvey("IsEmail") Then
MailStr = ""
MailStr = "Hi there," &vbcrlf &vbcrlf
MailStr = MailStr & "Another person has responded to your " & _
rsSurvey("SurveyName") & " Survey." & vbcrlf & vbcrlf
MailStr = MailStr & "Regards," & vbcrlf
MailStr = MailStr & " The Intranet Team"
set objMail = Server.CreateObject("CDONTS.NewMail")
objMail.To = rsSurvey("SurveyAdminEmail")
objMail.From = Application("WebmasterMail")
objMail.Value("Reply-To") = Application("WebmasterMail")
objMail.Subject = "Survey Response"
objMail.Body = Mailstr
objMail.Send
set objMail = Nothing
End If
End If
.
.
Finally, the respondent is redirected:
Clicking on the view results link on the default.asp page under survey admin will load the surveyresults.asp page:
If the survey is not an anonymous one, the administrator will be given the choice of viewing the overall results of the survey, or viewing the results per respondent as can be seen above.
Clicking on the View Results by Person link takes the administrator to the selectperson.asp page.
This page displays a list of users that have completed the selected survey:
The code checks to see if the survey is not anonymous, redirecting the administrator to the default.asp page if it is:
strSQL = "SELECT SurveyName, IsAnon FROM Survey WHERE ID = " & SurveyID
Set rsS = Server.CreateObject("ADODB.Recordset")
rsS.Open strSQL, Conn
If Not rsS.EOF Then
Heading = rsS("SurveyName")
IsAnon = rsS("IsAnon")
End If
rsS.Close
Set rsS = Nothing
If IsAnon Then Response.Redirect "default.asp"
.
.
The SQL query used to return the list of users excludes anonymous entries because of the INNER JOIN condition:
strSQL = "SELECT DISTINCT ID, Name, Surname FROM SurveyRespondent " & _ "INNER JOIN SurveyResult ON ID = RespondentID WHERE SurveyID = " & SurveyID strSQL = strSQL & " ORDER BY Name, Surname"
Clicking on a person's name will take the administrator to the resultsbyperson.asp page that is discussed next.
This page displays the survey as completed by the respondent that the administrator selected.
The code used to display the results is very similar to that found in survey.asp. Essentially, this page builds on survey.asp, including the respondent's answers instead of the choices that are associated with the questions.
The SQL query used to retrieve the information is as follows:
strSQL = "SELECT SS.ID AS [SectionID], SS.SectionName, SS.SectionOrder, " & _
"SQ.ID AS [QuestionID], SQ.QuestionOrder, SQC.ChoiceOrder, " & _
"SQC.ChoiceID, SQT.Value, SS.SectionDescription, SQ.Question, SC.ChoiceDescription, " & _
"SR.ChoiceID as AnsChoice, SR.Answer as AnsText " & _
"FROM Survey S " & _
"INNER JOIN SurveySection SS ON S.ID = SS.SurveyID " & _
"INNER JOIN SurveyQuestion SQ ON SS.ID = SQ.SectionID " & _
"LEFT OUTER JOIN SurveyQuestionChoice SQC ON SQ.ID = SQC.QuestionID " & _
"LEFT OUTER JOIN SurveyChoice SC ON SQC.ChoiceID = SC.ID " & _
"INNER JOIN SurveyQuestionType SQT ON SQ.QuestionTypeID = SQT.ID " & _
"INNER JOIN SurveyResult SR ON SQ.ID = SR.QuestionID " & _
"WHERE S.ID = " & SurveyID & " " & _
"AND SR.RespondentID = " & RespondentID & " " & _
"ORDER BY SS.SectionOrder, SQ.QuestionOrder, SQC.ChoiceOrder"
Set rsSurvey = Server.CreateObject("ADODB.Recordset")
rsSurvey.Open strSQL, Conn
The only difference between this query and the one on survey.asp is that we perform an INNER JOIN onto the SurveyResult table to incorporate the respondent's answers into the recordset returned.
Refer back to the section on survey.asp earlier in this article to see how we loop through the recordset and build up the string outputted, as the code is very similar.
This page displays the overall results for the survey selected by the administrator:
For the overall results page, each question is displayed, along with the total number of responses to the question, the number of responses per choice for a question, and the percentage of the total number of responses to a question that a choice received.
This was done so that the survey administrators could quickly, and easily, review the results for a survey and hone in on a particular question should they need to.
Let us walk through the code:
First, we retrieve all the survey's sections, questions and choices as before:
strSQL = "SELECT SS.ID AS [SectionID], SS.SectionName, SS.SectionOrder, " & _
"SQ.ID AS [QuestionID], SQ.QuestionOrder, SQC.ChoiceOrder, " & _
"SQC.ChoiceID, SQT.Value, SQ.Question, SC.ChoiceDescription " & _
"FROM Survey S " & _
"INNER JOIN SurveySection SS ON S.ID = SS.SurveyID " & _
"INNER JOIN SurveyQuestion SQ ON SS.ID = SQ.SectionID " & _
"LEFT OUTER JOIN SurveyQuestionChoice SQC ON SQ.ID = SQC.QuestionID " & _
"LEFT OUTER JOIN SurveyChoice SC ON SQC.ChoiceID = SC.ID " & _
"INNER JOIN SurveyQuestionType SQT ON SQ.QuestionTypeID = SQT.ID " & _
"WHERE S.ID = "& SurveyID & _
"ORDER BY SS.SectionOrder, SQ.QuestionOrder, SQC.ChoiceOrder"
Set rsSurvey = Server.CreateObject("ADODB.Recordset")
rsSurvey.Open strSQL, Conn
.
.
Then we start looping through the recordset, checking each row to see if we have a new question. If we do, we retrieve the total number of responses for the question:
.
.
If intQuestion <> intTemp Then
'new question
IsFirstAnswer = True
'retrieve the total number of responses for this question
strSQL = "SELECT Count(SurveyID) AS [Responses] FROM SurveyResult WHERE SurveyID = " & SurveyID & _
" AND SectionID = " & strS & " AND QuestionID = " & strQ
Set rsResponse = Conn.Execute(strSQL)
If Not rsResponse.EOF Then
TotResponse = rsResponse("Responses")
End If
rsResponse.Close
Set rsResponse = Nothing
strOutput = strOutput & "<tr><td class=""questionbackground"">" & rsSurvey("Question") & "</td>"
strOutput = strOutput & "<td class=""tableleft"">" & TotResponse & "</td>"
Else
If Not(IsFirstAnswer) Then
strOutput = strOutput & "<tr><td class=""questionbackground""> </td>"
strOutput = strOutput & "<td class=""tableleft""> </td>"
End If
End If
.
.
Now, for each choice in the question, we retrieve the number of responses for that choice. This results in a call to the database for each choice in the survey, but it was the simplest solution:
.
.
Case "mlt" 'Multiple choice
strSQL = "SELECT Count(SurveyID) AS [Responses] FROM SurveyResult WHERE SurveyID = " & _
SurveyID & _
" AND SectionID = " & strS & " AND QuestionID = " & strQ & " AND ChoiceID = " & strC
Set rsResponse = Conn.Execute(strSQL)
If Not rsResponse.EOF Then
strOutput = strOutput & "<td class=""tablemiddle"">"
strOutput = strOutput & "<b>" & strCD & "</b>"
strOutput = strOutput & " - " & rsResponse("Responses") & "</td>"
strOutput = strOutput & "<td class=""tableright"">"
intResponse = rsResponse("Responses")
If IsNull(intResponse) Then intResponse = 0
If TotResponse = 0 Then
strOutput = strOutput & "-</td></tr>"
Else
strOutput = strOutput & FormatNumber(intResponse / TotResponse * 100, 1) & _
"%</td></tr>"
End If
End If
rsResponse.Close
Set rsResponse = Nothing
Case "tta" 'Text area
strOutput = strOutput & "<td class=""tablemiddle"">"
strOutput = strOutput & "<a href=""Javascript:openWindow('viewtextanswers.asp?SurveyID=" & _
SurveyID & "&SectionID=" & strS & "&QuestionID=" & _
strQ & "&IsAnon=" & IsAnon & _
"');"" class=""link"">view text answers</a>"
strOutput = strOutput & "</td><td class=""tableright"">-</td>"
When we encounter a question that is of type Text (Single line) or Text (Multiple lines), instead of writing out each response, we provide a link to viewtextanswers.asp which opens in a new window and displays all the text answers for the question along with the respondent's name as can be seen below:
The code used to retrieve the answers is straightforward. There is a check to see if the survey is an anonymous one or not as can be seen below:
If IsAnon Then
strSQL = "SELECT Answer FROM SurveyResult WHERE SurveyID = " & SurveyID & " AND SectionID = " & _
SectionID & " AND QuestionID = " & QuestionID & " AND ChoiceID IS NULL AND Answer IS NOT NULL"
Else
strSQL = "SELECT Name + ' ' + Surname AS Fullname, Answer FROM SurveyResult INNER JOIN " & _
"SurveyRespondent ON SurveyResult.RespondentID = SurveyRespondent.ID WHERE SurveyID = " & _
SurveyID & " AND SectionID = " & SectionID & " AND QuestionID = " & QuestionID & _
" AND ChoiceID IS NULL AND Answer IS NOT NULL"
End If
Set rsA = Server.CreateObject("ADODB.Recordset")
rsA.Open strSQL, Conn
.
.
And that's it! You now have a complete, stand-alone solution for online surveys. The online survey generator can be plugged straight into your existing internet or intranet site and you can start churning out surveys to your heart's content.
To sum it all up, here is a rundown of the features of the online survey generator:
Q189657
HOWTO: Use the ADO SHAPE Command
Q213856
- INFO: Using Disconnected Hierarchical Recordsets
PRB:
SHAPE Provider Pulls Down All Records in Child
Table
|
| |||||||
|
|||||||||||||||||||||||||||||
| |||||||||||||||
|
| ASPToday is brought to you by
Wrox Press (http://www.wrox.com/). Please see our terms
and conditions and privacy
policy. ASPToday is optimised for Microsoft Internet Explorer 5 browsers. Please report any website problems to [email protected]. Copyright © 2001 Wrox Press. All Rights Reserved. |