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

By Peter McMahon

Part 2 of a Two-Part Article
This article, Extending the Customizable Banner Rotation System, is the second part of a two-part series. The first part, Developing a Customizable Banner Rotation System, discusses important topics leading up to this article. Be sure to have read the first part before tackling this article. Additionally, for more information on Banner Rotation Systems, refer to the Banner Rotation System Article Index.

- continued -

Banner Categories in a Search
When you enter a search in Yahoo!, the banner in the results page pertains to the search criteria that you entered. This is another way of convincing advertisers that their banner will be viewed almost exclusively by their target audience, and thus they are getting better value for money. You too can achieve this by running two searches internally each time a person runs a search on the site. However, you'd need to add another column to the Categories table first to store a list of keywords which would increase your chances of a match (you could run the search against the name of the category). Your Categories table would now look like this:

Categories Table
FieldType [Access]Type [SQL Server]
CategoryIDAutonumberInteger [Identity]
NameText [255]varchar [255]
KeywordsMemovarchar [1000]

You would now add appropriate keywords for each category:

1Computerscomputers internet software hardware
2Sportsport golf soccer olympics athletics
3Mediamedia news magazines music mp3s

Naturally you would add a lot more keywords than I have. Now to the code. I'm only going to build a quick-and-easy engine as the topic of this article is a Customisable Banner Rotator, not a customisable search engine. I'll assume that you have a table called SearchEntries in the same database as we've added the Banners and Categories tables to, that contains the list of links on your search engine. Here's a basic idea of the table layout for it:

Name URL Description Keywords
SearchEntries Table
FieldType [Access]Type [SQL Server]
EntryID Autonumber Integer [Identity]
Text [255] varchar [255]
Text [255] varchar [255]
Text [255] varchar [255]
Memo varchar [1000]

On our form that references to our search.asp file, we'll assume that it has a text inputbox named txtSearch and we use it to query our SearchEntries table using a SELECT SQL query on the Keywords column. That doesn't change. However, you need to get a CategoryID to select a banner with. This you do by running a SELECT SQL query on the Keywords column of the Categories table using the search text. You then use this value when you randomly grab a banner from the Banners table. Here's what our code would look like:

<!-- #INCLUDE FILE="adovbs.inc" -->
  Dim objConn, objRS, intCat, arrBanners, intMax, intRand, strSQL
  Set objConn = Server.CreateObject("ADODB.Connection")
  objConn.Open "DSN=BannerRotator;"
  Set objRS = objConn.Execute("SELECT CategoryID FROM Categories " & _
                  WHERE Keywords LIKE '%" & Request.Form("txtSearch") & "%'")
  If objRS.EOF Then
    intCat = 0
    IntCat = objRS("CategoryID")
  End If

  strSQL = "SELECT ImageSrc, LinkURL, ALT FROM Banners"

  If intCat <> 0 Then
    strSQL = strSQL & " WHERE CategoryID = " & intCat
  End If

  objRS.Open strSQL, objConn, adOpenKeyset, adLockOptimistic, adCmdText

  intMax = objRS.RecordCount
  arrBanners = objRS.GetRows()

  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)%>">

<B>Search Results</B><BR><BR>
  objRS.Open "SELECT Name, URL, Description FROM SearchEntries " & _
      "WHERE Keywords LIKE '%" & Request.Form("txtSearch") & "%'", _
      objConn, adOpenKeyset, adLockOptimistic, adCmdText

  While Not objRS.EOF
    Response.Write "<A HREF=" & objRS("URL") & ">" & _
                   objRS("Name") & "</A><BR>" & objRS("Description") & _

  objRS.Close         'Clean up...
  Set objRS = Nothing
  Set objConn = Nothing

Again I stress that this is by no means a definitive reference on how to build a search engine, so please don't treat it as such. However the method of determining which category of banner to display should prove sufficient for the needs of most websites. (For more information on building a search engine for your site, check out: Searching 4GuysFromRolla.com and Searching Through the Text of Each File on a Web Site!)

In all of our discussion of this banner roation system, we have failed to mention how to record statistics for the number of times a banner has been displayed and the number of times a banner has been clicked. In Part 2 we'll look at how to accomplish this!

  • Read Part 2!

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