End User Recipe: Meeting Request

The entry point to a conference room scheduling application is the meeting request form. From the user's perspective, the form is fairly straightforward. After entering a few descriptive details the meeting's name, owner, and description the when (time, date, and duration) and where are input.

When the request is submitted, however, all pretense of simplicity fades away. The request is first compared to existing meetings to check for a conflicting meeting. Should no conflict be found, the meeting details are added to the data source; but if another meeting is booked for the same room and time, the user is taken to another page where the particulars of the conflicting meeting are displayed.

This application page incorporates three recordsets, including one that gets the just-inserted data for confirmation purposes. You'll also find a somewhat sophisticated method of gathering time and date information and combining the details into a data source-friendly format.

Step 1: Implement Meeting Request Design

The meeting request page requires a fair degree of information, and you'll need to construct the form so that it remains easy to use. A series of text fields (both single line and text areas) are used to gather the basic descriptive information whereas the time and place information is primarily input through drop-down lists.

  1. Create a basic dynamic page, either by hand or derived from a template.

    graphics/book.gif In the ConferenceRoomScheduler folder, locate the folder for your server model and open the meeting_request page found there.

  2. Add a table to the content region of your page to contain the interface elements for the application.

    graphics/book.gif From the Snippets panel, drag the Recipes > ConferenceRoom > Wireframes > Meeting Request - Wireframe snippet into the Content editable region.

  3. Within the table, place another HTML table to hold the form and its elements: three text fields (meeting name, owner, and date), one text area for the description, three drop-down lists (start time, duration, and conference room) and a submit button. You'll also need a hidden field used to combine the date and time entries.

    graphics/book.gif ASP and ColdFusion users should place your cursor in the row below the words MEETING REQUEST and insert the Recipes > ConferenceRoom > Forms > Enter Meeting Request Form snippet; PHP users should use the Recipes > ConferenceRoom > Forms > Enter Meeting Request Form (PHP) snippet instead [r3-1].


    Figure r3-1.

    graphics/05fig01.jpg


  4. Make sure the method attribute of the <form> tag is set to POST and save the page.

Recipe Variations: User Interface Enhancement

Depending on your organization, you might find it useful to replace the open text field for meeting owner with a dynamic list tied to an employee data source. A similar technique was demonstrated in a number of the pages for the Employee Lookup application covered in Recipe 2.


Step 2: Add Database Components, Part 1

The meeting request page is fairly involved from a recordset perspective. The three recordsets range from straightforward to advanced in terms of their SQL sophistication. The simplest recordset, Rooms, is used to populate the list of conference rooms. A more advanced recordset is used to obtain the ID of the just inserted record that will be passed to create a confirmation page. The final recordset compares the requested meeting time and place to already-scheduled meetings. If a conflict is found, the user is redirected to a page detailing the existing meeting.

Let's work our way up to the more complex recordsets by starting with the easiest one first, Rooms.

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

  2. In simple view, enter the name of the recordset.

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

  3. Select the data source connection.

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

  4. Choose the table to work with.

    graphics/book.gif Select Rooms (rooms in PHP) from the Table list.

  5. Limit your data source columns to only those that are needed.

    graphics/book.gif Choose the Selected Columns option and highlight RoomID and RoomName.

    It's generally considered good practice to limit your data source fields to those that are necessary, and when one of the unnecessary fields is a memo type field such as RoomDescription that could hold a great deal of text, it's especially important.

  6. Leave the Filter option set to None.

  7. Set the Sort option to present an alphabetical listing of the rooms.

    graphics/book.gif Choose RoomName from the Sort list and accept the Ascending order.

  8. Verify your choices and click OK when you're done.

  9. Save your page.

The next recordset to add involves a useful technique for getting the ID of a just-inserted record for ASP and ColdFusion users; PHP offers a built-in function that handles this for us and we do not need to create the Inserted recordset. During the normal workflow of an insert record operation for these server models, an ID an automatically generated unique number would not be available until the application has successfully inserted the record and moved on to the next page. In this situation, we want to be able to display details of the just-inserted record on that next page, the confirmation page. To accomplish that, we'll need to pass the ID as part of the Insert Record server behavior which we'll get through the recordset we are about to add. A little later in the application set-up, we'll wrap a conditional statement around this recordset so that it is executed only if the Insert Record server behavior has been called.

Note

To accommodate the different dialogs for the various server models, the steps are presented separately here and when necessary throughout this recipe. In this instance, only steps for ASP and ColdFusion are included as the Inserted recordset is not necessary in PHP.


For ASP
  1. From the Server Behaviors 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 Inserted into the Name field.

  3. Choose the connection for the recordset.

    graphics/book.gif Select Recipes from the Connection list.

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

     
     SELECT TOP 1 ConferenceID FROM Conferences WHERE ConferenceName = 'NameParam' ORDER BY ConferenceID DESC 
     

    This SQL statement selects the last entered record of the data that matches the entry in the Meeting Name field. This assures us of retrieving the just inserted ID, even if there are other records with the same meeting name.

  5. In the Variable area, choose Add (+) and enter NameParam under the Name column.

  6. In the Default Value column, enter test.

    The test value is merely a placeholder; any other text string could be used.

  7. In the Run-time Value column, enter Request.Form("MeetingName") and click OK to close the dialog.

  8. Save your page.

For ColdFusion
  1. From the Server Behaviors 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 Inserted in the Name field.

  3. Choose your data source.

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

  4. Enter a user name and password if necessary.

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

     
     SELECT TOP 1 ConferenceID FROM Conferences WHERE ConferenceName = '#FORM.MeetingName#' ORDER BY ConferenceID DESC 
     

    This SQL statement selects the last entered record of the data that matches the entry in the Meeting Name field. This assures us of retrieving the just-inserted ID, even if there are other records with the same meeting name.

  6. In the Page Parameter section, choose Add (+) and, in the Add Parameter dialog, make sure FORM.MeetingName is chosen in the Name list.

  7. Enter test 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.

  9. Save your page.

Step 3: Add Database Components, Part 2

The final recordset needed for this page is required to check for a conflicting meeting. The SQL statement is fairly complex, primarily because a match for the requested place and specified date/time range is being checked; three different variables DateParam, DurationParam, and RoomParam must also be added. Like the Inserted recordset, a conditional statement will be wrapped around this recordset code later to ensure that it is executed only when needed.

For ASP

graphics/book.gif Because this SQL statement is so complex, we've included it as a snippet. However, you can't insert a snippet into a Dreamweaver dialog directly, but you can copy it before opening the Recordset dialog. To copy the SQL code to the clipboard, right-click (Control-click) the Recipes > ConferenceRoom > SQL > Conflict RS - ASP SQL Statement snippet and choose Copy Snippet from the context menu. Now, you're ready to return to the application page and insert the recordset.

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

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


    Figure r3-2.

    graphics/05fig02.jpg


    graphics/book.gif Enter Conflicts into the Name field.

  3. Choose the connection for the recordset.

    graphics/book.gif Select Recipes from the Connection list.

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

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

     

    [View full width]

    SELECT TOP 1 ConferenceID FROM ConferenceDisplay WHERE RoomID = RoomParam AND ((ConferenceStart <= #DateParam# AND ConferenceEnd > graphics/ccc.gif #DateParam#) OR (ConferenceStart < DateAdd('n',DurationParam*60,#DateParam#) AND graphics/ccc.gif ConferenceStart >= #DateParam#))
     

    The pound signs surrounding the DateParam field indicate that this is an Access date format field.

  5. In the Variable area, choose Add (+) and enter DateParam under the Name column.

  6. In the Default Value column, enter 1/1/1975.

  7. In the Run-time Value column, enter Request.Form("FullDate").

  8. Add another variable by choosing Add (+) and enter DurationParam under the Name column.

  9. In the Default Value column, enter 0.

  10. In the Run-time Value column, enter Request.Form("Duration").

  11. Choose Add (+) to insert one more variable and enter RoomParam under the Name column.

  12. In the Default Value column, enter 0.

  13. In the Run-time Value column, enter Request.Form("ConferenceRoom") and click OK to close the dialog.

  14. Save your page.

For ColdFusion

Because the SQL statement for this recordset is quite complex, it is included as a snippet. However, you can't insert a snippet into a Dreamweaver dialog directly, but ColdFusion allows us to insert it directly within a <cfquery> tag pair. To accomplish this, we'll insert the tag from the Insert bar rather than use the Recordset dialog. To complete this ColdFusion recordset, three <cfparam> tags will also have to be added.

  1. From Code view, position your cursor on a new line after the existing <cfparam> tag.

  2. From the CFML category of the Insert bar, choose the cfquery object to open the Tag Editor.

  3. In the General category of the Tag Editor, name the query appropriately.

    graphics/book.gif Enter Conflicts in the Query Name field.

  4. Choose your data source.

    graphics/book.gif Enter Recipes in the Data Source field.

  5. Enter a user name and password if necessary and choose OK to close the dialog.

  6. Make sure your cursor is positioned within the <cfquery> tag pair and enter the following code:

    graphics/book.gif From the Snippets panel, insert the Recipes > ConferenceRoom > SQL > Conflict RS - CFML SQL Statement snippet.

     

    [View full width]

    SELECT TOP 1 ConferenceID FROM ConferenceDisplay WHERE RoomID = #FORM.ConferenceRoom# AND ((ConferenceStart <= ###FORM.FullDate### AND ((ConferenceStart <= #CreateODBCDateTime(FORM.FullDate)# AND ConferenceEnd > graphics/ccc.gif #CreateODBCDateTime(FORM.FullDate)#) OR (ConferenceStart < DateAdd('n',#FORM.Duration#*60, #CreateODBCDateTime(FORM.FullDate)#) AND ConferenceStart >= #CreateODBCDateTime(FORM.FullDate)#))
     

    The CreateODBCDateTime() functions are used to convert the date values to ODBC driver compatible versions.

  7. Switch to Design view and, from the Bindings panel, choose Add (+) and select CFParam.

  8. In the CFParam dialog, enter FORM.FullDate in the Name field.

  9. Enter 1/1/1975 as the Default value.

  10. Choose Date from the Type list and click OK to close the dialog.

  11. From the Bindings panel, choose Add (+) and select CFParam to add another variable.

  12. In the CFParam dialog, enter FORM.Duration in the Name list.

  13. Enter 0 as the Default value.

  14. Choose Numeric from the Type list and click OK to close the dialog.

  15. From the Bindings panel, choose Add (+) and select CFParam to add a final variable.

  16. In the CFParam dialog, enter FORM.ConferenceRoom in the Name list.

  17. Enter 0 as the Default value.

  18. Choose String from the Type list and click OK to close the dialog.

  19. Save your page.

Both the CFParams and the Conflicts recordset should now be visible in the Binding panel.

For PHP

graphics/book.gif Because this SQL statement is so complex, we've included it as a snippet. However, you can't insert a snippet into a Dreamweaver dialog directly, but you can copy it before opening the Recordset dialog. To copy the SQL code to the clipboard, right-click (Control-click) the Recipes > ConferenceRoom > SQL > Conflict RS - PHP SQL Statement snippet and choose Copy Snippet from the context menu.

  1. From the Server Behaviors 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 Conflicts into the Name field.

  3. Choose the connection for the recordset.

    graphics/book.gif Select Recipes from the Connection list.

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

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

     
     SELECT ConferenceID FROM conferences,rooms WHERE conferences.ConferenceRoom = rooms.RoomID AND ConferenceRoom = 'RoomParam' AND (UNIX_TIMESTAMP(ConferenceStart) = 'DateParam' OR UNIX_TIMESTAMP(ConferenceStart) = UNIX_TIMESTAMP(INTERVAL HOUR('DurationParam')HOUR + INTERVAL MINUTE('DurationParam') MINUTE + FROM_UNIXTIME('DateParam'))) 
     
  5. In the Variable area, choose Add (+) and in the Add Parameter dialog enter DateParam under the Name field.

  6. In the Default Value field, enter 157766400, a UNIX timestamp equivalent to January 1, 1975.

  7. In the Run-time Value field, enter $_POST['FullDate'] and click OK to close the Add Parameter dialog.

  8. Add another variable by choosing Add (+) and, in the Add Parameter dialog, enter DurationParam in the Name field.

  9. In the Default Value field, enter 0.

  10. In the Run-time Value field, enter $_POST['Duration'] and click OK to close the Add Parameter dialog.

  11. Add the final variable by choosing Add (+) and, when the Add Parameter dialog opens, enter RoomParam in the Name field.

  12. In the Default Value field, enter 0.

  13. In the Run-time Value field, enter $_POST['ConferenceRoom'] and click OK to close the Add Parameter dialog.

  14. Click OK to close the Recordset dialog and save your page after Dreamweaver inserts the recordset.

A function to convert the FullDate value into a MySQL-friendly format is now needed. We'll insert the necessary code near the top of the page so that it will be executed first.

  1. In Code view, place your cursor after the code for including the connection that starts <?php require_once... and press Enter (Return).

  2. Insert the following code:

    graphics/book.gif From the Snippets panel, insert the Recipes > ConferenceRoom > CustomCode_PHP > Enter Meeting Request - Convert FullDate snippet.

     
     <?php if ((isset($_POST['FullDate']))||(isset($_POST['FullDate']))) {    $_POST['FullDate'] = strtotime($_POST['FullDate']);    $_POST['FullDate'] = strtotime($_POST['FullDate']); } ?> 
     

    Later in the recipe, we'll include a function that will revert the date values into strings so that they may be stored properly in the data source.

  3. Save your page.

Step 4: Data Binding Process

Most of the form elements of the request meeting page do not need to be bound to dynamic data at all. Of the three drop-down lists, only one the Room list has a dynamic connection; the other two lists are populated with static values. One other form element the hidden field that contains the full date value is bound to data, but not in the traditional sense. JavaScript behaviors are used to combine the time and date entries.

Before we add the JavaScript behaviors, let's populate the Rooms list with data. By binding the room data to a form element, we make it easier for the user to choose a location and simultaneously cut down on the error checking.

  1. In Design mode, select the ConferenceRoom list element.

  2. From the Property inspector, click the Dynamic button to open the Dynamic List/Menu dialog.

  3. In the Options from Recordset list, choose Rooms.

  4. In the Values list, select RoomID.

  5. In the Labels list, choose RoomName.

  6. Leave the Select Value Equal to field blank and click OK to close the dialog.

The two other lists are populated with static values. The Start Time list uses the same entries for both labels and values: half-hour increments starting at 7:00 AM and continuing until 7:00 PM. The Duration list, although also comprised of static values, is a little different. The labels are text descriptions of a variety of time lengths from 30 minutes to 5 hours, also in half-hour increments. The corresponding values, however, are given in decimal notation to allow for calculation in the SQL statements. For example, two and one-half hours is represented as 2.5.

To simplify user entry, this page separates the date and time into two form elements, one a text field and the other a list. However, most data sources combine dates and times into one field type. On this page, custom JavaScript code is used to merge the entries from the date text field and the time list into a hidden form element that later will be inserted into a new meeting record. Two JavaScript custom code statements are applied through the Call JavaScript behavior.

graphics/book.gif

Because of the length of the JavaScript involved, the custom code is provided in snippet format. To use the snippet in combination with the Call JavaScript behavior, you need to copy the snippet first by using the Copy Snippet extension installed with the recipe files. The snippet used in both applications of the behavior is found in Recipes > ConferenceRoom > ClientJavaScript > Meeting Request - Populate FullDate Hidden Form Element.


  1. Select the MeetingDate text field.

  2. From the Behaviors panel, choose Add (+) and select Call JavaScript.

  3. In the Call JavaScript dialog, insert the following code:

    graphics/book.gif Paste the clipboard by pressing Ctrl-V (Command-V).

     

    [View full width]

    document.MeetingRequest.FullDate.value = document.MeetingRequest.MeetingDate.value +' '+ graphics/ccc.gif document.MeetingRequest.StartTime.options [document.MeetingRequest.StartTime graphics/ccc.gif.selectedIndex].value
     
  4. Click OK to close the dialog and make sure the event is listed as onBlur in the Behaviors panel [r3-3].


    Figure r3-3.

    graphics/05fig03.jpg


Now, let's apply the same behavior to the StartTime list element.

  1. Select the StartTime list element.

  2. Again, from the Behaviors panel, choose Add (+) and select Call JavaScript.

  3. In the Call JavaScript dialog, insert the same code again:

    graphics/book.gif Paste the clipboard by pressing Ctrl-V (Command-V).

     

    [View full width]

    document.MeetingRequest.FullDate.value = document.MeetingRequest.MeetingDate.value +' '+ graphics/ccc.gif document.MeetingRequest.StartTime.options [document.MeetingRequest.StartTime graphics/ccc.gif.selectedIndex].value
     
  4. Click OK to close the dialog and make sure the event is listed as onChange in the Behaviors panel.

  5. Save the page.

Sorting Lists with Dynamic Dropdowns

Our recipe uses one drop-down list to display all the available conference rooms. This solution works fine for small to medium organizations, but when faced with numerous rooms in multiple buildings, it may be inadequate. Rather than list all the rooms in a single drop-down list, a better way might be to use multiple lists where a choice in one determines what is displayed in another. For example, one list might contain all the buildings in the organization; choose a particular building, and a second list is populated with the rooms available in just that building.

This technique, referred to as dynamic dropdowns, is typically developed with a combination of server- and client-side code. WebAssist offers an extension designed to handle this very situation. WA Dynamic Dropdowns ensures that your customers will find what they're looking for quickly.

For more information, visit http://www.webassist.com/Products/WADynamicDropdowns.asp.


Step 5: Insert Record for Meeting Request

The page is now ready to add the Insert Record server behavior. Although most of the form elements have corresponding data source fields, two are ignored. The values from the MeetingDate text field and StartTime list are combined into the hidden form field, FullDate, and so those two fields are disregarded.

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

  2. Select the desired data source connection.

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

  3. Choose the table in the data source that will receive the form values.

    graphics/book.gif From the Insert Into Table list, choose Conferences.

  4. Leave the After Inserting, Go To field empty.

    In this application, the redirection will be hand-coded; the page chosen depends on whether a conflicting meeting is found or not.

  5. Choose the form on the page from which to get the values.

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

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

    graphics/book.gif Set the MeetingName form element to the ConferenceName data source field as Text type.

    Set the MeetingOwner form element to the ConferenceBy data source field as Text type.

    Set the MeetingDescription form element to the ConferenceDescription data source field as Text type.

    Leave both the MeetingDate and StartTime form elements set to <ignore>.

    Set the FullDate form element to the ConferenceStart data source field as Date MS Access type.

    graphics/book.gif Set the Duration form element to the ConferenceDuration data source field as Numeric.

    Set the ConferenceRoom form element to the ConferenceRoom data source field as Numeric.

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

  8. Save the page.

For ColdFusion and PHP

After completing the Insert Record server behavior, PHP users will also need to add the date-to-string conversion function mentioned earlier.

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

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

    graphics/book.gif Select MeetingRequest 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 user name and password, if needed.

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

    graphics/book.gif Choose Conferences (conferences in PHP) from the Insert Into Table list.

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

    graphics/book.gif Make sure the ConferenceID data column is set to be an unused Primary Key.

    Set ConferenceRoom to the FORM.ConferenceRoom form element and submit as Numeric type in ColdFusion and Integer type in PHP.

    Set ConferenceName to the FORM.MeetingName form element and submit as Text type.

    Set ConferenceDescription to the FORM.MeetingDescription form element and submit as Text type.

    Set ConferenceStart to the FORM.FullDate form element and submit as Date MS Access type for ColdFusion and Date type for PHP.

    Set ConferenceDuration to the FORM.Duration form element and submit as Numeric type in ColdFusion and Integer type in PHP.

    Set ConferenceBy to the FORM.MeetingOwner form element and submit as Text type.

  7. Leave the After Inserting, Go to field blank and click OK to close the dialog.

  8. Save the page.

The following steps are for PHP users only:

  1. From the Server Behaviors panel, select the Insert Record server behavior just added.

  2. In Code view, you'll see the Insert Record code highlighted; move your cursor above this code block.

  3. Insert the following code:

    graphics/book.gif From the Snippets panel, insert the Recipes > ConferenceRoom > CustomCode_PHP > Enter Meeting Request - Reconvert FullDate snippet.

     
     <?php if (($_POST['FullDate']!="")||($_POST['FullDate']!="")) {    $_POST['FullDate'] = date("YmdHis",$_POST['FullDate']); $_POST['FullDate'] = date("YmdHis",$_POST['FullDate']); } ?> 
     
  4. Save your page.

Step 6: Add Server-Side Date Validation

To make sure the date entered by the user is in the proper format, this page employs server-side date validation. If an improper date is found, an error message appears on the page above the form. This is accomplished by having the page submit to itself with an argument indicating an error. Additional custom code is required to display the error message when such an argument is present.

The server-side date validation then is comprised of two parts: one code block to redirect the page with an argument and another to present a message. We'll insert the redirection code first.

Note

Both VBScript and CFML include functions for identifying a properly formatted date; however JavaScript and PHP do not. Those developers programming in ASP-JavaScript and PHP will need to include two code blocks one is a custom function to verify the date format and the other to call the function while developers using ASP-VBScript or ColdFusion will only require one custom code block.


  1. In Code view, place the cursor at the top of the page.

    ASP users should position the cursor after the <!-- include> statement.

  2. ASP-JavaScript users only: insert the following code:

    graphics/book.gif From the Snippets panel, insert the Recipes > ConferenceRoom CustomCode_JS > IsADate Function snippet.

    graphics/js.gif

    [View full width]

     <% function isADate(DateString)  {   var DateRegex = /^(\d{1,2})\/(\d{1,2})\/(\d{4})$/   var theMatch = DateString.match(DateRegex);   var validDate = false;   if (theMatch)  {     var DateObj = new Date(DateString);   if ((DateObj.getMonth()+1 == parseInt(theMatch[1])) &&  graphics/ccc.gif(DateObj.getDate() == parseInt(theMatch[2])) && (DateObj graphics/ccc.gif.getFullYear() == parseInt(theMatch[3])))       validDate = true;   }   return validDate; } %> 

  3. PHP users only: insert the following code:

    graphics/book.gif From the Snippets panel, insert the Recipes > ConferenceRoom > Custom Code_PHP > IsADate Function snippet.

    graphics/php.gif

     <?php function isADate($DateString) {   $validDate = false;   if (ereg("^[0-9]{1,2}\/[0-9]{1,2}\/[0-9]{4}$",$DateString)) {     $today = date("Y");     $submittedDate = explode("/",$DateString);     $month = $submittedDate[0];     $day = $submittedDate[1];     $year = $submittedDate[2];     if ($year >= $today) {       $validDate = (checkdate($month,$day,$year))?true:false;     }   }   return $validDate; } ?> 

  4. For all server models, insert the following code at the current cursor position (after the last code inserted for ASP-JS and PHP and at the top of the page for ASP-VB and ColdFusion):

    graphics/book.gif From the Snippets panel, open the Recipes > ConferenceRoom > Custom Code folder for your server model and insert the Meeting Request - Date Validation snippet.

    graphics/vb.gif

    [View full width]

     <% if (cStr(Request.Form("FullDate")) <> "")  then   if not (isDate(Request.Form("FullDate"))) then Response graphics/ccc.gif.Redirect("meeting_request.asp?badDate=true") end if %> 

    graphics/js.gif

    [View full width]

     <% if (String(Request.Form("MeetingDate"))!="undefined" && graphics/ccc.gif !isADate(String(Request.Form("MeetingDate"))))   Response.Redirect("meeting_request.asp?badDate=true") %> 

    graphics/cf.gif

     <cfif IsDefined("FORM.MeetingDate")>   <cfif not IsDate(FORM.MeetingDate)>     <cflocation url="meeting_request.cfm?badDate=true">   </cfif> </cfif> 

    graphics/php.gif

    [View full width]

     <?php if ((isset($_POST['MeetingDate'])) && (!isADate graphics/ccc.gif($_POST['MeetingDate']))) {   $url = "meeting_request.php?badDate=true";   header("Location: $url"); } ?> 

    Now that our validation routine is in place, we need to insert the error message that will be displayed if the URL variable baddate is present.

  5. In Design view, place your cursor where you want the error message to appear.

    graphics/book.gif Position the cursor in the table row above the Enter Meeting Request text.

  6. Insert the following code:

    graphics/book.gif From the Snippets panel, open the Recipes > ConferenceRoom > Custom Code folder for your server model and insert the Date Error - Dynamic Text snippet.

    graphics/vb.gif

     <% if (cStr(Request.QueryString("badDate")) <> "") Then Response.Write("The date you entered was not in the proper format.  Dates should be in the format mm/dd/yyyy.%> 

    graphics/js.gif

    [View full width]

     <%=(String(Request.QueryString("badDate")) != graphics/ccc.gif "undefined")?"The date you entered was not in the proper format.  Dates should be graphics/ccc.gif in the format mm/dd/yyyy.":""%> 

    graphics/cf.gif

    [View full width]

     <cfif isDefined("URL.badDate")>The date you entered was not graphics/ccc.gif in the proper format.  Dates should be in the format mm/dd/yyyy.</cfif> 

    graphics/php.gif

     <?php if (isset($_GET['badDate'])) {   echo "The date you entered was      not in the proper format.   Dates should be in the format      mm/dd/yyyy."; } ?> 

Note

Don't try to test the page at this point. The Conflicts recordset needs to be moved above the other recordsets and modified so that it is accessed only at the appropriate time.


Step 7: Hand Code Edits to Handle Meeting Conflicts

To get the Conflicts recordset fully operational, we're going to have to perform four operations:

  • Move the Conflicts recordset and its three parameter declarations above the Insert Record server behavior code.

  • Add a redirect statement to the end of the Conflicts recordset if a conflict is found.

  • Wrap all Conflicts recordset-associated code in a conditional statement so that it is executed only if the Insert Record operation is attempted.

  • Wrap the code that closes the Conflicts recordset in a similar conditional statement.

Let's get started by moving the Conflicts recordset code blocks.

  1. From the Server Behaviors panel, select the Conflicts recordset.

    Selecting the recordset in the Server Behaviors panel highlights a portion of the relevant code.

  2. In Code view, select the highlighted recordset and the three SQL parameter code blocks above the recordset code and cut it. The code to select is:

    graphics/vb.gif

    [View full width]

     <% Dim Conflicts__DateParam Conflicts__DateParam = "1/1/1975" If (Request.Form("FullDate") <> "") Then   Conflicts__DateParam = Request.Form("FullDate") End If %> <% Dim Conflicts__DurationParam Conflicts__DurationParam = "0" If (Request.Form("Duration") <> "") Then   Conflicts__DurationParam = Request.Form("Duration") End If %> <% Dim Conflicts__RoomParam Conflicts__RoomParam = "test" If (Request.Form("ConferenceRoom") <> "") Then   Conflicts__RoomParam = Request.Form("ConferenceRoom") End If %> <% Dim Conflicts Dim Conflicts_numRows Set Conflicts = Server.CreateObject("ADODB.Recordset") Conflicts.ActiveConnection = MM_Recipes_STRING Conflicts.Source = "SELECT TOP 1 ConferenceID FROM graphics/ccc.gif ConferenceDisplay WHERE (ConferenceStart <= #" + Replace graphics/ccc.gif(Conflicts__DateParam, "'", "''") + "# AND ConferenceEnd > #" graphics/ccc.gif + Replace(Conflicts__DateParam, "'", "''") + "#) OR  graphics/ccc.gif(ConferenceStart < DateAdd('n'," + Replace graphics/ccc.gif(Conflicts__DurationParam, "'", "''") + "*60,#" + Replace graphics/ccc.gif(Conflicts__DateParam, "'", "''") + "#) AND ConferenceStart graphics/ccc.gif >= #" + Replace(Conflicts__DateParam, "'", "''") + "#)" Conflicts.CursorType = 0 Conflicts.CursorLocation = 2 Conflicts.LockType = 1 Conflicts.Open() Conflicts_numRows = 0 %> 

    graphics/js.gif

    [View full width]

     <% var Conflicts__DateParam = "1/1/1975"; if (String(Request.Form("FullDate")) != "undefined" &&     String(Request.Form("FullDate")) != "") {   Conflicts__DateParam = String(Request.Form("FullDate")); } %> <% var Conflicts__DurationParam = "0"; if (String(Request.Form("Duration")) != "undefined" &&     String(Request.Form("Duration")) != "") {   Conflicts__DurationParam = String(Request.Form("Duration")); } %> <% var Conflicts__RoomParam = "0"; if (String(Request.Form("ConferenceRoom")) != "undefined" &&     String(Request.Form("ConferenceRoom")) != "") {   Conflicts__RoomParam = String(Request.Form("ConferenceRoom")); } %> <% var Conflicts = Server.CreateObject("ADODB.Recordset"); Conflicts.ActiveConnection = MM_Recipes_STRING; Conflicts.Source = "SELECT TOP 1 ConferenceID FROM graphics/ccc.gif ConferenceDisplay WHERE (ConferenceStart <= #"+ graphics/ccc.gif Conflicts__DateParam.replace(/'/g, "''") +"# AND graphics/ccc.gif ConferenceEnd > #"+ Conflicts__DateParam.replace(/'/g, "''") graphics/ccc.gif + "#) OR (ConferenceStart < DateAdd('n',"+ graphics/ccc.gif Conflicts__DurationParam.replace(/'/g, "''") + "*60,#"+ graphics/ccc.gif Conflicts__DateParam.replace(/'/g, "''") + "#) AND graphics/ccc.gif ConferenceStart >= #"+ Conflicts__DateParam.replace(/'/g, graphics/ccc.gif "''") + "#)"; Conflicts.CursorType = 0; Conflicts.CursorLocation = 2; Conflicts.LockType = 1; Conflicts.Open(); var Conflicts_numRows = 0; %> 

    graphics/cf.gif

    [View full width]

     <cfquery name="Conflicts" datasource="Recipes"> SELECT TOP 1 ConferenceID FROM ConferenceDisplay WHERE (ConferenceStart <= #CreateODBCDateTime(FORM.FullDate)# AND ConferenceEnd > #CreateODBCDateTime(FORM.FullDate)#) OR  graphics/ccc.gif(ConferenceStart < DateAdd('n',#FORM.Duration#*60 graphics/ccc.gif,#CreateODBCDateTime(FORM.FullDate)#) AND ConferenceStart >= graphics/ccc.gif #CreateODBCDateTime(FORM.FullDate)#) </cfquery> 

    graphics/php.gif

    [View full width]

     $DateParam_Conflicts = "157766400"; if (isset($_POST['FullDate'])) {   $DateParam_Conflicts = (get_magic_quotes_gpc()) ? graphics/ccc.gif $_POST['FullDate'] : addslashes($_POST['FullDate']); } $DurationParam_Conflicts = "0"; if (isset($_POST['Duration'])) {   $DurationParam_Conflicts = (get_magic_quotes_gpc()) ? graphics/ccc.gif $_POST['Duration'] : addslashes($_POST['Duration']); } $RoomParam_Conflicts = "0"; if (isset($_POST['ConferenceRoom'])) {   $RoomParam_Conflicts = (get_magic_quotes_gpc()) ? graphics/ccc.gif $_POST['ConferenceRoom'] : addslashes($_POST['ConferenceRoom']); } mysql_select_db($database_Recipes_PHP, $Recipes_PHP); $query_Conflicts = sprintf("SELECT ConferenceStart FROM graphics/ccc.gif conferences,rooms WHERE conferences.ConferenceRoom = rooms graphics/ccc.gif.RoomID AND ConferenceRoom = '%s' AND  (UNIX_TIMESTAMP graphics/ccc.gif(ConferenceStart) = '%s' OR UNIX_TIMESTAMP(ConferenceStart) = graphics/ccc.gif UNIX_TIMESTAMP (INTERVAL HOUR('%s')HOUR +  INTERVAL MINUTE(' graphics/ccc.gif%s') MINUTE + FROM_UNIXTIME('%s')))", $RoomParam_Conflicts graphics/ccc.gif,$DateParam_Conflicts, $DurationParam_Conflicts graphics/ccc.gif,$DurationParam_Conflicts, $DateParam_Conflicts); $Conflicts = mysql_query($query_Conflicts, $Recipes_PHP) or graphics/ccc.gif die(mysql_error()); $row_Conflicts = mysql_fetch_assoc($Conflicts); $totalRows_Conflicts = mysql_num_rows($Conflicts); 

    Note

    Because ColdFusion routinely places the SQL parameters (in the <cfparam> tags) at the top of the page, they do not need to be moved.

  3. Place the cursor on a new line after the date validation code inserted in the previous step near the top of the page and paste in the just-cut Conflicts recordset code.

  4. PHP Users only: Wrap PHP delimeters around the just-inserted code block.

    Before:

    graphics/php.gif

     <?php 

    After:

     
     ?> 
     

Note

Note that the selection does not include the <?php ?> delimiters, nor the Rooms recordset. Dreamweaver merges the code for the Conflicts recordsetwith that of the other recordsets. Therefore, after placing this portion of the Conflicts recordset elsewhere on the page, new delimiters must be manually added to it.


Now, we're ready to add a bit of code that checks the Conflicts recordset and, if at least one record is found, redirects the user to the meeting_conflict page. This redirection code is placed before the Insert Server Behavior to avoid adding conflicting meetings into the data source.

  1. In Code view, place your cursor on a new line after the just-moved Conflicts recordset.

  2. Insert the following code:

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

    graphics/vb.gif

    [View full width]

     <% if (Not Conflicts.EOF) Then Response.Redirect ("meeting_conflict.asp?ConferenceID") graphics/ccc.gif.value); %> 

    graphics/js.gif

    [View full width]

     <% if (!Conflicts.EOF) Then Response.Redirect ("meeting_conflict.asp?ConferenceID") graphics/ccc.gif.value); %> 

    graphics/cf.gif

    [View full width]

     <cfif Conflicts.RecordCount NEQ 0>   <cflocation url="meeting_conflict.cfm?ID=#Conflicts graphics/ccc.gif.ConferenceID#"> </cfif> 

    graphics/php.gif

    [View full width]

     <?php if ($totalRows_Conflicts > 0){   $url = "meeting_conflict.php?0" width="14" height="9" align="left" src="/books/2/711/1/html/2/files/ccc.gif" alt="graphics/ccc.gif">.$row_Conflicts['ConferenceID'];   header("Location: $url");   exit(); } ?> 

The third phase of adjusting the Conflicts recordset is to make it conditional. To do this, we'll need to wrap the Conflicts recordset and the parameters and redirection code just inserted into an If type statement. With this conditional code in place, the Conflicts-associated code is only executed after the Insert Record server behavior has been called.

  1. In Code view, highlight all the contiguous Conflicts-related code.

    graphics/cf.gif In ColdFusion, you don't need to select the <cfparam> tags, just the <cfquery> and the <cfif> Conflicts code blocks.

  2. Wrap the following code around the selection:

    graphics/book.gif From the Snippets panel, open the Recipes > ConferenceRoom > Custom Code folder for your server model and insert the If Statement - Only on Insert snippet.

    Before:

    graphics/vb.gif

     <% If (CStr(Request("MM_insert")) = "MeetingRequest") Then %> 

    After:

     
     <% end if %> 
     

    Before:

    graphics/js.gif

     <% if (String(Request("MM_insert")) == "MeetingRequest") { %> 

    After:

     
     <% } %> 
     

    Before:

    graphics/cf.gif

     <cfif IsDefined("FORM.MM_InsertRecord")> 

    After:

     
     </cfif> 
     

    Before:

    graphics/php.gif

     <?php if (isset($_POST['MM_insert'])) { ?> 

    After:

     
     <?php } ?> 
     
  3. Save the page.

The final action involving the Conflicts recordset is to apply the same conditional statement to the code block that explictly closes the recordset, found at the bottom of the page. This is an ASP and PHP-only step.

For ASP
  1. In Code view, move to the bottom of the page and select the code block that closes the Conflicts recordset. The code will look like this:

    graphics/vb.gif

     <% Conflicts.Close() Set Conflicts = Nothing %> 

    graphics/js.gif

     <% Conflicts.Close(); %> 

  2. Wrap the following code around the selection:

    graphics/book.gif From the Snippets panel, open the Recipes > ConferenceRoom > Custom Code folder for your server model and insert the If Statement - Only on Insert snippet.

    Before:

    graphics/vb.gif

     <% If (CStr(Request("MM_insert")) = "MeetingRequest") Then %> 

    After:

     
     <% end if %> 
     

    Before:

    graphics/js.gif

     <% if (String(Request("MM_insert")) == "MeetingRequest") { %> 

    After:

     
     <% } %> 
     
  3. Save your page.

For PHP

PHP users first will need to separate two merged code blocks and then make the second of the two conditional.

  1. At the bottom of the page, locate the code block that begins <?php mysql_free_result($Rooms);. The complete code block looks like this:

    graphics/php.gif

     <?php mysql_free_result($Rooms); mysql_free_result($Conflicts); ?> 

  2. This code block represents the closing of the two recordsets. Because we want to make one of those recordsets, Conflicts, conditional, you must separate the single block into two, like this:

    graphics/php.gif

     <?php mysql_free_result($Rooms); ?> <?php mysql_free_result($Conflicts); ?> 

  3. Select the second of the two blocks.

  4. Now wrap conditional code around the second code block, which clears the Conflicts recordset.

    graphics/book.gif From the Snippets panel, insert the Recipes > ConferenceRoom > CustomCode_PHP > If Statement Only on Insert snippet.

    Before:

    graphics/php.gif

     <?php if (isset($_POST['MM_update'])) { ?> 

    After:

     
     <?php } ?> 
     
  5. Save your page.

Step 8: Hand Code If Statements for Inserted Recordset

The final step to complete this page is to make the Inserted recordset conditional, as we did with the Conflicts recordset. However, the Inserted recordset does not need to be moved. In this instance, the conditional code is combined with redirection code that, after a successful record insertion, redirects the user to a confirmation page. The redirection code passes the ID of the new record through a URL parameter. PHP users will be able to take advantage of some built-in functionality and only need add a single function after the record insert code. Finally, ASP users will also need to make the code section that expressly closes the Inserted recordset conditional.

For ASP and ColdFusion
  1. From the Server Behaviors panel, select the Inserted recordset.

    Selecting the recordset in the Server Behaviors panel highlights a portion of the relevant code.

    ASP users will also need to select the code block defining the SQL parameter, NameParam.

  2. In Code view, select the following code:

    graphics/vb.gif

    [View full width]

     <% Dim Inserted__NameParam Inserted__NameParam = "test" If (Request.Form("MeetingName") <> "") Then   Inserted__NameParam = Request.Form("MeetingName") End If %> <% Dim Inserted Dim Inserted_numRows Set Inserted = Server.CreateObject("ADODB.Recordset") Inserted.ActiveConnection = MM_Recipes_STRING Inserted.Source = "SELECT TOP 1 ConferenceID FROM graphics/ccc.gif Conferences WHERE ConferenceName = '" + Replace graphics/ccc.gif(Inserted__NameParam, "'", "''") + "'  ORDER BY ConferenceID graphics/ccc.gif DESC" Inserted.CursorType = 0 Inserted.CursorLocation = 2 Inserted.LockType = 1 Inserted.Open() Inserted_numRows = 0 %> 

    graphics/js.gif

    [View full width]

     <% var Inserted__NameParam = "test"; if (String(Request.Form("MeetingName") ) != "undefined" &&     String(Request.Form("MeetingName") ) != "") {   Inserted__NameParam = String(Request.Form("MeetingName") ); } %> <% var Inserted = Server.CreateObject("ADODB.Recordset"); Inserted.ActiveConnection = MM_Recipes_STRING; Inserted.Source = "SELECT TOP 1 ConferenceID FROM graphics/ccc.gif Conferences WHERE ConferenceName = '"+ Inserted__NameParam graphics/ccc.gif.replace(/'/g, "''") + "'  ORDER BY ConferenceID DESC  "; Inserted.CursorType = 0; Inserted.CursorLocation = 2; Inserted.LockType = 1; Inserted.Open(); var Inserted_numRows = 0; %> 

    graphics/cf.gif

    [View full width]

     <cfquery name="Inserted" datasource="Recipes"> SELECT TOP 1 ConferenceID FROM Conferences WHERE graphics/ccc.gif ConferenceName = '#FORM.MeetingName#' ORDER BY ConferenceID DESC </cfquery> 

  3. Wrap the following code around the selection:

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

    Before:

    graphics/vb.gif

     <% If (CStr(Request("MM_insert")) = "MeetingRequest") Then %> 

    After:

     
     <% if (NOT Inserted.EOF) Then Response.Redirect("meeting_details.asp?ConferenceID").value) end if %> <% end if %> 
     

    Before:

    graphics/js.gif

     <% if (String(Request("MM_insert")) == "MeetingRequest") { %> 

    After:

     
     <% Response.Redirect("meeting_details.asp?ConferenceID").value); } %> 
     

    Before:

    graphics/cf.gif

    [View full width]

     <cfif IsDefined("FORM.MM_InsertRecord") AND FORM graphics/ccc.gif.MM_InsertRecord EQ "MeetingRequest"> 

    After:

     
     <cflocation url="meeting_details.cfm?ID=#Inserted.ConferenceID#"> </cfif> 
     

The final action for this page is to apply a conditional statement to the code block that explictly closes the Inserted recordset, found at the bottom of the page, so that it will be executed only if the record is inserted. Please note that this is a ASP-only step.

  1. In Code view, move to the bottom of the page and select the code block that closes the Conflicts recordset. The code will look like this:

    graphics/vb.gif

     <% Inserted.Close() Set Inserted = Nothing %> 

    graphics/js.gif

     <% Inserted.Close(); %> 

  2. Wrap the following code around the selection:

    graphics/book.gif From the Snippets panel, open the Recipes > ConferenceRoom > Custom Code folder for your server model and insert the If Statement - Only on Insert snippet.

    Before:

    graphics/vb.gif

     <% If (CStr(Request("MM_insert")) = "MeetingRequest") Then %> 

    After:

     
     <% end if %> 
     

    Before:

    graphics/js.gif

     <% if (String(Request("MM_insert")) == "MeetingRequest") { %> 

    After:

     
     <% } %> 
     
  3. Save your page.

To make sure that the date validation is working, enter a partial date such as month or day when testing in a browser. You can test the error message handling in Live Data view by entering badDate=true in the URL parameter field of the Live Data toolbar. Note that no question mark is necessary because the form uses the POST method rather than GET [r3-4].


Figure r3-4.

graphics/05fig04.jpg


Note

ASP-VBScript and ColdFusion developers will notice that the year is optional; this is a result of the Access function that considers any date with just the day and month to be in the current year.


For PHP
  1. From the Server Behaviors panel, select the Insert Record command.

  2. In Code view, position your cursor after the selected code block.

  3. Insert the following code:

    graphics/book.gif From the Snippets panel, insert the Recipes > ConferenceRoomScheduler > CustomCode_PHP > Redirect After Insert snippet.

    graphics/php.gif

     <?php if (isset($_POST['MM_insert'])) {   $url = "meeting_details.php?Location: $url"); } ?> 

  4. 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