Administrator Recipe: Edit Survey

The Edit Survey page serves three main functions: to modify the survey information such as title and start/end dates; to provide an overview of associated questions and a link to modify them; and to take the survey offline, even when in the declared active timeframe. A parameter, passed from the link in Survey Manager, determines the proper survey record to use; our custom code redirects the user to the Survey Manager page if the parameter is missing, which is a far better solution than displaying an empty recordset. Because the Edit Survey page is also the conduit to modifying the survey's questions, the URL parameter, if found, is put into a session variable and used to filter the survey recordset. In addition to the survey recordset, a second recordset for the questions is created for this page.

When the modifications to the survey record are complete, a standard Update Record server behavior stores the changes. In addition to updating the current survey record, the Edit Survey recipe describes how to delete a record. What makes this delete function different from others included in this book is that it also removes associated records in this case, orphaned questions.

Step 1: Implement Edit Survey Design

To handle the requirements for the Edit Survey page, the layout must accommodate both a form for displaying the survey fields and a content table for listing the questions.

  1. Create a page for the Edit Survey recipe, either from a template or from the File > New dialog.

    graphics/book.gif In the SurveyBuilder folder, locate the folder for your server model and open the Edit_Survey page.

  2. In the Content area of your page, create a header for the page a placeholder to hold both the form and the content table.

    graphics/book.gif From the Snippets panel, drag the Recipes > SurveyBuilder > Wireframes > Edit Survey Wireframe snippet into the Content editable region.

  3. In the form area, add a form with the following form elements: three text fields (for the survey title, start date, and end date), one text area (for the completion message), one checkbox to enable or disable the survey, and three buttons (to update or delete the survey or to add a new question).

    graphics/book.gif Place your cursor in the first row below the words EDIT SURVEY and insert the Recipes > SurveyBuilder > Forms > Edit Survey - Form snippet.

  4. In the Content area, create a table, 2 rows by 2 columns, to hold the questions.

    graphics/book.gif Place your cursor in the third row below the words EDIT SURVEY at the bottom of the content region and insert the Recipes > SurveyBuilder > ContentTables > Survey Questions - Content Table snippet [r5-6].


    Figure r5-6.

    graphics/07fig06.jpg


  5. Save the page.

Step 2: Adding Database Components

Quite often in this book's recipes when custom code is added, it is inserted after a standard Dreamweaver server behavior has been applied. In this case, however, we need to include the custom code first because the server behavior relies on the code's results.

If you've completed the Survey Manager recipe, you'll remember how a URL parameter (ID) is used to pass the SurveyID to various pages, including this one. Our custom code takes the URL parameter and puts it in a session variable that can be used when editing the associated questions. If the URL parameter is not found, the code redirects the user to the Survey Manager page to select a survey to work on. The user is also redirected to the Survey Manager page if the session has expired.

  1. In Code view, place the cursor at the top of the page, just after the DOCTYPE declaration, and press Enter (Return) to make a space.

    Don't worry about the exact placement of this code block; because of the way Dreamweaver inserts recordsets, we'll need to adjust its placement later in the recipe.

  2. Insert the following code:

    graphics/book.gif From the Snippets panel, open the Recipes > SurveyBuilder > Custom Code folder for your server model and insert the Set Session - SurveyID to Request ID snippet.

    graphics/vb.gif

     <% if (cStr(Request("ID"))<>"") then    Session("SurveyID") = cStr(Request("ID")) end if if (cStr(Session("ID"))="") then    Response.Redirect("survey_manager.asp") end if %> 

    graphics/js.gif

     <% if (String(Request("ID"))!="undefined")    Session("SurveyID") = String(Request("ID")); if (String(Session("ID"))=="undefined")    Response.Redirect("survey_manager.asp"); %> 

    graphics/cf.gif

     <cfif isDefined("URL.ID">    <cfset Session.SurveyID = URL.ID> </cfif> <cfif not IsDefined("Session.ID")>    <cflocation url="survey_manager.cfm"> </cfif> 

    graphics/php.gif

     <?php session_start(); if (isset($_GET['ID'])) {    $_SESSION['ID'] = $_GET['ID']; } else {    header("Location: survey_manager.php"); } ?> 

  3. When you're done, save the page.

Now we're ready to insert the first of two recordsets, both of which reference the session variable just set.

  1. From the Bindings panel, choose Add (+) and select Recordset (Query) from the list.

  2. In the simple Recordset dialog, give the recordset an appropriate name.

    graphics/book.gif Enter Survey in the Name field.

  3. Select the desired data source connection.

    graphics/book.gif Choose Recipes from the Connection (Data Source) list.

  4. Choose the needed recordset.

    graphics/book.gif From the Tables list, select Surveys (surveys in PHP).

  5. Leaving the Columns option set to All, set the four Filter list elements like this:

    graphics/350fig01.gif

  6. Keep the Sort option set to None and click OK to close the dialog.

The second recordset, which displays a list of the survey's questions, is a bit more complex. Records are needed from two data source tables: SurveyQuestions and QuestionTypes. To accomplish this, a SQL statement using an inner join is entered through the Advanced Recordset dialog.

For ASP

graphics/book.gif

As a preliminary step, from the Snippets panel, use the Copy Snippet command to copy the Recipes > Survey Builder > SQL > Edit Survey RS - ASP SQL Statement snippet to the Clipboard.


  1. From the Bindings panel, choose Add (+) and select Recordset (Query) from the list.

  2. In the Recordset dialog, choose Advanced.

  3. Enter an appropriate name for the recordset.

    graphics/book.gif Enter Questions in the Name field.

  4. Select the desired data source.

    graphics/book.gif Choose Recipes from the Connections list.

  5. In the SQL area, enter the following code:

    graphics/book.gif Paste the copied snippet into the SQL area by pressing Ctrl-V (Command-V).

     

    [View full width]

    SELECT * FROM SurveyQuestions INNER JOIN QuestionTypes ON SurveyQuestions.QuestionType = graphics/ccc.gif QuestionTypes.TypeID WHERE QuestionSurvey = IDParam
     
  6. In the Variables section, choose Add (+) to create a new variable.

  7. Enter the name for the variable.

    graphics/book.gif In the Name column, enter IDParam.

  8. Enter a default value for the variable.

    graphics/book.gif In the Default Value column, enter 1.

    Enter a run-time value for the variable.

    In the Run-Time Value column, enter Session("SurveyID").

    graphics/book.gif Click OK to close the dialog and insert the recordset.

For ColdFusion

graphics/book.gif

As a preliminary step, from the Snippets panel, use the Copy Snippet command to copy the Recipes > Survey Builder > SQL > Edit Survey RS - CF SQL Statement snippet to the Clipboard.


  1. From the Bindings panel, choose Add (+) and select Recordset (Query).

  2. Switch to the advanced view of the dialog and enter an appropriate name for the recordset.

    graphics/book.gif Enter Questions in the Name field.

  3. Choose your data source.

    graphics/book.gif Select Recipes from the Data Source list.

  4. Enter a username and password if necessary.

  5. In the SQL area, enter the following code:

    graphics/book.gif Paste the copied snippet into the SQL area by pressing Ctrl-V (Command-V)

     

    [View full width]

    SELECT * FROM SurveyQuestions INNER JOIN QuestionTypes ON SurveyQuestions.QuestionType = graphics/ccc.gif QuestionTypes.TypeID WHERE QuestionSurvey = #Session.SurveyID#
     
  6. In the Page Parameter section, choose Add (+) and, in the Add Parameter dialog, make sure Session.SurveyID is chosen in the Name list.

  7. Enter 0 as the Default Value and click OK to close the dialog.

  8. Verify your entries in the Recordset dialog and click OK to close that dialog.

For PHP

graphics/book.gif

As a preliminary step, from the Snippets panel, use the Copy Snippet command to copy the Recipes > Survey Builder > SQL > Edit Survey RS - PHP SQL Statement snippet to the Clipboard.


  1. From the Bindings panel, choose Add (+) and select Recordset from the list.

  2. In the Recordset dialog, choose Advanced.

  3. Enter an appropriate name for the recordset.

    graphics/book.gif Enter Questions in the Name field.

  4. Select the desired data source.

    graphics/book.gif Choose Recipes from the Connections list.

  5. In the SQL area, enter the following code:

    graphics/book.gif Press Ctrl-V (Command-V) to paste the copied snippet into the SQL area.

     

    [View full width]

    SELECT * FROM surveyquestions INNER JOIN questiontypes ON surveyquestions.QuestionType = graphics/ccc.gif questiontypes.TypeID WHERE questionsurvey = IDParam
     
  6. In the Variables section, choose Add (+) to create a new variable.

  7. Enter the name for the variable in the Add Variable dialog.

    graphics/book.gif In the Name field, enter IDParam.

  8. Enter a default value for the variable.

    graphics/book.gif In the Default Value field, enter 1.

  9. Enter a run-time value for the variable.

    graphics/book.gif In the Run-time Value field, enter $_SESSION['SurveyID'] and click OK to close the Add Variable dialog.

  10. Click OK again to close the Recordset dialog and insert the recordset.

Step 3: Data Binding Process

Now that we have our two recordsets ready to go, we'll bind the data to the various form and text elements. First, we'll attach data fields from the Survey recordset to the form elements. As with the Survey Manager page, PHP requires custom formatting code and a function for converting the date values into a usable format.

  1. From the Bindings panel, expand the Survey recordset.

  2. Place the data source fields onto the page in their respective areas:

    graphics/book.gif Drag SurveyName onto the SurveyTitle text field.

    graphics/vb.gifgraphics/js.gifgraphics/cf.gif ASP and ColdFusion users: Drag SurveyStartDate onto the SurveyStartDate text field.

    graphics/php.gif PHP users: Select the SurveyStartDate text field, switch to Code view, and then insert the Recipes > Survey Builder > CustomCode_PHP > Survey Manager - Reformat SurveyStartDate Column snippet into the value attribute.

    graphics/vb.gifgraphics/js.gifgraphics/cf.gif ASP and ColdFusion users: Drag SurveyEndDate onto the SurveyEndDate text field.

    graphics/php.gif PHP users: Select the SurveyEndDate text field, switch to Code view, and then insert the Recipes > Survey Builder > CustomCode_PHP > Survey Manager - Reformat SurveyEndDate Column snippet into the value attribute.

    graphics/book.gif Drag SurveyCompletedMessage onto the SurveyCompletionMessage text area.

  3. ColdFusion users should apply formatting to the two date form fields:

    graphics/cf.gif Select the SurveyStartDate form field and, from the Format column of the selected entry in the Bindings panel, choose Date/Time > 1/17/00 or whichever date format you would prefer.

    graphics/cf.gif Select the SurveyEndDate form field and, from the Format column of the selected entry in the Bindings panel, choose Date/Time > 1/17/00 or whichever date format you'd prefer.

  4. PHP users need to add another function to the page to reconvert the dates to their previous formats:

    graphics/php.gif In Code view, position your cursor at the end of the document and, from the Snippets panel, insert the Recipes > SurveyBuilder > CustomCode_PHP > Survey Manager - Reformat Date snippet.

    graphics/php.gif

    [View full width]

     <?php function reformatDate($date) {    return substr($date,5,2) . "/" . substr($date,8,2) . "/"  graphics/ccc.gif. substr($date,0,4); } ?> 

All that's left in the Survey recordset is to link the checkbox to a data field.

  1. Select the SurveyStatus checkbox next to the Live label.

  2. In the Property inspector, choose Dynamic to open the Dynamic CheckBox dialog.

  3. Select the lightning bolt next to the Check If field to open the Dynamic Data dialog.

  4. In the Dynamic Data dialog, select SurveyLive from the Survey recordset and click OK.

  5. In the Equal To field, enter True and click OK to close the Dynamic CheckBox dialog [r5-7].


    Figure r5-7.

    graphics/07fig07.jpg


One extra step is required for the ColdFusion and PHP server models for the survey section. A hidden field form element is needed to hold the session variable so that it can be used in the Update Record server behavior applied in the next step of the recipe.

  1. From the Forms tab of the Insert bar, choose Hidden Field.

  2. In the Property inspector, enter SurveyID in the Name field on the left side of the inspector.

  3. Enter the session variable data field as the hidden element's value:

    In the Property inspector, select the lightning bolt icon next to the Value field to open the Dynamic Data dialog.

    graphics/cf.gif In the Dynamic Data dialog, expand the CFParam entry and choose SESSION.SurveyID. Click OK when you're done.

    graphics/php.gif Enter the following code directly into the Init Val field of the Property inspector:

     
     <?php echo $_SESSION['SurveyID']; ?> 
     
  4. When you're done, save your page.

Let's move on to binding data in the Survey Questions area. To display a list of questions for a particular form, we'll bind two data fields from the Questions recordset.

  1. From the Bindings panel, expand the Questions recordset.

  2. Place the data fields on the page in the appropriate places.

    graphics/book.gif Drag QuestionText onto the table cell beneath the label Survey Questions.

    Drag QuestionType (TypeName in PHP) onto the table cell beneath the label Question Type.

Now we'll use one of the fields just inserted as a link for editing a particular question.

  1. Select the Questions.QuestionText dynamic text entry from the page.

  2. In the Property inspector, select the folder icon next to the Link field to open the Select File dialog.

  3. In the Select File dialog, choose Parameters.

  4. In the Parameters dialog, enter a variable name.

    graphics/book.gif Enter ID in the Name column.

  5. Enter a value for the new variable.

    graphics/book.gif In the Value column, select the lightning bolt symbol to open the Dynamic Data dialog. From there, choose QuestionsID from the Questions recordset and click OK to close the Dynamic Data dialog.

  6. Click OK to close the Parameters dialog.

  7. Choose a file to pass the parameter to.

    graphics/book.gif Select the edit_question file for your server model.

  8. Click OK to close the dialog and add the link.

Finally, we'll apply a Repeat Region server behavior to the entire row.

  1. From the Question recordset, select the row that contains the dynamic text elements.

    graphics/book.gif Choose <tr> from the Tag Selector.

  2. From the Server Behaviors panel, choose Add (+) and select Repeat Region from the list.

  3. In the Repeat Region dialog, choose the desired recordset.

    graphics/book.gif Select Questions from the Recordset list.

  4. Select the number of records you want to show.

    graphics/book.gif Choose Show All Records.

  5. Click OK when you're done.

  6. Save the page.

Step 4: Update the Modified Record

The Update Record server behavior stores the modified values in the Survey recordset. After the record has been updated, the user is taken back to the Survey Manager page.

For ASP
  1. From the Server Behaviors panel, choose Add (+) and select Update Record.

  2. In the Update Record dialog, select the desired data source connection. Choose Recipes from the Connection list.

  3. Choose the table that contains the data you are updating.

    graphics/book.gif From the Table to Update list, choose Surveys.

  4. Choose the recordset from which to get data source fields.

    graphics/book.gif From the Select Record From field, choose Survey.

  5. Set the primary key for the recordset.

    graphics/book.gif From the Unique Key Column list, choose SurveyID and make sure the Numeric option is selected.

  6. Select the file you would like to appear when the update is complete.

    graphics/book.gif For the After Updating, Go To field, select the survey_manager.asp page.

  7. Choose the form on the page from which to obtain the values.

    graphics/book.gif From the Get Values From list, choose EditSurvey.

  8. In the Form Elements area, set the form elements to their corresponding data source fields.

    graphics/book.gif Set the SurveyTitle form element to update the SurveyName data source field as Text.

    Set the SurveyStartDate form element to update the SurveyStartDate data source field as Date MS Access.

    Set the SurveyEndDate form element to update the SurveyEndDate data source field as Date MS Access.

    Set the SurveyCompletionMessage form element to update the SurveyCompletedMessage data source field as Text.

    Set the SurveyStatus form element to update the SurveyLive data source field as Checkbox MS Access.

  9. Make sure your entries are correct and then click OK to close.

For ColdFusion and PHP
  1. From the Server Behaviors panel, choose Add (+) and select Update Record.

  2. In the Update Record dialog, choose the current form.

    graphics/book.gif Select EditSurvey from the Submit Values From list.

  3. Select your data source from the list.

    graphics/book.gif Choose Recipes from the Data Source list.

  4. Enter your username and password, if needed.

  5. Select the table in the data source to insert into from the list.

    graphics/book.gif Choose Surveys (surveys for PHP) from the Insert into Table list.

  6. Set the data source fields to their corresponding form elements.

    graphics/book.gif Set SurveyID to the FORM.SurveyID form element as Numeric, with the Primary Key option selected.

    Set SurveyName to the FORM.SurveyTitle form element and submit it as Text.

    Set SurveyStartDate to the FORM.SurveyStartDate form element and submit it as Date MS Access type (Date in PHP).

    Set SurveyEndDate to the FORM.SurveyEndDate form element and submit it as Date MS Access type (Date in PHP).

    Set SurveyLive to the FORM.SurveyStatus form element and submit it as Checkbox MS Access type (Checkbox 1.0 in PHP).

    Set SurveyCompletedMessage to the FORM.SurveyCompletionMessage form element and submit it as Text.

  7. In the After Inserting, Go To field, enter the path to the file you want displayed after the record is updated.

    graphics/book.gif Choose Browse and select the survey_manager.cfm or survey_manager.php file, as appropriate.

  8. Check your entries to verify they are correct and, if so, click OK.

Step 5: Apply Delete Commands/Queries

So far, we've handled displaying and updating a survey's record. The next step and it's a fairly involved one is deleting the survey record. What makes it so involved is that not only must the current survey record be removed, but so must that survey's questions and responses to those surveys. These operations are accomplished through the use of SQL commands (known as queries in ColdFusion) covered in this step.

One command, RemoveOrphanQuestions, removes all the fields in the SurveyQuestions table that no longer have a corresponding SurveyID in the Surveys table:

 

[View full width]

DELETE SurveyQuestions.* FROM Surveys RIGHT JOIN SurveyQuestions ON [Surveys].[SurveyID]=[SurveyQuestions] graphics/ccc.gif.[QuestionSurvey] WHERE ([Surveys].[SurveyID]) Is Null;
 

A second command, RemoveOrphanResults, performs a similar function for the results of questions that no longer exist:

 

[View full width]

DELETE SurveyResults.* FROM SurveyQuestions RIGHT JOIN SurveyResults ON [SurveyQuestions] graphics/ccc.gif.[QuestionID]=[SurveyResults].[ResultQuestion] WHERE ((([SurveyQuestions].[QuestionID]) Is Null));
 

As you can see, the sequence of code application is important here. First the survey record is removed, and then the survey's questions, followed by the survey questions' responses.

A bit later in this chapter (Steps 6 and 7), we'll walk through moving the code to its proper location and linking it to the Delete button. It's best to wait to test these commands until all the steps have been completed.

For ASP

First, we'll insert a SQL command to remove the current record. The ID for the current record, MM_recordId, is gathered from the Update Server behavior, applied earlier.

  1. From the Bindings panel, choose Command.

  2. In the Command dialog, enter an appropriate name.

    graphics/book.gif Enter DeleteActive in the Name field.

  3. Choose the data source.

    graphics/book.gif From the Connections list, select Recipes.

  4. Set the type of command.

    graphics/book.gif From the Type list, choose Delete.

  5. Leave the Return Recordset Named options blank.

  6. In the SQL area, enter the following code:

    graphics/vb.gif

     DELETE FROM Surveys WHERE SurveyID = IDParam 

  7. In the Variable area, choose Add (+) to add a new SQL variable.

  8. Enter the name of the variable.

    graphics/book.gif In the Name column, enter IDParam.

    Insert the run-time value.

    graphics/book.gif In the Run-Time value column, enter Request("MM_recordId").

  9. Click OK to insert the command.

Note

You'll notice in both instances that a Stored Procedure command type is used. Access does not truly support stored procedures, but this technique allows us to directly execute a database command.


Next, we'll access a command included in the data source to remove the questions associated with the deleted survey.

  1. From the Bindings panel, choose Add (+) and choose Command.

  2. Enter an appropriate name for the command.

    graphics/book.gif Enter RemoveOrphanQuestions in the Name field.

  3. Choose a data source.

    graphics/book.gif From the Connections list, choose Recipes.

  4. Select the proper type of command.

    graphics/book.gif Choose Stored Procedure from the Type list.

  5. Leave the Return Recordset Named options empty.

  6. In the SQL area, enter the following command call:

     
     RemoveOrphanQuestions 
     
  7. Click OK to close the dialog and insert the command.

The final command to insert serves to remove any results attached to the previously removed questions.

  1. From the Bindings panel, choose Add (+) and choose Command.

  2. Enter an appropriate name for the command.

    graphics/book.gif Enter RemoveOrphanResults in the Name field.

  3. Choose a data source.

    graphics/book.gif From the Connections list, choose Recipes.

  4. Select the proper type of command.

    graphics/book.gif Choose Stored Procedure from the Type list.

  5. Leave the Return Recordset Named options empty.

  6. In the SQL area, enter the following command call:

     
     RemoveOrphanResults 
     
  7. Click OK to close the dialog and insert the command.

Note

Again, refrain from testing the delete command until you've completed the remaining steps for this page.


For ColdFusion

The first task is to insert a SQL query to remove the current record. We'll get the ID for the current record from the session variable SurveyID stored earlier.

  1. From the Bindings panel, choose Recordset (Query).

  2. In the Advanced Recordset dialog, enter an appropriate name.

    graphics/book.gif Enter DeleteActive in the Name field.

  3. Choose the data source.

    graphics/book.gif From the Data Source list, select Recipes.

  4. Enter the username and password, if required.

  5. In the SQL area, enter the following code:

    graphics/cf.gif

     DELETE FROM Surveys WHERE SurveyID = #Session.SurveyID# 

  6. Click OK to insert the query.

Next, we'll access a query included in the data source to remove the questions associated with the deleted survey.

  1. From the Bindings panel, choose Recordset (Query).

  2. In the Advanced Recordset dialog, enter an appropriate name.

    graphics/book.gif Enter RemoveOrphanQuestions in the Name field.

  3. Choose the data source.

    graphics/book.gif From the Data Source list, select Recipes.

  4. Enter the username and password, if required.

  5. In the SQL area, enter the following code:

    graphics/php.gif

     EXECUTE RemoveOrphanQuestions 

  6. Click OK to insert the command.

The final command to insert serves to remove any results attached to the previously removed questions.

  1. From the Bindings panel, choose Recordset (Query).

  2. In the Advanced Recordset dialog, enter an appropriate name.

    graphics/book.gif Enter RemoveOrphanResults in the Name field.

  3. Choose the data source.

    graphics/book.gif From the Data Source list, select Recipes.

  4. Enter the username and password, if required.

  5. In the SQL area, enter the following code:

    graphics/cf.gif

     EXECUTE RemoveOrphanResults 

  6. Click OK to insert the command.

Note

Again, refrain from testing the delete command until you've completed the remaining steps for this page.


For PHP

To handle the delete command in PHP, custom code is added. The code block inserted is conditional and only executes when the Delete button has been encountered. The code block must be placed following the session variable declaration.

  1. In Code view, place your cursor after the routine that sets the session variable. You'll find the code block near the top of the page. It looks like this:

    graphics/php.gif

     <?php session_start(); if (isset($_GET['ID'])) {     $_SESSION['ID'] = $_GET['SurveyID']; } else {     header("Location: survey_manager.php"); } ?> 

  2. Insert the following code:

    graphics/book.gif From the Snippets panel, insert the Recipes > SurveyBuilder > CustomCode_PHP > Edit Survey Delete Command snippet.

    graphics/php.gif

    [View full width]

     <?php mysql_select_db($database_Recipes_PHP, $Recipes_PHP); if ((isset($_POST['Delete'])) && ($_POST['Delete'] == graphics/ccc.gif "Delete")) {     // First delete the selected record     $deleteSQL = "DELETE FROM surveys WHERE SurveyID = " . graphics/ccc.gif $_SESSION['SurveyID'];     $deleteRes = mysql_query($deleteSQL,$Recipes_PHP);     // Now clean up orphaned questions     $deleteSQL = "DELETE surveyquestions.* FROM surveys graphics/ccc.gif RIGHT JOIN surveyquestions ON surveys.SurveyID = graphics/ccc.gif surveyquestions.QuestionSurvey WHERE surveys.SurveyID IS NULL";     $deleteRes = mysql_query($deleteSQL,$Recipes_PHP);     // Now clean up orphaned results     $deleteSQL = "DELETE surveyresults.* FROM graphics/ccc.gif surveyquestions RIGHT JOIN surveyresults ON surveyquestions graphics/ccc.gif.QuestionID=surveyresults.ResultQuestion WHERE graphics/ccc.gif surveyquestions.QuestionID IS NULL";     $deleteRes = mysql_query($deleteSQL,$Recipes_PHP); } ?> 

  3. Save your page.

PHP developers can skip the following two steps because the code is already properly placed and made conditional. Resume the recipe at Step 8.

Step 6: Move Commands to the Top of the Page

For the application to work properly, three groups of code the delete commands, the delete operation declaration, and the session variable declaration must be moved before the recordset declarations. As you can see from the Server Behaviors panel, the three delete commands (in ColdFusion, they appear with the prefix CFQUERY) added in the previous step now follow the two recordsets, Survey and Questions. Unfortunately, the Server Behaviors panel doesn't support drag-and-drop repositioning, so we'll have to move the code manually.

Note

This step is necessary only for ASP and ColdFusion users.


First, let's reposition the code creating the three commands:

  1. Switch to Code view.

  2. In the Server Behaviors panel, choose the DeleteActive command.

    The first of the three commands is selected in the code.

  3. Select the current code function as well as the two following it. The selected code should look like this:

    graphics/vb.gif

    [View full width]

     <% set DeleteActive = Server.CreateObject("ADODB.Command") DeleteActive.ActiveConnection = MM_Recipes_STRING DeleteActive.CommandText = "DELETE FROM Surveys WHERE graphics/ccc.gif SurveyID = " + Replace(DeleteActive__IDParam, "'", "''") + " " DeleteActive.CommandType = 1 DeleteActive.CommandTimeout = 0 DeleteActive.Prepared = true DeleteActive.Execute() %> <% set RemoveOrphanQuestions = Server.CreateObject("ADODB.Command") RemoveOrphanQuestions.ActiveConnection = MM_Recipes_STRING RemoveOrphanQuestions.CommandText = "RemoveOrphanQuestions" RemoveOrphanQuestions.CommandType = 4 RemoveOrphanQuestions.CommandTimeout = 0 RemoveOrphanQuestions.Prepared = true RemoveOrphanQuestions.Execute() %> <% set RemoveOrphanResults = Server.CreateObject("ADODB.Command") RemoveOrphanResults.ActiveConnection = MM_Recipes_STRING RemoveOrphanResults.CommandText = "RemoveOrphanResults" RemoveOrphanResults.CommandType = 4 RemoveOrphanResults.CommandTimeout = 0 RemoveOrphanResults.Prepared = true RemoveOrphanResults.Execute() %> 

    graphics/js.gif

    [View full width]

     <% set DeleteActive = Server.CreateObject("ADODB.Command") DeleteActive.ActiveConnection = MM_Recipes_STRING DeleteActive.CommandText = "DELETE FROM Surveys WHERE graphics/ccc.gif SurveyID = " + Replace(DeleteActive__IDParam, "'", "''") + " " DeleteActive.CommandType = 1 DeleteActive.CommandTimeout = 0 DeleteActive.Prepared = true DeleteActive.Execute() %> <% set RemoveOrphanQuestions = Server.CreateObject("ADODB.Command") RemoveOrphanQuestions.ActiveConnection = MM_Recipes_STRING RemoveOrphanQuestions.CommandText = "RemoveOrphanQuestions" RemoveOrphanQuestions.CommandType = 4 RemoveOrphanQuestions.CommandTimeout = 0 RemoveOrphanQuestions.Prepared = true RemoveOrphanQuestions.Execute() %> <% set RemoveOrphanResults = Server.CreateObject("ADODB.Command") RemoveOrphanResults.ActiveConnection = MM_Recipes_STRING RemoveOrphanResults.CommandText = "RemoveOrphanResults" RemoveOrphanResults.CommandType = 4 RemoveOrphanResults.CommandTimeout = 0 RemoveOrphanResults.Prepared = true RemoveOrphanResults.Execute() %> 

    graphics/cf.gif

     <cfquery name="DeleteActive" datasource="Recipes"> DELETE FROM Surveys WHERE SurveyID=#Session.SurveyID# </cfquery> <cfquery name="RemoveOrphanQuestions" datasource="Recipes"> EXECUTE RemoveOrphanQuestions </cfquery> <cfquery name="RemoveOrphanResults" datasource="Recipes"> EXECUTE RemoveOrphanResults </cfquery> 

  4. Cut the code by choosing Edit > Cut or press Ctrl-X (Command-X).

  5. Place the cursor at the top of the page. ASP users should place the cursor just after the Connection Include.

  6. Paste the code by choosing Edit > Paste or press Ctrl-V (Command-V).

  7. Save the page.

Next, we'll need to move the delete operation declaration. (This step is only necessary for the ASP server models.)

  1. In Code view, use Find and Replace to locate the first instance of DeleteActive__IDParam.

    Note the double underscore characters in the name.

  2. Cut the surrounding function by choosing Edit > Cut or press Ctrl-X (Command-X). The code to cut looks like this:

    graphics/vb.gif

    [View full width]

     <% if(Request("MM_recordId") <> "") then DeleteActive__IDParam graphics/ccc.gif = Request("MM_recordId") %> 

    graphics/js.gif

    [View full width]

     <% if(String(Request("MM_recordId")) != "undefined") { graphics/ccc.gif DeleteActive__IDParam = String(Request("MM_recordId"));}} %> 

  3. Move to the top of the page above the three commands moved previously.

  4. Paste the code by choosing Edit > Paste or press Ctrl-V (Command-V).

Finally, we'll move the session definition to the top of the page.

  1. In Code view, locate the code function above the opening <html> tag. The code should look like this:

    graphics/vb.gif

     <% if (cStr(Request("SurveyID"))<>"") then   Session("SurveyID") = cStr(Request("SurveyID")) end if if (cStr(Session("SurveyID"))="") then   Response.Redirect("survey_manager.asp") end if %> 

    graphics/js.gif

     <% if (String(Request("SurveyID"))!="undefined")   Session("SurveyID") = String(Request("SurveyID")); if (String(Session("SurveyID"))=="undefined")   Response.Redirect("survey_manager.asp"); %> 

    graphics/cf.gif

     <cfif isDefined("URL.SurveyID")>   <cfset Session.SurveyID = URL.SurveyID> </cfif> <cfif not IsDefined("Session.SurveyID")>   <cflocation url="survey_manager.cfm"> </cfif> 

    The ColdFusion code is located just above the <cfquery> tags.

  2. Select the code and cut it using Edit > Cut or press Ctrl-X (Command-X).

  3. Place the cursor at the top of the page under the Connection Include and paste the code by choosing Edit > Paste or pressing Ctrl-V (Command-V).

Note

Don't test the page until we make the delete code conditional! You could inadvertently delete a survey record.


Step 7: Add Conditional Code

As the code now stands, the current survey and all its associated questions and responses would be automatically deleted when this page is executed. Obviously, we want these actions to take place only when the Delete button is selected. To achieve this, we must make certain the code is conditional by wrapping it with an If-then clause so that it executes only when desired.

Our conditional clause is added in two parts. The first sets up the condition and is placed before the DeleteActive declaration and the three commands/queries. The second follows the third command/query and redirects the user to a specific page when the delete action is complete.

  1. In Code view, position the cursor after the session variable declaration moved in the previous step and before the delete operation declaration.

  2. Insert the following code:

    graphics/book.gif From the Snippets panel, open the Recipes > SurveyBuilder > Custom Code folder for your server model and insert the If Statement - Delete Operation snippet.

    graphics/vb.gif

     <% if (cStr(Request("Delete"))<>"") then %> 

    graphics/js.gif

     <% if (String(Request("Delete"))!="undefined") { %> 

    graphics/cf.gif

    [View full width]

     <cfif IsDefined("Session.SurveyID") AND IsDefined("FORM graphics/ccc.gif.Delete")> 

    Now let's add the second half of our conditional code.

  3. Place the cursor below the third delete command/query, which took care of removing the orphaned survey responses.

  4. Insert the following code:

    graphics/book.gif From the Snippets panel, open the Recipes > SurveyBuilder > Custom Code folder for your server model and insert the Redirect Survey Manager snippet.

    graphics/vb.gif

     <% Response.Redirect("survey_manager.asp") end if %> 

    graphics/js.gif

     <% Response.Redirect("survey_manager.asp"); } %> 

    graphics/cf.gif

     <cflocation url="survey_manager.cfm"> </cfif> 

  5. Save the page.

There's one last step to take, but the page can be previewed now if you like.

Step 8: Link to Entering New Questions

The final step is to configure the Add New Question button. We'll use the Go To URL behavior to open the appropriate add_question file while passing the survey ID as a parameter.

  1. Select the Add New Question form button.

  2. From the Behaviors panel, choose Add (+) and select Go To URL from the list.

  3. In the Go to URL dialog, select Browse to open the Select File dialog [r5-8].


    Figure r5-8.

    graphics/07fig08.gif


  4. Enter the following code in the File field:

    graphics/vb.gif

     add_question.asp?ID=<%=Session("SurveyID")%> 

    graphics/js.gif

     add_question.asp?ID=<%=Session("SurveyID")%> 

    graphics/cf.gif

     add_question.cfm?ID=<cfoutput>#Session.SurveyID#</cfoutput>

    graphics/php.gif

     add_question.php?ID=<?php echo $_SESSION['SurveyID']; ?> 

  5. Click OK to close the dialog.

  6. Save your page.



Macromedia Dreamweaver MX 2004 Web Application Recipes
Macromedia Dreamweaver MX 2004 Web Application Recipes
ISBN: 0735713200
EAN: 2147483647
Year: 2003
Pages: 131

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net