An Ingenious Banner Rotation System, Part 3

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!