Extending AdRotator Component

It is possible to know how many people have clicked on a particular advertisement, if we use AdRotator component. But this is not directly done by the AdRotator component. We need to write a small program to trap this information.

We should use the redirection page specified in the Rotator Schedule file to track the number of clicks. Specifying a redirection page in the Rotator Schedule file is a must to track the clicks.

The following is the redirection file used in the sample described in the AdRotator component page.

<!-- Beginning of adRedir.asp file -->

<%
Response.Redirect Request.QueryString("URL")
%>

<!-- End of adRedir.asp file -->

Let's extend the above adRedir.asp file to record clicks.

The Click on an advertisement is called "Click Though".

Let us record the click through in a database.

In the adRedir.asp page, we will connect to the database and record the click through for the advertisement clicked. We can count the click through date wise.

To implement it, we should have a database similar to the following. In this example we are using SQL Server 7.0 as the database.

Now let's look at the code. Please note that we are interacting with the database before the user is getting re directed to the Advertisement's destination.

<!-- Beginning of adRedir.asp file -->

<%

' Connecting to the database.

Set MyConn = Server.CreateObject("ADODB.Connection")
MyDSN = "DSN=aspAlliance;uid=prasad;pwd=kvnm;"
MyConn.Open MyDSN

' Open a Record Set to check whether the advertisement has a counter already for today's date.

sqlStr = "SELECT clickThrough FROM adStats " & _
    "WHERE dateStats = '" & DATE & "' AND " & _
    "advtURL = '" & Request.QueryString("URL") & "';"
Rs.Open sqlStr, MyConn, 3

' Check the Record Set's Record count.

IF Rs.RecordCount = 1 THEN

    ' Increase the counter by one if there is already a counter existing for this advertisement for today.

    sqlStr = "UPDATE adStats SET clickThrough = clickThrough + 1 " & _
        "WHERE dateStats = '" & DATE & "' AND " & _
        "advtURL = '" & Request.QueryString("URL") & "';"

ELSE

    ' Create the counter with initial value of 1 for this advertisement for today.

    sqlStr = "INSERT INTO adStats (advtURL, dateStats, clickThrough) VALUES('" & _
        Request.QueryString("URL") & "', '" & DATE & "', 1);"
END IF

' Close the Record set.

Rs.Close

' Close the Database connection.

MyConn.Execute sqlStr
MyConn.Close

' Redirecting the user to the Advertisement's URL

Response.Redirect Request.QueryString("URL")

%>

<!-- End of adRedir.asp file -->

Hope it is all clear!

Mail your comments (and doubts) to pkvnm@yahoo.com

Back to: Advertisement Management Systems - Overview