Dreamweaver MX - The Application Panel

Update Record

Updating records is different from inserting records in that when we insert we don't need to retrieve records, we just need to insert a completely new record into a specific table. Since the Insert Record does this it basically does everything we need. Due to this people assume the Update Record must also do everything on its own, but it doesn't. Unlike inserting records, when we update a record we need to retrieve the record we want to update, that is, we need to find it, open it and then update the record. The Update Record behavior will do the third part, but it will not automatically find and open the record you want to update.

However, we now know how to retrieve records don't we - this means that we always need to create a recordset before we use the Update Record behavior. We have to tell the Update Record behavior which record to update.

So how did we retrieve the book records associated with the categories? We passed the category_id to the books page and filtered the book table by this ID. Similarly we can pass the book_id to a new page that will reopen the books table and filter for the specific book_id we passed. We will then bind the values of the database columns to an HTML form and add the Update Record behavior.

Open your books.asp page. Reload the rsBooks recordset (go to the Bindings window, and double-click the rsbooks recordset), and make sure you add the book_id column to the SQL SELECT statement. We need to retrieve the ID number of each record, as we will pass this to the next page. OK out of the Recordset dialog box.

Now go back to your page, and add the text "edit" within the repeat region besides the other dynamic text. It will be repeated besides each record when the page is viewed live. Turn the "edit" text into a hyperlink that points to a page called edit_book.asp, which we will create in a second.

We want to pass the book_id as a URL Parameter. You already know how to do this. You could use the Go To Detail Page behavior or manually add it from the Link dialog box. Let's do it manually:

·       Highlight the edit link and click the yellow folder besides the Link field from the Properties window - this should bring up the Select File dialog box

·       Make sure edit_book.asp is entered for the File Name

·       Click the Parameters button besides the URL field towards the bottom to bring up the Parameters dialog box

·       Add a new URL parameter by clicking the + button and name it book_id

·       Click on the Value field, then click the lightning bolt to the right of it; in the Dynamic Data dialog box that appears, select the book_id column from your rsBooks recordset. (If you don't see it, it means that you haven't added it to the recordset.)

OK all the dialog boxes to close them. Now view the updated categories3.asp page in a browser, and click through to a books.asp page. Hover your mouse over each of the edit hyperlinks that appear, and you'll notice that the appropriate book_id is attached to the end of each URL.

Now let's create our edit_book.asp page.

Go back to Dreamweaver MX and create a new dynamic ASP web page - save it as edit_book.asp. Create a new recordset called rsUpdateBook, making sure the conn_webprodmx connection is chosen. Enter the following SQL in the SQL box:

Now add a new variable, with the following values - MMColParam for Name, 0 for Default Value, and Request.QueryString("book_id") for Run-time Value.

Hit OK. This query will retrieve the information for the book whose ID is equal to that passed in the URL Parameter.

Just as for the Insert Record behavior, we have to create a form that reflects the database columns and table we want to update. But I've been having you do things the long way. Now that you know how to use the individual server behaviors, instead of creating the form we will use one of Dreamweaver MX's timesaving Objects - the Record Update Form.

Select Insert > Application Objects > Record Update Form, and you will be presented with the following dialog box:

As seen above, select the conn_webprodmx connection, books table to update, rsUpdateBook recordset, book_id as the unique key column and redirect to categories3.asp after updating.

Remove the following form fields from the list: book_image_path, category_id, and book_id (you should always remove unique IDs from the Update Record behavior because no one should ever be allowed to edit these values in normal circumstances. Also, we don't need to edit the image path through this form). Hit OK and the Update Record form should be created for you in your document.

Test your page in a browser, and try editing some of your book records.

We left the category_id out of the update to start off with, as explained above, but we might want to update this - that is, we might want to place a book into a different category. Let's do that now. We need to do three things: add the category_id column to the rsUpdateBook recordset, create a new recordset to retrieve the categories, add a field for the category ID in our update form, and add the category_id column to the Update Record behavior.

On the edit_book.asp page, get up the details for the rsUpdateBook recordset again, and add the category_id column to the SELECT SQL statement. Your SQL should now look like this:

We also need to add a form field to the update form. Before we do that we should think about how we want to display the categories for display. The only thing stored in the book record that associates the book record with a particular category is the category_id. Most likely we will not have memorized the ID numbers of our categories - it would be more practical to retrieve the categories and select which category we want to place the book in from a listbox field. Thus we need to create a new recordset and retrieve the categories.

Create a new recordset called rsGetCategories. Make sure the conn_webprodmx connection is selected, and type the following SQL into the appropriate field:

Hit OK. In your form, add a row above the seventh row that contains the Submit button (there should now be eight rows in all), and type the text "category" in the left column of this row. Now add a listbox field to the update form in the right column of this row (use Insert > Form Objects > List/Menu).

We want to populate the labels and values of the listbox from the rsGetCategories recordset - rename the listbox field "category_id". Select the listbox field and click the button labeled Dynamic from Properties to open the Dynamic List/Menu Server Behavior. (You could also get this Server Behavior by going to Server Behaviors > + > Dynamic Form Elements > Dynamic List/Menu.) You should see the following:

Select the right listbox field is selected from the menu labeled Menu, as seen above. Leave the Static Options empty. In the next field, select rsGetCategories. Populate Values with the category_id column and Labels with the category column (we would rather see the category name in the listbox).

The next option, Select Value Equal To is very important. We want to make sure that when we load the page, the listbox selects the category that it is currently in by default, otherwise we could change the category by accident when we submit the form. Select the lightning bolt to the right of the field to bring up the recordsets that have been created for this page. Select the category_id column from the rsUpdateBook recordset, rather than from the rsGetCategories recordset.

Finally, we need to reload the Update Record behavior to make sure it is included in the update. Double-click Update Record from the Server Behavior list in the Application Panel. When the dialog box opens, make sure that the field labeled category_id (which is the listbox field) submits to the category_id column numerically in the Form Elements option list (see it highlighted below). If you don't do this, the category_id may be ignored by default. The Update Record dialog box should now look like this:

Test your pages in a browser, and try moving some books into new categories.