Update Record - Page 19

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.

Insert Record (Cont.) - Page 18
Dynamic Dreamweaver MX
Update Record (Cont.) - Page 20