bombers.k12.ar.us/bombers.k12.ar.us.tar/bombers.k12.ar.us/survey/CreateSurveyAction.asp

Text File | 2006-11-29 | 25KB | 548 lines

<!--#Include File="Include/Top_inc.asp"-->
<%
'***********************************************************************
'   Application: SelectSurveyASP Advanced v8.1.9
'   Author: Aaron Baril for ClassApps.com
'   Page Description: This page creates a new survey either from scratch or by
'                      copying an existing survey.
'
'   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/Utility_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"-->
<%


    'If the user does not have "Create" or "Admin" permission, redirect them to the access denied page.
    If lngUserSecurityLevel <> SUR_SECURITY_LEVEL_CREATE And lngUserSecurityLevel <> SUR_SECURITY_LEVEL_ADMIN Then
        Response.Redirect "AccessDenied.asp?Reason=" & SUR_ACCESS_DENIED_NOT_ADMIN_SECURITY_LEVEL
    End If

    Dim strSQL
    Dim conNewSurvey
    Dim lngOriginalSurveyID
    Dim lngNewSurveyID
    Dim rsOriginalSurvey
    Dim strStartDate
    Dim strEndDate
    Dim lngTempID
    Dim lngMinimumNumberResponses
    Dim lngMaximumNumberResponses
    Dim lngRowTextWidth
    Dim lngAnswerTotal
    Dim strOldToNewItemIDs
    Dim strOldToNewAnswerIDs
    Dim lngNewItemID
    Dim lngNewAnswerID
    Dim strOwners
    Dim strMinimumValue
    Dim strMaximumValue
    Dim strItemText
    Dim strItemSubText
    Dim strItemAlias
    Dim vaItemIDs
    Dim i
    Dim lngAnswerValue

    'Initialization
    Set rsOriginalSurvey = Server.CreateObject("ADODB.Recordset")
    Set conNewSurvey = Server.CreateObject("ADODB.Connection")
    '***SB 5/30/2006
    '3 minutes
    conNewSurvey.CommandTimeout = 180
    conNewSurvey.connectionTimeout = 180
    server.ScriptTimeout = 180
    conNewSurvey.Open SURVEY_APP_CONNECTION
    lngNewSurveyID = ID_GetNextAvailableID("SurveyGenerationSurvey")

    If CLng(Request.Form("optType")) = SUR_CREATE_SURVEY_FROM_SCRATCH Then
        'This section creates a new survey from scratch

        'Initialize the start and end dates


'***SB 6/22/2006
            If SUR_DATABASE_TYPE = "SQLServer" Then
                strEndDate = "(DATEADD([day], 30, GETDATE()))"
                strStartDate = "GETDATE()"
            End If
            If SUR_DATABASE_TYPE = "MSAccess" Then
                strEndDate = "(NOW + 30)"
                strStartDate = "(NOW)"

            End If
            If SUR_DATABASE_TYPE = "MySQL" Then
                strEndDate = "(DATE_ADD(CURRENT_DATE, INTERVAL 30 DAY))"
                strStartDate = "(CURRENT_DATE)"
            End If
            If strStartDate = "" Then
                strStartDate = CustomDateFormatInsert(Date())
                strEndDate = CustomDateFormatInsert(DateAdd("m", 1, Date()))
            End If

        'Create a new survey using all of the default values
        strSQL = "INSERT INTO sur_survey(survey_id, title, template_id, closed_date, " & _
                 "created_date, launched_date, start_date, end_date, maximum_responses_for_survey, response_count, " & _
                 "completion_action, survey_complete_message, survey_complete_display_close, " & _
                 "survey_complete_display_continue, survey_complete_display_reports, survey_language, status, report_sharing_enabled_yn, " & _
                 "report_security, page_numbering_format, question_numbering_format, owners, respondent_access_level, " & _
                 "survey_complete_redirect_url, response_email_addresses, admin_email_address, highlight_responses_yn, " & _
                 "maximum_responses_per_user, modify_survey_within_days, back_button_display, cancel_button_display, " & _
                 "cancel_button_redirect_url) " & _
                 "VALUES(" & lngNewSurveyID & ", " & _
                 SQLEncode(Request.Form("txtTitle")) & ", " & _
                 SUR_DEFAULT_TEMPLATE_ID & ", " & _
                 "Null, " & strStartDate & ", Null, " & _
                 strStartDate & ", " & _
                 strEndDate & ", " & _
                 SUR_UNLIMITED_RESPONSES & ", 0, " & _
                 SQLEncode(SUR_DEFAULT_COMPLETION_ACTION) & ", " & _
                 "'Thank you for taking the survey!', " & _
                 SQLEncode(SUR_DEFAULT_SURVEY_COMPLETE_DISPLAY_CLOSE) & ", " & _
                 SQLEncode(SUR_DEFAULT_SURVEY_COMPLETE_DISPLAY_CONTINUE) & ", " & _
                 SQLEncode(SUR_DEFAULT_SURVEY_COMPLETE_DISPLAY_REPORTS) & ", " & _
                 SQLEncode(SUR_DEFAULT_SURVEY_LANGUAGE) & ", " & _
                 SQLEncode(SUR_STATUS_DESIGN) & ", " & _
                 SQLEncode(SUR_BOOLEAN_NEGATIVE) & ", " & _
                 SQLEncode(SUR_REPORT_SECURITY_OWNERS_ONLY) & ", " & _
                 SQLEncode(SUR_PAGE_NUMBERING_DISPLAY_PROGRESS_BAR) & ", " & _
                 SQLEncode(SUR_QUESTION_NUMBERING_ENTIRE_SURVEY) & ", " & _
                 SQLEncode(GetUserName()) & ", " & _
                 SUR_RESPONDENT_ACCESS_LEVEL_MULTIPLE_ANONYMOUS & ", " & _
                 "'', " & _
                 "'', " & _
                 SQLEncode(GetUserEmailAddress()) & ", " & _
                 SQLEncode(SUR_BOOLEAN_NEGATIVE) & ", " & _
                 SUR_UNLIMITED_RESPONSES & ", " & _
                 "30, " & _
                 SQLEncode(SUR_DEFAULT_BACK_BUTTON) & ", " & _
                 SQLEncode(SUR_DEFAULT_CANCEL_BUTTON) & ", " & _
                 "'')"
        conNewSurvey.Execute ConvertSQL(strSQL), , adCmdText
    Else 'SUR_CREATE_SURVEY_FROM_EXISTING
        lngOriginalSurveyID = Request.Form("cboSurvey")

        '1.  Copy all of the data from the survey table (SUR_SURVEY)
        strSQL = "SELECT survey_id, title, template_id, closed_date, created_date, launched_date, report_sharing_enabled_yn, " & _
                    "start_date, end_date, maximum_responses_for_survey, response_count, completion_action, " & _
                    "survey_language, status, report_security, page_numbering_format, question_numbering_format, " & _
                    "respondent_access_level, survey_complete_redirect_url, highlight_responses_yn, admin_email_address, " & _
                    "maximum_responses_per_user, modify_survey_within_days, back_button_display, cancel_button_display, " & _
                    "survey_complete_display_close, survey_complete_display_continue, survey_complete_display_reports, " & _
                    "cancel_button_redirect_url, response_email_addresses, survey_complete_message, owners " & _
                    "FROM sur_survey WHERE survey_id = " & lngOriginalSurveyID
        rsOriginalSurvey.Open ConvertSQL(strSQL), SURVEY_APP_CONNECTION, adOpenForwardOnly, adLockReadOnly, adCmdText
        rsOriginalSurvey.MoveFirst

        'Create values needed for the insert

'***SB 6/22/2006
            If SUR_DATABASE_TYPE = "SQLServer" Then
                strEndDate = "(DATEADD([day], 30, GETDATE()))"
                strStartDate = "GETDATE()"
            End If
            If SUR_DATABASE_TYPE = "MSAccess" Then
                strEndDate = "(NOW + 30)"
                strStartDate = "(NOW)"

            End If
            If SUR_DATABASE_TYPE = "MySQL" Then
                strEndDate = "(DATE_ADD(CURRENT_DATE, INTERVAL 30 DAY))"
                strStartDate = "(CURRENT_DATE)"
            End If
            If strStartDate = "" Then
                strStartDate = CustomDateFormatInsert(Date())
                strEndDate = CustomDateFormatInsert(DateAdd("m", 1, Date()))
            End If

'**SB end

        'The only way that the current user's name could not be in the list of owners is if the user is an admin.  In that
        'case, add the user's name to the list of owners.
        strOwners = rsOriginalSurvey("owners")
        If IsUserSurveyOwner(lngOriginalSurveyID) = False Then
            strOwners = strOwners & ";" & GetUsername()
        End If

        '1.  Insert all of the data from the survey table (SUR_SURVEY)
        strSQL = "INSERT INTO sur_survey(survey_id, title, template_id, closed_date, created_date, launched_date, " & _
                    "report_sharing_enabled_yn, start_date, end_date, maximum_responses_for_survey, response_count, completion_action, " & _
                    "survey_complete_message, survey_language, status, report_security, page_numbering_format, question_numbering_format, " & _
                    "owners, respondent_access_level, survey_complete_redirect_url, response_email_addresses, " & _
                    "admin_email_address, maximum_responses_per_user, modify_survey_within_days, " & _
                    "survey_complete_display_close, survey_complete_display_continue, survey_complete_display_reports, " & _
                    "back_button_display, cancel_button_display, cancel_button_redirect_url, highlight_responses_yn) " & _
                    "VALUES(" & lngNewSurveyID & ", " & SQLEncode(Request.Form("txtTitle")) & ", " & _
                    rsOriginalSurvey("template_id") & ", " & _
                    "Null, " & strStartDate & ", Null, " & _
                    SQLEncode(rsOriginalSurvey("report_sharing_enabled_yn")) & ", " & _
                    strStartDate & ", " & _
                    strEndDate & ", " & _
                    rsOriginalSurvey("maximum_responses_for_survey") & ", 0, " & _
                    SQLEncode(rsOriginalSurvey("completion_action")) & ", " & _
                    SQLEncode(rsOriginalSurvey("survey_complete_message")) & ", " & _
                    SQLEncode(rsOriginalSurvey("survey_language")) & ", " & _
                    SQLEncode(SUR_STATUS_DESIGN) & ", " & _
                    SQLEncode(rsOriginalSurvey("report_security")) & ", " & _
                    SQLEncode(rsOriginalSurvey("page_numbering_format")) & ", " & _
                    SQLEncode(rsOriginalSurvey("question_numbering_format")) & ", " & _
                    SQLEncode(strOwners) & ", " & _
                    rsOriginalSurvey("respondent_access_level") & ", " & _
                    SQLEncode(rsOriginalSurvey("survey_complete_redirect_url")) & ", " & _
                    SQLEncode(rsOriginalSurvey("response_email_addresses")) & ", " & _
                    SQLEncode(rsOriginalSurvey("admin_email_address")) & ", " & _
                    rsOriginalSurvey("maximum_responses_per_user") & ", " & _
                    rsOriginalSurvey("modify_survey_within_days") & ", " & _
                    SQLEncode(rsOriginalSurvey("survey_complete_display_close")) & ", " & _
                    SQLEncode(rsOriginalSurvey("survey_complete_display_continue")) & ", " & _
                    SQLEncode(rsOriginalSurvey("survey_complete_display_reports")) & ", " & _
                    SQLEncode(rsOriginalSurvey("back_button_display")) & ", " & _
                    SQLEncode(rsOriginalSurvey("cancel_button_display")) & ", " & _
                    SQLEncode(rsOriginalSurvey("cancel_button_redirect_url")) & ", " & _
                    SQLEncode(rsOriginalSurvey("highlight_responses_yn")) & ")"
        rsOriginalSurvey.Close
        conNewSurvey.Execute ConvertSQL(strSQL), , adCmdText

        '2.  Copy all of the data from the item table (SUR_ITEM)
        strSQL = "SELECT * FROM sur_item WHERE item_id IN (" & _
                 ConvertSQLInClause("SELECT item_id " & _
                    "FROM sur_survey_to_item_mapping WHERE survey_id = " & lngOriginalSurveyID, "item_id") & ")"
        rsOriginalSurvey.Open ConvertSQL(strSQL), SURVEY_APP_CONNECTION, adOpenForwardOnly, adLockReadOnly, adCmdText

        'Make sure that there are items for this survey
        If Not rsOriginalSurvey.EOF Then
            rsOriginalSurvey.MoveFirst

            'Build the SQL statements to insert the new records into the SUR_ITEM table
            Do While Not rsOriginalSurvey.EOF

                'Create values needed for the insert
                lngMinimumNumberResponses = rsOriginalSurvey("minimum_number_responses")
                If IsNull(lngMinimumNumberResponses) = True Then
                    lngMinimumNumberResponses = "Null"
                End If
                lngMaximumNumberResponses = rsOriginalSurvey("maximum_number_responses")
                If IsNull(lngMaximumNumberResponses) = True Then
                    lngMaximumNumberResponses = "Null"
                End If
                lngRowTextWidth = rsOriginalSurvey("row_text_width")
                If IsNull(lngRowTextWidth) = True Then
                    lngRowTextWidth= "Null"
                End If
                lngAnswerTotal = rsOriginalSurvey("answer_total")
                If IsNull(lngAnswerTotal) = True Or Len(lngAnswerTotal) = 0 Then
                    lngAnswerTotal = "Null"
                End If
                strItemAlias = rsOriginalSurvey("item_alias")
                If IsNull(strItemAlias) = True Or Len(strItemAlias) = 0 Then
                    strItemAlias = ""
                End If

                'Create the new ID for the new item
                lngTempID = ID_GetNextAvailableID("SurveyGenerationItem")

                'Create dictionary of old item ID's to new item ID's
                strOldToNewItemIDs = strOldToNewItemIDs & rsOriginalSurvey("item_id") & ";" & lngTempID & ";"

                strSQL = "INSERT INTO sur_item(item_id, item_type_id, item_text, item_sub_text, item_alias, required_yn, " & _
                            "random_answer_order_yn, email_address_yn, other_yn, other_text, minimum_value, maximum_value, answer_total, " & _
                            "minimum_number_responses, maximum_number_responses, row_text_width, display_format, image_path, " & _
                            "image_width, image_height, image_alignment, default_value, database_sql, database_dsn, " & _
                            "subitem_count) VALUES(" & _
                            lngTempID & ", " & _
                            rsOriginalSurvey("item_type_id") & ", " & _
                            SQLEncode(rsOriginalSurvey("item_text")) & ", " & _
                            SQLEncode(rsOriginalSurvey("item_sub_text")) & ", " & _
                            SQLEncode(strItemAlias) & ", " & _
                            SQLEncode(rsOriginalSurvey("required_yn")) & ", " & _
                            SQLEncode(rsOriginalSurvey("random_answer_order_yn")) & ", " & _
                            SQLEncode(rsOriginalSurvey("email_address_yn")) & ", " & _
                            SQLEncode(rsOriginalSurvey("other_yn")) & ", " & _
                            SQLEncode(rsOriginalSurvey("other_text")) & ", "
                'For minimum and maximum values, make sure that Null is not used
                If IsNull(rsOriginalSurvey("minimum_value")) = True Then
                    strSQL = strSQL & "'', "
                Else
                    strSQL = strSQL & SQLEncode(rsOriginalSurvey("minimum_value")) & ", "
                End If
                If IsNull(rsOriginalSurvey("maximum_value")) = True Then
                    strSQL = strSQL & "'', "
                Else
                    strSQL = strSQL & SQLEncode(rsOriginalSurvey("maximum_value")) & ", "
                End If
                strSQL = strSQL & lngAnswerTotal & ", " & _
                            lngMinimumNumberResponses & ", " & _
                            lngMaximumNumberResponses & ", " & _
                            lngRowTextWidth & ", " & _
                            SQLEncode(rsOriginalSurvey("display_format")) & ", " & _
                            SQLEncode(rsOriginalSurvey("image_path")) & ", " & _
                            SQLEncode(rsOriginalSurvey("image_width")) & ", "
                'For image height, make sure that Null is not used
                If IsNull(rsOriginalSurvey("image_height")) = True Then
                    strSQL = strSQL & "'', "
                Else
                    strSQL = strSQL & SQLEncode(rsOriginalSurvey("image_height")) & ", "
                End If
                strSQL = strSQL & SQLEncode(rsOriginalSurvey("image_alignment")) & ", " & _
                            SQLEncode(rsOriginalSurvey("default_value")) & ", " & _
                            SQLEncode(rsOriginalSurvey("database_sql")) & ", " & _
                            SQLEncode(rsOriginalSurvey("database_dsn")) & ", "
                If IsNull(rsOriginalSurvey("subitem_count")) = True Then
                    strSQL = strSQL & "Null"
                Else
                    strSQL = strSQL & SQLEncode(rsOriginalSurvey("subitem_count"))
                End If
                strSQL = strSQL & ")"

                conNewSurvey.Execute ConvertSQL(strSQL), , adCmdText
                rsOriginalSurvey.MoveNext
            Loop
        End If
        rsOriginalSurvey.Close

        '3.  Copy all of the data from the item answer table (SUR_ITEM_ANSWER)
        strSQL = "SELECT * FROM sur_item_answer WHERE item_id IN (" & _
                 ConvertSQLInClause("SELECT item_id " & _
                    "FROM sur_survey_to_item_mapping WHERE survey_id = " & lngOriginalSurveyID, "item_id") & ")"
        rsOriginalSurvey.Open ConvertSQL(strSQL), SURVEY_APP_CONNECTION, adOpenForwardOnly, adLockReadOnly, adCmdText

        'Make sure that there are item answers for this survey
        If Not rsOriginalSurvey.EOF Then
            rsOriginalSurvey.MoveFirst

            'Build the SQL statements to insert the new records into the SUR_ITEM_ANSWER table
            Do While Not rsOriginalSurvey.EOF
                'Get the new item ID from the collection that maps old item ID's to new item ID's
                lngNewItemID = GetValueFromCollection(strOldToNewItemIDs, CStr(rsOriginalSurvey("item_id")))

                'Create the new answer ID for the new answer
                lngTempID = ID_GetNextAvailableID("SurveyGenerationAnswer")

                'Create dictionary of old item ID's to new item ID's
                strOldToNewAnswerIDs = strOldToNewAnswerIDs & rsOriginalSurvey("answer_id") & ";" & lngTempID & ";"

                'Get the answer value
                If IsNull(rsOriginalSurvey("answer_value")) = True Then
                    lngAnswerValue = "Null"
                Else
                    lngAnswerValue = rsOriginalSurvey("answer_value")
                End If

                strSQL = "INSERT INTO sur_item_answer(item_id, answer_id, answer_text, answer_value, order_number, " & _
                            "default_yn) VALUES(" & _
                            lngNewItemID & ", " & _
                            lngTempID & ", " & _
                            SQLEncode(rsOriginalSurvey("answer_text")) & ", " & _
                            lngAnswerValue & ", " & _
                            rsOriginalSurvey("order_number") & ", " & _
                            SQLEncode(rsOriginalSurvey("default_yn")) & ")"
                conNewSurvey.Execute ConvertSQL(strSQL), , adCmdText
                rsOriginalSurvey.MoveNext
            Loop
        End If
        rsOriginalSurvey.Close

        '4.  Copy all of the data from the mapping table (SUR_SURVEY_TO_ITEM_MAPPING)
        strSQL = "SELECT * FROM sur_survey_to_item_mapping WHERE survey_id = " & lngOriginalSurveyID
        rsOriginalSurvey.Open ConvertSQL(strSQL), SURVEY_APP_CONNECTION, adOpenForwardOnly, adLockReadOnly, adCmdText

        'Make sure that there are items in the mapping table for this survey
        If Not rsOriginalSurvey.EOF Then
            rsOriginalSurvey.MoveFirst

            'Build the SQL statements to insert the new records into the SUR_SURVEY_TO_ITEM_MAPPING table
            Do While Not rsOriginalSurvey.EOF
                'Get the new item ID from the collection that maps old item ID's to new item ID's
                lngNewItemID = GetValueFromCollection(strOldToNewItemIDs, CStr(rsOriginalSurvey("item_id")))
                strSQL = "INSERT INTO sur_survey_to_item_mapping(survey_id, item_id, order_number, page_number) VALUES(" & _
                            lngNewSurveyId & ", " & _
                            lngNewItemID & ", " & _
                            rsOriginalSurvey("order_number") & ", " & _
                            rsOriginalSurvey("page_number") & ")"
                conNewSurvey.Execute ConvertSQL(strSQL), , adCmdText
                rsOriginalSurvey.MoveNext
            Loop
        End If
        rsOriginalSurvey.Close

        '5.  Copy all of the data from the page condition table (SUR_PAGE_CONDITION)
        strSQL = "SELECT * FROM sur_page_condition WHERE survey_id = " & lngOriginalSurveyID
        rsOriginalSurvey.Open ConvertSQL(strSQL), SURVEY_APP_CONNECTION, adOpenForwardOnly, adLockReadOnly, adCmdText

        'Make sure that there are page conditions for this survey
        If Not rsOriginalSurvey.EOF Then
            rsOriginalSurvey.MoveFirst

            'Build the SQL statements to insert the new records into the SUR_PAGE_CONDITION table
            Do While Not rsOriginalSurvey.EOF
                'Generate a new value for the page_condition_id column
                lngTempID = ID_GetNextAvailableID("SurveyGenerationPageConditionID")

                'Get the new item ID from the collection that maps old item ID's to new item ID's
                lngNewItemID = GetValueFromCollection(strOldToNewItemIDs, CStr(rsOriginalSurvey("dependent_item_id")))

                'Get the new answer ID.  If the old answer ID equals SUR_ANSWER_ID_NULL then use SUR_ANSWER_ID_NULL.
                'Otherwise, get the answer from the collection that maps old answer ID's to new answer ID's

                If CStr(rsOriginalSurvey("answer_id")) = CStr(SUR_ANSWER_ID_NULL) Then
                    lngNewAnswerID = SUR_ANSWER_ID_NULL
                Else
                    lngNewAnswerID = GetValueFromCollection(strOldToNewAnswerIDs, CStr(rsOriginalSurvey("answer_id")))
                End If

                strSQL = "INSERT INTO sur_page_condition(page_condition_id, survey_id, page_number, dependent_item_id, " & _
                            "group_number, operator_id, answer_id, answer_text) VALUES(" & _
                            lngTempID & ", " & _
                            lngNewSurveyID & ", " & _
                            rsOriginalSurvey("page_number") & ", " & _
                            lngNewItemID & ", " & _
                            rsOriginalSurvey("group_number") & ", " & _
                            rsOriginalSurvey("operator_id") & ", " & _
                            lngNewAnswerID & ", " & _
                            SQLEncode(rsOriginalSurvey("answer_text")) & ")"
                conNewSurvey.Execute ConvertSQL(strSQL), , adCmdText
                rsOriginalSurvey.MoveNext
            Loop
        End If
        rsOriginalSurvey.Close

        '6.  Copy all of the data from the subitem table (SUR_SUBITEM)

'SB 6/13/2006 fix for sql locking issue
if SUR_DATABASE_TYPE = "SQLServer" then
        strSQL = "SELECT * FROM sur_subitem (NOLOCK) WHERE item_id IN (" & _
                 ConvertSQLInClause("SELECT item_id " & _
                    "FROM sur_survey_to_item_mapping WHERE survey_id = " & lngOriginalSurveyID, "item_id") & ")"
else
        strSQL = "SELECT * FROM sur_subitem WHERE item_id IN (" & _
                 ConvertSQLInClause("SELECT item_id " & _
                    "FROM sur_survey_to_item_mapping WHERE survey_id = " & lngOriginalSurveyID, "item_id") & ")"
end if

        rsOriginalSurvey.Open ConvertSQL(strSQL), SURVEY_APP_CONNECTION, adOpenForwardOnly, adLockReadOnly, adCmdText

        'Make sure that there are subitems for this survey
        If Not rsOriginalSurvey.EOF Then
            rsOriginalSurvey.MoveFirst
            'Build the SQL statements to insert the new records into the SUR_SUBITEM table
            Do While Not rsOriginalSurvey.EOF
                'Get the new item ID from the collection that maps old item ID's to new item ID's
                lngNewItemID = GetValueFromCollection(strOldToNewItemIDs, CStr(rsOriginalSurvey("item_id")))

                'Create the new answer ID for the new answer
                lngTempID = ID_GetNextAvailableID("SurveyGenerationSubitem")

                strSQL = "INSERT INTO sur_subitem(subitem_id, item_id, subitem_text, order_number) " & _
                            "VALUES(" & _
                            lngTempID & ", " & _
                            lngNewItemID & ", " & _
                            SQLEncode(rsOriginalSurvey("subitem_text")) & ", " & _
                            rsOriginalSurvey("order_number") & ") "

                conNewSurvey.Execute ConvertSQL(strSQL), , adCmdText
                rsOriginalSurvey.MoveNext
            Loop
        End If
        rsOriginalSurvey.Close


        '7.  Copy all of the data from the page property table (SUR_PAGE)
        strSQL = "SELECT * FROM sur_page WHERE survey_id = " & lngOriginalSurveyID
        rsOriginalSurvey.Open ConvertSQL(strSQL), SURVEY_APP_CONNECTION, adOpenForwardOnly, adLockReadOnly, adCmdText

        'Make sure that there are page property entries for this survey
        If Not rsOriginalSurvey.EOF Then
            rsOriginalSurvey.MoveFirst

            'Build the SQL statements to insert the new records into the SUR_PAGE table
            Do While Not rsOriginalSurvey.EOF
                strSQL = "INSERT INTO sur_page(survey_id, page_number, page_title, page_introduction) " & _
                            "VALUES(" & _
                            lngNewSurveyID & ", " & _
                            rsOriginalSurvey("page_number") & ", " & _
                            SQLEncode(rsOriginalSurvey("page_title")) & ", " & _
                            SQLEncode(rsOriginalSurvey("page_introduction")) & ")"
                conNewSurvey.Execute ConvertSQL(strSQL), , adCmdText
                rsOriginalSurvey.MoveNext
            Loop
        End If
        rsOriginalSurvey.Close

        '8.  If there are any pipes in the items, update them to use the new item ID's
        strSQL = "SELECT item_id, item_sub_text, item_text " & _
                 "FROM sur_item " & _
                 "WHERE item_id IN (" & _
                 ConvertSQLInClause("SELECT item_id FROM sur_survey_to_item_mapping WHERE survey_id = " & lngNewSurveyID, "item_id") & ")"
        Set rsOriginalSurvey = conNewSurvey.Execute(ConvertSQL(strSQL), , adCmdText)
        If Not rsOriginalSurvey.EOF Then
            rsOriginalSurvey.MoveFirst
            Do While Not rsOriginalSurvey.EOF
                strItemText = rsOriginalSurvey("item_text")
                strItemSubText = rsOriginalSurvey("item_sub_text")

                'Create an array of all the pairs of new and old item ID's
                vaItemIDs = Split(strOldToNewItemIDs, ";")

                'Iterate through the item ID's.  If any of the old item ID's are found as pipes in the item text or item
                'sub text, update the text to use the new item ID
                For i = 0 To UBound(vaItemIDs) Step 2
                    'If there is a pipe, update it
                    If InStr(strItemText, "###" & vaItemIDs(i) & "###") > 0 Or InStr(strItemSubText, "###" & vaItemIDs(i) & "###") > 0 Then
                        strItemText = Replace(strItemText, "###" & vaItemIDs(i) & "###", "###" & vaItemIDs(i + 1) & "###")
                        strItemSubText = Replace(strItemSubText, "###" & vaItemIDs(i) & "###", "###" & vaItemIDs(i + 1) & "###")
                        strSQL = "UPDATE sur_item " & _
                                 "SET item_text = " & SQLEncode(strItemText) & ", " & _
                                 "item_sub_text = " & SQLEncode(strItemSubText) & _
                                 " WHERE item_id = " & rsOriginalSurvey("item_id")
                        conNewSurvey.Execute ConvertSQL(strSQL), , adCmdText
                    End If
                Next
                rsOriginalSurvey.MoveNext
            Loop
        End If
        rsOriginalSurvey.Close

        '9.  Copy all of the data from the hidden fields table (SUR_HIDDEN_FIELD)
        strSQL = "SELECT * FROM sur_hidden_field WHERE survey_id = " & lngOriginalSurveyID
        rsOriginalSurvey.Open ConvertSQL(strSQL), SURVEY_APP_CONNECTION, adOpenForwardOnly, adLockReadOnly, adCmdText

        'Make sure that there are hidden fields before copying
        If Not rsOriginalSurvey.EOF Then
            rsOriginalSurvey.MoveFirst

            'Build the SQL statements to insert the new records into the SUR_HIDDEN_FIELD table
            Do While Not rsOriginalSurvey.EOF
                strSQL = "INSERT INTO sur_hidden_field(hidden_field_id, survey_id, order_number, hidden_field_type, " & _
                            "hidden_field_key, hidden_field_display_name) VALUES(" & _
                            ID_GetNextAvailableID("SurveyGenerationHiddenField") & ", " & _
                            lngNewSurveyID & ", " & _
                            rsOriginalSurvey("order_number") & ", " & _
                            SQLEncode(rsOriginalSurvey("hidden_field_type")) & ", " & _
                            SQLEncode(rsOriginalSurvey("hidden_field_key")) & ", " & _
                            SQLEncode(rsOriginalSurvey("hidden_field_display_name")) & ")"
                conNewSurvey.Execute ConvertSQL(strSQL), , adCmdText
                rsOriginalSurvey.MoveNext
            Loop
        End If
        rsOriginalSurvey.Close

    End If

    'Clean up
    conNewSurvey.Close
    Set conNewSurvey = Nothing

    'Redirect to the modify survey page for the survey just created
    Response.Redirect "ModifySurvey.asp?SurveyID=" & lngNewSurveyID
%>