Anilrgowda

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 <CODE>Banners</CODE>.
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 <CODE>Banners</CODE> and it will have the following structure:
<CENTER><TABLE cellSpacing=1 border=1><TBODY><TR><TH colSpan=2><CODE>Banners</CODE></TH></TR><TR><TD>BannerID</TD><TD>Long</TD></TR><TR><TD>Image</TD><TD>varchar(100)</TD></TR><TR><TD>URL</TD><TD>varchar(100)</TD></TR><TR><TD>Hint</TD><TD>varchar(100)</TD></TR><TR><TD>Shown</TD><TD>Long</TD></TR><TR><TD>Clicked</TD><TD>Long</TD></TR></TBODY></TABLE></CENTER>
Here is some example data:
<CENTER><TABLE cellSpacing=1 border=1><TBODY><TR><TH>BannerID</TH><TH>Image</TH><TH>URL</TH><TH>Hint</TH><TH>Shown</TH><TH>Clicked</TH></TR><TR><TD>1</TD><TD>gausBanner.gif</TD><TD>http://www.gaus.co.yu</TD><TD>Visit our site</TD><TD>1</TD><TD>1</TD></TR></TBODY></TABLE></CENTER>
and here is how can you add table to your SQL database:
<TABLE width="95%" border=0><TBODY><TR><TD width="100%" bgColor=#cccccc><CODE><XMP>create table Banners(BannerID int identity PRIMARY KEY,Image varchar(100),URL varchar(100),Hint varchar(100),Shown int,Clicked int,)</XMP></CODE></TD></TR></TBODY></TABLE>
Retrieving and diplaying Ad's
Everything you need is just put folowing line of code anywhere you want Ad to be shown:
<TABLE width="95%" border=0><TBODY><TR><TD width="100%" bgColor=#cccccc><CODE><!--#include virtual="BannersInc.asp"--></CODE> </TD></TR></TBODY></TABLE>
and to copy following two files in the same directory.
<CODE>BannersInc.asp</CODE> shows random Ad from your Banners table and creates link to destination URL.
Here is code for <CODE>BannersInc.asp</CODE> file.
BannersInc.asp
<TABLE width="95%" border=0><TBODY><TR><TD width="100%" bgColor=#cccccc><CODE><%@ Language=VBScript %>
<%<XMP> 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=YourU ID;" & _ "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 = "" ResponseString = "" & ImageString & "" Response.Write ResponseString Response.End '----- Clean up memory Banners.Close MyConnection.Close Set Banners=Nothing Set MyConnection=Nothing</XMP>%></CODE></TD></TR></TBODY></TABLE>
Be sure to provide correct values for SERVER,UID,PWD and DATABASE If your Images are not in current directory then you should adjust <CODE>ImageString</CODE>. 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 <CODE>RedirectMe.asp</CODE>
RedirectMe.asp
<TABLE width="95%" border=0><TBODY><TR><TD width="100%" bgColor=#cccccc><CODE><%@ Language=VBScript %>
<% <XMP> 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=YourU ID;" & _ "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</XMP>%></CODE></TD></TR></TBODY></TABLE>
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 <CODE>Banners</CODE> table. Happy Programming!