A Complete Database Accessing Web Site!

<p><font face="Verdana"><b>Background</b></font>
<p><font face="Verdana">     I set up the admissions site at
my college as what I like to think of as a web application as opposed to a bunch
of web pages. It is an ASP application that pulls all of its information- menu
titles and links, sub menu titles and links, and page content- from a database.
The information retrieved from the database is determined by parameters passed
through the query string. All of the menu links on the page link back to this
main page with a query string. For example, when you click on a main menu link
on the nav. bar, the page reloads with the main menu item's corresponding
content and all of the sub menu links listed under the main menu link. If you
are interested in how the tables are set up or the links are generated, please <a href="mailto:cyberbob@freewwweb.com">email
me</a>.</font></p>
<p><font face="Verdana">There are administration pages that allow non-computer
savvy people to add and remove menu items and content to and from the database,
thus modifying the web site. These people were not satisfied with the menu items
simply being displayed in alphabetical order. They wanted to come up with their
own order.</font>
<p><font face="Verdana"><b>How it works</b></font></p>
<p><font face="Verdana">I started out by adding an additional numerical field to
the menu tables in the database called <code>zorder</code>. A text box
corresponding to this field was added to the admin pages and the main page was
modified so that when it loaded the menu links, it sorted them by their <code>zorder</code>
field first and then their title field, whereas before they were sorted only by
the title field. If the user left the <code>zorder</code> field blank, I could
have done one of two things: give that item the next available <code>zorder</code>
number making it the last in the list; or give all items with blank <code>zorder</code>
values the same maximum <code>zorder</code> value thus making them all be
displayed alphabetically at the end.</font>
<p><font face="Verdana">I would have liked to just leave it at this, but I
foresaw two problems: these same non-computer savvy people having problems with
the idea of using a <code>zorder</code> field on the admin pages, and I would
get tired of manually updating all of the <code>zorder</code> values every time
I wanted to insert a menu link.</font>
<p><font face="Verdana">First of all, I labeled the <code>zorder</code> field on
the entry and edit forms <code>Menu Position</code> and I used a drop down box
of numbers to prevent users from entering erroneous values. The drop down box
contained the numbers from 1 to the maximum <code>zorder</code> number and
defaulted to the last possible number (1 plus the maximum <code>zorder</code>
numer) when they were adding a new item and to the existing <code>zorder</code>
number when they were modifying an item.</font>
<p><font face="Verdana">If the user enters a <code>zorder</code> number that is
already in use, you will have to update the following <code>zorder</code>
numbers accordingly. Given that <code>selectedzorder</code> refers to the <code>zorder</code>
number (menu position) that the user selected for the current record then the
following SQL query will perform the required update on the following menu
records:</font>
<p> 
<table border="0" width="95%">
 <tbody>
 <tr>
 <td bgColor="#cccccc" width="100%"><code><font face="Verdana">Update
 main_menu set zorder=zorder+1where zorder >= selectedzorder;</font></code></td>
 </tr>
 </tbody>
</table>
<p><font face="Verdana">To retrieve the maximum <code>zorder</code> value for
use in your drop down box you could use this query:</font>
<p> 
<table border="0" width="95%">
 <tbody>
 <tr>
 <td bgColor="#cccccc" width="100%"><code><font face="Verdana">Select
 Max(zorder) from main_menu;</font></code></td>
 </tr>
 </tbody>
</table>
<p><font face="Verdana">The same rules apply to the sub menu table with obvious
modifications.</font>
<p><font face="Verdana"><br>
<b>Installation</b></font>
<p><font face="Verdana">To install the web application, just un-zip or un-Rar
the attatched file into a directory on your ASP-enabled server. I just have mine
in the root of my PWS.</font></p>
<p><font face="Verdana">If you set it up like I did you would access the demo
home page like this: http://localhost/default.asp<br>
and the admin page like this: http://localhost/admin/default.asp</font>
<p><font face="Verdana">The database is stored in the admin directory. Password
protect the admin directory to protect the admin pages and the database. From
there it's all pretty self explanatory.</font>
<p><font face="Verdana">New features allow you to make that menu hierarchy as
deep as you like, and the order of menus is now changed with little arrow
buttons. I also re-wrote the interface on the front of the admin page and added
a site statistics page. Plus, you can cut and paste entire menu trees! This
makes it so easy to reorganize your site.</font>
<p><font face="Verdana">I'll apologize in advance for the lack of commenting and
some of the obscure code in places. I usually don't go to a lot of trouble on
comments on something this small and straight-forward not to mention it's the
product of quite a few late nights. If you find any blatently bad coding, please
let me know.</font></p>
<p><font face="Verdana">I worked pretty hard on this; if you decide to use part
of this code or the entire application please give credit where credit is due. I
am a very poor college student who likes computer toys and fine beers (a lot),
so if you decide to use this for commercial purposes (i.e. your business's
intranet or Internet site) you have to pay me something out of common courtesy
and your profits (heh, heh, j/k).</font>
<p><font face="Verdana">In any case, I'd love to see how you guys apply this. <a href="mailto:cyberbob@freewwweb.com">E-mail</a>
me with links and descriptions! If you think this calls for another article, let
me know and I'll throw something together and submit it.</font>
<p><font face="Verdana">Thanks for all of you interest in my first article!!</font>
<p><i><font face="Verdana">Note: you must have ADO 2.0 or greater installed,
since the OLE-DB driver is used in <code>connect.inc</code>. You can <a href="http://www.microsoft.com/data">download</a>
ADO 2.0 for free, or you can just alter <code>connect.inc</code> so it uses a
DSNless connection or a System DSN.</font></i></p>