Administrator Recipe: Edit Conference Room

Because meeting rooms may vary over time with new features such as speakerphones and videoconferencing facilities, the application needs a way to modify existing room descriptions. The Edit Conference Room page displays the room name and its specifications in a text field for easy modification.

Rather than navigating from a detail page by clicking a link, users here change from one room to another by going to a drop-down list. Although this takes a little extra coding, it effectively simplifies the user experience.

Step 1: Implement Design

Let's start by creating the basic page that will require a list, a text field, a text area, and a submit button. ColdFusion users will also need to add a hidden field to be used by the Update Record behavior.

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 edit_conferenceroom 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 > Edit Conference Room - Wireframe snippet into the Content editable region.
3.	Within the table, place another HTML table to hold the form's four elements. Place your cursor in the row below the words EDIT CONFERENCE ROOM and insert the Recipes > ConferenceRoom > Forms > Edit Conference Room - Form snippet [r3-22]. r3-22. ColdFusion and PHP users should drag a hidden form element from the Forms category of the Insert bar onto the page next to the submit button and name it RoomID.
4.	Save your page before continuing.

Step 2: Add Database Components

Two recordsets are used in this page, but they both are based on the same data source table. One recordset is used to display the entries to be modified, while the other serves to populate a list elementwhich in turn ultimately controls what is in the first recordset.

Let's start with the simple recordset first, which eventually will be used to populate the Room drop-down list.

1.	From the Server Behaviors panel, choose Add (+) and select Recordset (Query).
2.	In simple view, enter the name of the recordset. Enter RoomList 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 for PHP) from the Table list.
5.	Limit your data source columns to only those that are needed. Choose the Selected Columns option and highlight RoomID and RoomName.
6.	Leave the Filter and Sort option set to None and click OK when you're done.

The second recordset uses a dynamic variable to create the WHERE clause of the SQL statement.

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 Rooms 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 * FROM Rooms RoomWhere
5.	In the Variable area, choose Add (+) and enter RoomWhere under the Name column.
6.	In the Default Value column, enter WHERE RoomID <> 0.
7.	In the Run-time Value column, enter RoomWhere and click OK to close the dialog.

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 Rooms 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 * FROM Rooms #RoomWhere#
6.	In the Page Parameter section, choose Add (+) and, in the Add Parameter dialog, make sure RoomWhere is chosen in the Name list.
7.	Enter WHERE RoomID <> 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

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 Rooms 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 * FROM rooms WHERE RoomWhere
5.	In the Variable area, choose Add (+) and, in the Add Parameter dialog, enter RoomWhere in the Name field.
6.	In the Default Value field, enter RoomID <> 0.
7.	In the Run-time Value field, enter $RoomWhere and click OK to close the dialog.

Step 3: Data Binding Process

In addition to the normal binding of data source fields to form elements, this page requires that special attention be paid to the list element for the data binding to be complete. The list element will be populated dynamically and will also use JavaScript to change the currently displayed room.

1.	Select the ConferenceRoom list element and click the Dynamic button on the Property inspector.
2.	In the Dynamic List/Menu dialog, choose RoomList from the Options From Recordset list.
3.	Set the Values list to RoomID and Labels to RoomName.
4.	For the Select Value Equal To field, click the lightning bolt icon to open the Dynamic Data dialog.
5.	Choose RoomsID from the Rooms recordset and click OK twice to close the dialogs.

Now let's add the JavaScript using Dreamweaver's Call JavaScript behavior.

Note

Make sure you assign the RoomsID field from the Rooms recordset and not RoomList. Remember that the Rooms recordset is filtered to display the record currently being edited.

Because this JavaScript statement is so complex, we've included it as a snippet. To copy the SQL code to the clipboard, first place your cursor in a text string in Design view. Expand the Recipes > ConferenceRooms > Client JavaScript folder for your server model. Then, right-click (Control click) the Edit Conference Room - Change Room snippet and choose Copy Snippet from the context menu.

1.	With the ConferenceRoom list selected, from the Behaviors panel, choose Add (+) and select Call JavaScript.
2.	In the Call JavaScript dialog, enter the following code: Press Ctrl-V (Command-V) to insert the previously copied snippet: [View full width] document.location.href = 'edit_conferenceroom .asp?RoomID='+ document.UpdateConferenceRoom .ConferenceRoom.options[document. UpdateConferenceRoom.ConferenceRoom.selectedIndex] .value [View full width] document.location.href = 'edit_conferenceroom .asp?RoomID='+ document.UpdateConferenceRoom .ConferenceRoom.options[document. UpdateConferenceRoom.ConferenceRoom.selectedIndex] .value [View full width] document.location.href = 'edit_conferenceroom .cfm?RoomID='+ document.UpdateConferenceRoom .ConferenceRoom.options[document. UpdateConferenceRoom.ConferenceRoom.selectedIndex] .value [View full width] document.location.href = 'edit_conferenceroom .php?RoomID='+ document.UpdateConferenceRoom .ConferenceRoom.options[document. UpdateConferenceRoom.ConferenceRoom.selectedIndex] .value
3.	Verify the code and click OK to close the dialog.
4.	Make sure that the event is set to onChange.

Now we're ready to finish off the data binding by associating the text form elements with their data source equivalents.

1.

From the Bindings panel, expand the Rooms recordset.

2.

Place the needed data source fields into position on the page:

	Drag RoomName to the RoomName text field.
	Drag RoomDescription to the RoomSpecs text area [r3-23].
	For PHP and ColdFusion: Drag RoomID to the RoomID hidden field.

r3-23.

Step 4: Update RecordConference Room

Let's add the Update Record server behavior.

For ASP

1.

From the Server Behaviors panel, choose Add (+) and then Update Record.

2.

In the Update Record dialog, select the data source connection.

Choose Recipes from the Connection list.

3.

Choose the table containing the data you are updating.

From the Table to Update list, choose Rooms.

4.

Choose the recordset from which to get data source fields.

From the Select Record From field, choose Rooms.

5.

Set the Primary Key for the recordset.

From the Unique Key Column list, choose RoomID and make sure the Numeric option is selected.

6.

Leave the After Updating, Go To field blank.

By leaving this field blank, the page will resubmit to itself and allow the user to modify another room if desired.

7.

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

From the Get Values From list, choose UpdateConferenceRoom.

8.

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

	Set the RoomName form element to update the RoomName data source field as Text.
	Set the RoomsSpecs form element to update the RoomDescription data source field as Text.
	Set the ConferenceRoom form element to be ignored.

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.

Select UpdateConferenceRoom 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 Rooms (rooms in PHP) from the Insert Into Table list.

6.

Set the data source fields to their corresponding form elements.

	Set RoomID to the FORM.RoomID form element as Numeric type in ColdFusion and FORM.ConferenceRoom as Integer type in PHP. Select Primary Key.
	Set RoomName to the FORM.RoomName form element and submit as Text type.
	Set RoomDescription to the FORM.RoomsSpecs element and submit as Text type.

7.

Choose the page to visit after the update is complete.

Select the Pass Original Query String option, which will insert ColdFusion code like this: #CurrentPage#?#CGI.QUERY_STRING#.

Leave the After Updating, Go To field blank.

8.

Save your page.

Step 5: Hand-Code Room WHERE Clause

The final step for this page (and for the application) is to add custom code to build the WHERE clause of the SQL statement. The code serves to update the variable inserted when the Rooms recordset was defined, RoomWhere.

1.	In Code view, locate the Rooms recordset and place the cursor before the beginning of that code block. For ASP, the cursor should be placed before the Rooms_RoomWhere variable for the Rooms recordset is declared. To find this code quickly, go to the Server Behaviors panel, choose the Recordset (Rooms) entry, and choose Code view. The variable is declared in the code block above the selection. In PHP, due to the merging of code blocks, go to the top of the page and place the cursor just after the <?php require_once line.
2.	Insert the following code: From the Snippets panel, open the Recipes > ConferenceRooms > Custom Code folder for your server model and insert the Set RoomWhere for Rooms snippet. <% Dim RoomWhere RoomWhere = "" if (cStr(Request("RoomID")) <> "") then RoomWhere = "WHERE RoomID = " & Request("RoomID") end if %> <% var RoomWhere = "undefined"; if (String(Request("RoomID")) != "undefined") RoomWhere = "WHERE RoomID = " + Request("RoomID"); %> <cfif isDefined("URL.RoomID")> <cfset RoomWhere = "WHERE RoomID = #URL.RoomID#"> </cfif> <?php if (isset($_GET['RoomID'])) { $RoomWhere = "RoomID = " + $_GET['RoomID']; } ?>
3.	Save the page.

You page is ready for testing. If you're working with a live data source, it's best to modify existing room descriptions and then immediately change them back [r3-24].

r3-24.