Multipage Hit Counter

SQL Databased Multipage Hit Counter





# 1 - You want a Multipage Hit Counter to keep track of the number of Hits each individual webpage gets in your website.


# 2 - You also want to store the Webpage Hit Count Values into a SQL Database, using Stored Procedures.





Using 2 Webpages as an example:





--------------------------------------------------------------------------------





Inside webpage #1 put insert the following:








<%@ Page Language="VB" Debug="true" %>





<%@ import Namespace="System.Data" %>





<%@ import Namespace="System.Data.SQLClient" %>





<script runat="server">





Sub Page_Load(Source as Object, E as EventArgs)





Dim objCon As New SQLConnection("server=yourServerName;User id=idName;password=yourPassword;database=HitsCounter")





Dim cmd As SQLCommand = New SQLCommand("EXEC dbo.webcounter1", objCon)





objCon.Open()





Dim r as SQLDataReader





r = cmd.ExecuteReader()





r.read()





strcounter3.text = "Hits : " & r.item(0)





end sub





</script>











Also insert the following inside the body tags of webpage #1


<asp:Label id="strcounter3" font-size="X-Large" font-bold="True" bordercolor="Silver" width="300px" borderstyle="Inset" forecolor="Lime" visible="True" runat="server"></asp:Label>





Marked in Red webcounter1 is the Stored Procedures Name.


ALso marked in<B> Red counter3 is the name of the Database.


This is where you would make your changes inside the Webpages.





--------------------------------------------------------------------------------





Use the following Stored Procedure with Webpage #1





CREATE PROCEDURE dbo.webcounter1


WITH RECOMPILE


AS


BEGIN


SET NOCOUNT ON


DECLARE @hits INT


SELECT Hit FROM counter3


WHERE


ID = 1


update counter3 set hit = hit + 1


EXEC sp_recompile counter3


END


GO








--------------------------------------------------------------------------------





The Database Table has 2 columns which consists of an ---ID & Hit Column -- The Table Name is (counter3)


The best way to explain is by showing so posted below are Webpages 1 & 2 along with there Stored Procedures.








--------------------------------------------------------------------------------








--------------------------------------------------------------------------------





Inside webpage #2 put insert the following:








<%@ Page Language="VB" Debug="true" %>





<%@ import Namespace="System.Data" %>





<%@ import Namespace="System.Data.SQLClient" %>





<script runat="server">





Sub Page_Load(Source as Object, E as EventArgs)





Dim objCon As New SQLConnection("server=yourServerName;User id=idName;password=yourPassword;database=HitsCounter")





Dim cmd As SQLCommand = New SQLCommand("EXEC dbo.webcounter2", objCon)





objCon.Open()





Dim r as SQLDataReader





r = cmd.ExecuteReader()





r.read()





strcounter3.text = "Hits : " & r.item(0)





end sub





</script>











Also insert the following inside the body tags of webpage #2


<asp:Label id="strcounter3" font-size="X-Large" font-bold="True" bordercolor="Silver" width="300px" borderstyle="Inset" forecolor="Lime" visible="True" runat="server"></asp:Label>





--------------------------------------------------------------------------------





Use the following Stored Procedure with Webpage #2





CREATE PROCEDURE dbo.webcounter2


WITH RECOMPILE


AS


BEGIN


SET NOCOUNT ON


DECLARE @hits INT


SELECT Hit FROM counter3


WHERE


ID = 2


update counter3 set hit = hit + 1


EXEC sp_recompile counter3


END


GO








--------------------------------------------------------------------------------





Now this type of Hit Counter works fine to a point.


Visiting Webpage #1 accumilates 1 Hit & Visiting Webpage #2 Accumilates 2 Hits.


And each time there 's a revisit to these pages they acquire 2 Hits each.


I'm Guessing the problem is in the Stored Procedure storing there values in Memory.


If Someone can Help Figure Out how to fix this. This could be a nice Website Multipage Hit Counter. I can use some help Guys feel free to advise.