Updating a Record

We've created a means for a user to see the data and add a new record to the data. Now, let's add the capability for the user to update a record. Naturally, Dreamweaver MX provides a method for doing that, as well. Although this time, we're going to need to involve another page.

Dreamweaver MX provides the Record Update Form menu option, which lets you quickly create a form that will update the current record. Since we've opted to show the user several records at a time, it doesn't make much sense to add an update form to our current page. How would we specify which record is to be updated?

By putting the update form on a second page and using the Go To Detail Page Server Behavior, we can easily accomplish our goals. Before we do so, however, let's examine the Go To Detail Page Server Behavior a bit more.

The Go To Detail Page Server Behavior

Dreamweaver MX employs a series of mechanisms called the Master-Detail Forms concept, by which you can organize sets of pages into a hierarchical structure allowing the site visitor to drill down through your data to the desired level of detail by following a series of links. You'll learn more about creating a master/detail structure in Chapter 21. But we can use part of that concept for our purposes. The Go To Detail Page Server Behavior let's you create a link around an image, text, or whatever is linkable that passes a URL parameter to the linked page. This parameter consists of a singular value from a recordset. Generally, this value is the primary key of the table. You can also choose to pass existing URL and/or Form parameters. (These would be parameters passed into the calling page— not parameters that the calling page generates.)

If you put this Go To Detail Page link inside a repeating region, Dreamweaver MX repeats it for each value in the region, assigning the proper key to the URL parameter that corresponds to the current record. Hence, you can create dynamic links for a particular record with ease. Let's add this to our current form.

1. To add a new column to the right of our table that will contain the word Update, right-click the rightmost table cell—the one that contains the word Category—and choose Table ® Insert Rows Or Columns to open the Insert Rows Or Columns dialog box.

2. Add one column to the right of the selection by clicking Column and then clicking After Current Column. Dreamweaver MX adds a thin column, but leaves the cursor in that newly inserted column.

3. Enter Options in the header cell. You'll need to add the same formatting to this text as the other cells in order to make it all pretty. We're adding the plural form because we're also going to add a link to let the user delete a record from this column in a later section.

4. Click below the Options header in the empty cell, and enter [Update]. (Include the brackets.)

Your table should now look similar to Figure 20.31.

Figure 20.31: We've added an Update column to our table.

Now that we have the text we want to use as the link, let's apply the Server Behavior that will send the proper information to our page. Follow these steps:

1. Select the text you want for the link—in this case, the [Update] text you just entered.

2. In the Server Behaviors tab on the Applications panel, click the plus symbol, and then choose Go To Detail Page, as you can see in Figure 20.32, to open the Go To Detail Page dialog box, as shown in Figure 20.33.

   
   Figure 20.32: Go To DetailPage lets us create a dynamic link to another page.

   
   Figure 20.33:   You can choose which field to send as a URL parameter in the Go To Detail Page dialog box.

You can see that the Link drop-down list box already contains our desired link text as a selection. You can change this by choosing the images Dreamweaver MX gathers from the page and adds as possible links as well. Since we want to use our link text, we'll not choose another image.

In the Detail Page text box, enter the name of the page to which you want to link. Even though we haven't created ours yet, we'll enter UpdateCategory.asp since that's what we're going to call our update page.

The Pass URL Parameter text box is tied directly to the Column drop-down list box. If you change the Column drop-down list by selecting different values, the selected value will pop into this field. You can also type over the text in the box. If you enter a parameter name that is different from those listed in the Column drop-down list, Dreamweaver MX will not overwrite your text.

In the Recordset drop-down list box, you choose a recordset from those that you've defined for this page. The Column category will change to reflect columns within the chosen recordset. In our case, we're going to select from the only recordset we've currently defined, ShowCategories. We want to use the CategoryID primary key as the parameter to pass, so we select that as well. Now, click OK and preview your page. It should look similar to Figure 20.34. If you look in the status bar of your browser window as you move your mouse over the Update links, you'll see that the CategoryID parameter is indeed updating properly for each record.

Figure 20.34: We've added an update link using the Go To Detail Page Server Behavior.

Adding Our Update Page

To create our update page, we're going to employ a couple of other Server Behaviors that Dream- weaver MX provides to make our development lives a little easier. Plus, we're going to use the Filter option available when defining a recordset, which we'll do first. Follow these steps:

1. Create a new page.

2. Add a title across it such as Update Book Category.

3. In the Bindings tab on the Application panel, click the plus sign and choose Recordset to open the Recordset dialog box.

4. In the Name box, enter GetCat.

5. Choose MDW_Books (or whatever your DSN is) as the connection.

6. Select the tblCategories table, and make sure that the All option is selected for Columns.

Now, we're going to create a filter for our recordset, which tells Dreamweaver MX to obtain only the records matching the specific criteria we're about to choose.

13. In the Filter drop-down list box, choose CategoryID.

14. Dreamweaver MX default to equals (=) in the next drop-down list box, which is good in this case, but you could choose from any of the logical operators in the list.

15. You use the Filter drop-down list box to tell the filter what type of data to look for. You can choose from among six types, including Session Variable, Cookie, and URL Parameter, which is what we want in this instance.

16. The next drop-down list box is tied to the first drop-down list and should currently contain CategoryID. If you write the filter out, it should look like this: CategoryID = URL Parameter CategoryID.

Your Recordset dialog box should now look similar to that in Figure 20.35.

Figure 20.35: This is the recordset definition for our update page.

17. To verify that everything works as it should, click the Test button and enter one of the CategoryIDs from the table, 3 for instance, in the field. Dreamweaver MX should return the record corresponding to that CategoryID.

Notice that we're only receiving one record. That's exactly as it should be since we're only going to update one record at a time. Using the primary key ensures that we're going to get only one record back since a primary key cannot be duplicated. (See Chapter 5 for more information on databases and their behavior.) Save the recordset and let's move on to adding the Update Record. Follow these steps:

1. Choose Insert ® Application Objects ® Record Update Form to open the Record Update Form dialog box.

2. Choose the proper Connection (MDW_Books) and Table To Update (tblCategories) settings again.

3. Select the recordset you just created—in our case, GetCat—if it's not already chosen for you.

4. Select the primary key column CategoryID for the Unique Key Column field, if its not already chosen for you.

5. Since we want the user to go back to the UpdateCategory page when they finish updating, add UpdateCategory.asp to the After Updating, Go To field.

You should, by now, see the tblCategories field in the Form Fields panel. Remember that we can't update an autonumber field, which is the data type of our CategoryID field. So we could remove it from the panel by selecting it and clicking the minus button, or we could change the way Dream- weaver MX will handle it.

We want the user to be able to see the category ID so that they're sure they're editing the proper record.

23. Click the CategoryID field.

24. From the Display As drop-down list box, select Text.

The Display As field controls how the data will be displayed on the form. Submit As controls how Dreamweaver MX should format the data when sending it to the server. Since we chose to display the data as text, Dreamweaver MX deactivates the Submit As field, hence removing any chance for an erroneous update to this field.

Click OK and save your page as UpdateCategory.asp.

Your page should now look similar to that in Figure 20.36.

Figure 20.36: Our update page is ready.

Let's Test It

OK, now we're ready to test our work. Go back to the BookCategory.asp page and either preview it or open it in a browser. Paging through the data, we see that "Selfhelp" is misspelled, as Figure 20.37 shows. This should be "Self Help," so click the Update link next to it, change the text in the Update Record window (see Figure 20.38) and click Update Record. It works, as you can see in Figure 20.39.

Figure 20.37: "Selfhelp" is misspelled.

Figure 20.38: Click Update to open the Update Record window, and correct the text.

Figure 20.39: Open the BookCategory.asp page in a browser, and see that indeed the spelling of the category changed.

We've added quite a lot of functionality to our forms primarily just by clicking options. Such is the power of Dreamweaver MX! Now lets add an option to delete a record, in case the user wants to remove an erroneous category.