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

Rotating Banner Ads using a Database
By Vladimir Djacic


  • This is simple Ad rotation system based on ASP-SQL Server combination.
  • All Ad info (except images, which are asumed to be in current directory) is stored in SQL server table caled Banners.
  • The system is intended to provide easy random rotation and counting of Ad showing and clicks.

    Data structure
    First we will set up a table in the database called Banners and it will have the following structure:

    Banners
    BannerIDLong
    Imagevarchar(100)
    URLvarchar(100)
    Hintvarchar(100)
    ShownLong
    ClickedLong

    Here is some example data:

    BannerIDImageURLHintShownClicked
    1gausBanner.gifhttp://www.gaus.co.yuVisit our site11

    and here is how can you add table to your SQL database:

    create table Banners ( BannerID int identity PRIMARY KEY, Image varchar(100), URL varchar(100), Hint varchar(100), Shown int, Clicked int, )

    Retrieving and diplaying Ad's
    Everything you need is just put folowing line of code anywhere you want Ad to be shown:

    <!--#include virtual="BannersInc.asp"-->

    and to copy following two files in the same directory.

    BannersInc.asp shows random Ad from your Banners table and creates link to destination URL.

    Here is code for BannersInc.asp file.

    BannersInc.asp

    <%@ Language=VBScript %>
    <% Response.Buffer=True 'Define our ADO constants const adOpenStatic = 3 const adLockOptimistic = 3 '----- Create and Open Connection Set MyConnection = Server.CreateObject("ADODB.Connection") MyConnection.ConnectionString = "PROVIDER=SQLOLEDB;SERVER=YourServerName;UID=YourUID;" & _ "PWD=YourPWD;DATABASE=YourDATABASE" MyConnection.Open '----- Pick Ad from database SQLBanners = "Select * from Banners" Set Banners = Server.CreateObject("ADODB.Recordset") Banners.CursorType = adOpenStatic Banners.LockType = adLockOptimistic Banners.Open SQLBanners, MyConnection Randomize Timer Banners.Move Int(RND * CInt(Banners.RecordCount)) '----- Increment Shown field value Banners("Shown") = Banners("Shown") + 1 Banners.Update '----- Create and display Response ImageString = "<img src=""" & Banners("Image") & """ alt=""" & Banners("Hint") & """>" ResponseString = "<a href=""RedirectMe.asp?URL=" & Banners("URL") ResponseString = ResponseString & "&BannerID=" & Banners("BannerID") & """>" & ImageString & "</a>" Response.Write ResponseString Response.End '----- Clean up memory Banners.Close MyConnection.Close Set Banners=Nothing Set MyConnection=Nothing %>

    Be sure to provide correct values for SERVER,UID,PWD and DATABASE If your Images are not in current directory then you should adjust ImageString. Each time Ad is clicked users Clicked value for appropriate Ad is incremented and users browser is redirected to Ad's URL.

    Here is code for RedirectMe.asp

    RedirectMe.asp

    <%@ Language=VBScript %>
    <% Response.Buffer=True 'Define our ADO constants const adOpenStatic = 3 const adLockOptimistic = 3 '----- Create and Open Connection Set MyConnection = Server.CreateObject("ADODB.Connection") MyConnection.ConnectionString = "PROVIDER=SQLOLEDB;SERVER=YourServerName;UID=YourUID;" & _ "PWD=YourPWD;DATABASE=YourDATABASE" MyConnection.Open '----- Increment Clicked field value SQLBanners = "Select * from Banners Where BannerID=" & Request.QueryString("BannerID") Set Banners = Server.CreateObject("ADODB.Recordset") Banners.CursorType = adOpenStatic Banners.LockType = adLockOptimistic Banners.Open SQLBanners, MyConnection Banners("Clicked") = Banners("Clicked") + 1 Banners.Update Response.Redirect(Request.QueryString("URL")) Response.End '----- Clean up memory Banners.Close MyConnection.Close Set Banners=Nothing Set MyConnection=Nothing %>

    After some short and easy customization and running this code on your site, you will have some useful, additional information about your Ad's which can be later easily extracted from Banners table.

    Happy Programming!


    Attachments:

  • Download the source code for BannersInc.asp in text format
  • Download the source code for RedirectMe.asp in text format


  • Article Information
    Article Title: Rotating Banner Ads using a Database
    Article Author: Vladimir Djacic
    Published Date: Sunday, September 12, 1999
    Article URL: http://www.4GuysFromRolla.com/webtech/091299-1.shtml


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