SitePoint Sponsor
Article
About the Author
Mitchell Harper
Mitchell is the lead developer for Interspire, who develop re-brandable Internet software and tools to help Web developers increase their customer base and make more revenue. Mitchell can be reached via email at mitchell@interspire.com.
Mitchell Harper has written 11 articles for SitePoint with an average reader rating of 8.9.
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 [1].
Project Overview
Basically, our simple project will do three things:
- Allow us to add and delete banners from our rotation schedule
- Display banners on our site using very simple ASP script
- 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:\inetpub\wwwroot), and then fire it up using your Web browser. You should get a page that looks similar to this:
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.
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 files\common
files\system\ado\msado15.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:\inetpub\wwwroot\banners"
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 files\common
files\system\ado\msado15.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 files\common files\system\ado) 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:\inetpub\wwwroot\banners"
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 [2]. 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:\myscripts\images 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 files\common
files\system\ado\msado15.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:\inetpub\wwwroot\banners"
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:\inetpub\wwwroot\banners"
…
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:
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 468x60 banners, 460x60, 215x125 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:
Create the following code and save it to a file called bannercode.asp
<%@ Language="VBScript" %>
<!-- METADATA Type="TypeLib" File="c:\program files\common
files\system\ado\msado15.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:
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 files\common
files\system\ado\msado15.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.
[1] http://www.webmasterbase.com/examples/bannermgmt/bannercode.zip
[2] http://www.persists.com
Download sample chapters of any of our popular books.
