Dreamweaver MX - The Application Panel

Insert Record

The Insert Record behavior can be found under Server Behaviors. It does exactly what its title suggests. With this behavior you do not have to open a recordset - it creates all the code necessary to insert a record into your database. The only thing you need to do is create an HTML form. Each column will be paralleled with a form field except the autonumber column, which is automatically generated by the database. Any other column that will be automatically generated should be left alone. For example, some database tables have a column that holds the date and time the record is added, generated automatically by Access.

As we mentioned above, the form should parallel the database table you will be inserting data into. Each column will receive its value from the form field; therefore you have to take into account the database column's data type.

We will attempt to insert a new category into our categories table - let's take a look at our categories table again:

There are only two columns. One of the two is an autonumber so we will only need to insert into one column using one form field. A single textfield will therefore serve this purpose just fine.

For sake of time, we will add the insert record form on this page. In a real-world application you may decide to add the insert record form on a separate page. There is no major difference. The only difference is that depending on the type of web application you will most likely only want certain people inserting records into your database. Nevertheless this has nothing to do with where we put our insert record form. You will have to hide or disallow access to administrative features through user logins. You will then be able to allow or disallow users to certain areas based on some database column. You will learn this concept later in this chapter. In the meantime, we just want to learn how to simply allow a user to insert records into your database online.

Add a Horizontal Rule at the bottom of your categories3.asp page. Insert a table with one column, 2 rows, and 50% width underneath the horizontal rule. In the first row type the text, "Add Category". Insert an empty form in the second row. Insert a new table with 2 rows and columns with 100% width inside the form. (Make sure you insert the table inside the form.)

Type the text "category:" in the left column of the first row. Insert a textfield in the right column of the first row by placing your cursor there and selecting Insert > Form Objects > Text Field. Name the textfield "category". Insert a button in the right column of the second row, and change the label of the button to "Insert Category". The form should now look something like this:

Now that we have created our form we can add the Insert Record Behavior. Select Server Behaviors > + > Insert Record, and you will be presented with the Insert Record dialog box:

As seen above:

·       Select the conn_webprodmx connection from the Connection menu

·       Select the categories table to insert the form values into. Put categories3.asp (to redirect to after insertion) in the After Inserting, Go To field, by browsing or typing (you could redirect to any page you like. For example you might decide to redirect to a page that thanks the user for the insertion before showing the results)

·       Select the correct form in your document that holds the insert record form. By default it will be named Form1 unless you changed the name yourself. Since we named our form field the same as our database field, Dreamweaver MX should automatically choose the textfield named category to be the source of data to be inserted into the category database table (in situations where they are not called the same thing, you can make the association manually here, by changing the text in the Form Elements field)

·       The category should be submitted as Text (see the Submit As field)

Hit OK. View your categories3.asp page in a browser and try to insert a new category - the new category should automatically appear in the categories list. Not only this, but it will automatically be placed in the list alphabetically. Hover your mouse over the new category and look at the stats bar; you will see its new ID number that was generated by the database. Click the new category to go to the books.asp page; you should receive the message, "Sorry, no records found. Please go back and select another category" because we have not added any books under this category yet.

You now know how to insert records into your database online. Before we move onto the Update Record behavior, let's try to insert records into the books table for our new category. Of course, before we add the necessary Insert Record behavior we need to create an HTML form that reflects the database table - let's take another look at the columns for the books table:

We need to create a form with 8 form fields. You may be wondering about the category_id column - how will we know the category ID of the specific category for the book we are adding? There are different ways of doing this. We could open the categories table on the same page of this insert page and bind the categories to a list menu. We would bind the category_id as the value of each <option> tag, and have this list menu insert into the category_id of our books table.

However, one of the things to aim for when building web applications is code reuse. We should always try to build code that will be reusable for other options. In this case, we already opened the categories table on categories3.asp so why do it again? Once we select a category on this page, the category_id is automatically passed to the books.asp page as a URL parameter.

Therefore, it makes sense to have the facility for inserting new books on the books.asp page, because here we have already chosen the category_id we are dealing with, so we don't need to worry about it again. We can bind the category_id URL Parameter to a form textfield or hidden field. It is very important that the category id is inserted properly because it is how books are associated with their respective categories.

So, let's create our facility for inserting new books into the database! Insert a horizontal rule at the bottom of your books.asp page. Insert a table with 2 rows, 1 column and 50% width underneath the horizontal rule. Type the text "Add Book" in the first row of this table. Insert an empty form in the second row, and insert a new table inside this form, with 8 rows, 2 columns, and 100% width.

In the left-hand column of this table, type out the following in each respective row: book_title, book_author_fname, book_author_lname, book_price, book_isbn, and page_count. Leave the last two left-hand rows empty. Insert a textfield into each right-hand row that has text entered into the left-hand row (the first six). Insert a hidden field in the seventh right-hand row (for the category_id - see below) and a button in the eighth right hand row. Our form should now look like this:

Now rename each textfield and the hidden field with the names of their respective database columns. Change the label of the Submit button to "Add Book". Lastly, we need to make sure the value of the hidden field is the category ID that will be coming from the URL Parameter named category_id, passed from the categories3.asp page. Highlight the hidden field for the category_id and change its value to the following:

Now we need to add the Insert Record Behavior. Select Server Behaviors > + > Insert Record. Choose the conn_webprodmx connection, insert into the books table, and redirect to the books.asp page. Make sure the form fields are sending their data to the relevant database columns by checking through the statements in the Form Elements box. Your Insert Record dialog box should now look like this:

The Server Behavior will add another hidden field into the form - do not delete this field. Dreamweaver MX uses this method for the Insert Record, Update Record, and Delete Record server behaviors. You can simply ignore the hidden field added by Dreamweaver MX for these three server behaviors.

If you recall from Chapter 5, you can easily check to see if a form has been submitted to the page using a condition statement. When you add the behavior, Dreamweaver MX uses the name of the form as a variable in the ASP Insert Record code block and also adds it as a hidden field in the form itself. When the page is loaded, the ASP Insert Record code block then checks through a conditional statement to see whether this field has been submitted, which essentially means that the Insert Record form has been submitted. If so it executes the entire insert code block, otherwise it loads the page normally without executing the insert record action.

We can now add books for a particular category. Regarding the book_image_path column, as you know, we have created a static path to a default image that will be used for each book. This path will be the default value for each new record added (check this by looking at the books table in Access). Hence, we will not insert a book_image_path from the form. You could change this of course, to either change the path manually, or upload an image from this form.

Test this now by loading your categories3.asp page, selecting any category you like, and trying to insert a new book. You should see the new record listed on the page once you submit the form.

It took a while to create the insert form for the books.asp page. We had to format it, add and rename all the fields, and finally add the Insert Record Behavior. Well, as luck would have it, Dreamweaver MX has an object that does this all in one step! You can find it under Insert > Application Objects > Record Insertion Form. You will not have to manually create the form to reflect the database table. The object will create the form and add the Insert Record Server behavior for you. When you get a chance, go ahead and try it.

Now that we know how to insert records let's update them!