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

Here is some example data:

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: