建立一个广告交换及跟踪系统

  First you need a database to store your banners. We are using 2 tables; tblBanners and tblVendors:

tblBanners:
bID - auto number (banner ID)
bBanner - text (image file)
bUsedViews - number (# of views the banner has received)
bTotalViews - number (# of impressions the vendor has paid for)
bClicks - number (# of clicks the banner has received)
bURL - text (URL of the site)
bShow - yes/no (used to show and hide banners)
vID - number (vendor ID)

tblVendors:
vID - autonumber (vendor ID - links to tblBanners.vID)
vName - text (Vendor's name)
etc..........


Now that the database is set up, we need to randomly display the banner on our pages and increment the 'views' by 1:

First you need to open your database connection on the page you want your banner to be viewed on. We use a DSN-less connection; you can find it at the following address:

http://www.askasp.com/toolbox.asp?Expand=True&ID=2#tool

Here is what the SQL might look like:

SQL = "SELECT tblBanners.bID, tblBanners.bImage, tblBanners.bUsedViews, tblBanners.bLastViewed "
SQL = SQL & "FROM tblBanners "
SQL = SQL & "WHERE (((tblBanners.bShow)=True) AND ((tblBanners.bTotalViews)>[tblBanners].[bUsedViews]));"

In the above SQL, we are checking for the 'bShow' flag to be True, and that the UsedViews is less than the TotalViews.

Now that we have all of the banners that we can display, we need to display a random one. We can do this by grabbing the total number of banners, moving to the first record, and the moving to a random number, for example:


Dim rndMax, rndNumber

Randomize

rndMax = Int(RecordSet.RecordCount)
rndNumber = Int(RND * rndMax)

RecordSet.Move rndNumber


Now that we have moved to our random banner, we now need to display the banner on our page (I am sure you know how to do that, so I wont bore you with the details). However, Instead of using the banner's URL in the link, we are going to use a redirect page so we can count the clicks. All we need to do is use the banner ID in the HREF tag, for example:

a href="redirect.asp?ID=<%= BANNER ID %>"

Now that we have the link set up, we can move on to our redirect.asp page. On this page, we are going to grab the ID that we are passing in the Query String, and grabbing the RecordSet that matches. Once we have the RecordSet, we can grab the banner's URL, increase the Clicks by 1, and send the user to the destination URL. Below is the code for the redirect.asp page:

<%
If Request.QueryString("ID") = "" Then
Response.Redirect("default.asp")
End If

Dim varSiteToRedirect, varURLToRedirect

varSiteToRedirect = Int(Request.QueryString("ID"))


SQL = "SELECT tblBanners.bID, tblBanners.bURL, tblBanners.bClicks "
SQL = SQL & "FROM tblBanners "
SQL = SQL & "WHERE (((tblBanners.bID)=" & varSiteToRedirect & "));"

varDatabaseName = "ask_asp_data.mdb"
%>

<!--#include file="common/data_conn_open.asp"-->

<%
If Not RecordSet.BOF Then
RecordSet.MoveFirst
End If

varURLToRedirect = RecordSet.Fields("bURL")

RecordSet.Fields("bClicks") = (RecordSet.Fields("bClicks") + 1)
RecordSet.Update
%>

<!--#include file="common/data_conn_close.asp"-->

<% Response.Redirect(varURLToRedirect) %>