Create your own Banner Management Application

If you’re like me, then you probably run one or more small sites that either provide information to your visitors, or sell a range of products and services targeted at a specific audience. You have people that you trust managing and updating content, you’ve spent many, many hours of your time developing the site, and you may even attract a couple of thousand visitors every day.

Along with the profits that you generate through either membership charges or product sales, wouldn’t it be nice to manage a simple banner rotation and stats system on your site? With this system, you could display a random banner at the top of every page on your site, track impressions and click-thrus, and, as your site gains credibility, you can reap the profits by allowing others to advertise on your site.

What You’ll Need

To create the simple app that I’m about to explain, you’ll need:

  • A webserver (preferably a Win2k box) capable of running ASP scripts
  • SQL Server 2000
  • Basic ASP knowledge
  • Basic SQL Syntax and Enterprise Manager knowledge

Please note that all the code included in this article can be downloaded here.

Project Overview

Basically, our simple project will do three things:

  1. Allow us to add and delete banners from our rotation schedule
  2. Display banners on our site using very simple ASP script
  3. Track impressions and click-thrus for each banner that’s displayed on our site

Let’s start with the first step: the addition and deletion of banners from our rotation schedule. Don’t worry if you don’t understand parts of the code: it will all make sense by the end of the article.

Step 1: Develop a Web app to add banners to our rotation schedule

In this step, we’ll develop a simple Web page that will allow us to add banners to our rotation schedule. A rotation schedule is just a fancy name for the list of all of our banners (which will be stored in a database).

Firstly, we’ll need to create a basic HTML form. This form will allow us to select an image for our banner, and create a name for this banner in the rotation schedule. Copy the code below and paste it into a file named addbanner1.asp.

<html> 
<head>
<title> My Banner Management Site </title>
</head>
<body bgcolor="#ffffff">
 <form enctype="multipart/form-data" name="frmBanner"  
action="addbanner2.asp" method="post">
   <pre>
<h1>Add a Banner</h1>
Banner Image: <input type="file" name="banner_image">
Banner Name:  <input type="text" name="banner_name">
Banner URL:   <input type="text" name="banner_url">
<br><br>
<input type="submit" value="Add Banner">
   </pre>
 </form>
</body>
</html>

Save addbanner1.asp into a directory that your Web server can process (by default this directory is c:inetpubwwwroot), and then fire it up using your Web browser. You should get a page that looks similar to this:

503addabanner

The code behind this page is just simple HTML. I’ve broken the code into chunks and described it below:

<html> 
<head>
<title> My Banner Management Site </title>
</head>

The code above shows the opening HTML tags. Firstly, the <html> tag tells the browser to interpret the entire page as an HTML document. Next, the <head> tag tells the browser that we’re describing some meta-data for our Web page. The only metadata we’re describing is the <title> tag, which sets the title in the top bar of our Web browser’s window. Lastly, the title tag is the closed (</title>) followed by the <head> tag.

<body bgcolor="#ffffff"> 
 <form enctype="multipart/form-data" name="frmBanner"  
action="addbanner2.asp" method="post">
   <pre>
<h1>Add a Banner</h1>
Banner Image: <input type="file" name="banner_image">
Banner Name:  <input type="text" name="banner_name">
Banner URL:   <input type="text" name="banner_url">
<br><br>
<input type="submit" value="Add Banner">
   </pre>
 </form>
</body>
</html>

Next, the <body> tag is used to mark the start of our document. Within the <body> tag, we also set the background colour of our page to white (#FFFFFF is the hexadecimal code for white).

After this, the <form> tag tells our browser that we want to capture some information for processing. Notice the enctype="multipart/form-data" attribute of our form. This is the most important part of the whole page. It tells the browser that we want to upload our image, and that it must be ready to handle it in binary mode instead of the normal ASCII mode. The action="addbanner2.asp" attribute of the form tells the browser to send all the information that’s captured in this form to the addbanner2.asp page for processing.

Secondly, we have our form elements. The first is a file box through which we can browse for our banner image. The second element is just a simple text box in which we can type the name of the banner, and the third is another text box for the URL that the banner will re-direct to when it’s clicked. We also have a submit button which tells the browser to submit our form to addbanner2.asp.

Lastly, we end our form with the </form> tag, and tell the browser that we are at the end of our Web page with the </html> tag.

Saving the banner details to our database:

The next step involves the use of SQL Server 2000 Enterprise manager to create our tables, and ASP script to add our banner to the database. Start off by opening Enterprise Manager (Start -> Programs -> Microsoft SQL Server -> Enterprise Manager). This is the program that lets us create our database and tables.

We want to create a new database. Call the new database myAdStuff. Once you’ve created the database, we’ll need to create just one simple table named "banners". The fields for this table are shown below (note that the bannerId field is an auto incrementing identity field, so make sure you set its identity property to ‘yes’ and make it a primary key.

503bannertable

Now that we’ve created our database and our banners table, we can close SQL Server Enterprise Manager and start on the ASP code that will add a banner to our database.

Adding a new banner using ASP script:

If you were paying attention over the last section or two, you’ll remember that our "Add a Banner" form posts our form data to addbanner2.asp. We’ll create the addbanner2.asp page with this code (again, don’t worry if you don’t understand it, it’ll all make sense at the end of the article):

<%@ Language="VBScript" %>  
<!-- METADATA Type="TypeLib" File="c:program filescommon  
filessystemadomsado15.dll" -->  
<%  
 dim objConn  
 dim objRS  
 dim objUpload  
 dim strBanner_Image  
 dim strBanner_Name  
 dim strBanner_URL  
 dim strBanner_Path  
 
 set objConn = Server.CreateObject("ADODB.Connection")  
 set objRS = Server.CreateObject("ADODB.Recordset")  
 set objUpload = Server.CreateObject("Persits.Upload.1")  
 strBanner_Path = "c:inetpubwwwrootbanners"    
 objUpload.OverwriteFiles = True  
 objUpload.Save strBanner_Path  
 
 if objUpload.Files.Count = 0 then  
   Response.Write "* Error ** You must select a file to upload.  
<a href='javascript:history.go(-1)'>Click here</a> to go back."    
   Response.End  
 end if  
 
 strBanner_Image = objUpload.Files(1).ExtractFileName  
 strBanner_Name = objUpload.Form.Item("banner_name").Value  
 strBanner_URL = objUpload.Form.Item("banner_url").Value  
   
 if strBanner_Name = "" then  
   Response.Write "* Error ** You must enter a name for this banner.  
<a href='javascript:history.go(-1)'>Click here</a> to go back."    
   Response.End  
 end if  
 
 if strBanner_url = "" then  
   Response.Write "* Error ** You must enter a url for this banner.  
<a href='javascript:history.go(-1)'>Click here</a> to go back."    
   Response.End  
 end if  
   
 objConn.Open "Provider=SQLOLEDB; Data Source=localhost; Initial  
Catalog=myAdStuff; UId=sa; Pwd="  
 objRS.Open "select * from banners", objConn, adOpenDynamic,  
adLockPessimistic  
 objRS.AddNew  
 objRS("bannerName") = strBanner_Name  
 objRS("bannerURL") = strBanner_Url  
 objRS("bannerImage") = strBanner_Image  
 objRS("bannerImpressions") = 0  
 objRS("bannerClickThrus") = 0  
 objRS.Update  
 objRS.Close  
 objConn.Close  
   
 set objRS = nothing  
 set objConn = nothing  
   
 Response.Redirect "banners.asp"  
%>

Don’t be scared off by the ASP code above, it really is quite simple. Let’s take a look at it step by step:

<%@ Language="VBScript" %>  
<!-- METADATA Type="TypeLib" File="c:program filescommon  
filessystemadomsado15.dll" -->

If you’ve had even the smallest amount of exposure to ASP, you should be familiar with the <%@ Language="VBScript" %> directive. This directive is optional, but it should be included. It simply lets the ASP engine know that our page will contain VBScript code and not Jscript.

The next tag may look unfamiliar. This is a special type of tag called a server-side include (or SSI for short). This tag tells the ASP engine to extract all constants and variables (knows as a type library) from the msado15.dll file (which is located in the c:program filescommon filessystemado) directory on our Web server. Msado15.dll (MSADO15 – Microsoft ActiveX Data Objects version 1.5) contains all of the constants that we’ll use when we declare our database objects, such as cursor types, lock types, etc.

<%  
 dim objConn  
 dim objRS  
 dim objUpload  
 dim strBanner_Image  
 dim strBanner_Name  
 dim strBanner_Url  
 dim strBanner_Path

These basic variable declarations simply dimension our database objects, our special upload object (which I’ll describe shortly) and some simple string variables, which will hold the details of our banner.

  set objConn = Server.CreateObject("ADODB.Connection")  
 set objRS = Server.CreateObject("ADODB.Recordset")  
 set objUpload = Server.CreateObject("Persits.Upload.1")  
 strBanner_Path = "c:inetpubwwwrootbanners"    
 objUpload.OverwriteFiles = True  
 objUpload.Save strBanner_Path

The three "set…" lines instantiate the database and upload objects that our script uses to handle our data. The first two set commands declare our database connection and recordset objects, which will be used to connect to and update our database respectively. The next line, set objUpload = Server.CreateObject("Persits.Upload.1") makes reference to our ActiveX object which will handle the uploading of our banner image. The ActiveX component is called Persits ASP Upload and can be downloaded here. Once you’ve downloaded the self-extracting executable, it’s just a simple matter of running the install program and restarting your Web server.

Moving on, the strBanner_Path variable will hold the location of the directory in which we want our banners to reside once they’ve been uploaded. This should be a sub-directory of the directory that holds your ASP scripts on the server (for example, if my scripts were located in c:myscripts, I would use something like c:myscriptsimages for the images directory. Make sure the directory for you images exists before you run the script!). Change this variable if you need to. After this, we just set our Persits upload component to overwrite image files if they already exist, and set the save parameter to the strBanner_Path variable.

if objUpload.Files.Count = 0 then  
   Response.Write "* Error ** You must select a file  
to upload. <a href='javascript:history.go(-1)'>Click here</a> to  
go back."    
   Response.End  
 end if  
   
 strBanner_Image = objUpload.Files(1).ExtractFileName  
 strBanner_Name = objUpload.Form.Item("banner_name").Value  
 
 if strBanner_Name = "" then  
   Response.Write "* Error ** You must enter a name for  
this banner. <a href='javascript:history.go(-1)'>Click here</a> to  
go back."    
   Response.End  
 end if  
 
 if strBanner_Url = "" then  
   Response.Write "* Error ** You must enter a url for  
this banner. <a href='javascript:history.go(-1)'>Click here</a> to  
go back."    
   Response.End  
 end if

As with all good programs, scripts and components, we’ll include some basic error checking and handling procedures. In the first couple of lines, we use our upload objects file collection property to check if a banner image has been uploaded. If one hasn’t (if objUpload.Files.Count = 0) then we’ll show an error message and terminate the script.

We’ll then retrieve the image filename from the Files collection of our upload object, as well as the name and url that we typed in for this banner. Remember that we can’t access these using the Request.Form collection, because we have set our forms enctype attribute to "multipart/form-data" and this blocks all access to the Request.Form collection when a page is processed. Instead, our upload component handles the variables parsed from the Request.Form collection for us, and stores them in its Form collection.

objConn.Open "Provider=SQLOLEDB; Data Source=localhost; Initial   
Catalog=myAdStuff; UId=sa; Pwd="  
 objRS.Open "select * from banners", objConn, adOpenDynamic,  
adLockPessimistic

The next part of our ASP script uses our database connection object (objConn) to connect to our database. If your database is not on the same machine as your ASP scripts, then change the Data Source=localhost attribute to match the name of your SQL Server.

When we’ve connected to our database, we’ll open our recordset object (objRS). There are a number of ways to open a recordset object, but we will use the easiest one to keep this example simple. The first parameter, "select * from banners" can be any valid SQL query. It’s simply used to interact with the database for now. The next parameter, "objConn", sets a reference to our database connection object, which means that our recordset object will use objConn to execute its queries. Next, the "adOpenDynamic" cursor types tells SQL Server that we want to have "dynamic" access to our database: both read and write, not just read, which is adOpenForwardOnly. The last parameter sets the lock type on our database.

  objRS.AddNew  
 objRS("bannerName") = strBanner_Name  
 objRS("bannerUrl") = strBanner_Url  
 objRS("bannerImage") = strBanner_Image  
 objRS("bannerImpressions") = 0  
 objRS("bannerClickThrus") = 0  
 objRS.Update

The code above is the main part of our ASP script. This code tells SQL server that we want to add a new record to our database (objRS.AddNew). We then set the field name and value pairs that we want to add to our new record (bannerName, bannerUrl, bannerImage, bannerImpressions and bannerClickThrus) and call the Update method to save the record. Notice that we don’t have to specify the values of the bannerId field because it’s an auto-incrementing field.

  objRS.Close  
 objConn.Close  
   
 set objRS = nothing  
 set objConn = nothing  
   
 Response.Redirect "banners.asp"  
%>

Lastly, we close the recordset and connection objects, set them to nothing (so the resources they used are freed up), and send a re-direct response code to our browser. See how the Response.Redirect command is telling our browser to go to banners.asp? This page doesn’t exist, but will be used to show a list of all of the banners in our schedule. Now that we can add a banner, let’s create the code to list our banners and also to delete banners when we no longer want them in our schedule.

Listing Banners:

Believe it or not, we’ve overcome the hardest part of our coding! For this step, we want to create a list of the banners in rotation, and we also want to be able to delete a banner if we need to. To start, let’s create a simple ASP script that will show our banners in a table on an HTML page. Copy the code shown below into a file named banners.asp. Store this file with addbanner1.asp and addbanner2.asp.

<%@ Language="VBScript" %>   
<!-- METADATA Type="TypeLib" File="c:program filescommon    
filessystemadomsado15.dll" -->  
 
<html>  
<head>  
<title> My Banner Management Site </title>  
</head>  
 
<body bgcolor="#FFFFFF">  
<form name="frmBanner" action="delbanner.asp" method="post">  
<pre><h1>Current Banners in Rotation</h1></pre>  
<a href="addbanner1.asp">[ Add Banner ]</a><br><br>  
<table width="100%" align="left" cellspacing="2" border="1"  
bordercolor="white" cellpadding="3" border="0" bgcolor="lightgreen">  
<tr>  
<td width="7%">  
<font face="verdana" size="2"><b>Delete</b></font>  
</td>  
<td width="23%">  
<font face="verdana" size="2"><b>Banner Name</b></font>  
</td>  
<td width="10%">  
<font face="verdana" size="2"><b>Impressions</b></font>  
</td>  
<td width="10%">  
<font face="verdana" size="2"><b>Click-Thrus</b></font>  
</td>  
<td width="50%">  
<font face="verdana" size="2"><b>Banner Image</b></font>  
</td>  
</tr>  
 
<%  
dim objConn  
dim objComm  
dim objRS  
dim strBanner_Path    
     
set objConn = Server.CreateObject("ADODB.Connection")  
set objComm = Server.CreateObject("ADODB.Command")  
set objRS = Server.CreateObject("ADODB.Recordset")  
 
strBanner_Path = "c:inetpubwwwrootbanners"  
 
objConn.Open "Provider=SQLOLEDB; Data Source=localhost; Initial    
Catalog=myAdStuff; UId=sa; Pwd="  
objComm.ActiveConnection = objConn  
objComm.CommandType = adCmdText  
objRS.ActiveConnection = objConn  
objRS.CursorType = adUseForwardOnly  
objRS.LockType = adLockReadOnly  
 
objComm.CommandText = "select * from banners order by bannerName asc"  
set objRS = objComm.Execute  
 
while not objRS.EOF  
%>  
<tr>  
 <td bgcolor="#E9E9E9" width="7%">  
   <font face="verdana"    
size="2"><b>  
     <a href="delbanner.asp?bannerId=<%=objRS("bannerId")%>">[  
Delete ]</a>  
   </b></font>  
 </td>  
 <td bgcolor="#FFFFC0" width="23%">  
   <font face="verdana"    
size="2"><b><%=objRS("bannerName")%></b></font>  
 </td>  
 <td bgcolor="#FFFFC0" width="10%">  
   <font face="verdana"    
size="2"><b><%=objRS("bannerImpressions")%></b></font>  
 </td>  
 <td bgcolor="#FFFFC0" width="10%">  
   <font face="verdana"    
size="2"><b><%=objRS("bannerClickThrus")%></b></font>  
 </td>  
 <td width="50%">  
   <font face="verdana" size="2"><b><a  
href="<%=objRS("bannerUrl")%><img border="0" src="<%="banners/" &    
objRS("bannerImage")%>"></a></b></font>  
 </td>  
</tr>  
<%  
objRS.MoveNext  
wend  
%>  
</table>  
</form>  
</body>  
</html>

The code shown above uses very similar objects and techniques to those that we discussed in the "Adding a Banner" pages earlier in this article. There are however, some new ideas used here, and they’re what we’ll describe now.

dim objComm   
...  
set objComm = Server.CreateObject("ADODB.Command")  
...  
objComm.ActiveConnection = objConn  
objComm.CommandType = adCmdText

In this part of our ASP script, we introduce a new database object called a command object. A command object is used to parse commands to SQL server. These commands can take the form of stored procedures, text commands, commands held in files, etc. For our banner system, we only want to pass plain old text queries to SQL server. To do this, we set the CommandType parameter of our command object (objComm) to adCmdText, which means that any query that’s passed to SQL server should be interpreted as plain text.

Through the command object, we get a list of all of our banners using a simple select query. The results for the query are stored in our recordset object (objRS), shown below:

objComm.CommandText = "select * from banners order by bannerName asc"   
set objRS = objComm.Execute

Now that we have a list of rows in our recordset object, we can loop through each row, and display the data in our HTML table. We will display a link to delete the banner (which I’ll explain later), the banner’s name, impression and click-thru numbers, and image. Remember to change the strBanner_Path variable to the directory where you’ve saved your banners.

strBanner_Path = "c:inetpubwwwrootbanners"   
...  
while not objRS.EOF  
%>  
...  
<%  
objRS.MoveNext  
wend  
%>

If all goes well, you’ll be presented with a list of banners in your database, just like the one shown below:

503currentbanners

Deleting a banner:

No doubt, you’ll be curious as to how to go about deleting a banner from your rotation, right? To delete a banner, we will create a new page called delbanner.asp. Once you’ve created the page, you can click on the [ Delete ] link that’s shown on the "Current Banners in Rotation" page to remove a banner from the list. Create a new page called delbanner.asp and add this code to it:

<%@ Language="VBScript" %>    
<%    
 dim objConn    
 set objConn = Server.CreateObject("ADODB.Connection")    
   
 objConn.Open "Provider=SQLOLEDB; Data Source=localhost; Initial    
Catalog=myAdStuff; UId=sa; Pwd="    
 objConn.Execute "delete from banners where bannerId=" &    
Request.QueryString("bannerId")    
   
 Response.Redirect("banners.asp")    
%>

I told you it was simple to delete a banner! Lets look at this code in more detail:

<%@ Language="VBScript" %>

You should beable to remember what this tag does. It’s an ASP directive that tells the ASP engine that we’re using VBScript in our ASP page. Next, we declare our database connection and open it:

  dim objConn    
 set objConn = Server.CreateObject("ADODB.Connection")    
   
 objConn.Open "Provider=SQLOLEDB; Data Source=localhost; Initial    
Catalog=myAdStuff; UId=sa; Pwd="    
   
objConn.Execute "delete from banners where bannerId=" &    
Request.QueryString("bannerId")    
   
objConn.Close    
set objConn = nothing    
   
 Response.Redirect("banners.asp")    
%>

Lastly, we’ll use our connection object (yes, you can execute an SQL query using a connection object as well!) to parse the SQL query to delete a banner. If you jump back to the banners.asp page, you’ll notice that when you run your mouse over a banner’s [Delete] link, you’ll see something like http://localhost/delbanner.asp?bannerId=23 in the status bar of your browser. It’s the bannerId=23 part of the URL that is used in our SQL query as the id of the banner we’ll delete. In our example above, Request.QueryString("bannerId") would evaluate to 23.

Finally, when the command has executed, we close and free our database connection and re-direct the browser back to the list of banners.

That’s all for step one. In step two, we’ll display the banners on our Website and track impressions through our management system.

Step 2: Displaying and tracking banners, impressions and click-thrus

By now you should have created the four pages that I described in part one: addbanner1.asp, addbanner2.asp, banners.asp and delbanner.asp. You should save these pages in some sort of admin folder and use your Web server to configure the security permissions on this directory so that a username a password is required to access your banner scripts.

Having said that, I’ll now demonstrate the code we’ll use to retrieve a banner from our database, display it on our Web page and track both impressions and click-thrus for each banner.

Displaying a banner on your Website:

No doubt you’ve been to thousands of Websites that display banners across the top of the screen. These could be 468×60 banners, 460×60, 215×125 etc. The point is that banners are the most popular form of advertising on the Internet.

To display a banner from the database on our site, four steps are required as pictured here:

503bannerdisplaydiagram

Create the following code and save it to a file called bannercode.asp

<%@ Language="VBScript" %>     
<!-- METADATA Type="TypeLib" File="c:program filescommon      
filessystemadomsado15.dll" -->    
<%    
 function GetBanner()    
   dim objConn    
   dim objComm    
   dim objRS    
   
   dim strBanner_VirtualPath    
   dim strBannerHTML    
   dim intNumBanners    
   dim intRandom    
   
   set objConn = Server.CreateObject("ADODB.Connection")    
   set objRS = Server.CreateObject("ADODB.Recordset")    
   
   strBanner_VirtualPath = "/banners/"              
   objConn.Open "Provider=SQLOLEDB; Data Source=localhost;    
Initial Catalog=myAdStuff; UId=sa; Pwd="    
   objRS.ActiveConnection = objConn    
   objRS.LockType = adLockReadOnly    
   objRS.CursorType = adUseForwardOnly    
       
   objRS.Open "select count(*) from banners"    
   intNumBanners = objRS.Fields(0).Value    
         
   if intNumBanners = 0 then    
     exit function    
   end if    
   
   Randomize    
   intRandom = int(rnd(1)*intNumBanners)      
   objRS.Close    
   objRS.Open "select * from banners"    
   objRS.Move intRandom    
   
   strBannerHTML = "<a href='bannerclick.asp?bannerId=" &      
objRS("bannerId") & "'><img border='0' src='" & strBanner_VirtualPath      
& objRS("bannerImage") & "'></a>"        
   Response.Write strBannerHTML    
   
   objConn.Execute "UPDATE banners SET bannerImpressions =    
bannerImpressions + 1 WHERE bannerId = " & objRS("bannerId")    
   objConn.Close    
   
   set objConn = nothing    
   set objRS = nothing    
   
 end function    
%>

Now, let’s break down the unfamiliar code…

Let’s start with the function declaration:

function GetBanner()      
 dim objConn      
 dim objRS      
 dim strBanner_VirtualPath      
 dim strBannerHTML      
 dim intNumBanners      
 dim intRandom      
     
 set objConn = Server.CreateObject("ADODB.Connection")      
 set objRS = Server.CreateObject("ADODB.Recordset")      
     
 strBanner_VirtualPath = "/banners/"          
 objConn.Open "Provider=SQLOLEDB; Data      
Source=localhost; Initial Catalog=myAdStuff; UId=sa; Pwd="      
 objRS.ActiveConnection = objConn      
 objRS.LockType = adLockReadOnly      
 objRS.CursorType = adUseForwardOnly

This function is responsible for the retrieval and display of a banner at random from our database. Firstly, we declare a new database connection and recordset. After this, we set the virtual path for the location of our banners folder in the strBanner_VirtualPath variable. Lastly, we open a connection to our database and set the ActiveConnection of our recordset object to point to this connection.

  objRS.Open "select count(*) from banners"      
 intNumBanners = objRS.Fields(0).Value      
     
 if intNumBanners = 0 then      
   exit function      
 end if      
     
 Randomize      
 intRandom = int(rnd(1)*intNumBanners)        
     
 objRS.Close

This next part of our code is where the random number generation takes place. Firstly, we use our recordset object to get a count of the number of records in our banners table. The query we have used (select count(*) from banners) returns a table with one field containing one value. If we executed this query using query analyser when we had three banners in our table, we would achieve this result:

503bannercount

After we store the return value into the variable intNumBanners, we run a quick check to make sure that there is at least one banner in our table. If there isn’t, we exit the function and return nothing.

Now comes the random banner generation. First, we call the Randomize function which basically tells our ASP engine this: "When we call the Rnd() function, make sure we get a random number every time". It sounds confusing, but really, it isn’t. Then we use the Rnd() function to generate a random integer between 1 and the number of records in our database (in my case, 3), followed by closing our recordset object.

  objRS.Open "select * from banners"      
 objRS.Move intRandom      
       
 strBannerHTML = "<a href='bannerclick.asp?bannerId=" &      
objRS("bannerId") & "'><img border='0' src='" &      
strBanner_VirtualPath & objRS("bannerImage") & "'></a>"          
 Response.Write strBannerHTML

Because we closed our recordset object previously, we must open it again. However, this time we select all the rows in the banners table as our query. Because we’re using a recordset with a dynamic cursor (objRS.CursorType = adUseDynamic), we have the ability to move to any row in that recordset. objRS.Move intRandom uses our randomly generated number as the row to move to. So If we generated a random number of 3, we would move to the third row in our recordset (remember that recordsets are like a zero-based array, so we’d be moving to row number three but the row would have an index of 2).

The last line is used to build the HTML string that we will output in our browser. The code uses simple anchor and image tags to display our banner with a link. Notice the URL in the anchor tag (bannerclick.asp?bannerId=" & objRS("bannerId")). This is the next and final page that we will discuss. It’s the page that handles the addition of click-thrus and re-directs the user to the URL that corresponds to that particular banner. More on that later, though.

  objConn.Execute "UPDATE banners SET bannerImpressions =      
bannerImpressions + 1 WHERE bannerId = " & objRS("bannerId")      
 objConn.Close      
         
 set objConn = nothing      
 set objRS = nothing      
       
end function

The final part of our GetBanner function uses our connection object to increase the impression count for the randomly selected banner, and frees up the memory used by our connection and recordset objects.

I’ve got the function, now what?

So, now that we’ve stored our GetBanner() function in bannercode.asp, how do we display the banners on our Website? It’s a simple matter of adding two lines of code to any existing ASP page. Lets look at an example:

<!-- #INCLUDE file="bannercode.asp" -->      
<html>      
<head>      
<title> My Test Banner </title>      
</head>      
     
<body>      
 <%=GetBanner()%>      
</body>      
</html>

As you can see from the example above, we simply use an SSI (Server-Side include) to reference the file that holds our GetBanner function. Then we simply call this function with either the <%= GetBanner() %> method or <% Response.Write GetBanner() %> method.

In the next and final part of the tutorial, we’ll create the bannerclick.asp page that handles the click-thrus and re-directions when a banner is clicked from our site.

Step3: Handling Banner Clicks

Now, for the last – and easiest – step of our project, tracking the click-thrus. A click-thru occurs when a visitor clicks on any of the advertisements displayed on your site. When the visitor clicks the advertisements link, the click-thru count for that banner is updated, and then the visitor is re-directed to the page that contains the information/products relevant to that advertisement.

Remember how our GetBanner() function didn’t link the banner directly to its URL, and linked it to bannerclick.asp instead? Well, bannerclick.asp acts as the intermediate page between our site and the advertiser’s site, updating the click-thru rate for that banner and then re-directing the user to the site of the advertiser, as mentioned above.

Without further ado, lets create a new page called bannerclick.asp and save it in the same directory as bannercode.asp. Now type this code into your bannerclick.asp file:

<%@ Language="VBScript" %>       
<!-- METADATA Type="TypeLib" File="c:program filescommon        
filessystemadomsado15.dll" -->      
     
<%      
 dim objConn      
 dim objRS      
 dim strBannerURL      
     
 set objConn = Server.CreateObject("ADODB.Connection")      
 set objRS = Server.CreateObject("ADODB.Recordset")      
       
 objConn.Open "Provider=SQLOLEDB; Data Source=localhost; Initial        
Catalog=myAdStuff; UId=sa; Pwd="      
 objRS.ActiveConnection = objConn      
 objRS.LockType = adLockReadOnly      
 objRS.CursorType = adUseForwardOnly      
       
 objRS.Open "select bannerURL from banners where bannerId=" &        
Request.QueryString("bannerId")      
 if objRS.EOF then              
   Response.Write "Invalid banner id"      
   Response.End      
 end if      
         
 strBannerURL = objRS("bannerURL")      
 objRS.Close      
     
 objConn.Execute "UPDATE banners SET bannerClickThrus =        
bannerClickThrus + 1 WHERE bannerId = " &      
Request.QueryString("bannerId")      
 objConn.Close      
         
 set objConn = nothing      
 set objRS = nothing      
     
 Response.Redirect(strBannerURL)      
%>

There shouldn’t be much new code here. I’ll just run through a couple of lines, the ones that matter.

  objRS.Open "select bannerURL from banners where bannerId=" &       
Request.QueryString("bannerId")      
     
 if objRS.EOF then      
   Response.Write "Invalid banner id"      
   Response.End      
 end if      
     
 strBannerURL = objRS("bannerURL")

This part of our code opens our recordset object, and selects only the bannerURL field from the record whose id matches the Request.QueryString("bannerId") variable (which comes from the link off the banner). If a record with this id doesn’t exist, then we write "Invalid banner id" to the browser and simply end the page. If it does exist, we assign the value of the bannerURL field to the strBannerURL variable.

 objConn.Execute "UPDATE banners SET bannerClickThrus =        
bannerClickThrus + 1 WHERE bannerId = " &        
Request.QueryString("bannerId")

If we skip a couple of lines down, we use our connection object to increase the number of click-thrus for this banner by one, again using the Request.QueryString("bannerId") as the id of the banner to update.

Response.Redirect(strBannerURL)

Last but not least, we re-direct the visitor to the actual URL behind the banner, and presto, it's all done.

Conclusion

Now that you have an idea of how to write a banner ad management system for your Website, think about how you can use it to generate a steady stream of revenue. Why not combine the code that I've described with a couple of other SitePoint.com articles that relate to advertising, and really get serious? If there are trillions of dollars being thrown around the Internet, there should be at least a couple of thousand dollars with your name on them ...right?

Please Note: To keep this article as small as possible, rigorous error handling has been omitted in some cases.

Free book: Jump Start HTML5 Basics

Grab a free copy of one our latest ebooks! Packed with hints and tips on HTML5's most powerful new features.

No Reader comments

Comments on this post are closed.