Data structure
First we will set up a table in the database called Banners and it will have the following structure:
Banners |
|---|
| BannerID | Long |
| Image | varchar(100) |
| URL | varchar(100) |
| Hint | varchar(100) |
| Shown | Long |
| Clicked | Long |
Here is some example data:
| BannerID | Image | URL | Hint | Shown | Clicked |
|---|
| 1 | gausBanner.gif | http://www.gaus.co.yu | Visit our site | 1 | 1 |
and here is how can you add table to your SQL database:
<![CDATA[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 %>
<%<![CDATA[ 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 %>
<% <![CDATA[ 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!
discuss this topic to forum
