Online Databases with MS FrontPage - 1 by mercy2beans108


									            Online Databases with Front Page and Access
                               Day 1
Even though the web offers us the ability to easily update information for audience in static
HTML pages, the true power comes with dynamically updated pages, especially those
interfacing with a database. This interfacing has become much easier with the development of
newer web editing packages such as MS FrontPage. In these lessons we will be using MS-
FrontPage 2003 and MS-Access 2003.

Today if you have ever created a form in MS Word, you would be about 2-3 steps away from
developing an online form on the web. The greatest thing about this is that the online form
results would end up in a MS Access Database.

Think about that. If you were to had out a paper survey to your staff, you would then have to
hand count the results or hand enter all the information into a database so you can electronically
tally your results. With an online database survey, as the users complete the survey it is
automatically entered into database.

You could use this system to collect and update contact information. You could create an online
form that will allow all your staff to publish news events on a web page and they don’t have to
know anything about web development. You can even create online test that are automatically
graded. The possibilities are endless. The only limitation is your imagination.

So let’s get started. We will start by creating a simple form that collets some student information.

                 (Use the exact field names provided otherwise parts of the exercise will fail)

Create a Simple Form

   1. Open a new page and save it in the designated location as “StudentInfo.asp”
       (In order for these web pages to work with databases you must save them as “asp” pages. ASP stands for
       Active server pages)
   2. Hit your enter key to place a blank line at the top of your page
   3. Select “Insert” from the menu bar, “Form” from the drop down menu and “Form” from
      the pop up Menu

                                                    1 / 17
                                                              (You can also float/dock the form menu pop
                                                               up so that it will always be available as a
                                                                                 tool bar)

4. On your Student Info page you will now have dotted box which contains both a submit
   and reset button
    (This is the inserted form and any information that you wish to collect must reside inside the dotted box)
5. Place the insertion point before the submit button inside the inserted form and press the
   Enter key a 2 times to create some empty space in the form
6. At the top of the form (inside the dotted box) type and center the title Student Information
   Form and press enter.
7. Type First Name: and press the space bar twice. (Make sure you are back to left justified)
8. Select Insert–Form–Textbox
   from the menu bar or the textbox
   button from the docked form menu
9. Double click on the inserted textbox and set the Name property to FirstName no spaces
   and the tab order to 1. (We will leave all the other options with the default settings) Click on the
   OK button.
    (The textbox name will be field name created in the access database by FrontPage)
10. Continue that process with the following textbox settings.

                                                  2 / 17
             Label                                  Text Box Properties
         (Form Labels)                     Name                           Tab Order
           Last Name                     LastName                                2
             Address                      Address                                3
               City                         City                                 4
               State                        State                                5
               Zip                           Zip                                 6
              Phone                        Phone                                 7

11. Your project should look like the following:

                                                      (Now that this is done it would be nice if the
                                                            labels and text boxes lined up)

12. Still inside the inserted form, between the Phone: line and the submit button, insert a
    table with 2 columns 7 rows, border size of 0, cell spacing set to 2, table width set to
    about 60% and centered. (table – insert – table)
13. Place the labels and the text boxes in the table remove the extra spacing between the
    Student Information Form title and the added table. Save your work. Your project
    should now look like the following.

                                           3 / 17
Now that we have the form built, we need to create the database and the web connections to
collect the student information electronically. With other packages this could actually be a
very code intensive project, but FrontPage makes this easy.
1. Right click in an empty portion of the form and select Form Properties
2. Select Send to Database and click on the Options button
3. Click on Create Database button
4. Click on the OK button when the database was created successfully.
   (Notice that the database connection name is the same as the asp file name. If you want the database and
   database connection to have a different name then the asp file you would need to originally name the asp
   file with the name you want for those and after you have created the database and saved the page just
   rename the asp page to what you want. Front page will still maintain the connections to the database. Just
   make sure you save your work after the database creation and then rename the asp file.)
5. Click OK two more times. Save the page
   (You now have a global.asa file and an fpcb folder added to your web site. The fpdb folder contains the
   access database files which your forms will submit their data to and the global.asa file contains the
   database connection controls. Do not edit or remove the global.asa file.)
6. Open the form in a browser window (preview in browser) and add some data to the database

                                        (only two or three records)
7. Open the database and verify that the information has indeed been added to the database
   (Open the fpdb folder and double click the studentinfo.mdb file. You must have MS-Access to do this.)
8. While you still have the results table open, switch to design view by clicking on
   the 1st button on the tool bar
9. You need to add another field with the name classPeriod and it should be a text field.
   (I added this field after the phone field by right clicking on the Remote_computer_name field and selecting
   insert row)
10. Close the results table not the Database
   (make sure you answer yes to saving the changes made to the table)
We now need to add some additional data to this database file. I have already created a
spreadsheet with some additional student data. So at this point you get to learn how to
import a spreadsheet into an existing database. This is the part where field names pay a very
important part. If you did not follow the same naming conventions that I did during the text
field setups in the Form we will have some problems here.
1. Import “Master Student Table” spreadsheet into database
   In the MS-Access program with the StudentInfo database still open:
       a. Select File – Get external data – Import from the menu bar

                                                 4 / 17
           b. Find and select the MasterStudent spreadsheet the downloaded resources files.
              (Don’t forget to change the Files of type: ) and double click the spreadsheet file or click
              on Import
           c. Accept the defaults on the first screen of the Import Spreadsheet Wizard
           d. Select “First Row Contains Column Headings” and click on Next
           e. Choose to import data into existing table, choose the Results table from the drop
              down list and click on Next
           f. Click on Finish and click on OK.
           g. Close the Access program
           h. Wait until database is completely closed.
                (The cursor will have an hour glass with it while the file is still closing)
   2. Make sure you have saved all your work.

Create a Database Results Page

Now that we have data collected in a web based database, we need to get this information out
without using FrontPage or without opening the database.

   1. Open a new page and save it as StudentResults.asp in the designated location
   2. Type Student Information Report centered at the top of the page, make it a Heading
      One style, and press the Enter key
   3. Select Insert from the menu bar and this time select Database from the drop down menu
      and Results from the pop up menu
   4. Make sure that the “ASP” and “Use an existing
      database connection” radio buttons are selected in
      the “Database Results Wizard” dialog box
   5. Make sure that the correct database connection is
      selected. (It should be called “StudentInfo”) Click
       (You are selecting the database connection to the student
       information database that we created in the previous section)

   6. The record source should be Results. Click next
       (This is the actual table in the built database that stores the
       student information)

                                                       5 / 17
   7. Click the “More Options” button and click the Ordering button
        (The edit list button will allow you to remove fields from the report but for now we will leave all the

   8. Sort by LastName by adding LastName from the “Available fields:” box to the “Sort
      order” box. Click OK, OK, and Next
   9. Accept the defaults here and click Next
        (This has to do with how the resulting information is displayed the web page.)
   10. Select the “Display all records together” radio button and click finish
        (You can select to display the data in groups which would speed up the page load time if you have a lot of
        resulting records)
        The page should look like the following.

   11. Save the page and preview it in the browser
        (Remember to us the “Preview in Browser” button not the preview tab at the bottom of the page)
You will notice on the results page that you have some extra data. By default a web submission
contains a timestamp, IP address some other network data. You will notice that only the
submissions you made via the web page have that extra information.
We now see the results but we want to make some changes and remove that extra data. Close the
browser, which should return you to the StudentResults.asp page in FrontPage.
   1. We will begin by double clicking anywhere in the yellow cells labeled Database Results
      region (one marks start and the other marks end)
   2.   You may get a dialog box warning you that your re-entering the Database Results
        Wizard. Click on OK.
        (We are now back in the Database Results Wizard with all the selections we made in creating this results
   3. Leave the defaults on the first screen. Click on next.

                                                       6 / 17
   4. Leave defaults. Click on next.
   5. Click on the “Edit List…” button.
       (We will now remove some of the fields from our report)
   6. Remove Remote_Computer, User_name, Browser_type and Timestamp from the
      Displayed fields box.
   7. Click on OK and Next
   8. Leave defaults and click Next
   9. Click Finish
   10. Click Yes on the question of regenerating the Database Results region.
   11. Save your work and Preview in Browser.
You now have a much cleaner looking output. You will now notice that the entries you made
did not have any classperiod information. You will also notice that the labels at the top of the list
might not be as clean as we like them to be. If you close the browser and return to FrontPage
you can actually change the titles listed at the top of the table.
   1. Edit the title FirstName, LastName and classPeriod. We will just and some space and
      capitalization where needed. (First Name, Last Name and Class Period)
   2. Save your work and preview in browser
You could have actually changed these titles to completely different words because the
connection to the database actually occurs in the Database Results region. (The area between the
yellow cells) This is area were you need to be careful what you change. In fact, you should
probably leave this area alone until you become more experienced.
Well, congratulations are in order. You have now managed to build an online database with web
page form submission and a simple web results page. Pat yourself on the back and this is
probably a good time to take a break. At least stand up and stretch a bit before you move on the
next section.

More Form Elements

We now need to cover some different kinds of form elements. In the previous section all we
covered is textboxes.

Let’s return back to the Student Information Form page and add some more form elements.

   1. Make sure your browser window has been closed
   2. Open or return to the StudentInfo.asp file
   3. Click below the table and above the “Submit” “Reset” buttons(inside the form) to place
      the insertion point there.
   4. Select Insert – Horizontal Line from the menu and hit the Enter key
       (Just to create some visual separation)

                                                   7 / 17
5. Type and Center Extracurricular Activities and press the enter key
6. Insert a table with four rows and two columns 60% width and centered
7. Click in the first cell and select
   Insert–Form–Checkbox from the menu
   bar or the checkbox button from the
   docked form menu
8. Press the spacebar twice and type Band
9. Double click on the check box to set the properties
10. Name is band, Value is Yes, it should be “Not Checked”
    and the tab order should be 8. Click OK
   (You can set the value to equal what every you want it to be if the user
   checks this box and you can set the box to be checked by default)
11. Press the tab key to jump the next cell
12. Now repeat steps 7-11 for the following:
      Label / Field Name                Tab           Label / Field Name      Tab
               Choir                      9                 Basketball        10
             Football                    11                   Baseball        12
               Track                     13                    Tennis         14
                UIL                      15

   Your table Extracurricular Table should look like the following:

13. Add another centered heading below this table called Gender and press enter
14. Select Insert–Form–Option Button from
    the menu bar or the “Option button”
    button from the docked form menu
15. Press the spacebar twice and type Male
16. Double click on the option button sometimes referred to
    a radio button and set the properties as follows;

   Group Name=Gender, Value=Male, Not Selected, Tab
   Order 16. Click OK

                                                  8 / 17
17. Place five spaces after the word Male and add another
    radio button for Female with the Group Name
    remaining as Gender, the Value changed to Female
    and Tab Order set to 17.
   (With radio buttons of the same group name, only one value can
   be selected and that is what is submitted to the database)
18. Add a new centered heading for Class Period below the gender buttons and press the
    enter key
19. Select Insert–Form–Drop Down Box from the menu bar
    or the “Drop Down Box” button from the docked form
20. Double Click on the drop down box and set the
    properties as follows; Name=classPeriod, Height=1,
    Tab Order=18, Allow Multiple Selection=No
   (Unlike the Option Button you can have multiple selection with this
   field type if you set the allow to Yes)
21. To add possible choices from which your visitor can
    select from you will click the Add button. Add the
    following items.
   •   Choice = Make a Selection
       Specify Value = Unchecked
       Initial State = Selected
   •   Choice = First
       Specify Value = Unchecked
       Initial State = Not Selected
   •   Choice = Second
       Specify Value = Unchecked
       Initial State = Not Selected
   •   Choice = Third
       Specify Value = Unchecked
       Initial State = Not Selected
   •   Continue through Sixth
    (You can display a choice to user such as First and submit something else to the database such as 1 by
   selecting “Specify Value:” and assigning that choice “1”)
22. Now to insure that a choice is actually made here we will set some data validation rules.
    Click the Validate Button

                                                9 / 17
   23. Select the “Disallow First Choice” check box
       (This will reject a database submission if a selection other than
       “make a selection” is not made)
   24. Type Class Period in the Display name: box
       (When the user gets an error, they will be told with data box
       didn’t meet the rules and this is the name of the data box that
       will be displayed)
   25. Click OK and OK
   26. Save your work
   We have now added some additional fields that are not in the database so we need to update
   the database.
   27. Right click in the form and select Form Properties. Click on Options and select Update
   28. Click OK, OK, OK
   29. Save your Work and preview this is browser.
   If you were to make some more entries this additional data will now be collected in your
   Please note that if you change the field names on your form and then perform a database
   update, the database will create anther field with this new name. The original field in the
   database does not get updated.
   I understand that this has probably already been a lot of information, but this has not been a
   difficult task. All you have to do is remember these steps.

Adding Search Capabilities to Your Database Results Pages

Earlier when we created a results page, our output was every single record in the database. It
sure would be nice if we could have some search parameters to narrow down the output. Well
guess what, that is what is up next.

   1. Let’s gets started by making sure you have no preview browsers open.
   2. Open a new page and save it ClassPeriodQuery.asp
   3. Type in the heading Class Period Report centered with a heading one style at the top of
      your new page and press the enter key.
   4. Select Insert – Database – Results from the menu bar
   5. Use the existing connection (studentInfo) and click Next
   6. Record Source is still the “Results” table
   7. Click the “Edit List” button and remove all the fields but
      FirstName, LastName and ClassPeriod. Click OK.

                                                     10 / 17
8. Click the “More Options” button and in the More Options dialog box click the “Criteria”

9. In the Criteria dialog box Click the “Add” button and set the criteria as listed in the 3rd
   box above
10. Click OK for Add Criteria, Click OK for Criteria
11. From the More Options dialog box click on Ordering and set ordering as LastName First
    and FirstName Last by first adding LastName to the Sort order box and then add
12. Click OK for Ordering
13. Click OK for More Options and Next on the Database Results Wizard
14. Accept the defaults here and click Next
15. Here you will set the options as follows:

                                                             (The Add search form option will add a text
                                                             box where you visitor will enter their choice
                                                                     for the search parameter)

16. Click Finish, save your work and preview this form in your browser
17. Type in a class period and press the submit query button
   (You need to remember that the database contains the words first, second, third not 1,2,3 or 1st 2nd, 3rd)
18. Type in another class period and click the submit query button again
19. Close the browser

                                                 11 / 17
Let’s now create another database results page that has two search criteria.
   20. Create another new page and save this as queryByName.asp. Follow the steps above
       and in step 8 you will add two criteria, FirstName equals FirstName and
       LastName=LastName. The rest of the steps should be the same.
   21. Save this work and preview in browser
   22. Search for Mark Henson, Kim May and Terry Deluna
   23. Close Browser

Search Page that Submits Information to another Page

If you look at the result pages with search capabilities you will notice that the search query
parameters and the resulting search are both on the output page. You can eliminate the search
parameters from the output by having your search query parameters sent to another page where
only the results will be displayed. So let’s begin.

   1. Open a new page and save it as searchByName.asp
   2. Follow the steps in the previous section to create a form that provides two search criteria,
      FirstName and LastName.
       (Take querybyname.asp and save as searchbyname.asp to save time)
   3. Now you will need to create a new page and save it as searchResults.asp
   4. On the searchByName.asp page, click anywhere in the results table and select Table–
      Select–Table from the menu
   5. Select Edit–Cut from the menu
   6. Return to the searchResults.asp page and select Edit–Paste from the menu to paste the
      results table onto this page
   7. Return to the searchByName.asp page and right click in the form (inside the dotted box) and
      select Form Properties
   8. Select the “Send to Other” radio button and click the Options button

   9. Set the options as pictured the second picture above.

                                                 12 / 17
   10. Click OK for the Options dialog box, Click OK for form properties and save both pages
       (File-Save All from menu)
   11. Open the searchByName.asp page in a browser and enter the first and last name of
       someone you have put in the database
   12. Press the enter key to view the results
   13. Click the “Back” button on the browser and enter another name in the database
       (Good web page development rules dictate that you should place a link on the results page back to the
       Search page)

Search Page that uses Drop Down and Submits Values to another

As I mentioned earlier when we were searching by Class Period, your visitors may have a
problem finding any of the information because they entered 1st instead of “first” or 2nd instead
of “second” … This is a very common problem and this can be avoided by using a dropdown
box with a list of only the acceptable choices. Let’s give this a try.

Now I can just enter a drop down list in a form that has the choices but it would so nice if this list
was dynamically created from a database as well.

   1. Open the studentInfo database
       (StudentInfo.mdb in the fpdb folder)
   2. With the Table Objects selected create a new table in design view by clicking on the new
      button and then selecting Design View in the New Table dialog box and clicking OK.

   3. This table will need only one text field named ClassPeriod and you will save the table
      under the name ClassPeriodTable (select no for creating a primary key)

                                                 13 / 17
4. Open the table in datasheet view by just double clicking on ClassPeriodTable and enter
   First, Second, Third, Fourth, Fifth and Sixth for the classperiod values

5. Close the table and database
6. Create two new pages and naming one SearchByClass.asp and the other
7. On the first page (searchByClass.asp),
   select Insert–Form–Form from the menu
   or use form button on the docked form
   menu bar and press the enter key a couple of times
8. Click near the top and inside the form and type and center Please make class period
   selection and press the enter key
9. Select Insert–Database–Results from the menu
10. Use an existing database connection StudentInfo as the connection listed and click on
11. Record source radio button selected with ClassPeriodTable as record source
    Click on Next
12. Accept defaults (no changes)
    Click Next
13. Set this screen as displayed below and click next

                                         14 / 17
14. Click Finish
15. Right click inside the form and select Form Properties
16. Select the “Send to Other” radio button and click the Options button

17. Set the options in accordance with the second illustration above
18. Click OK for the options dialog box and OK for the Form properties dialog box
19. Save your work
20. Open or switch to the searchByClass2.asp file
21. Select Insert – Database – Results from the menu
22. Select the studentInfo existing database connection on first screen. Set Results as the
    record source for the second screen. On the third screen Edit the list to only include
    FirstName, LastName & ClassPeriod information. Under More Options set the Criteria to
    ClassPeriod = ClassPeriod. On the fourth screen of the wizard set the formatting options
    to List-one field per item and remove Add Labels, leave horizontal separator and select
    Paragraphs for list options. On the last screen select to display all records together and
    remove Add search form.
23. Return to or open the searchByClass.asp file, preview in browser and select a class
    period. Click the submit button.
24. Cool!!!!!!!!!!!!!!!!!!!!!!
    Close the browser.
This is probably another good time to take a break.

                                         15 / 17
Create a Form that Submits to a Form that Submits to a Database

The following forms will be the most complicated that you have created so far. What we have
here is a form that will collect data from one database and place it in a new form which you will
add additional information to and then submit to another database. Does this sound confusing?

(You could be running a sales company where you already have a customer base with all the customer contact info
in one database and as they make an order the contact info is pulled from this database and placed on the order
form automatically and the order is then submitted to the orders database.)

    1. Create a new file and save it as silly.asp
    2. Insert–Form–Form from the menu or use
       form button on the docked form menu bar
       and press the enter key a couple of times
    3. Type and center Select Student at the top of the form and press enter
    4. Place two separate database results inside this form.
        •    Type First Name: followed by 2 spaces
             Select Insert – Database – Results from the menu
             Use existing database connection – StudentInfo
             Use the Results Record Source
             Edit list

        •    They will both use the studentInfo database connection, the Results for the record
             source and the field list is edited to include only FirstName, LastName, and
             ClassPeriod. Step 4 of the wizard is set to Drop-Down List with FirstName for
             “Display” and FirstName for “Submit” values on the first database results and
             LastName for both “Display” and “Submit” values on the second database results.
             Leave the default values for step 5.
    5. Set the form properties to send to other with the options set to Post the data to silly2.asp
    6. Save your work
    7. Create a new file and save it as silly2.asp
    8. Insert a database results into a new form on this page, select the studentInfo connection,
       the Results record source, include only FirstName, LastName, and ClassPeriod in the
       field list. Set the criteria under more options and step 4 of wizard in accordance with the
       illustrations below.

                                                    16 / 17
9. On step 5 of the Database Results Wizard make sure to select the Display all records
   together radio button and uncheck Add search form
10. If your Database Results region on the page contains a submit query and reset button,
    select and delete them.
11. Click below the database results and create a 4-row 2-column table with these questions
    and a drop down list for each question. The drop down lists should be named toe, tongue,
    finger, and bellybutton.

        Which one of your toes is the longest?
        Can you roll your tongue?
        Can you bend any finger at the first knuckle?
        Is your belly button an inee or an outee?

12. Set the form properties to Send to database under options create a new database (silly2)
13. Save all your work
14. Open the silly.asp file in the browser and select a name. (Remember, the first and the last
    name must be a matched pair.) Submit the form.
15. On silly2.asp, fill out the additional information for the person that you selected on
    silly.asp and then click the submit button to send that data to the silly2 database.
16. Open the silly2 database and verify that the information is there.

                                           17 / 17

To top