| The entry point to a conference room scheduling application is the meeting request form. From the user's perspective, the form is fairly straightforward: First, enter a few descriptive detailsthe meeting's name, owner, and descriptionand then input the time, date, duration and meeting location. When the request is submitted, however, all pretense of simplicity fades away. The request is first compared with existing meetings to check for conflicting times and places. 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 sourcefriendly 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.
 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.
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, which will be used to combine the date and time entries.
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].
r3-1. 
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 the application covered in Recipe 2, "Employee Lookup." |
| 4. | Make sure the method attribute of the <form> tag is set to POST and save the page.
|
Step 2: Add Database Components, Part 1 From a recordset perspective, the meeting request page is fairly involved. 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's time and place with 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.
Enter Rooms in the Name field.
| 3. | Select the data source connection.
Choose Recipes from the Connection (Data Source) list.
| 4. | Choose the table to work with.
Select Rooms (rooms in PHP) from the Table list.
| 5. | Limit your data source columns to only the needed ones.
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. When one of the fields is a memo type field that could hold a great deal of text, such as RoomDescription, it's especially important to keep the number of data source fields to a minimum.
| 6. | Leave the Filter option set to None.
| 7. | Set the Sort option to present an alphabetical listing of the rooms.
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; because PHP offers a built-in function that handles this for us, we do not need to create the Inserted recordset. During the normal workflow of an insert record operation for these server models, an IDan automatically generated unique numberwould not be available until the application had 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 behaviorwhich we'll get through the recordset we are about to add. A little later in the application setup, 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, these steps are presented for specific servers here and when necessary throughout this recipe. In this instance, only steps for ASP and ColdFusion are included, as the Inserted recordset is not needed 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.
Enter Inserted in the Name field.
| 3. | Choose the connection for the recordset.
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.
Enter Inserted in the Name field.
| 3. | Choose your data source.
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 checks 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 variablesDateParam, DurationParam, and RoomParammust 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 Because this SQL statement is so complex, we've included it as a snippet. Although you can't insert a snippet into a Dreamweaver dialog directly, you can copy it before opening the Recordset dialog. To copy the SQL code to the clipboard, first place your cursor in a text string in Design view. Then, 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].
r3-2. 
Enter Conflicts in the Name field.
| 3. | Choose the connection for the recordset.
Select Recipes from the Connection list.
| 4. | In the SQL area, enter the following code:
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 >  #DateParam#) OR (ConferenceStart < DateAdd('n',DurationParam*60,#DateParam#) AND ConferenceStart >= #DateParam#))
The number 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. Although you can't insert a snippet into a Dreamweaver dialog directly, ColdFusion lets you 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.
Enter Conflicts in the Query Name field.
| 4. | Choose your data source.
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:
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 > #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 drivercompatible 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 Bindings panel. For PHP Because this SQL statement is so complex, we've included it as a snippet. Although you can't insert a snippet into a Dreamweaver dialog directly, you can copy it before opening the Recordset dialog. To copy the SQL code to the clipboard, first place your cursor in a text string in Design view. Then, 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.
Enter Conflicts in the Name field.
| 3. | Choose the connection for the recordset.
Select Recipes from the Connection list.
| 4. | In the SQL area, enter the following code:
Press Ctrl-V (Command-V) to paste in the copied snippet.
[View full width] 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 in the Name field.
| 6. | In the Default Value field, enter 157766400, a Unix time stamp 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.
|
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 onethe Room listhas a dynamic connection; the other two are populated with static values. One other form elementthe hidden field that contains the full date valueis 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 ConferenceRoom 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 a.m. and continuing until 7 p.m. The Duration list, although also composed 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 in decimal notation to allow for calculation in the SQL statements. For example, two and a 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 merges 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. Because of the length of the JavaScript involved, the custom code is provided in snippet format. To copy the SQL code to the clipboard, first place your cursor in a text string in Design view. Then, right-click (Control-click) the Recipes > ConferenceRoom > ClientJavaScript > Meeting Request - Populate FullDate Hidden Form Element snippet and choose Copy Snippet from the context menu. 1. | Select the MeetingDate text field.
| 2. | From the Behaviors panel, choose Add (+) and select Call JavaScript.
Make sure you're selecting from the Behaviors panel and not the Server Behaviors panel.
| 3. | In the Call JavaScript dialog, insert the following code:
Paste from the clipboard by pressing Ctrl-V (Command-V).
[View full width] document.MeetingRequest.FullDate.value = document.MeetingRequest.MeetingDate.value +' '+ document.MeetingRequest.StartTime.options [document.MeetingRequest.StartTime .selectedIndex].value
| 4. | Click OK to close the dialog and make sure the event is listed as onBlur in the Behaviors panel [r3-3].
r3-3. 
|
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:
Paste from the clipboard by pressing Ctrl-V (Command-V).
[View full width] document.MeetingRequest.FullDate.value = document.MeetingRequest.MeetingDate.value +' '+ document.MeetingRequest.StartTime.options[document.MeetingRequest. StartTime .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.
|
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. Sorting Lists with Dynamic Drop-Downs 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 a user is 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 in which 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 drop-downs, 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 www.webassist.com/go/Recipes. |
For ASP 1. | From the Server Behaviors panel, choose Add (+) and select Insert Record.
| 2. | Select the data source connection.
Choose Recipes from the Connection list.
| 3. | Choose the table in the data source that will receive the form values.
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.
| 5. | Choose the form on the page from which to get the values.
From the Get Values From list, choose MeetingRequest.
| 6. | In the Form Elements area, set the form elements to their corresponding data source fields.
| 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. | | | 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 1. | From the Server Behaviors panel, choose Add (+) and select Insert Record.
| 2. | In the Insert Record dialog, choose the current form.
Select MeetingRequest from the Submit Values From list.
| 3. | Select your data source from the list.
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.
Choose Conferences (conferences in PHP) from the Insert Into Table list.
| 6. | Set the data source fields to their corresponding form elements.
| 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 Text type in PHP. | | | Set ConferenceBy to the FORM.MeetingOwner form element and submit asText type. |
In PHP, be sure to submit ConferenceDuration as Text type; the colon in the time value (e.g., 00:30) is not acceptable in MySQL.
| 7. | Leave the After Inserting, Go To field blank, and click OK to close the dialog.
| 8. | Save the page.
|
Step 6: Add Server-Side Date Validation To make sure the date the user enters is in the proper format, this page employs serverside 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 is composed 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. For ASP-JS and PHP Both VBScript and CFML include functions for identifying a properly formatted date; JavaScript and PHP do not, however. Developers programming in ASP-JavaScript and PHP will need to include two code blocksone a custom function to verify the date format and the other to call the functionwhile developers using ASP-VBScript or ColdFusion will require only one custom code block 1. | In Code view, place the cursor at the top of the page.
ASP-JavaScript users should position the cursor after the <!-- include> statement. PHP users should position the cursor after the <?php require_once> statement.
| 2. | Insert the following code (ASP-JavaScript users only):
From the Snippets panel, insert the Recipes > ConferenceRoom CustomCode_JS > IsADate Function snippet.
| [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])) && (DateObj.getDate() == parseInt (theMatch[2])) && (DateObj.getFullYear() == parseInt(theMatch[3]))) validDate = true; } return validDate; } %>
|
| 3. | Insert the following code (PHP users only):
From the Snippets panel, insert the Recipes > ConferenceRoom > Custom Code_PHP > IsADate Function snippet.
| [View full width] <?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; } ?>
|
|
For All Server Models 1. | 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):
From the Snippets panel, open the Recipes > ConferenceRoom > Custom Code folder for your server model and insert the Meeting Request - Date Validation snippet.
| [View full width] <% if (cStr(Request.Form("FullDate")) <> "") then if not (isDate(Request.Form("FullDate")) AND Trim (cStr(Request("MeetingDate"))) <> "") then Response.Redirect("meeting_request.asp?badDate=true") end if %>
|
| [View full width] <% if (String(Request.Form ("MeetingDate"))!="undefined" && !isADate(String (Request.Form("MeetingDate")))) Response.Redirect("meeting_request.asp?badDate=true") %>
|
| <cfif IsDefined("FORM.MeetingDate")> <cfif not IsDate(FORM.MeetingDate)> <cflocation url="meeting_request.cfm?badDate=true"> </cfif> </cfif>
|
| [View full width] <?php if ((isset($_POST['MeetingDate'])) && (!isADate ($_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.
| 2. | In Design view, place your cursor where you want the error message to appear.
Position the cursor in the table row above the Enter Meeting Request text.
| 3. | Insert the following code:
From the Snippets panel, open the Recipes > ConferenceRoom > Custom Code folder for your server model and insert the Date Error - Dynamic Text snippet.
| [View full width] <% 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.%>
|
| [View full width] <%=(String(Request.QueryString("badDate")) != "undefined")?"The date you entered was not in the proper format. Dates should be in the format mm/dd /yyyy.":""%>
|
| [View full width] <cfif isDefined("URL.badDate")>The date you entered was not in the proper format. Dates should be in the format mm/dd/yyyy.</cfif>
|
| [View full width] <?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."; } ?>
|
| 4. | Save your page.
|
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: 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.
Move the Conflicts recordset and its three parameter declarations above the Insert Record server behavior code. All server models except ColdFusion need this step. Add a redirect statement to the end of the Conflicts recordset if a conflict is found. Wrap all Conflicts recordsetassociated 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:
| [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 ConferenceDisplay WHERE (ConferenceStart <= #" + Replace (Conflicts__DateParam, "'", "''") + "# AND ConferenceEnd > #" + Replace(Conflicts__DateParam, "'", "''") + "#) OR (ConferenceStart < DateAdd('n' ," + Replace(Conflicts__DurationParam, "'", "''") + "*60,#" + Replace(Conflicts__DateParam, "'", "''") + "#) AND ConferenceStart >= #" + Replace (Conflicts__DateParam, "'", "''") + "#)" Conflicts.CursorType = 0 Conflicts.CursorLocation = 2 Conflicts.LockType = 1 Conflicts.Open() Conflicts_numRows = 0 %>
|
| 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: | | | <?php
|
| After: | | | ?>
|
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 Record server behavior code to avoid adding conflicting meetings into the data source. Note Notice that the selection does not include the <?php ?> delimiters or the Rooms recordset. Dreamweaver merges the code for the Conflicts recordset with that of the other recordsets. Therefore, after placing this portion of the Conflicts recordset elsewhere on the page, new delimiters must be added to it manually.
1. | In Code view, place your cursor on a new line after the just-moved Conflicts recordset.
ColdFusion users can identify the Conflicts recordset code by selecting its entry from the Server Behaviors panel and switching to Code view.
| 2. | Insert the following code:
From the Snippets panel, open the Recipes > ConferenceRoom > Custom Code folder for your server model and insert the Meeting Conflict Redirect snippet.
| [View full width] <% if (Not Conflicts.EOF) Then Response.Redirect ("meeting_conflict.asp?0" width="14" height="9" align="left" src="/books/2/710/1/html/2/images/ccc.gif" />("ConferenceID").value); %>
|
| [View full width] <% if (!Conflicts.EOF) Then Response.Redirect ("meeting_conflict.asp?0" width="14" height="9" align="left" src="/books/2/710/1/html/2/images/ccc.gif" />("ConferenceID").value); %>
|
| <cfif Conflicts.RecordCount NEQ 0> <cflocation url="meeting_conflict.cfm?ID= #Conflicts.ConferenceID#"> </cfif>
|
| [View full width] <?php if ($totalRows_Conflicts > 0){ $url = "meeting_conflict.php?0" width="14" height="9" align="left" src="/books/2/710/1/html/2/images/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 executed only after the Insert Record server behavior has been called. 1. | In Code view, highlight all the contiguous Conflicts-related code.
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:
From the Snippets panel, open the Recipes > ConferenceRoom > CustomCode folder for your server model and insert the If Statement - Only on Insert snippet.
| Before: | | | [View full width] <% If (CStr(Request("MM_insert")) = "MeetingRequest") Then %>
|
| After: | | | <% end if %>
|
| Before: | | | [View full width] <% if (String(Request("MM_insert")) == "MeetingRequest") { %>
|
| After: | | | <% } %>
|
| Before: | | | <cfif IsDefined("FORM.MM_InsertRecord")>
|
| After: | | | </cfif>
|
| Before: | | | <?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:
| <% Conflicts.Close() Set Conflicts = Nothing %>
|
| <% Conflicts.Close(); %>
|
| 2. | Wrap the following code around the selection:
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: | | | [View full width] <% If (CStr(Request("MM_insert")) = "MeetingRequest") Then %>
|
| After: | | | <% end if %>
|
| Before: | | | [View full width] <% 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:
| <?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:
| <?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.
From the Snippets panel, insert the Recipes > ConferenceRoom > CustomCode_PHP > If Statement - Only on Insert snippet.
| Before: | | | <?php if (isset($_POST['MM_update'])) { ?>
|
| After: | | | <?php } ?>
|
| 5. | Save your page.
|
Two functionsone to convert the FullDate value into a MySQL-friendly format and another to convert it backare now needed. These code blocks are inserted on each side of the just-inserted conditional code wrapping around the Conflicts recordset. 1. | In Code view, place your cursor above the code for including the connection that starts <?php if (isset($_POST['MM_update'])), and press Enter (Return).
| 2. | Insert the following code:
From the Snippets panel, insert the Recipes > ConferenceRoom > CustomCode_PHP > Enter Meeting Request - Convert FullDate snippet.
| <?php if (isset($_POST["FullDate"])) { $_POST['FullDate'] = strtotime($_POST['FullDate']); } ?>
|
| 3. | Place your cursor after the code block that closes the conditional statement, <?php } ?>, and press Enter (Return) to make a new line.
| 4. | Insert the following code:
From the Snippets panel, insert the Recipes > ConferenceRoom > CustomCode_PHP > Enter Meeting Request - Reconvert FullDate snippet.
| [View full width] <?php if (isset($_POST["FullDate"])) { $_POST['FullDate'] = date("YmdHis" ,$_POST['FullDate']); } ?>
|
| 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 need to add only a single function after the record insert code. Finally, ASP users will also need to make conditional the code section that expressly closes the Inserted recordset. 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:
| [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 Conferences WHERE ConferenceName = '" + Replace (Inserted__NameParam, "'", "''") + "' ORDER BY ConferenceID DESC" Inserted.CursorType = 0 Inserted.CursorLocation = 2 Inserted.LockType = 1 Inserted.Open() Inserted_numRows = 0 %>
|
| [View full width] <cfquery name="Inserted" datasource="Recipes"> SELECT TOP 1 ConferenceID FROM Conferences WHERE ConferenceName = '#FORM.MeetingName#' ORDER BY ConferenceID DESC </cfquery>
|
| 3. | Wrap the following code around the selection:
From the Snippets panel, open the Recipes > ConferenceRoom > CustomCode folder for your server model and insert the Redirect after Insert snippet.
| Before: | | | [View full width] <% If (CStr(Request("MM_insert")) = "MeetingRequest") Then %>
|
| After: | | | [View full width] <% if (NOT Inserted.EOF) Then Response.Redirect("meeting_details.asp?0" width="14" height="9" align="left" src="/books/2/710/1/html/2/images/ccc.gif" /> Inserted.Fields("ConferenceID").value) end if %> <% end if %>
|
| Before: | | | [View full width] <% if (String(Request("MM_insert")) == "MeetingRequest") { %>
|
| After: | | | [View full width] <% Response.Redirect("meeting_details .asp?ConferenceID").value); } %>
|
| Before: | | | [View full width] <cfif IsDefined("FORM.MM_InsertRecord") AND FORM .MM_InsertRecord EQ "MeetingRequest">
|
| After: | | | [View full width] <cflocation url="meeting_details.cfm?ID=#Inserted .ConferenceID#"> </cfif>
|
|
For ASP The final action for ASP users on this page is to apply a conditional statement to the code block that explicitly closes the Inserted recordset, found at the bottom of the page, so that it will be executed only if the record is inserted. 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:
| <% Inserted.Close() Set Inserted = Nothing %>
|
| <% Inserted.Close(); %>
|
| 2. | Wrap the following code around the selection:
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: | | | [View full width] <% If (CStr(Request("MM_insert")) = "MeetingRequest") Then %>
|
| After: | | | <% end if %>
|
| Before: | | | [View full width] <% 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 the month or day when you are 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]. r3-4. 
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:
From the Snippets panel, insert the Recipes > ConferenceRoomScheduler > CustomCode_PHP > Redirect After Insert snippet.
| <?php if (isset($_POST['MM_insert'])) { $url = "meeting_details.php?Location: $url"); } ?>
|
| 4. | Save your page.
|
|
