Working with a client's Intranet team, I could not help noticing that whenever the Corporate Communications division requested that the team put a survey online on their Intranet, which was on a fairly regular basis, the team went away and built the survey from scratch. This was not the best approach for several reasons:

• The developers waste time producing said surveys, instead of working on the really juicy higher priority projects.
• Corporate communications are constantly calling up the developers to make slight alterations to wording, or to correct spelling mistakes.
• There was not a consistent look and feel across the surveys that were produced.

As such, this was not a very efficient solution. I approached both parties and suggested that I could develop an online survey generator that would empower the corporate guys to produce their own surveys in their own time, and free up the developers from what was essentially a mind-numbing task. Needless to say, I got the green light to produce the system.

Next, we needed to assess exactly what would be needed for an online survey. Based on previous paper-based surveys and the few that the Intranet team had produced online we came up with the following simple model:

• A survey needs to have a name, description and contact information for the "owner" of the survey.
• A survey should be broken down into one or more sections, each section with it's own description.
• A section consists of one or more questions.
• Each question is of a particular type:

• Multiple choice (Radio Buttons) - the user selects a single choice from the options provided
• Drop-down list - the user selects a single choice from the list.
• Check boxes - the user can select one or more choices.
• Text - single line (Text Field) - the user types in their answer / choice.
• Text - multiple lines (Text Area) - the user types in their answer / choice.

• Questions other than those of type Text can have one or more choices, and a user would be able to select their choice(s).

(Note: The application has been built making extensive use of Cascading Style Sheets (CSS), focusing solely on the Internet Explorer browser and as such, the pages will not be formatted correctly under Netscape's browser. The focus on IE alone was a feasible one, as this application was to be used on the client's Intranet and they had standardized on IE throughout the company.)

Armed with this information, we can proceed with designing the database.

Database Structure

Note on the support material: I have provided two sets of source code in the support material: one for running the application on a SQL Server database, and the other for running the application on a Microsoft Access database. The reason being that there are some differences in the SQL queries used in SQL Server and Access. For example, Microsoft Access doesn't support SQL Server's Convert() function.

I've tried to keep the database fairly normalized where possible. The diagram below will show you the relationships between the tables:

There is referential integrity in the database and the code.

The relationships between tables is as follows:

• Survey - one to many - SurveySection
• SurveySection - one to many - SurveyQuestion
• SurveyQuestion - one to one - SurveyQuestionType
• SurveyQuestion - one to one - SurveyQuestionChoice
• SurveyChoice - one to one - SurveyQuestionChoice

Let us examine each table. I cover the field name and it's description. Should you want to find out the data type of each field, examine the table in the database:

Survey - contains all the high-level information relating to a survey: it's name, description and contact details.

This table consists of the following fields:

• ID - The unique identifier and primary key for this table
• SurveyName - The name of the survey
• SurveyAdministrator - The name of the administrator or owner of this survey
• SurveyAdminEmail - The administrator or owner's email address
• SurveyAdminTel - The administrator or owner's telephone number
• SurveyDescription - The blurb about the survey

SurveySection - contains the information about a section: it's name, description, the survey it belongs to, and its order in the list of sections for the survey.

This table consists of the following fields:

• ID - The unique identifier and primary key for this table
• SurveyID - The ID of the survey that this section is linked to
• SectionName - The name of the section
• SectionOrder - The order of this section in the survey
• SectionDescription - The blurb about the section

SurveyQuestion - contains the information about a question: the question itself, the section it belongs to and it's order in the list of questions for the section.

This table consists of the following fields:

• ID - The unique identifier and primary key for this table
• SectionID - The ID of the section that this question is linked to
• QuestionOrder - The order of this question in the section
• QuestionTypeID - The ID of this question's type
• Question - The text of the question

SurveyQuestionType - contains the information about the question type (this is a system table). Each question will tie to one of these types.

This table consists of the following fields:

• ID - The unique identifier and primary key for this table
• Name - The name of the type, be it a Checkbox, Drop-down list, etc
• Type - An abbreviated description of the type, e.g. chk, ddl, etc

SurveyChoice - contains all the choices available in the system. Each question will have one or more choices.

This table consists of the following fields:

• ID - The unique identifier and primary key for this table
• ChoiceDescription - The text of the choice

SurveyQuestionChoice - this table links choices to questions in the system, as well as the order in which the choices will appear for each question.

This table consists of the following fields:

• QuestionID - The ID of the question (forms the primary key with ChoiceID)
• ChoiceID - The ID of the choice (forms the primary key with QuestionID)
• ChoiceOrder - The order of this choice in the question

Piecing the System Together

Source code: The source material contains all the source code and includes a SQL script file to create the database on SQL Server as well as an Access database with a sample survey loaded already. Feel free to implement whichever database suits you.

Note: There is a file called conn.inc in the include directory of the source code. 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.

There are a few ways in which I could have designed the admin interface into the system. Two that spring to mind are:

• An admin page consisting of links into all the areas of the system

• A drill-down approach where the user is required to capture a survey before progressing to survey sections, etc.

I adopted the approach of drilling down into the survey in an attempt to provide the Corporate Communications guys (who are not very technical at all) with a logical, step-by-step method of capturing a survey. Create a new survey, add sections to the survey, add questions to the sections, and finally add choices to the questions.

Let us step through the entire process:

Displaying and editing the surveys in the system

/admin/Default.asp

This page is the entry point into the system. No security, for example in the form of user access via a login page, has been built into this system as I wanted to make the system as generic as possible. This page displays a list of surveys in the system.

The SQL query used to return these results looks like this:

strSQL = "SELECT SurveyName AS [Survey], SurveyAdministrator AS [Administrator], "
strSQL = strSQL & "'edit / "
strSQL = strSQL & "delete / "
strSQL = strSQL & "view details' AS [Action] FROM Survey"

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 write the output to the screen as 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 & "    "
              For i = 0 To fldCount
              Select Case i
                     Case 0
                        strOutput = strOutput & ""
                        Case fldCount
                        strOutput = strOutput & ""
                     Case Else
                        strOutput = strOutput & ""
              End Select
              strOutput = strOutput & rsSurvey.Fields(i).Value & "" & vbCrlf
              Next
              strOutput = strOutput & ""
              rsSurvey.MoveNext
           Loop
           Response.Write strOutput
       End If
  %>

For more information on this approach, see my previous 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:

This is a pretty simple 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 ourselves.

The delete code looks like this:

  <%
  Conn.BeginTrans
  strSQL = "DELETE FROM SurveyQuestionChoice WHERE QuestionID IN "
  strSQL = strSQL & "(SELECT ID FROM SurveyQuestion WHERE SectionID IN "
  strSQL = strSQL & "(SELECT ID FROM SurveySection WHERE SurveyID = " &   SurveyID & "))"
  Conn.Execute strSQL
  strSQL = "DELETE FROM SurveyQuestion WHERE SectionID IN "
  strSQL = strSQL & "(SELECT ID FROM SurveySection WHERE SurveyID = " &   SurveyID & ")"
  Conn.Execute strSQL
  strSQL = "DELETE FROM SurveySection WHERE SurveyID = " & SurveyID
  Conn.Execute strSQL
  strSQL = "DELETE FROM Survey WHERE ID = " & SurveyID
  Conn.Execute strSQL
  Conn.CommitTrans
  %>

The SQL delete statements are encapsulated in a transaction so that should the delete fail along the way, everything will rollback, thus avoiding compromising the integrity of the data.

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.

At the bottom of the table I display a count of all the surveys in the table. This value is stored in the rowCount variable that you can see in the code above.

/admin/editsurvey.asp

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. The submit button in this case is a standard input button that calls the validate() JavaScript function on the onclick() event. Validation was very important in this system because we wanted to make it as foolproof as possible, as the users are not very computer literate.

This is a pretty useful validation function that you could use with some small customization on any of your own pages. The validate() function makes use of some other functions, namely isTelNum(), isAlpha() and isEmail(), which you could plug straight into your pages with no changes. If you don't make any use of the Online Survey Generator, at least make sure you look at these functions, as they will help quite a bit when it comes to client-side validation of forms.

Once the database has been updated with the changes, the user is redirected to the list of surveys (/admin/default.asp).

Displaying and editing the sections in the selected survey

/admin/surveydetails.asp

This page displays a list of all sections for the selected survey. If a SurveyID is not passed through to the page, the user is redirected to the /admin/default.asp page (this is the approach that I implemented, as I wanted to keep the error handling to a minimum.) The sections appear in the order defined by the Section Order value that we will see on the next screen. As with the page that displays the list of surveys, the user is able to add, edit, delete or view the details of a section. At the bottom of the screen is a link that the user can click on to drill back up to the survey level.

Clicking on the view details link drills the user down into the selected section, presenting a list of questions associated to the section, as can be seen further down in this article.

/admin/editsection.asp

This page, in conjunction with the doeditsection.asp page, handles the adding and editing of section details. If a SurveyID is not passed through to the page, the user is redirected to the /admin/default.asp page. If a SectionID is passed to the page, we are editing an existing section and the submit button reflects as much, otherwise it displays Add Section.

The most interesting part of this screen is the Section Order drop-down list. This was initially a text field where the user could type in a value for the section order. Standard JavaScript validation was in place to make sure that the user inputted numeric values only, but this turned out to not be enough. As the users rightly pointed out, keeping track of the next available section order value was a nuisance, and moving a section up or down in the order became very messy, with duplicate section orders. I resolved it with the following piece of code:

strSQL = "SELECT MAX(SectionOrder) AS [Max] FROM SurveySection WHERE SurveyID = " & SurveyID
Set rsMax = Conn.Execute(strSQL)
If Not rsMax.EOF Then
   If IsNull(rsMax("Max")) Then
      Max = 0
   Else
      Max = rsMax("Max")
   End If
End If
rsMax.Close
Set rsMax = Nothing

First, I retrieve the maximum SectionOrder value for the selected survey using the MAX() function in SQL. If there are no sections for the selected survey, we set the Max value to 0:

If Not(ID = "" Or IsNull(ID)) Then
Else
   Max = Max + 1
   SectionOrder = Max
End If

Next, we check to see if we are adding or editing a section by checking the value of the ID variable. If we are editing an existing section, the Max value and the SectionOrder stays the same for the section. If we are adding a new section however, we need to increment the Max value by 1 as we now have an extra section to include in the ordering. We also set the SectionOrder for the new section to the Max value as we are assuming that the user will be adding this section to the end of the survey. The user can of course, change this at any time.

  <%
     For i = 1 to Max
        If i = SectionOrder Then
  %>
   <%=i%>
  <%
        Else
  %>
   <%=i%>
  <%
        End If
     Next
  %>

We store the initial value of the SectionOrder variable in a hidden field called OrigOrder. This value will be used in the /admin/doeditsection.asp page to determine whether we need to update the section order values of all the sections or not.

Finally, we build the drop-down list using the Max value for the number of sections, and the SectionOrder value to set the selected value.

On the /admin/doeditsection.asp page which handles the updating of the database, I use the following code to update the section order values of all the sections in the selected survey:

SectionOrder = Request("SectionOrder")
OrigOrder = Request("OrigOrder")

First, we store the values passed through to the page in the defined variables. OrigOrder contains the original section order value for the section, before editing, and SectionOrder contains the section order value after editing.

If OrigOrder <> SectionOrder Then
   'rearrange the order of sections before inserting / updating the current section
   strSQL = "SELECT ID, SectionOrder FROM SurveySection WHERE SurveyID = " & SurveyID & _
   " AND SectionOrder BETWEEN "
   If OrigOrder > SectionOrder Then
      strSQL = strSQL & SectionOrder & " AND " & OrigOrder
   Else
      strSQL = strSQL & OrigOrder & " AND " & SectionOrder
   End If
   Set rsOrder = Conn.Execute(strSQL)
   If Not rsOrder.EOF Then
      Do While Not rsOrder.EOF
         If OrigOrder > SectionOrder Then
            strSQL = "UPDATE SurveySection SET SectionOrder = SectionOrder + 1 WHERE ID = " & rsOrder("ID")
            Conn.Execute strSQL
         Else
            strSQL = "UPDATE SurveySection SET SectionOrder = SectionOrder - 1 WHERE ID = " & rsOrder("ID")
            Conn.Execute strSQL
         End If
         rsOrder.Movenext
      Loop
   End If
   rsOrder.Close
   Set rsOrder = Nothing
End If

This section of code selects all sections that are affected by the re-ordering of the current section. Then, if we have moved our section up in the ordering, all the sections between our section's new position and its old position are incremented by 1 to move them to a lower display order. The sections will move to a higher display order if the opposite occurs and our section moves to a lower display order.

As you will see further on, I have implemented this drop-down list approach throughout the rest of the system to handle the ordering of questions and sections too. I like it because it restricts the user's choice when it comes to ordering to a valid set of options. Also, the user makes the change to the section's order in one place and the code handles the updating of the other sections.

Displaying and editing the questions in the selected section

/admin/sectiondetails.asp

This page displays a list of all questions for the selected section. If either the SurveyID or the SectionID is not passed through to the page, the user is redirected to the /admin/default.asp page. The questions appear in the order defined by the question order value. The question's type is also displayed to provide the user with a quick view of the section's structure. As with the page that displays the list of sections, the user is able to add, edit, delete or view the details of a question. At the bottom of the screen is a link that the user can click on to drill back up to the section level, or even higher to survey level.

Clicking on the view details link drills the user down into the selected question, presenting a list of choices associated to the question, as can be seen further down in this article.

/admin/editquestion.asp

This page, in conjunction with the doeditquestion.asp page, handles the adding and editing of question details. This page uses the same functionality explained earlier in the article to create the Question Order drop-down list. The only other bit of functionality is a check built into the page for when the user changes the question type from one that has choices to a text type. A user is warned that proceeding with the change will result in all the choices being deleted from the system.

Displaying and editing the choices in the selected question

/admin/questiondetails.asp

This page displays the question, it's type, and a list of choices if the question's type allows for choices. If either the SurveyID, SectionID or QuestionID is not passed through to the page, the user is redirected to the /admin/default.asp page.

The choices appear in the order defined by the Choice Order value. The user is able to add, edit or delete a choice to the selected question. At the bottom of the screen is are links that the user can click on to drill back up to the question, section or survey level.

/admin/editchoice.asp

This page, in conjunction with the doeditchoice.asp page, handles the adding and editing of choice details. This page uses the same functionality explained earlier in the article to create the Choice Order drop-down list.

When adding a choice, a user can either select a choice from the drop-down list, or create a new one by typing in a new one. If the user opts to enter a new choice, and this choice already exists in the system, the new choice is not inserted but the ID of the existing one is used instead.

When editing a choice, a user can only change the choice's order.

Summary

We now have the system in place that will be used to generate our online surveys. Our surveys meet the following user requirements:

• The survey needs to have a name, description and contact information for the "owner" of the survey.
• The survey should be broken down into one or more sections, each section with it's own description.
• A section should consist of one or more questions.
• Each question is of a particular type:

Multiple choice (Radio Buttons) - the user selects a single choice from the options provided

Drop-down list - the user selects a single choice from the list.

Check boxes - the user can select one or more choices.

Text - single line (Text Field) - the user types in their answer / choice.

Text - multiple lines (Text Area) - the user types in their answer / choice.

Questions other than those of type Text can have one or more choices, and a user would be able to select their choice(s).

We have also built in functionality to keep the administration / creation of a survey as "fool-proof" as possible by restricting the user to a valid range of numbers when specifying the Section, Question, and Choice-Order.

You can now display the surveys and process the results yourself, or wait until Part 2 where we will build onto the current functionality of the system.

Preview of Part 2

In Part 2 we will work on the following aspects of the survey generator:

• displaying the survey to the end-user
• adding functionality to capture the personal details of the survey respondent
• handling the capturing of the user's responses to the survey
• notifying the survey owner / administrator of a new response to the survey
• displaying overall and per-respondent survey results.

  RATE THIS ARTICLE Please rate this article (1-5). Was this article... Useful? No Yes, Very Innovative? No Yes, Very Informative? No Yes, Very Brief Reader Comments? Your Name: (Optional)	USEFUL LINKS Related Tasks: Click here to download the support material for this article Enter Technical Discussion on this Article Technical Support on this article - [email protected] See other articles in the Site Design category See other In-Depth articles Reader Comments on this article Go to Previous Article

  Related ASPToday Articles Some Useful Generic Functions for Formatting Data Returned from a DB (December 1, 2000) Search the ASPToday Living Book Index Full Text Advanced

  Index Entries in this Article administration console database design designing generating Intranet survey isAlpha function isEmail function isTelNum function JavaScript MAX function Normalization questions and answers SQL SQL DELETE statement SQL query surveys surveys, displaying surveys, editing validate function

  HOME | TODAY | INDEX | SEARCH | REFERENCE | FEEDBACK | ADVERTISE | SUBSCRIBE

  .NET Framework Components Data Access DNA 2000 E-commerce Performance Security Admin Site Design Scripting XML/Data Transfer Other Technologies

  
  

  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.