• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL

Lesson 15. Building a Web Survey > Inserting Data into the Database

Inserting Data into the Database

All of the data that needs to be inserted into the database now exists as a separate form element within the survey_p2 form. The user enters some of that data on this page, while other data is completed dynamically in hidden fields. To the Insert Record server behavior and the database, the distinction is irrelevant: They just need the element's name, its data, and the field to which it should be written. And that's exactly the information you are prompted to give in the Insert Record dialog.

Click to select the Submit Survey button in the form. In the Server Behaviors panel, click the + button, and choose Insert Record from the list.

This step is no different in principle from what you did in Lesson 13, when you built the registration page that wrote the first name, last name, username, and password data to a new record in the users table of elearning.mdb. The only difference here is that there will be a lot more than four fields and the target table is survey, rather than users.

When you are finished with this step, the familiar Insert Record dialog appears.

Be sure that the Submit Values From field lists survey_p2. In the Data Source drop-down menu, choose elearning. In the Insert Into Table field, choose survey.

After a moment, a long list appears in the Columns area. You should remember this from Lesson 13, but it is so important that you should make sure that you really understand what is happening.

This area lets you specify the mapping from the (source) form to the (target) database table. Each row uses the following syntax: [database table field name] is [mapped to form element] as [data type]. Your job is to go through each database table field and ensure that the proper data is mapped to it, and that the data is sent as the correct data type.

Now, because the names of each of the form elements match the names of the database table fields, Dreamweaver has already guessed what the mapping should be. If you've followed all the steps in preceding tasks exactly, Dreamweaver guessed correctly, so you could click OK, and everything would work. But let's take a moment to look at it more closely.

When you create a database table, you specify the data type for each field that you create. I created the survey database table after I had created the survey itself, so I already knew what should be entered as numeric and what should be entered as text (the only two data types used in this survey). All of the questions involving a range of possible responses (Strongly Disagree to Strongly Agree) are entered in the numeric data type (0–4).

Data types are categories that data can be grouped in. If a given set of data is in a category, your computer can manipulate that data in certain ways. For example, data submitted in the Number data type can be arithmetically manipulated—you can calculate the average, or the median, or perform more sophisticated statistical analyses. Data stored using the Text data type cannot be manipulated arithmetically. Boolean data is either true or false. You used the Boolean data type in the Flash lessons, when you set a given movie clip instance's visibility to false. Visibility is a good example of a property needing a Boolean value, because you couldn't set a visibility property (which is either on or off) to a number, such as 19, or a text string, such as Brendan Witt. Neither of those pieces of data would mean anything as a specified visibility property. It's critical, therefore, that when you map the form element to its target database table field, you also send that data in the correct format.

The Columns area makes this process easy. To see what I mean, click 'q3 gets value from FORM.q3 as Numeric' in the list. Now look at the drop-down menus below the Columns area. The Value drop-down menu contains a list of all of the available sources (that is, form elements). To map a given form element to the selected database table field, simply select it from this list (again, Dreamweaver has already correctly done so for you). The Submit As drop-down menu contains a list of data types. By far, the two most common you will use are Text and Numeric, but you will see a list of others in there as well. To change the data type submitted to the database, choose it from this list. (Again, do not change any values here, because Dreamweaver has already guessed them all correctly for you.)

Now that you understand how the Columns area works, and the function it fulfills, you might be wondering whether you need to worry about it, since Dreamweaver appears to be pretty good at guessing the values to begin with. You need to worry any time the form element names are not identical to the table field names. It is precisely to avoid having to deal with this that I try to give my form elements the same name as my form fields, but sometimes this is not possible.

Finally, click the 'KeyID is an Unused Primary Key' (at the top of the Columns area). You will see that the Value is set to None, and that the Submit As setting is grayed out. This is as it should be—that field is automatically assigned a unique value by the database every time a new record is created.

In the After Inserting, Go To field, type or browse to thanks.cfm. Click OK to complete the dialog.

The file thanks.cfm is a static HTML file that says the record was added and provides a link back to welcome.cfm. That file has no idea whether anything was submitted anywhere, successfully or otherwise—there is no code on it to verify that. But the only way the user can get to this page (short of typing its URL directly) is to be redirected there after the successful processing of the Insert Record server behavior.

Before you click OK, the dialog should look as it appears in the accompanying screenshot.

In the Site panel, Shift-select both survey1.cfm and survey2.cfm and click the Put button to put them to the server.

With these two files uploaded, the survey is now ready to test.

Pop quiz! To test the functionality of the survey component of the elearning application, which file should you begin with? Why?

If you answered login.cfm, because survey2.cfm needs the session variable generated on that page so it can upload that data to the server, then you are correct! Otherwise, if you just begin with survey1.cfm and fill in all the answers, proceed to survey2.cfm and fill in all of those answers, and click Submit, then one of the form elements (the hidden field bound to the MM_Username session variable) will be undefined, and you'll get an error message. If you try survey1.cfm without first logging in, you should be diverted to notloggedin.cfm anyway.

In the Site panel, select login.cfm and press F12 to test the file in a browser. Log in using any username/password you have registered. Proceed to, take, and submit the survey.

If you made it to the thanks.cfm page, then everything went off without a hitch. If you own Microsoft Access, then you can open elearning.mdb's survey table and see the results you entered. The accompanying screenshot shows what the survey table looks like in Access after the first user took the survey. Chances are your values are all different, but then again, that's the whole point of the survey.

If you got an error, then any number of things could have gone wrong. The server might be down, or there was a typo in one of your form element names or values, or you made a mistake in the Insert Record dialog, perhaps inadvertently changing something in the Insert Record dialog's Columns area, failing to enter thanks.cfm in the After Inserting, Go To field, or specifying the wrong data source or table.

Close the browser, select login.cfm again, and press F12 to start the test all over again. This time, when you get to the first page of the survey, click Submit without entering any values in the survey. When you arrive on survey2.cfm, scroll down and take a look at the error message.

The problem here is caused by the hidden fields. ColdFusion, per your instructions, is trying to dynamically load values into those fields. Unfortunately, since you didn't enter any values on the first page of the form, there are none to load. So ColdFusion throws an error. You never want your user to see an error message like this one.

There are two ways to prevent this error from occurring. You can either force the user to enter values, and not let her or him progress to this page until something has been entered on the first page of the survey, or you can tell ColdFusion to replace all null values with bogus data (such as the number 999, which clearly is not in the 0-4 range used in the survey). In this lesson, you will use the first method. And once again, you will turn to form validation to do so.



Not a subscriber?

Start A Free Trial

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint