Inserting Information into a Database | Macromedia ColdFusion 5 Training from the Source (With CD-ROM)

I l @ ve RuBoard

One of the most important aspects of a database application is being able to add new information into it. Even if you have a dynamic site, you still have to keep the content in the database fresh. Just as ColdFusion allows you to interact with the database to retrieve information, it also allows you to insert information in much the same manner. You will be using a form to insert new information into the database.

Choose File > Open and select productinsertform.cfm from the Lesson6\Start folder on the CD-ROM.

This is the form for inserting information into the database. The form has already been built for you, but some key aspects are missing. As you can see, there are form fields for each of the fields in the database, except productid, which you will set later on the action page.
In the Action attribute of the Form tag, add productinsert.cfm .

The productinsert.cfm template is the one that will actually insert the information into the database. It will take the values that are entered into the form and put them in the database. Without this attribute, the form would not know where to post the information.
Fill in each of the name attributes with the following values: ProductName, ProductDesc, ProductPrice .

The form is now complete.

You may have noticed that these names match the field names in the database. You'll find out later how smart and useful this is, when you are actually inserting the values into the database. It is much easier to reference the form field names in the action page if you know that they match the database field names . There is also another reason for this, which you'll see in the next task.

NOTE

Make sure to save this template to your local hard drive. Create a folder entitled Lesson6\Start in your Inetpub\ wwwroot folder to store the files in. Also remember to move the images and the style sheet over from the Lesson6\Start folder on your CD-ROM so the template will display properly.
Choose File >> New and select Blank Document from the New Document window.

The page you're creating is the one that's actually going to do the database insert. None of the code on this page will ever be seen by the user , so you do not need to create any HTML tags.
Start at the top of the page and add the following <cfquery> tag:
```
 <cfquery name="productinsert" datasource="products"> 
```
You still use the <cfquery> for inserting data, just as you would with retrieving data. You're giving the query a name and specifying what data source to work with. The only difference between inserting and retrieving data will be the SQL that is inside the <cfquery> tags.
After the <cfquery> tag add the following SQL:
```
 INSERT INTO tblProducts    (   ProductName,   ProductDesc,   ProductPrice   ) 
```
By using the INSERT keyword in SQL, you are preparing to insert information into the database. The next keyword, INTO, seems pretty obvious. It designates what table in the database you are inserting information intoin this case, the tblProducts table. Following the table name, you supply a list of the fields you are going to insert values into. They must be enclosed within one set of parentheses, and each value must be separated by a comma. Youdon't have to put parentheses on separate lines as done here, but it is good formatting practice to do so.

NOTE

Because the ProductID field is an auto number field in Access, you don't have to specify it in the list of values you want to insert; Access will take care of this for you. It will take the number of the last record inserted, add one to it, and automatically insert it into the ProductID field.
After the closing parenthesis, add the following SQL:
```
 VALUES    (   '#form.ProductName#',   '#form.ProductDesc#',   '#form.ProductPrice#'   ) 
```
The VALUES attribute specifies the values that are to be entered into the previously specified fields. The values must correspond to the order of the field names. For example, if ProductName is the first field listed in the INTO statement, it must also be the first value listed in the VALUES statement. If it isn't, the SQL will still work, but you would end up with the wrong values in the fields.

When inserting values, they must all be enclosed in parentheses. Text fields to be inserted into the database need to be individually enclosed in single quotes (as shown above), but integer fields do not. Either way, each value must be separated with a comma.
Add the closing </cfquery> tag.

The query is now finished. When you run this page, a new record will be inserted into the database. The information will come from the form that was submitted.

However, when this page is done executing, all the user would see is a blank screen. This would probably not be a good idea. You want to send the user somewhere else once the information was inserted. Sending them back to the customerinsertform.cfm would be a good place.

NOTE

ColdFusion offers a shortcut for inserting information into the database without using any SQL. The <cfinsert> tag will insert the information using just the data source and the table name, as long as the form field names match the database field names exactly. So <cfinsert datasource="products" tablename="tblProducts"> would accomplish the same thing as the SQL code above.

The <cfinsert> tag can also be used to add information to a database when you have form fields whose names don't specifically match tables in that database. Within the <cfinsert> tag, you use the Formfields attribute to specify which fields you want to go into each table.
At the bottom of your page, add the following CFML:
```
 <cflocation url="productinsertform.cfm"> 
```
The <cflocation> tag allows you to specify any URL as a destination. Once ColdFusion encounters the <cflocation> tag, it stops processing the page it's on, and immediately sends the user to the page specified by the tag. Any code that comes after the <cflocation> tag will not be executed. In this case, you are sending users back to the page they started on.
Choose File > Save and save the document as productinsert.cfm in the Lesson6\Start folder on your hard drive.

Make sure that the productinsertform.cfm is saved in that folder as well. With the code as it's written, both of these files need to be in the same directory in order to function properly. (They could go in separate directories as long as the Action attribute in the productinsertform.cfm template points to the proper location of the productinsert.cfm template.)

Now you need to see how it works.
Open a browser and enter the following URL into the address window: http://127.0.0.1/Lesson6/Start/productinsertform.cfm .

Here you see the form that you created earlier in the task.
Fill out the form with your information and click Add Product.

Whatever information you enter into the form is added to the database. When you click Add Product the productinsert.cfm template is called, the information inserted, and you are returned to the productinsertform.cfm template. The only way you can know if the information was inserted is to physically check the database. Once you return to the form, the form fields will be empty. You shouldn't see much, if anything, between clicking the Add Product button and redisplay of the productinsertform.cfm template.
Choose Start > Programs > Microsoft Access.

If you do not have Access, don't worry; you will be able to determine if your information was added in a later task.
Select More Files under Open an existing file and click OK. Browse to the Inetpub\databases directory and select products.mdb.

You need to select More Files to find your database, unless you have opened it previously, in which case it appears in the listing of databases below More Files.
Click Open.

The main screen for the Products database appears. You should see the tblProducts table in the window.
Double-click on tblProducts.

A window appears, showing all the records in the tblProducts table. There should be five records, with the last record being the one that you entered in the productinsertform.cfm template.

I l @ ve RuBoard