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

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
Technology Jobs
ASP ASP.NET ASP FAQs Message Board Feedback ASP Jobs
Print this page.
Published: Sunday, September 12, 1999

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.

    - continued -

    '

    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

  • Software Developer / Programmer - Distributed Systems (NYC)
    Next Step Systems
    US-NY-New York

    Justtechjobs.com Post A Job | Post A Resume


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