Article
Create your own Banner Management Application
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:
