If you would like to keep track of how many times users click through on a link then here's a script that will keep count of clicks in an Access database.
start by creating a database called LinkCounter.mdb for the information.
Create the tables as follows:
Organisations
-------------
ID (Autonumber)
ID1 (int)
Organisation (text)
TrackerLog
----------
ID (Autonumber)
link_id (int) SAME AS Organisations.ID1
date
hit_count (int)
Now create an ASP script page. The name can be anything you wish, with an asp extension.
Start with creating the page controls. Set the buffer to true then it will enable output of both text from the response object as well as HTML.
I set the page to expire immediately although it probably isn't necesssary. I do it as a matter of habit, knowing I can always take it out if desired.
I've also found that it's actually quite difficult to keep a page from cacheing and so I throw in everything I can to make it refresh. Remove what you don't want.
|
|
<%@ LANGUAGE="VBSCRIPT" %>
<%
'-----------------------------------------------------------
'>>>>>>>SET PAGE AND CACHE CONTROLS<<<<<<<<<<<
'-----------------------------------------------------------
Response.Buffer=true
Response.ExpiresAbsolute = #January 1, 1990 00:00:01#
Response.Expires=Now()-1
Response.AddHeader "Cache-Control", "must-revalidate"
Response.AddHeader "Cache-Control", "no-cache"
Response.AddHeader "cache-control", "private"
Response.AddHeader "pragma", "no-cache"
|
|
Next, define the constants that are going to be required in the script. These ADODB constants are the most common ones I use and prevents an include file being required for ADOVBS.inc.
|
|
'-----------------------------------------------------------
'>>>>>>>DEFINE CONSTANTS<<<<<<<<<<<
'-----------------------------------------------------------
' ADODB Constants
'----------------
'---- CursorTypeEnum Values ----
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3
'---- LockTypeEnum Values ----
Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3
Const adLockBatchOptimistic = 4
'---- CursorLocationEnum Values ----
Const adUseServer = 2
Const adUseClient = 3
|
|
Next dimension the variables that are going to be needed by the script. The first varible I usually get is the page name. I find using this indirect reference saves me a lot of errors.
This is followed by a connection to the database and a DSN-less connection to enable portability.
Last in this section, define the queries needed to extract the information from the database for processing and display.
|
|
'------------------------------------------------------------
'>>>>>>>DEFINE VARIABLES<<<<<<<<<<<
'------------------------------------------------------------
Dim strScriptName, db, rs, strSQL1,strSQL2,strSQL3,strSQL4
'Get Page name
'--------------
strScriptName = Request.ServerVariables("SCRIPT_NAME")
'Create connection to database
'------------------------------
set rs = Server.CreateObject("ADODB.Recordset")
db = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ= " & _
Server.MapPath("LinkCounter.mdb")
'Define queries to be used in script
'-----------------------------------
strSQL1 = "SELECT * FROM Organisations "
strSQL2 = "SELECT link_id, sum(hit_count) AS mysum"_
& " FROM TrackerLog GROUP BY link_id"
strSQL3 = "SELECT * FROM TrackerLog WHERE " _
& " date = #" & FormatDateTime(now,vbshortdate)_
& "# AND " & " link_id = " & Request("ID")
strSQL4 = "SELECT * FROM TrackerLog "
|
|
Now that all the information needed is available data processing can begin. Based upon the user's selection, the corresponding function or subroutine is called to process the request.
After processing, the information is displayed as the link and the user is provided with an option, for the purpose of this demo, to reset all counts to zero.
|
|
'----------------------------------------------------
'>>>>>>>BEGIN PROCESSING<<<<<<<<<<<
'-----------------------------------------------------
'Determine action to take based on user input
'--------------------------------------------
If Request("ID") <> "" then
rs.open strSQL3,db,adOpenStatic
numrecords=rs.recordcount
rs.close
if numrecords <=0 then
Call AddNewEntry()
Else
Call UpdateEntry(strSQL3)
End if
ElseIf Request("reset") <> "" Then
Call Reset(strSQL4)
Else
'No action requested - Continue
End If
'Create arrays of links and hit values to display
'------------------------------------------------
Orglist = MakeArray(strSQL1)
hitcount= MakeArray(strSQL2)
'Display results
'---------------
For i = 0 to Ubound(Orglist,2)
response.write "<br><A href='" & strScriptName & "?page="_
& Server.URLEncode(Orglist(2,i)) & "&ID="_
& i+1 & "'</A><b>" & Orglist(2,i) _
& "</b><I>(Clicked " & " "_
& hitcount(1,i) & " times)</I>"
next
'For Demo allow reset of all hits to zero
'----------------------------------------
%>
<Form Action="<%=strScriptname%>" >
<Input type="submit" Name="reset" Value="Reset Count to zero">
</Form>
<%
|
|
The functions and subroutines used follow. Each includes a statement of purpose and explanation of parameters.
|
|
'-----------------------------------------------------
'>>BEGIN FUNCTIONS AND SUBROUTINES<<
'------------------------------------------------------
'************************************************
Function MakeArray(strSQL)
'
'PURPOSE: Create an array of values for display of links
'
'PARAMETERS:
' strSQL Defines SQL statement
'*************************************************
rs.open strSQL,db,adOpenStatic
numrecords=rs.recordcount
If numrecords <= 0 then
response.write "<br>" & "<FONT color='red' >"_
&"<I>no records found</I></FONT>"
response.end
end if
MakeArray = rs.getrows
rs.close
End function
'**********************************************
Sub AddNewEntry()
'
'PURPOSE: Create entry for clicked link on a given date
'
'PARAMETERS:
' none
'***********************************************
rs.Open "TrackerLog",db, adOpenStatic,adLockOptimistic
rs.AddNew
rs("Link_id") = Request("ID")
rs("Date") = FormatDateTime(now,vbshortdate)
rs("Hit_Count") = 1
rs.Update
rs.Close
End Sub
'**********************************************
Sub UpdateEntry(strSQL)
'
'PURPOSE: modify entry for clicked link on a given date
'
'PARAMETERS:
' strSQL Defines SQL statement
'**********************************************
rs.Open strSQL,db, adOpenStatic,adLockOptimistic
rs("Hit_Count") = rs("Hit_Count")+1
rs.Update
rs.close
End sub
'**********************************************
Sub Reset(strSQL)
'
'PURPOSE: Reset entry for clicked link to 0
'
'PARAMETERS:
' strSQL Defines SQL statement
'***********************************************
rs.Open strSQL,db, adOpenStatic,adLockOptimistic
rs.movefirst
For i = 1 to rs.recordcount
rs("Hit_Count") = 0
rs.Update
rs.movenext
next
rs.close
End sub
set rs=nothing
%> | (From: aspkey)
|
Sitemap Links Advertising
Copyright © 2003-2005 downcodes.comregistration numbers:Hebei ICP: 05003145
