bombers.k12.ar.us/bombers.k12.ar.us.tar/bombers.k12.ar.us/survey_unconfigured/TakeSurveyAction.asp

Text File | 2006-06-27 | 40KB | 796 lines

<!--#Include File="Include/Top_inc.asp"-->
<%
'***********************************************************************
'   Application: SelectSurveyASP Advanced v8.1.9
'   Author: Aaron Baril for ClassApps.com
'   Page Description: This page works with TakeSurvey.asp, and process
'                     the survey once it has been submitted by a user.
'
'   COPYRIGHT NOTICE
'
'   See attached Software License Agreement
'
'   (c) Copyright 2002 - 2006 by ClassApps.com.  All rights reserved.
'***********************************************************************
%>
<!--#Include File="Include/Config_inc.asp"-->
<!--#Include File="Include/adovbs_inc.asp"-->
<!--#Include File="Include/ID_inc.asp"-->
<!--#Include File="Include/CurrentUser_inc.asp"-->
<!--#Include File="Include/SurveySecurity_inc.asp"-->
<!--#Include File="Include/Constants_inc.asp"-->
<!--#Include File="Include/Collection_inc.asp"-->
<!--#Include File="Include/Encryption_inc.asp"-->
<!--#Include File="Include/SurveyUtility_inc.asp"-->
<!--#Include File="Include/SurveyPageCondition_inc.asp"-->
<!--#Include File="Include/Utility_inc.asp"-->
<%
    Dim strSQL
    Dim conSurvey
    Dim conUpdate
    Dim conDelete
    Dim rsItems
    Dim rsAnswers
    Dim rsSubItems
    Dim rsPageConditions
    Dim rsSurvey
    Dim lngItemID
    Dim lngResponseID
    Dim lngEmailAddressID
    Dim lngSurveyID
    Dim lngAnonymousUserID
    Dim strAnswerText
    Dim strOtherText
    Dim vaAnswers
    Dim i
    Dim flgExecuteSQL
    Dim flgUpdatingSurvey
    Dim flgInsertedResponse
    Dim lngAnswerID
    Dim lngActualPageNumber
    Dim lngNextPageNumber
    Dim lngDisplayPageNumber
    Dim lngPageToDeleteAnswers
    Dim lngTotalPageCount
    Dim lngCurrentPageNumber
    Dim strResponseStartDate
    Dim strColumnsCollection
    Dim lngColumnCount
    Dim strEditValue
    Dim rsHiddenFields
    Dim strHiddenFieldValue
    Dim arrNameValuePair
    Dim arrQueryString
    Dim flgHadAnswerableQuestions
    Dim strItemIDValues

    'Initialization
    Set conSurvey = Server.CreateObject("ADODB.Connection")
    Set conUpdate = Server.CreateObject("ADODB.Connection")
    Set conDelete = Server.CreateObject("ADODB.Connection")
    Set rsItems = Server.CreateObject("ADODB.Recordset")
    Set rsHiddenFields = Server.CreateObject("ADODB.Recordset")
    Set rsAnswers = Server.CreateObject("ADODB.Recordset")
    Set rsSubItems = Server.CreateObject("ADODB.Recordset")
    Set rsPageConditions = Server.CreateObject("ADODB.Recordset")
    Set rsSurvey = Server.CreateObject("ADODB.Recordset")
    conSurvey.Open SURVEY_APP_CONNECTION
    lngSurveyID = DecryptSurveyID(Request.Form("SurveyID"))
    lngActualPageNumber = Request.Form("ActualPageNumber")
    lngCurrentPageNumber = Request.Form("ActualPageNumber")
    lngDisplayPageNumber = Request.Form("DisplayPageNumber")
    lngColumnCount = 0

    '10/17/2005
    Dim rsTmpAns
    Dim tmpAnsSql
    Set rsTmpAns = Server.CreateObject("ADODB.Recordset")



    'Record whether or not we are updating a survey
    If Request.Form("hdnUpdatingSurvey") = "True" Then
        flgUpdatingSurvey = True
    Else
        'Duplicate page submissions only matter on inserts.  When updating a survey (above), the extra submission still
        'ends up being a correct update of the previous values.

        'If the correct value is in Session, this is not an update
        If Session(SUR_UNIQUE_RESPONSE_IDENTIFIER) = CStr(lngSurveyID) & CStr(lngActualPageNumber) Then
            flgUpdatingSurvey = False
        Else
            'The correct value is not in session, so make this an update, and use the response ID from session
            If Len(Session(SUR_CURRENT_RESPONSE_ID)) = 0 Then
                flgUpdatingSurvey = False
            Else
                flgUpdatingSurvey = True
                lngResponseID = Session(SUR_CURRENT_RESPONSE_ID)
            End If
        End If
    End If

    'Clear out the value put into session for preventing duplicate responses
    Session(SUR_UNIQUE_RESPONSE_IDENTIFIER) = ""

    'Get all the items for the survey
    strSQL = "SELECT i.item_type_id As item_type_id, i.item_id As item_id " & _
                "FROM sur_item i, sur_survey_to_item_mapping m, sur_item_type it " & _
                "WHERE i.item_id = m.item_id " & _
                "AND m.page_number = " & lngActualPageNumber & _
                " AND it.item_type_id = i.item_type_id " & _
                "AND question_yn = " & SQLEncode(SUR_BOOLEAN_POSITIVE) & _
                " AND m.survey_id = " & lngSurveyID & _
                " ORDER BY m.order_number"
    rsItems.Open ConvertSQL(strSQL), SURVEY_APP_CONNECTION, adOpenForwardOnly, adLockReadOnly, adCmdText

    'If this was a new response, insert a row into the SUR_RESPONSE table.  Otherwise, simply store the value of
    'the previous response.  If there is a value in the ResponseID field, the entry has already been written to the
    'SUR_RESPONSE table
    If flgUpdatingSurvey = True Or Len(Request.Form("ResponseID")) > 0 Then
        'If the response ID was set above while checking if the user clicked the back button, do not set it again.
        If Len(lngResponseID) = 0 Then
            lngResponseID = Request.Form("ResponseID")
        End If

        'Track the fact that the user has responded to thiis page on this survey
        Session(SUR_SESSION_TAKING_SURVEY & lngSurveyID) = Session(SUR_SESSION_TAKING_SURVEY & lngSurveyID) & "Page" & lngActualPageNumber & "||"
    Else 'New survey
        'If the survey is not in manual entry mode and the current user has already taken the survey
        'and the survey does not allow multiple responses by a user, no more responses can be submitted.
        If IsManualResponseEntryMode(lngSurveyID) = False And HasUserCompletedSurvey(lngSurveyID) = True And IsMultipleResponseSurvey(lngSurveyID) = False Then
            Response.Redirect "AccessDenied.asp?SurveyID=" & lngSurveyID & "&Reason=" & SUR_ACCESS_DENIED_ALREADY_TAKEN
        End If

        'The rsItems recordset only contains records if there were answerable questions on the previous page.  If
        'there were no answerable questions on the previous page, do not record a response.
        If Not rsItems.EOF Then
            'Get ID for new response and create a response in the SUR_RESPONSE table
            lngResponseID = ID_GetNextAvailableID("SurveyGenerationResponse")

            'If the user is not logged in, get their anonymous user ID.
            If IsLoggedIn() = False Then
                lngAnonymousUserID = GetAnonymousUserID()
            Else
                lngAnonymousUserID = "Null"
            End If

            'Store the response ID in session in order to ensure that no page is submitted twice.
            Session(SUR_CURRENT_RESPONSE_ID) = CStr(lngResponseID)

            'Track the survey and pages responded to in a Session variable
            Session(SUR_SESSION_TAKING_SURVEY & lngSurveyID) = "||" & SUR_SESSION_TAKING_SURVEY & lngSurveyID & "||Page1||"

            'Set a cookie indicating that the user has taken the survey.  This cookie is based on the survey ID
            Response.Cookies(SUR_APPLICATION_COOKIE)(SUR_COOKIE_SURVEY & Request.ServerVariables("REMOTE_ADDR") & lngSurveyID) = lngResponseID
            Response.Cookies(SUR_APPLICATION_COOKIE).Expires = DateAdd("yyyy", 10, Date())

            'Record the email address ID in the database if there is one to associate with this suryey.
            If Len(Request.Form("EmailAddressID")) > 0 Then
                lngEmailAddressID = Request.Form("EmailAddressID")

                'Update the response count total
                strSQL = "UPDATE sur_email_message " & _
                         "SET email_response_count = email_response_count + 1 " & _
                         "WHERE email_message_id = " & Request.Form("EmailMessageID")
                conSurvey.Execute ConvertSQL(strSQL), , adCmdText

                'Update the history of emails sent out to indicate that a response was recorded for this item
                strSQL = "UPDATE sur_email_sent_history " & _
                         "SET response_date = GETDATE(), " & _
                         "response_id = " & lngResponseID & ", " & _
                         "current_status = " & SQLEncode(SUR_EMAIL_STATUS_RESPONDED) & _
                         " WHERE url_unique_id = " & SQLEncode(Request.Form("EID"))
                conSurvey.Execute ConvertSQL(strSQL), , adCmdText

            Else
                lngEmailAddressID = "Null"
            End If

            'If the response start datetime has any periods (.) in it, replace them with colons (:).  On a server with
            'European datetime format, the time is often rendered with periods, as in 04.55.33

            strResponseStartDate = Request.Form("ResponseStartDate")

            strSQL = "INSERT INTO sur_response(response_id, survey_id, email_address_id, anonymous_id, username, " & _
                        "response_start_date, ip_address, completed_yn) VALUES(" & _
                        lngResponseID & ", " & lngSurveyID & ", " & _
                        lngEmailAddressID & ", " & _
                        lngAnonymousUserID & ", "
            'If the response type is manual, do not record a username
            If Request.Form("ResponseType") = SUR_RESPONSE_TYPE_MANUAL Then
                strSQL = strSQL & SQLEncode(SUR_USER_MANUAL) & ", "
            Else
                strSQL = strSQL & SQLEncode(GetUsername()) & ", "
            End If
'**SB 6/22/2006
            strSQL = strSQL & SQLEncode(CustomDateFormatDisplay(strResponseStartDate)) & ", " & SQLEncode(Request.ServerVariables("REMOTE_ADDR")) & _
                        ", " & SQLEncode(SUR_BOOLEAN_NEGATIVE) & ")"


            conSurvey.Execute ConvertSQL(strSQL), , adCmdText


            'Record any hidden fields associated with the survey
            strSQL = "SELECT hidden_field_id, hidden_field_type, hidden_field_key " & _
                     "FROM sur_hidden_field " & _
                     "WHERE survey_id = " & lngSurveyID & _
                     " ORDER BY order_number"
            Set rsHiddenFields = conSurvey.Execute(ConvertSQL(strSQL), , adCmdText)
            If Not rsHiddenFields.EOF Then
                rsHiddenFields.MoveFirst
                Do While Not rsHiddenFields.EOF
                    strHiddenFieldValue = ""
                    Select Case rsHiddenFields("hidden_field_type")
                        Case SUR_HIDDEN_FIELD_TYPE_COOKIE
                            strHiddenFieldValue = Request.Cookies(rsHiddenFields("hidden_field_key"))
                        Case SUR_HIDDEN_FIELD_TYPE_QUERYSTRING
                            'Split the querystring to find the correct name/value pair
                            arrQueryString = Split(Request.Form("PostedQueryString"), "&")
                            For i = 0 To UBound(arrQueryString)
                                arrNameValuePair = Split(arrQueryString(i), "=")
                                If arrNameValuePair(0) = rsHiddenFields("hidden_field_key") Then
                                    strHiddenFieldValue = arrNameValuePair(1)
                                    Exit For
                                End If
                            Next
                        Case SUR_HIDDEN_FIELD_TYPE_SESSION
                            strHiddenFieldValue = Session(rsHiddenFields("hidden_field_key"))
                    End Select

                    'Insert the hidden field into the response
                    strSQL = "INSERT INTO sur_response_hidden_field (hidden_field_id, response_id, answer_text) VALUES(" & _
                             rsHiddenFields("hidden_field_id") & ", " & _
                             lngResponseID & ", " & _
                             SQLEncode(strHiddenFieldValue) & ")"
                    conSurvey.Execute ConvertSQL(strSQL), , adCmdText

                    rsHiddenFields.MoveNext
                Loop
            End If

            'Update the response count in the SUR_SURVEY table
            strSQL = "UPDATE sur_survey SET response_count = response_count + 1 WHERE survey_id = " & lngSurveyID
            conSurvey.Execute ConvertSQL(strSQL), , adCmdText

            flgHadAnswerableQuestions = True
        Else
            flgHadAnswerableQuestions = False
        End If
    End If

    'Make sure that the previous page had answerable questions
    If Not rsItems.EOF Then
        'Loop through all the items, extracting the response(s) for each question and inserting them into the database
        rsItems.MoveFirst
        Do While Not rsItems.EOF

            'Initialization
            strAnswerText = SUR_ANSWER_TEXT_NULL
            strOtherText = SUR_ANSWER_TEXT_NULL
            flgExecuteSQL = True
            lngItemID = rsItems("item_id")

            Select Case CLng(rsItems("item_type_id"))
                Case SUR_ITEM_HEADING, SUR_ITEM_IMAGE, SUR_ITEM_MESSAGE, SUR_ITEM_HTML, SUR_ITEM_HORIZONTAL_RULE:
                    'No need to execute SQL since this is just a heading
                    flgExecuteSQL = False
                Case SUR_ITEM_YES_NO, SUR_ITEM_TRUE_FALSE:
                    lngAnswerID = SUR_ANSWER_ID_NULL
                    strOtherText = SUR_ANSWER_TEXT_NULL

                    'If NONE is selected, then use the NULL value; otherwise, get the answer text
                    If Request.Form("QID" & CStr(lngItemID)) = SUR_COMBO_NONE Then
                        strAnswerText = SUR_ANSWER_TEXT_NULL
                    Else
                        'If no anwer was selected, use the NULL value; otherwise, get the answer text
                        If Len(Trim(Request.Form("QID" & CStr(lngItemID)))) > 0 Then
                            strAnswerText = Trim(Request.Form("QID" & CStr(lngItemID)))
                        Else
                            strAnswerText = SUR_ANSWER_TEXT_NULL
                        End If
                    End If
                Case SUR_ITEM_SINGLE_SELECT_DROPDOWN:
                    'If NONE is selected, then use the NULL value; otherwise, get the answer text
                    strAnswerText = SUR_ANSWER_TEXT_NULL
                    If Request.Form("QID" & CStr(lngItemID)) = SUR_COMBO_NONE Then
                        lngAnswerID = SUR_ANSWER_ID_NULL
                    Else
                        lngAnswerID = Request.Form("QID" & CStr(lngItemID))
                    End If
                    strOtherText = SUR_ANSWER_TEXT_NULL
                Case SUR_ITEM_SINGLE_SELECT_OPTIONS:
                    'If no answer is specified, use the NULL value
                    strAnswerText = SUR_ANSWER_TEXT_NULL
                    If Len(Request.Form("QID" & CStr(lngItemID))) = 0 Then 'No answer specified
                        lngAnswerID = SUR_ANSWER_ID_NULL
                    Else
                        'If the "OTHER" answer is selected, set the answer ID to NULL
                        If Request.Form("QID" & CStr(lngItemID)) = SUR_ANSWER_OTHER Then
                            lngAnswerID = SUR_ANSWER_ID_NULL
                        Else
                            lngAnswerID = Request.Form("QID" & CStr(lngItemID))
                        End If
                    End If

                    'See if the "OTHER" option was selected.
                    If Request.Form("QID" & CStr(lngItemID)) = SUR_ANSWER_OTHER Then
                        strAnswerText = SUR_ANSWER_TEXT_OTHER_SELECTED
                        strOtherText = Trim(Request.Form("QID" & CStr(lngItemID) & "OTHERTEXT"))

                        'If no text was entered in the "Other" text box, use the null value
                        If Len(strOtherText) = 0 Then
                            strOtherText = SUR_ANSWER_TEXT_NULL
                        End If
                    End If
                Case SUR_ITEM_MATRIX_SINGLE_SELECT_OPTIONS, SUR_ITEM_MATRIX_RATING_SCALE:
                    'Set a flag to indicate whether or not a row has been inserted for the option buttons.  It's possible not to
                    'insert any data if the question is not required and the user does not enter any values.  For correctness and
                    'consistency in the reporting, it's necessary to have an entry for "null" responses to the checkboxes
                    flgInsertedResponse = False

                    'For updates, delete all of the previous answers before inserting the current set of answers.
                    If flgUpdatingSurvey = True Then
                        strSQL = "DELETE FROM sur_response_answer WHERE response_id = " & lngResponseID & " AND item_id = " & lngItemID
                        conSurvey.Execute ConvertSQL(strSQL), , adCmdText
                    End if

                    'Loop through each of the subitems and see if an option button was selected.  If so, insert an
                    'entry into the database.
                    strSQL = "SELECT subitem_id, subitem_text FROM sur_subitem WHERE item_id = " & lngItemID & " ORDER BY order_number"
                    Set rsSubItems = conSurvey.Execute(ConvertSQL(strSQL), , adCmdText)

                    rsSubItems.MoveFirst
                    Do While Not rsSubItems.EOF
                        'If an answer was submitted for this item, insert a record into the database
                        If Len(Trim(Request.Form("QID" & CStr(lngItemID) & "||SIID" & rsSubItems("subitem_id")))) > 0 Then
                            'For matrix rating scale questions, record the numeric value as the answer text
                            If CLng(rsItems("item_type_id")) = SUR_ITEM_MATRIX_RATING_SCALE Then
                                strAnswerText = Request.Form("QID" & CStr(lngItemID) & "||SIID" & rsSubItems("subitem_id"))
                            Else 'For matrix option questions, there is no answer text to record
                                strAnswerText = SUR_ANSWER_TEXT_NULL
                            End If

                            'Insert a record for each selected answer
                            strSQL = "INSERT INTO sur_response_answer(response_id, item_id, subitem_id, answer_id, answer_text, other_text) VALUES(" & _
                                        lngResponseID & ", " & lngItemID & ", " & rsSubItems("subitem_id") & ", " & _
                                        Request.Form("QID" & CStr(lngItemID) & "||SIID" & rsSubItems("subitem_id")) & ", " & _
                                        SQLEncode(strAnswerText) & ", " & _
                                        SQLEncode(SUR_ANSWER_TEXT_NULL) & ")"
                            conSurvey.Execute ConvertSQL(strSQL), , adCmdText
                            flgInsertedResponse = True
                        End If
                        rsSubItems.MoveNext
                    Loop
                    rsSubItems.Close
                    Set rsSubItems = Nothing

                    'If no value was selected for the option button, insert an answer with all null values, as explained above.
                    If flgInsertedResponse = False Then
                        strSQL = "INSERT INTO sur_response_answer(response_id, item_id, answer_id, answer_text, other_text) VALUES(" & _
                                    lngResponseID & ", " & lngItemID & ", " & SUR_ANSWER_ID_NULL & ", " & _
                                    SQLEncode(SUR_ANSWER_TEXT_NULL) & ", " & SQLEncode(SUR_ANSWER_TEXT_NULL) & ")"
                        conSurvey.Execute ConvertSQL(strSQL), , adCmdText
                    End If

                    'All SQL has already been executed, so do not allow any other SQL statements to execute for this item
                    flgExecuteSQL = False
                Case SUR_ITEM_MATRIX_TEXT_BOXES:
                    'Set a flag to indicate whether or not a row has been inserted for the textboxes.  It's possible not to
                    'insert any data if the question is not required and the user does not enter any values.  For correctness and
                    'consistency in the reporting, it's necessary to have an entry for "null" responses to the checkboxes
                    flgInsertedResponse = False

                    'For updates, delete all of the previous answers before inserting the current set of answers.
                    If flgUpdatingSurvey = True Then
                        strSQL = "DELETE FROM sur_response_answer WHERE response_id = " & lngResponseID & " AND item_id = " & lngItemID
                        conSurvey.Execute ConvertSQL(strSQL), , adCmdText
                    End if

                    'Create recordsets for both the subitems (rows) and answers (columns).
                    strSQL = "SELECT subitem_id, subitem_text FROM sur_subitem " & _
                             "WHERE item_id = " & lngItemID & " ORDER BY order_number"
                    Set rsSubItems = conSurvey.Execute(ConvertSQL(strSQL), , adCmdText)
                    strSQL = "SELECT answer_id FROM sur_item_answer " & _
                             "WHERE item_id = " & lngItemID & " ORDER BY order_number"
                    Set rsAnswers = conSurvey.Execute(ConvertSQL(strSQL), , adCmdText)

                    'Loop through the answers recordset and create a collection.  Doing this allows us to create
                    'the recordset forward-only, and to loop through it only once, which both improve performance.
                    rsAnswers.MoveFirst
                    Do While Not rsAnswers.EOF
                        lngColumnCount = lngColumnCount + 1
                        strColumnsCollection = strColumnsCollection & CStr(lngColumnCount) & ";" & rsAnswers("answer_id") & ";"
                        rsAnswers.MoveNext
                    Loop
                    rsAnswers.Close
                    Set rsAnswers = Nothing

                    'Loop through the subitems (rows), and within each loop, loop through the answers (columns) to extract
                    'the value for each text box.
                    rsSubItems.MoveFirst
                    Do While Not rsSubItems.EOF
                        'For each row, loop through each column, and extract the value from the control.
                        For i = 1 To lngColumnCount
                            'If an answer was submitted for this item, insert a record into the database
                            If Len(Trim(Request.Form("QID" & lngItemID & "||SIID" & rsSubItems("subitem_id") & "||AID" & GetValueFromCollection(strColumnsCollection, CStr(i))))) > 0 Then
                                'Insert a record for each selected answer
                                strSQL = "INSERT INTO sur_response_answer(response_id, item_id, subitem_id, answer_id, answer_text, other_text) VALUES(" & _
                                            lngResponseID & ", " & lngItemID & ", " & rsSubItems("subitem_id") & ", " & _
                                            GetValueFromCollection(strColumnsCollection, CStr(i)) & ", " & _
                                            SQLEncode(Request.Form("QID" & lngItemID & "||SIID" & rsSubItems("subitem_id") & "||AID" & GetValueFromCollection(strColumnsCollection, CStr(i)))) & ", " & _
                                            SQLEncode(SUR_ANSWER_TEXT_NULL) & ")"
                                conSurvey.Execute ConvertSQL(strSQL), , adCmdText
                                flgInsertedResponse = True
                            End If
                        Next
                        rsSubItems.MoveNext
                    Loop
                    rsSubItems.Close
                    Set rsSubItems = Nothing

                    'If no value was selected for the option button, insert an answer with all null values, as explained above.
                    If flgInsertedResponse = False Then
                        strSQL = "INSERT INTO sur_response_answer(response_id, item_id, subitem_id, answer_id, answer_text, other_text) VALUES(" & _
                                    lngResponseID & ", " & lngItemID & ", " & SUR_ANSWER_ID_NULL & ", " & SUR_ANSWER_ID_NULL & ", " & _
                                    SQLEncode(SUR_ANSWER_TEXT_NULL) & ", " & SQLEncode(SUR_ANSWER_TEXT_NULL) & ")"
                        conSurvey.Execute ConvertSQL(strSQL), , adCmdText
                    End If

                    'All SQL has already been executed, so do not allow any other SQL statements to execute for this item
                    flgExecuteSQL = False
                Case SUR_ITEM_MATRIX_MULTISELECT_CHECKBOXES:
                    'Set a flag to indicate whether or not a row has been inserted for the checkboxes.  It's possible not to
                    'insert any data if the question is not required and the user does not enter any values.  For correctness and
                    'consistency in the reporting, it's necessary to have an entry for "null" responses to the checkboxes
                    flgInsertedResponse = False

                    'For updates, delete all of the previous answers before inserting the current set of answers.
                    If flgUpdatingSurvey = True Then
                        strSQL = "DELETE FROM sur_response_answer WHERE response_id = " & lngResponseID & " AND item_id = " & lngItemID
                        conSurvey.Execute ConvertSQL(strSQL), , adCmdText
                    End if

                    'Create recordsets for both the subitems (rows) and answers (columns).
                    strSQL = "SELECT subitem_id, subitem_text FROM sur_subitem " & _
                             "WHERE item_id = " & lngItemID & " ORDER BY order_number"
                    Set rsSubItems = conSurvey.Execute(ConvertSQL(strSQL), , adCmdText)
                    strSQL = "SELECT answer_id FROM sur_item_answer " & _
                             "WHERE item_id = " & lngItemID & " ORDER BY order_number"
                    Set rsAnswers = conSurvey.Execute(ConvertSQL(strSQL), , adCmdText)

                    'Loop through the answers recordset and create a collection.  Doing this allows us to create
                    'the recordset forward-only, and to loop through it only once, which both improve performance.
                    rsAnswers.MoveFirst
                    Do While Not rsAnswers.EOF
                        lngColumnCount = lngColumnCount + 1
                        strColumnsCollection = strColumnsCollection & CStr(lngColumnCount) & ";" & rsAnswers("answer_id") & ";"
                        rsAnswers.MoveNext
                    Loop
                    rsAnswers.Close
                    Set rsAnswers = Nothing

                    'Loop through the subitems (rows), and within each loop, loop through the answers (columns) to extract
                    'the value for each checkbox.
                    rsSubItems.MoveFirst
                    Do While Not rsSubItems.EOF
                        'For each row, loop through each column, and extract the value from the control.
                        For i = 1 To lngColumnCount
                            'If an answer was submitted for this item, insert a record into the database
                            If Request.Form("QID" & lngItemID & "||SIID" & rsSubItems("subitem_id") & "||AID" & GetValueFromCollection(strColumnsCollection, CStr(i))) = "on" Then
                                'Insert a record for each selected answer
                                strSQL = "INSERT INTO sur_response_answer(response_id, item_id, subitem_id, answer_id, answer_text, other_text) VALUES(" & _
                                            lngResponseID & ", " & lngItemID & ", " & rsSubItems("subitem_id") & ", " & _
                                            GetValueFromCollection(strColumnsCollection, CStr(i)) & ", " & _
                                            SQLEncode(SUR_ANSWER_TEXT_NULL) & ", " & _
                                            SQLEncode(SUR_ANSWER_TEXT_NULL) & ")"
                                conSurvey.Execute ConvertSQL(strSQL), , adCmdText
                                flgInsertedResponse = True
                            End If
                        Next
                        rsSubItems.MoveNext
                    Loop
                    rsSubItems.Close
                    Set rsSubItems = Nothing

                    'If no value was selected for the option button, insert an answer with all null values, as explained above.
                    If flgInsertedResponse = False Then
                        strSQL = "INSERT INTO sur_response_answer(response_id, item_id, answer_id, answer_text, other_text) VALUES(" & _
                                    lngResponseID & ", " & lngItemID & ", " & SUR_ANSWER_ID_NULL & ", " & _
                                    SQLEncode(SUR_ANSWER_TEXT_NULL) & ", " & SQLEncode(SUR_ANSWER_TEXT_NULL) & ")"
                        conSurvey.Execute ConvertSQL(strSQL), , adCmdText
                    End If

                    'All SQL has already been executed, so do not allow any other SQL statements to execute for this item
                    flgExecuteSQL = False
                Case SUR_ITEM_MULTISELECT_CHECKBOXES:
                    'Set a flag to indicate whether or not a row has been inserted for the checkboxes.  It's possible not to
                    'insert any data if the question is not required and the user does not select any values.  For correctness
                    'and consistency in the reporting, it's necessary to have an entry for "null" responses to the checkboxes.
                    flgInsertedResponse = False

                    'For updates, delete all of the previous answers before inserting the current set of answers.
                    If flgUpdatingSurvey = True Then
                        strSQL = "DELETE FROM sur_response_answer WHERE response_id = " & lngResponseID & " AND item_id = " & lngItemID
                        conSurvey.Execute ConvertSQL(strSQL), , adCmdText
                    End if

                    'Loop through each of the answers and see if it is checked in the form.  If so, insert an entry
                    'into the database.
                    strSQL = "SELECT answer_id FROM sur_item_answer " & _
                                "WHERE item_id = " & lngItemID & " ORDER BY order_number"
                    Set rsAnswers = conSurvey.Execute(ConvertSQL(strSQL), , adCmdText)

                    rsAnswers.MoveFirst
                    Do While Not rsAnswers.EOF
                        If Request.Form("QID" & CStr(lngItemID) & "||CHECKBOX||" & rsAnswers("answer_id")) = "on" Then
                            'Insert a record for each checked answer
                            strSQL = "INSERT INTO sur_response_answer(response_id, item_id, answer_id, answer_text, other_text) VALUES(" & _
                                        lngResponseID & ", " & lngItemID & ", " & rsAnswers("answer_id") & ", " & _
                                        SQLEncode(SUR_ANSWER_TEXT_NULL) & ", " & SQLEncode(SUR_ANSWER_TEXT_NULL) & ")"
                            conSurvey.Execute ConvertSQL(strSQL), , adCmdText
                            flgInsertedResponse = True
                        End If
                        rsAnswers.MoveNext
                    Loop
                    rsAnswers.Close
                    Set rsAnswers = Nothing

                    'See if the "OTHER" check box was checked, and if so, insert a record in the database.  Set the flag for
                    'executing the SQL based on whether or not an "OTHER" value entered.
                    lngAnswerID = SUR_ANSWER_ID_NULL
                    If Request.Form("QID" & CStr(lngItemID) & "||OTHERCHECKBOX") = "on" Then
                        'If the answer ID is "OTHER", insert NULL for the answer_id column, insert a value indicating that
                        '"OTHER" was selected in the answer column, and insert the "OTHER" value in the other_text column
                        strAnswerText = SUR_ANSWER_TEXT_OTHER_SELECTED
                        strOtherText = Trim(Request.Form("QID" & CStr(lngItemID) & "OTHERTEXT"))

                        'If no text was entered in the "Other" text box, use the null value
                        If Len(strOtherText) = 0 Then
                            strOtherText = SUR_ANSWER_TEXT_NULL
                        End If

                        'Insert a record for the "other" item
                        strSQL = "INSERT INTO sur_response_answer(response_id, item_id, answer_id, answer_text, other_text) VALUES(" & _
                                    lngResponseID & ", " & lngItemID & ", " & lngAnswerID & ", " & _
                                    SQLEncode(strAnswerText) & ", " & SQLEncode(strOtherText) & ")"
                        conSurvey.Execute ConvertSQL(strSQL), , adCmdText
                        flgInsertedResponse = True
                    End If

                    'If no value was entered for the checkboxes, insert an answer with all null values, as explained above.
                    If flgInsertedResponse = False Then
                        strSQL = "INSERT INTO sur_response_answer(response_id, item_id, answer_id, answer_text, other_text) VALUES(" & _
                                    lngResponseID & ", " & lngItemID & ", " & SUR_ANSWER_ID_NULL & ", " & _
                                    SQLEncode(SUR_ANSWER_TEXT_NULL) & ", " & SQLEncode(SUR_ANSWER_TEXT_NULL) & ")"
                        conSurvey.Execute ConvertSQL(strSQL), , adCmdText
                    End If

                    'All SQL has already been executed, so do not allow any other SQL statements to execute for this item
                    flgExecuteSQL = False
                Case SUR_ITEM_RANKING, SUR_ITEM_CONSTANT_SUM, SUR_ITEM_OPEN_ENDED_ONE_OR_MORE_LINES:
                    'Set a flag to indicate whether or not a row has been inserted for the ranking items.  It's possible not to
                    'insert any data if the question is not required and the user does not enter any values.  For correctness and
                    'consistency in the reporting, it's necessary to have an entry for "null" responses to the checkboxes
                    flgInsertedResponse = False

                    'For updates, delete all of the previous answers before inserting the current set of answers.
                    If flgUpdatingSurvey = True Then
                        strSQL = "DELETE FROM sur_response_answer WHERE response_id = " & lngResponseID & " AND item_id = " & lngItemID
                        conSurvey.Execute ConvertSQL(strSQL), , adCmdText
                    End if

                    'Loop through each of the subitems and see if a value was entered in the form.  If so, insert an
                    'entry into the database.
                    strSQL = "SELECT subitem_id, subitem_text FROM sur_subitem WHERE item_id = " & lngItemID & " ORDER BY order_number"
                    Set rsSubItems = conSurvey.Execute(ConvertSQL(strSQL), , adCmdText)

                    rsSubItems.MoveFirst
                    Do While Not rsSubItems.EOF
                        'If an answer was submitted for this item, insert a record into the database
                        If Len(Trim(Request.Form("QID" & CStr(lngItemID) & "||SIID" & rsSubItems("subitem_id")))) > 0 Then
                            'Insert a record for each checked answer
                            strSQL = "INSERT INTO sur_response_answer(response_id, item_id, subitem_id, answer_id, answer_text, other_text) VALUES(" & _
                                        lngResponseID & ", " & lngItemID & ", " & rsSubItems("subitem_id") & ", " & _
                                        SUR_ANSWER_ID_NULL & ", " & _
                                        SQLEncode(Request.Form("QID" & CStr(lngItemID) & "||SIID" & rsSubItems("subitem_id"))) & ", " & _
                                        SQLEncode(SUR_ANSWER_TEXT_NULL) & ")"
                            conSurvey.Execute ConvertSQL(strSQL), , adCmdText
                            flgInsertedResponse = True
                        End If
                        rsSubItems.MoveNext
                    Loop
                    rsSubItems.Close
                    Set rsSubItems = Nothing

                    'If no value was entered for the ranking options, insert an answer with all null values, as explained above.
                    If flgInsertedResponse = False Then
                        strSQL = "INSERT INTO sur_response_answer(response_id, item_id, answer_id, answer_text, other_text) VALUES(" & _
                                    lngResponseID & ", " & lngItemID & ", " & SUR_ANSWER_ID_NULL & ", " & _
                                    SQLEncode(SUR_ANSWER_TEXT_NULL) & ", " & SQLEncode(SUR_ANSWER_TEXT_NULL) & ")"
                        conSurvey.Execute ConvertSQL(strSQL), , adCmdText
                    End If

                    'All SQL has already been executed, so do not allow any other SQL statements to execute for this item
                    flgExecuteSQL = False
                Case SUR_ITEM_OPEN_ENDED_ONE_LINE, SUR_ITEM_DATE, SUR_ITEM_NUMBER, SUR_ITEM_DATABASE_DROPDOWN:
                    'Check to see if a value was supplied for this question, and set the answer text equal to the value if it was
                    'supplied or to the NULL value if not supplied
                    lngAnswerID = SUR_ANSWER_ID_NULL
                    strOtherText = SUR_ANSWER_TEXT_NULL
                    If Len(Trim(Request.Form("QID" & CStr(lngItemID)))) = 0 Then
                        strAnswerText = SUR_ANSWER_TEXT_NULL
                    Else
                        strAnswerText = Trim(Request.Form("QID" & CStr(lngItemID)))
                    End If
                Case SUR_ITEM_OPEN_ENDED_COMMENTS_BOX:
                    'Check to see if a value was supplied for this question, and set the answer text equal to the value if it was
                    'supplied or to the NULL value if not supplied
                    lngAnswerID = SUR_ANSWER_ID_NULL
                    strOtherText = SUR_ANSWER_TEXT_NULL
                    If Len(Request.Form("QID" & CStr(lngItemID))) = 0 Then
                        strAnswerText = SUR_ANSWER_TEXT_NULL
                    Else
                        strAnswerText = Trim(Request.Form("QID" & CStr(lngItemID)))
                    End If
            End Select

            'If everything is OK, save the answer

'---10/28/2005 for responses that are not deleted before insert, prevent multiple responses.
'If flgExecuteSQL = True Then
'    'query to see if user already clicked done then the back button
'    tmpAnsSql = "SELECT * FROM sur_response_answer WHERE response_id = " & lngResponseID & " AND item_id = " & lngItemID
'    rsTmpAns.Open ConvertSQL(tmpAnsSql), SURVEY_APP_CONNECTION, adOpenForwardOnly, adLockReadOnly, adCmdText
'    If rsTmpAns.EOF Then
'        'do nothing
'    Else
'        If Session("SUR_COMPLETE") = lngResponseID then
'            'user already hit done then back dont insert multiple responses for this response id
'            flgExecuteSQL = False
'        End If
'    End If
'    rsTmpAns.Close
'End If
            If flgExecuteSQL = True Then
                'Either update the results from a previously taken survey or insert results for a first time survey

'10/17/2005 fixing multiple response issue w/multiple response surveys vs single response.
'--the below prevents duplicate inserts for the same question for same responseid.
'    tmpAnsSql = "SELECT * FROM sur_response_answer WHERE response_id = " & lngResponseID & " AND item_id = " & lngItemID
'    rsTmpAns.Open ConvertSQL(tmpAnsSql), SURVEY_APP_CONNECTION, adOpenForwardOnly, adLockReadOnly, adCmdText
'    If NOT rsTmpAns.EOF AND Session("SUR_COMPLETE") = lngResponseID then
'        flgUpdatingSurvey = True
'    End If
'    rsTmpAns.Close


                If flgUpdatingSurvey = True Then

                    strSQL = "UPDATE sur_response_answer " & _
                                "SET answer_text = " & SQLEncode(strAnswerText) & ", " & _
                                "other_text = " & SQLEncode(strOtherText) & ", " & _
                                "answer_id = " & lngAnswerID & _
                                " WHERE response_id = " & lngResponseID & _
                                " AND item_id = " & lngItemID
                Else 'Inserting results for a new survey
                    strSQL = "INSERT INTO sur_response_answer(response_id, item_id, answer_id, answer_text, other_text) VALUES(" & _
                                lngResponseID & ", " & lngItemID & ", " & lngAnswerID & ", " & _
                                SQLEncode(strAnswerText) & ", " & SQLEncode(strOtherText) & ")"
                End If
                 conSurvey.Execute ConvertSQL(strSQL), , adCmdText
            End If

            rsItems.MoveNext
        Loop
    End If 'If Not rsItems.EOF Then

    'Clean up
    rsItems.Close
    conSurvey.Close
    Set rsItems = Nothing
    Set conSurvey = Nothing

    'Preserve the current actual page number, plus one, as the first page to start deleting "dangling" answers,
    'as described below.
    lngPageToDeleteAnswers = lngActualPageNumber + 1

    'Get the next page to display.  If there is no response ID, then this was a first page with no answerable
    'questions, so just add one to the current page
    If Len(lngResponseID) = 0 Then
        lngNextPageNumber = lngActualPageNumber + 1
    Else
        lngNextPageNumber = GetNextPageInSurvey(lngSurveyID, lngResponseID, lngActualPageNumber)
    End If

    'If the user is updating a survey, delete responses for all pages between the page just executed and the page
    'about to be displayed.  This is necessary for the odd case where page condition logic allows a user to answer
    'a question, but when they update their survey, that question is not displayed the second time around because
    'of a different answer to a question.
    conDelete.Open SURVEY_APP_CONNECTION
    If lngNextPageNumber = SUR_END_OF_SURVEY Then
        'If there are no more pages to display, delete all of the answers on all of the remaining pages in the survey.
        'Doing this will delete "dangling" responses on all of the remaining pages.

        'Begin by getting total page count, and setting the page number pointer to the next page.
        lngTotalPageCount = GetPageCount(lngSurveyID)
        lngCurrentPageNumber = lngCurrentPageNumber + 1

        'Loop through the remaining pages in the survey
        Do While lngCurrentPageNumber <= lngTotalPageCount
            'Delete all of the answers from sur_response_answer table for "dangling answers," as explained above.
            strSQL = "DELETE FROM sur_response_answer " & _
                     "WHERE response_id = " & lngResponseID & _
                     " AND item_id IN (" & _
                        ConvertSQLInClause("SELECT item_id " & _
                        "FROM sur_survey_to_item_mapping " & _
                        "WHERE page_number = " & lngCurrentPageNumber & _
                        " AND survey_id = " & lngSurveyID, "item_id") & ")"
            conDelete.Execute ConvertSQL(strSQL), , adCmdText
            lngCurrentPageNumber = lngCurrentPageNumber + 1
        Loop
    Else
        'Loop through the pages between the page just processed and the next page to be displayed.
        Do While lngNextPageNumber > lngPageToDeleteAnswers
            'Delete all of the answers from sur_response_answer table for "dangling answers," as explained above.
            strSQL = "DELETE FROM sur_response_answer " & _
                     "WHERE response_id = " & lngResponseID & _
                     " AND item_id IN (" & _
                        ConvertSQLInClause("SELECT item_id " & _
                        "FROM sur_survey_to_item_mapping " & _
                        "WHERE page_number = " & lngPageToDeleteAnswers & _
                        " AND survey_id = " & lngSurveyID, "item_id") & ")"
            conDelete.Execute ConvertSQL(strSQL), , adCmdText
            lngPageToDeleteAnswers = lngPageToDeleteAnswers + 1
        Loop
    End If
    conDelete.Close
    Set conDelete = Nothing

    'If the survey is completed, update the response to indicate that it is complete
    If lngNextPageNumber = SUR_END_OF_SURVEY Then
        conUpdate.Open SURVEY_APP_CONNECTION
        strSQL = "UPDATE sur_response " & _
                    "SET completed_yn = " & SQLEncode(SUR_BOOLEAN_POSITIVE) & ", " & _
                    "response_end_date = GETDATE() " & _
                    "WHERE response_id = " & lngResponseID
        conUpdate.Execute ConvertSQL(strSQL), , adCmdText
        conUpdate.Close
        Set conUpdate = Nothing
    End If

    'Set a variable indicating whether or not the page is in edit mode (being done by a survey owner or an admin)
    If Request.Form("AdminOrOwnerEditResponse") = SUR_BOOLEAN_POSITIVE_DISPLAY Then
        strEditValue = SUR_BOOLEAN_POSITIVE_DISPLAY
    Else
        strEditValue = SUR_BOOLEAN_NEGATIVE_DISPLAY
    End If

    '--9/29/2005: fix multiple answers for responseid bug
    '--10/28/2005: move this after update/insert so saves last page
    If Request.Form("DoneClicked") = 1 Then
        Session("SUR_COMPLETE") = Request.Form("SurveyComplete")
    End If

    'If this is the last page, redirect to the survey complete page.  If this is not the last page of the survey,
    'redirect to the next page in the survey.
    If lngNextPageNumber = SUR_END_OF_SURVEY Then
        Response.Redirect "TakeSurveyActionComplete.asp?DisplayHeader=" & Request.QueryString("DisplayHeader") & "&SurveyID=" & EncryptSurveyID(lngSurveyID) & "&Edit=" & strEditValue & "&ResponseID=" & lngResponseID
    Else
        'If there were predefined answers tacked onto the querystring, add them to the querystring
        If Len(Request.Form("ItemIDValues")) > 0 Then
            strItemIDValues = "&" & Request.Form("ItemIDValues")
        Else
            strItemIDValues = ""
        End If

        'Change the format of the URL in the case where a survey taken via an EID (email invitation) had no answerable
        'questions on the first page.  This is necessary in order to track the response being from an email invitation
        'on the second page of the survey.
        If flgHadAnswerableQuestions = False And Len(Request.Form("EID")) > 0 Then
            Response.Redirect "TakeSurvey.asp?SurveyID=" & EncryptSurveyID(lngSurveyID) & "&PreviousActualPageNumber=" & CStr(Request.Form("ActualPageNumber")) & "&PreviousDisplayPageNumber=" & CStr(Request.Form("DisplayPageNumber")) & "&PreviousQuestionNumber=" & Request.Form("DisplayPageNumber") & "&ActualPageNumber=" & CLng(lngNextPageNumber) & "&DisplayPageNumber=" & CLng(lngDisplayPageNumber) + 1 & "&ResponseID=" & lngResponseID & "&QuestionNumber=" & Request.Form("QuestionNumber") & "&Edit=" & strEditValue & "&DisplayHeader=" & Request.QueryString("DisplayHeader") & "&EID=" & Request.Form("EID") & strItemIDValues
        Else
            Response.Redirect "TakeSurvey.asp?SurveyID=" & EncryptSurveyID(lngSurveyID) & "&PreviousActualPageNumber=" & CStr(Request.Form("ActualPageNumber")) & "&PreviousDisplayPageNumber=" & CStr(Request.Form("DisplayPageNumber")) & "&PreviousQuestionNumber=" & Request.Form("DisplayPageNumber") & "&ActualPageNumber=" & CLng(lngNextPageNumber) & "&DisplayPageNumber=" & CLng(lngDisplayPageNumber) + 1 & "&ResponseID=" & lngResponseID & "&QuestionNumber=" & Request.Form("QuestionNumber") & "&Edit=" & strEditValue & "&DisplayHeader=" & Request.QueryString("DisplayHeader") & strItemIDValues
        End If
    End If
%>