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

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

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!

    - continued -

    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


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

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