To develop a customizable system we need to, as the saying goes, "look back to look forward." For starters, we should examine those tools that we are currently provided with to see what we can improve and innovate on. We'll start with an examination of\ Microsoft's solution, the Ad Rotator (To learn more about the Ad Rotator, be sure to read: Using the Ad Rotator!).

The Microsoft Solution: The Ad Rotator
Microsoft bundles their Ad Rotator component with IIS. This COM component provides functionality to load banners from a text file with the banner definitions, including the alternate text, image size and image location. The component only has a few properties and one method. These are outlined below, with a brief description of their usage and functionality, in the table below:

The banner definition file takes the following form:

REDIRECT url
WIDTH integer
HEIGHT integer
BORDER border
*
imageURL
linkURL
alternate text
impressions

The first line, REDIRECT url, refers to the page that the produced anchor tag must link to and pass the banner's link URL to as a querysting. This is because the Ad Rotator component does not produce a link directly to the site, but rather to an ASP page that you must develop, which can simply redirect to the URL passed in the querystring, or perform logging operations.

The WIDTH parameter specifies the value to be inputted into the WIDTH attribute of the produced image tag, in pixels. The HEIGHT parameter specifies the value to be inputted into the HEIGHT attribute of the produced image tag, in pixels. The BORDER parameter specifies the value to be inputted into the BORDER attribute of the produced image tag, in pixels. The asterisk (*) indicates the start of the actual banner definitions. imageURL is the location of the banner file (for example, http://www.somesite.com/images/banner_large.gif). linkURL is the page/site that the banner should link to (eg. http://www.somesite.com). alternate text is the text that will be placed in the ALT attribute of the image text (eg. "Come see Somesite's services!"). impressions is the ratio of hits that the banner should receive. It has limit of 10000. This value is relative to the amount of other banners, and their respective impression weightings. If there are three banners defined, with weightings 2, 3 and 5, the first banner will be displayed 20% of the time, the second 30% and the third 50%.

Implementation
The first thing that you need to do is create the banner definitions file (call it adrot.txt if you intend to copy-and-paste the code and create the system while you read). I firmly believe in learning through examples, so below is a sample banner definition file with hypothetical banners and links:

REDIRECT redirect.asp
WIDTH 480
HEIGHT 60
BORDER 0
*
http://www.somesite.com/banner.gif
http://www.somesite.com
Visit Somesite Now!
4
http://www.anothersite.com/banner.gif
http://www.anothersite.com
Come on over to Anothersite!
4
http://www.yetanothersite.com/banner.gif
http://www.yetanothersite.com
Yetanothersite.com ?your online widget retailer
2

In the above example, somesite.com and anothersite.com's banners will be displayed 40% of the time each and yetanothersite.com's banner will be displayed 20% of the time. Now we need to create the ASP to insert the above banner. This code will be added to each page that will have a banner displayed on it. It would probably be more suitable to place it in an include file, to make administration easier, especially if you need to change something, but here it is:

<%
  Dim objAd
  'Create the component
  Set objAd = Server.CreateObject("MSWC.AdRotator")
  
  'Get the ad from adrot.txt and write the link
  Response.Write objAd.GetAdvertisement("adrot.txt")

  'Destroy the object
  Set objAd = Nothing
%>

This code simply generates the link and image tag and writes it to the page. You then need to create the redirection file which will redirect the user to the link in the linkURL section of the banner definition file, as well as perform click-thru logging functions, if required. For this example, I will just perform the redirection. (If you are coding this app as we go along, call this file redirect.asp)

That's it! All the hard work of randomly selecting a banner ad is done for you by the component. (For those of you who don't want to copy-and-paste the above code into the respective files, a download for this sample is provided at the bottom of the article.) However, this ease-of-use, as is usually the case, comes with the price of inflexibility. With this system in place, how to you monitor banner impressions (which banner advertisers often demand)? And how do you handle different categories of banners for different parts of a site? And what if a client deposits his payment and wants his banner running NOW and you're on a week holiday half a world away from your Web server? (My solution only helps if you can get to an Internet Cafe, but if you couldn't you wouldn't have gotten you're client's e-mail informing you to make the change anyway) If you've got 5,000 clients advertising, administering that text file is going to become really tedious. The solution ?an architecture designed to be flexible and scalable to large volumes of data ?a database.

Now that we've looked at how to create a simple banner rotation system using Microsoft's Ad Rotator component, it's time that we look at a more customizable, powerful solution: a custom, data-driven banner rotation application. In Part 2 we'll examine one way that one can go about creating such a system!

The Database Solution
There are two primary advantages of using a database to store the banner definitions. Firstly, a database is flexible. In this article I will only cover some of the things that you will be able to do by using a database as a backend that you would not be able to do (or at least without any degree of difficulty or impracticality) using the Ad Rotator component and its text file backend. The second major advantage is that it is very easy to build a Web-based administration system for the banners. This makes the administration of the system accessible from anywhere in the world, and you no longer need to worry about version control of the banner definitions.

The Basic Database Table Structure
We will expand on the table structure later, but for now we only require one table ?Banners ?which will contain the banner image location, the link URL and the alternate text. We obviously want a unique ID for each banner, for administrative and logging purposes, so here's the final structure:

The Banner Rotation Code
Again, I will expand on this later, but for the moment we'll keep this code to the absolute minimum so you can easily understand the basics of the system. What we want to do is grab all the banners definitions out of the Banners table and store them in an array. We then work out how many banner definitions there are, and generate a random number accordingly (using the number of definitions as a maximum). This will enable us to input an image (IMG) tag with the randomly chosen banner into the page (using the random number as an index in the banner definitions array).

<!-- #INCLUDE FILE="adovbs.inc"-->
<%
  Dim objRS, arrBanners, intMax, intRand
  Set objRS = Server.CreateObject("ADODB.Recordset")
  objRS.Open "SELECT ImageSrc, LinkURL, ALT FROM Banners", _
        "DSN=BannerRotator", adOpenKeyset, _
        adLockOptimistic, adCmdText
  intMax = objRS.RecordCount
  arrBanners = objRS.GetRows()
  objRS.Close
  Set objRS = Nothing

  Randomize
  intRand = Round(Rnd(intMax) * (intMax - 1))
%>
<A HREF="<%=arrBanners(1,intRand)%>" TARGET="_top">
  <IMG SRC="banners/<%=arrBanners(0,intRand)%>" WIDTH="468"
       HEIGHT="60" BORDER="0" ALT="<%=arrBanners(2,intRand)%>">
</A><BR>

Note: It is important not to omit setting the CursorType, LockType and CommandType properties of the Recordset object due to the use of the RecordCount method later on. I have included them in the Open method of the Recordset object.

This code also assumes that all your banner images are stored in a subdirectory of the folder/directory that this file is in, called /banners. This code is sufficient, so that if you create the database and BannerRotator DSN and add a few entries to the Banners table, it will work. However, this code does not offer any advantages over Microsoft's AdRotator component, other than that you can easily build a Web-based interface for adding, editing and removing banner definitions. I will cover the Web-based admin section later, but for now I'm going to start showing you what you can do with this database backend.

Banner Categories
Most larger websites that use customized banner rotation systems have different categories of banners for different sections of their sites. For example, Yahoo! shows web hosting-related banners when you visit its Web Hosting section in its directory, and golf-related banners when you visit its Golf section. This can be a major selling point if you are aiming to sell advertising, as you can offer advertisers targeted ads, so that all of the impressions you're charging them for are being viewed by people in their target audience.

The best way to go about this is to create a table that will store the list of categories, and then be able to link it to the Banners table. We'll call the new table Categories, and add one more column to the Banners table, CategoryID, a foreign key to the primary key in the Categories table. Here are the new banner database table definitions:

We now need to change our code to incorporate this enhancement. Firstly, we need to know which category a specific page is going to use for its banner rotator. I'm going to assume that it is passed as a querystring variable called catid. I will discuss other options for automatically determining the category later on.

<!-- #INCLUDE FILE="adovbs.inc" -->
<%
  Dim objRS, arrBanners, intMax, intRand
  Set objRS = Server.CreateObject("ADODB.Recordset")
  objRS.Open "SELECT ImageSrc, LinkURL, ALT FROM Banners " & _
             "WHERE CategoryID = " & Request.QueryString("catid"), _
             "DSN=BannerRotator", adOpenKeyset, _
             adLockOptimistic, adCmdText
  intMax = objRS.RecordCount
  arrBanners = objRS.GetRows()
  objRS.Close
  Set objRS = Nothing

  Randomize
  intRand = Round(Rnd(intMax) * (intMax - 1))
%>
<A HREF="<%=arrBanners(1,intRand)%>" TARGET="_top">
   <IMG SRC="banners/<%=arrBanners(0,intRand)%>" WIDTH="468"
        HEIGHT="60" BORDER="0" ALT="<%=arrBanners(2,intRand)%>">
</A><BR>

Let's assume you're building a web directory, and your Categories table looks something like this:

Your Web directory's home page would include links that look something like this:

You would also include code that says that if no category is specified (either because you forgot to link to the file with a querystring, or you did it intentionally), the banner rotator will pull a banner from any category. You would do this by adding an IF...THEN condition to generate the SQL query. With that change, our code would now look like:

<!-- #INCLUDE FILE="adovbs.inc" -->
<%
  Dim objRS, arrBanners, intMax, intRand, strSQL
  Set objRS = Server.CreateObject("ADODB.Recordset")
  strSQL = "SELECT ImageSrc, LinkURL, ALT FROM Banners"

  If CInt(Request.QueryString("catid")) <> 0 Then
    strSQL = strSQL & " WHERE CategoryID = " & _
             Request.QueryString("catid")
  End If
  
  objRS.Open strSQL, "DSN=BannerRotator", adOpenKeyset, _
             adLockOptimistic, adCmdText
  intMax = objRS.RecordCount
  arrBanners = objRS.GetRows()
  objRS.Close
  Set objRS = Nothing

  Randomize
  intRand = Round(Rnd(intMax) * (intMax - 1))
%>
<A HREF="<%=arrBanners(1,intRand)%>" TARGET="_top">
    <IMG SRC="banners/<%=arrBanners(0,intRand)%>" WIDTH="468"
         HEIGHT="60" BORDER="0" ALT="<%=arrBanners(2,intRand)%>">
</A><BR>

That wraps up this first part of a two part series. In the second part (which should be available by January 15th, 2001), we'll examine how to enhance with our banner rotation script so that certain banners appear when certain search terms are used to search the site! Until then, happy programming!

discuss this topic to forum