July 2001--I have avoided serious database work my
entire computing career. This may or not have been a smart career
move, after all just about everything computing ends up being
database driven, but for some reason dBase, SQL et al just never
clicked for me. I struggled with tasks that others completed with
But eventually events catch up with all of us and
if you want to run with the big dogs you have to get off the porch
so how do you run with the pack?
Server platform discussions approach jihads and
I'm no expert. The server that this site runs on is Linux and it
certainly has it's benefits. But my clients use NT and I've never
used MySQL (the database engine that's frequently used on Linux) so
I've used Windows NT, SQL and IIS for getting data online. If you're
starting from scratch you'll want to consider all the options
available to you and get good advice and opinions from those who
live and breath such things. I'm not sure there's one right answer,
but for better or worse I'm NT centric on this issue.
If you don't have a local server you'll find a
huge selection of hosting companies that will provide you with the
tools you need either on a dedicated or shared server.
Hostindex is a great
resource for helping you narrow down your selection. Be careful not
only about the facilities they offer but also the connectivity they
offer. Often cut-rate companies will put you on an overloaded server
or their connection to the Internet is sadly lacking. I used to
recommend companies but they started out great and then fell apart
so you're on your own.
In this most simple form you need NT server (I use
4.0 but many of the hosting companies have moved to Windows 2000),
some sort of ODBC (open database connectivity) connection to your
data and some way of serving it up. Of course having some data will
IIS (Internet Information Server) in NT 4 makes
this relatively easy if you have FrontPage extensions installed and
use an Access database as your data source. In fact it's pretty much
just a point and click operation.
Copy/move your Access database file to your
FrontPage web folder. Create a new page in FrontPage or bring up
your standard template and move the cursor to a point on the page
and click Insert/Database/Results and that will start the database
wizard. Select new connection and select your database as the
Once your database is "connected" to FrontPage you
can select a record source. Next you'll see a dialog to edit the
fields. You'll probably want to do this. While you can delete fields
later on it's just as easy to remove fields you don't want displayed
Click more options/criteria. This will allow you
to create your selection criteria for your user search method.
Perhaps you want to users to be able to search your product catalog
on a field called product. In that case in the criteria box you'd
set program equals product. This will generate the search box and
generate the code to perform that function.
The next step is the results wizard and you can
play with the options but my personal preference (and the format I
see most often) is the "List - one field per item". If you're
displaying a catalog you may want to use the table option. You'll
probably want to bypass the option to "add labels" but your database
field names may be what you want to present. In either case you can
play with it.
The final step of the wizard is to generate the
search form. If you haven't selected anything in the criteria step
above you won't have this option so if it's not available back up
and set up some search criteria.
Save your page. FrontPage should warn you to save
it as an .ASP page and you'll want to do so. Publish it to your web
server and cross your fingers.
Log on to your web site and run the page and
search your database. Hopefully everything is working okay and
you're seeing results from your database.
I guess this is sort of subjective but I think the
"native" output from the search wizard is pretty much unusable or at
best ugly. Fortunately it's easy to reformat some of the output.
Using the List format I like to then group the
fields into a table. Move to the beginning of the first field name
(<<field1>> for instance) that displays in the Database Results box
and hit enter to create a blank line. Click tables/insert/table and
select the number of columns and rows you'll need to accommodate all
Now that your table is built left click the field
name (<<field1>>) and drag it to the position in the table where you
want it to display. After you've built the table with your database
fields you can right click the field name and change the font. Next
right click the cell, select cell properties and select a background
color for the cell.
Using this method you can make visually pleasing
output and use fonts and colors to highlight certain elements as
well as save screen real estate.
Adding a Drop Down Selection Menu
The above method gives the user a blank box where
they can type in data and it will search the database for a match.
If your users are like me and can't spell or if you have esoteric
items it may make more sense to present the user with a list of
pre-defined search items.
Right click on the form field and select Form
Copy the field name, then click cancel.
With the field still selected, use Insert/Form/
Drop Down Menu to replace the text field.
Right-click the drop down menu and select Form
Paste the form field name into the name field and
add your selections.
It is possible to populate a drop down list from a
database so if you have a lot of items you want to include you may
wish to explore that option. However I believe that a big list
defeats the purpose of a drop down menu and you may want to explore
some other type user interface to drill down into your database
(hire me, I need the money in this dot-gone environment!).
Multiple Search Criteria
Perhaps it makes sense to allow your customers to
search on multiple items. Simply use the criteria section and
FrontPage will automatically generate multiple input boxes. You can
use the above method of adding a drop down selection menu and create
two drop down menus or one free text entry box that can be used in
conjunction with a drop down menu.
Adding Value to Your Database Results
Suppose you have products in your database that
are represented on another web page on your site? This might be a
detailed product sheet or spec or even user instructions or a FAQ
page. How do you get your database results to point to that page?
Open your database and edit the field property to
include the URL to the page. I like to make the font bold just to
highlight it even more. Thus the entry for widget in field1 would
The <a href="widgets.htm"> part points to the
page. It may be that it's in another directory as the above assumes
it's in the root directory. If it's in another directory prefix the
page name with that directory such as "/products/widgets.htm" if
it's located in the products directory.
The <b> is html coding to turn on bold font,
"Widgets" is the text you want displayed and </b> and </a> turn off
bold font and the hyperlink respectively.
Right click on the field that field will appear it
in FrontPage, select Database Column Value Properties and put a
checkmark in the "Column Value Contains HTML" box. This won't effect
any non-html data but it will cause the Widgets entry to display the
html code as a hyperlink with bold font. If you see Widgets as it
was entered above rather than a bold hyperlink that's the problem,
the attribute for column values needs to be set.
The Bottom Line
Cool, you've done it! While it's not as fancy as
what you'd get if it was all hand coded you now have a very
presentable database search site that's a great leg up on presenting
your data online. It's something you can maintain yourself and it
didn't cost you a fortune to do it.
I spoke of alternate ways of allow you user to
drill down to the data, how do you do that? I like to use multiple
search features to narrow down the search step by step but that
involves passing parameters from search query to another and that
can be tricky. What if you want to add a search to one page, say
your home page but don't want the results displayed on that page?
And what about non-database data? You may have
manuals, product specs, frequently asked questions and other info
your customers could use but how do you get them on your web site
and easily located by your customers?
Next time we'll take on those tasks and even look
at some custom ASP coding.
Until then best in computing.