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

Sample Chapters
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Stump the SQL Guru!
XML Info
Author an Article
Print this page.
Published: Tuesday, January 09, 2001

Extending the Customizable Banner Rotation System, Part 2

By Peter McMahon

  • Read Part 1

  • In Part 1 we examined how to have particular banners appear when particular search terms where used in a site search. In this part we'll examine how to record impressions and click-through statistics!

    - continued -

    Recording Impressions and Click-throughs/Hits
    As previously discussed, the recording of impressions and click-throughs is vitally important to advertisers. Now that you've targeted advertisers' audiences, you need to be able to record how many people are seeing their banners, and how many people are clicking on them. It is obviously also a bonus if you can generate graphs for advertisers to see, based on these two statistics.

    Recording Impressions
    Firstly, you'll need to add another table to your database to store the impressions (ie. each impression has a separate entry). I suggest a layout like this:

    Banner_Impressions Table
    FieldType [Access]Type [SQL Server]
    ImpressionIDAutonumberInteger [Identity]
    BannerIDNumberInteger [4]
    IPText [255]varchar [255]

    Now you need to add an entry into this table each time a banner is displayed. Naturally you now need to pull out the BannerID from the banners table. Here's the revised code:

    <!-- #INCLUDE FILE="adovbs.inc" -->
      Dim objConn, objRS, arrBanners, intMax, intRand, strSQL, intBannerID
      Set objConn = Server.CreateObject("ADODB.Connection")
      objConn.Open "DSN=BannerRotator;"
      Set objRS = Server.CreateObject("ADODB.Recordset")
      strSQL = "SELECT BannerID, ImageSrc, LinkURL, ALT FROM Banners"
      If CInt(Request.QueryString("catid")) <> 0 Then
        strSQL = strSQL & " WHERE CategoryID = " & Request.QueryString("catid")
      End If
      objRS.Open strSQL, objConn, adOpenKeyset, adLockOptimistic, adCmdText
      intMax = objRS.RecordCount
      arrBanners = objRS.GetRows()
      Set objRS = Nothing
      intRand = Round(Rnd(intMax) * (intMax - 1))
      'Add entry
      objConn.Execute "INSERT INTO Banner_Impressions (BannerID, IP, DateAndTime) " & _
                      "VALUES (" & arrBanners(0,intRand) & ", '" & _
                      Request.ServerVariables("REMOTE_ADDR") & "', #" & Now & "#)"
      Set objConn = Nothing
    <A HREF="<%=arrBanners(2,intRand)%>" TARGET="_top">
        <IMG SRC="banners/<%=arrBanners(1,intRand)%>" WIDTH="468" 
             HEIGHT="60" BORDER="0" ALT="<%=arrBanners(3,intRand)%>">

    Note: If you are using SQL Server, please replace the Access date deliminers (# - hash) with the appropriate ones (' - apostrophe)

    It is important to note that I have adjusted the indices in the array to reflect the inclusion of the BannerID field in the resulting recordset and inherently, the array.

    Recording Click-throughs/Hits
    We'll also need a table to store banner click-throughs, or hits. This will take almost exactly the same form as that of the Banner_Hits table.

    Banner_Hits Table
    FieldType [Access]Type [SQL Server]
    HitIDAutonumberInteger [Identity]
    BannerIDNumberInteger [4]
    IPText [255]varchar [255]

    We now need to include an intermediatory step between when the banner is displayed and when the browser goes to the desired URL so that we can trap and record the hit. This is done by passing the BannerID and URL to another file using a querystring, which then logs the hit and redirects the user to the URL. Thus the banner is now displayed using the following link:

    <A HREF="hit.asp?BannerID=<%=arrBanners(0,intRand)%>&URL=<%=arrBanners(2,intRand)%>" TARGET="_top"><IMG SRC="banners/<%=arrBanners(1,intRand)%>" WIDTH="468" HEIGHT="60" BORDER="0" ALT="<%=arrBanners(3,intRand)%>"></A><BR>

    Notice that we are now linking not directly to the URL, but to hit.asp, which will look something like this:

      Dim objConn
      Set objConn = Server.CreateObject("ADODB.Connection")
      objConn.Execute "INSERT INTO Banner_Hits (BannerID, IP, DateAndTime) " & _
                      "VALUES (" & Cint(Request.QueryString("BannerID")) & ", '" & _
                      Request.ServerVariables("REMOTE_ADDR") & "', #" & Now & "#)"
      Set objConn = Nothing
      Response.Redirect Request.QueryString("URL")

    This simply adds a record to the Banner_Hits table, and then redirects the user to the specified URL.

    Web-based Administration
    I'm not going to go in-depth here, but suffice to say that all you need to do is add, delete and update the Banners table as you normally would, using ADO and SQL. The only two areas that you need to look out for are the uploading of the banner images to your server, and the deletion of these images when you delete a record from the Banners table. For the former, I strongly recommend the use of Persits Software's ASPUpload component (see links at the bottom of the article for information on obtaining it), although you can obviously use your regular upload component if you have done ASP uploads before. (For more information on uploading files from the client to the server, check out the Uploading Article Index!)

    Deleting items is done via the FileSystemObject COM component in the Scripting library. The basic procedure is that you grab the ImageSrc field out of the record that you're going to delete, check if this file does exist (it could have been deleted using FTP etc), and then go ahead and delete it if it does. You'd then proceed to delete the record. Here's a basic idea of what the code would look like (I'll assume that the ID of the banner to be deleted has been passed as a form variable called hidID):

      Dim objConn, objRS, objFSO
      Set objConn = Server.CreateObject("ADODB.Connection")
      objConn.Open "DSN=BannerRotator;"
      Set objRS = objConn.Execute("SELECT ImageSrc FROM Banners " & _
                           "WHERE BannerID = " & Request.Form("hidID"))
      If Not objRS.EOF Then
        Set objFSO = Server.CreateObject("Scripting.FileSystemObject")
        If objFSO.FileExists(Server.MapPath("banners/" + objRS("ImageSrc"))) Then
          objFSO.DeleteFile(Server.MapParh("banners/" + objRS("ImageSrc")))
        End If
      End If
      Set objRS = Nothing
      objConn.Execute "DELETE FROM Banners WHERE BannerID = " & Request.Form("hidID")
      Set objConn = Nothing

    For more information on deleting files with the FileSystemObject object, check out this popular FAQ at ASPFAQs.com: How can I delete a file from the Web server's file system?

    As you've hopefully seen from above, the advantages of developing your own custom banner system are very great indeed, and well worth the effort. However, there are many areas that this article has not covered, where new and innovative ideas can be applied. Such things as displaying banners according to the viewer's geographical location (based on IP address) haven't been discussed, and are very exciting ways in which you can offer your advertisers better value for money. Another way in which this method may be improved is by the encapsulation of most of this code into a COM object, although with the advent of ASP.NET, ASP effectively becomes a compiled medium, so most speed advantages will be lost, although the other advantages, such as ease of redistribution, still apply. I hope that I've managed to teach you the basics of the system though, from which you will be able to expand and modify for your own needs.

    Happy Programming!

  • By Peter McMahon


  • Download the complete source code in ZIP format
  • View the Banner Rotation Article Index

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