Create your own Banner Management Application

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. 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.