Sometimes, it's better to roll your own ...
Programming is more than click and drag.  


Even though the site is still heavily focused on Microsoft Access, I've been working and playing with other tools for a few years now.  I just never got around to writing about them here. 

After I got serious about sharpening my skills with ASP.NET, Microsoft's web building technology that blends HTML with .NET code to deliver dynamic web pages, it didn't take me long to think of ways that I could use it with this site. Drewslair.com has gone through a mini-evolution over the years, starting out in Microsoft Frontpage and then shedding all the FrontPage-specific features after Microsoft dropped support for the FrontPage extensions on Linux and my hosting service raised the possibility of dropping them in the future. This prompted me to learn about Cascading Style Sheets and Server-Side Includes and other fun stuff so I could keep the same look and feel. 

This time around, switching to ASP.NET meant moving away from FrontPage entirely and redoing the site in Microsoft Visual Studio.  It took awhile but it was a fun project and I learned a lot in the process.

I don't see myself needing to add user logins to the site anytime soon but I did have fun playing around with the navigation features and master pages that I could use to replace the Server-Side Includes that I've been using to share borders across the site for the past year or so. The tutorial that I was using advised using tables to setup the format for the master page that would be inherited by other pages but I quickly verified that the style sheet formatting worked just as well and I was able to keep the same look.

What I thought would be an interesting touch would be to display random quotes in the top border of the site next to the site logo. It looked kind of empty as I'd never come up with something to put there.

That's a big empty space.
(Click to view full size.)

I initially decided to store the quotes in a SQL Server 2005 database, pull them at random during the load event of the master page and display them using a Literal control which accepts literal text, including HTML tags, and inserts it into the page. By feeding the right HTML code to the control, I could make it display the quote text with a carriage return between the quote and the author's name and position itself at exactly the spot on the page I wanted. Before that, I had to decide how I was going to get the data that I needed from the database. Here are the steps I was going to follow.

  1. Count the number of records in the quotes table, indicating the number of quotes available.
  2. The quote IDs are sequential, so generate a random number between 1 and the number of quotes available.
  3. Go back to the database and get the record with a quote ID matching the number generated.
  4. If there's any error in retrieving a quote, manually display a quote that's hard-coded into the site but not present in the database.  This keeps the site working and lets me know there's a problem when I view the site.

Ideally, step #4 would then have the site send me a notification by e-mail of the problem and maybe write the details of the error to a logfile but that can come later. 

Visual Studio features a SQLDataSource control which makes binding data to a collection of page controls a breeze. You can throw this control on a page, configure it and have the controls reference it to display data. Saving and updating data back to the database is also ridiculously easy. The problem was that in order to get the steps above to happen in the right order, I needed to code the steps listed above in Visual Basic rather than plot them out through controls on the page and from what I can tell so far, the SQLDataSource was never really meant to be used from code. I was hopeful at first, seeing easy ways to change the control's SQL command but I never did find a way to get the data that it was retrieving.

That's when I decided to go back to being a programmer and write my own code to manage the connection to the database and accept SQL commands to return the data I needed in the form of string values and data tables. It took me longer than using the SQLDataSource but was much more satisfying and gave me the control I needed. Then I wrote the code in the Page_Load event of the master page to pass the necessary SQL commands to the data class, read the data coming back and display it on the page.  Any page that inherits the master page would then display a quote as shown below.

That's much better.
(Click to view full size.)

After playing around with this for awhile and seeing it work, I started thinking that an entire SQL Server database was a little much for a collection of quotes that was, for the most part, read-only.  It also required me to either login to the site control panel and go through a number of screens to add and edit quotes or setup a new page on the website just for that purpose and arrange the security so that nobody else could hack into it.  That's when I decided to change to an XML quotes file instead.  XML is a flat-file text format that uses custom tags very similar to HTML to format data in a way that can be easily translated to and from other database formats.  Here's a sample of the quote file in XML:

<Quote> 
<QuoteText>I love deadlines. I like the whooshing sound they make as they fly by.</QuoteText>
<Author>Douglas Adams</Author>
</Quote>

<Quote>
<QuoteText>Every composer knows the anguish and despair occasioned by forgetting ideas which one had no time to write down.</QuoteText>
<Author>Hector Berlinoz</Author>
</Quote>

<Quote>
<QuoteText>For disappearing acts, it's hard to beat what happens to the eight hours supposedly left after eight of sleep and eight of work.</QuoteText>
<Author>Doug Larson</Author>
</Quote>

As you can see, each quote block is marked by tags defining the data.  The names of these tags are completely at my discretion as long as they're consistent.  This results in a simple text file that I can easily update as needed.  The program can then import the data from this file into a table, using the tags as column names and follow roughly the same steps to select a random quote; count the number available, generate a random number between 1 and the number available and then display the quote corresponding with that number in the list.   There are fewer things that can go wrong since the program is dealing with a text file rather than going through another piece of software to access a proprietary database format.  It's probably faster, too.


I couldn't put this out live on the Web until I switched to a hosting service that supported ASP.NET.  I hated leaving my old service but in addition to the support for the new features, I gained some extra web resources and was able to setup two sites on the same hosting account.  I finally decided to have one for Drewslair.com and one for AndrewComeau.com (.net, .org ...).  AndrewComeau.com, which is now online, can host the new ASP.NET pages and Drewslair.com can remain the same for now to preserve the links on Google and other search engines.

 

© 2010, Andrew Comeau, except where otherwise noted. Material contained on this website should not be republished without permission from the author. Questions?  Comments?  You can e-mail me at this address.

Microsoft is a registered trademark of Microsoft Corporation in the United States and other countries.