To read the article online, visit http://www.4GuysFromRolla.com/webtech/092700-1.3.shtml

An Ingenious Banner Rotation System, Part 3

By Bill Graziano


  • Read Part 1
  • Read Part 2

  • In Part 2 we looked at creating an administration page to manage the various advertisements. In this part we'll look at displaying the ads!

    Displaying Ads
    I have a page called BANNER_Display.asp which contains a subroutine called BannerDisplay. This page executes a stored procedure called spBANNERDisplay01 which returns one randomly selected record from the BANNER_Ads table. My code to select a record looks like this:

    Declare @MaxValue int, @RandomNumber float
    Select @MaxValue = max(WeightScaled) from BANNER_Ads
    
    Select @RandomNumber = rand() * @MaxValue
    
    Select TOP 1 *
    From BANNER_Ads
    Where WeightScaled >= @RandomNumber
    Order by WeightScaled ASC
    

    I use the SQL Server function rand() to generate a random number. You can pass it a seed or have SQL Server determine a seed for you. It will use the same seed for each random number requested inside a batch. Since I only request one random number per batch I was ok using this approach. I multiply my random number times the maximum WeightScaled and use this to select my record. If I had code running to implement impression tracking I would insert a record into an impression tracking table right after the select. The WeightScaled field has an index on it to improve performance of this query.

    BANNER_Display.asp looks at the AdType field and decides whether to display HTML or build a linked image. That codes looks something like this:

    If Trim(bobjRS("AdType")) = "HTML" Then
      Response.Write bobjRS("HTML")
    End If
    
    If Trim(bobjRS("AdType")) = "Image" Then
      Response.Write "<a href=" & trim(bobjRS("LinkToURL")) & ">"
      Response.Write "<IMG SRC=" & trim(bobjRS("ImageURL")) & " BORDER=0 "
      Response.Write " WIDTH=" & trim(bobjRS("Width"))
      Response.Write " HEIGHT=" & trim(bobjRS("Height")) 
      Response.Write " ALT=" & Chr(34) & trim(bobjRS("ALTText")) & Chr(34)
      Response.Write "></a>"
    End If
    

    Well, there you go. A simple, quick way to build a slightly improved Ad Rotator. I plan to add features to this in the future including Advertisers, ad locations, impression and click tracking, impression and click caps and or course, some way to track revenue. I just never seem to run out of features :-) Enjoy!

  • By Bill Graziano


    Attachments:

  • Download the ASP pages and SQL script (in ZIP format)
  • Visit SQLTeam.com


  • Article Information
    Article Title: An Ingenious Banner Rotation System, Part 3
    Article Author: Bill Graziano
    Published Date: Wednesday, September 27, 2000
    Article URL: http://www.4GuysFromRolla.com/webtech/092700-1.3.shtml


    Copyright 2017 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers