When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
Published: Wednesday, January 03, 2001

Developing a Customizable Banner Rotation System, Part 2

By Peter McMahon


  • Read Part 1

  • In Part 1 we examined one (of many) ways to create a banner rotation system: by using Microsoft's free Ad Rotator component. In this part we'll examine using a more customizable approach: a custom, data-driven app.

    - continued -

    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:

    Banners Database Structure
    FieldType [Access]Type [SQL Server]
    BannerIDAutonumberInteger [Identity]
    ImageSrcText [255]varchar [255]
    LinkURLText [255]varchar [255]
    ALTText [255]varchar [255]

    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.

    A Note from the Web Master...
    The RecordCount property is used to determine the number of records in the Recordset, which is then used as the upper bound for the random number picker (hence the need for the cursor type and lock type. However, this can be avoided by using the UBound function on the array (arrBanners).

    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:

    Banners Database Structure
    FieldType [Access]Type [SQL Server]
    BannerIDAutonumberInteger [Identity]
    CategoryIDNumberInteger
    ImageSrcText [255]varchar [255]
    LinkURLText [255]varchar [255]
    ALTText [255]varchar [255]

    Categories Database Structure
    FieldType [Access]Type [SQL Server]
    CategoryIDAutonumberInteger [Identity]
    NameText [255]varchar [255]

    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:

    CategoryIDName
    1Computers
    2Sport
    3Media

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

    <A HREF="computers/index.asp?catid=1">Computers & Internet</A><BR>
    <A HREF="sport/index.asp?catid=2">Sport</A><BR>
    <A HREF="media/index.asp?catid=3">Media</A><BR>

    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!

  • By Peter McMahon

  • Read Part 2 of this Two-Part Article!


    Attachments:

  • Download the complete source code in ZIP format
  • View the Banner Rotation Article Index
  • Read the Second Part of this article...


  • ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article