ASP .NET database hit counter

[Slade]

Ok, here I have my hit counter as a text file.

PrivateSub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) HandlesMyBase.Load
 
IfNot Page.IsPostBack Then
Dim nCount As Int32 = 108
nCount = GetCounterValue()
lblTest.Text = nCount.ToString()
EndIf
EndSub


PrivateFunction GetCounterValue() As Int32
Dim ctrFile As StreamReader
Dim ctrFileW As FileStream
Dim sw As StreamWriter
Dim strPath AsString = Server.MapPath("indexcount.txt")
Dim strCounterContents AsString
Dim nCounter As Int32
If (File.Exists(strPath)) Then
	 ctrFile = File.OpenText(strPath)
	 strCounterContents = ctrFile.ReadLine().ToString()
	 ctrFile.Close()
	 nCounter = Convert.ToInt32(strCounterContents)
Else

nCounter = 0
EndIf
 
nCounter += 1
ctrFileW = New FileStream(strPath, FileMode.OpenOrCreate, FileAccess.Write)
sw = New StreamWriter(ctrFileW)
sw.WriteLine(Convert.ToString(nCounter))
sw.Close()
ctrFileW.Close()
Return nCounter
EndFunction

My question is, is there any simple way to modify this to have the number of hits stored in a database?

[Paladine]

Hey Slade, it is actually rather easy, and I would recommend an Access DB to handle something like this, but it is up to you.

Create a table in Access called tblHits with 2 columns; ID (Primary Key),
and one called Hit_Count. I have modified your code to show what I would do;

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) HandlesMyBase.Load

        If Not Page.IsPostBack Then
            Dim nCount As Int32 = 108 '  ||||| Why did you initialize this to 108?  Doesn't matter.
            nCount = GetCounterValue()
            lblTest.Text = nCount.ToString()
        End If
    End Sub

    Private Function GetCounterValue() As Int32
        Dim objCmd As OleDbCommand
        Dim objReader As OleDbDataReader
        Dim strSQL As String
        Dim intNewCount As Int32
        Dim MyConn As OleDbConnection = New OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\wwwroot\Northwind.mdb;User ID=Admin;Password=;")
        strSQL = "SELECT Hit_Count FROM tblHits"

        objCmd = New OleDbCommand(strSQL, MyConn)
        If MyConn.State = ConnectionState.Closed Then
            MyConn.Open()
        End If
        objReader = objCmd.ExecuteReader(CommandBehavior.CloseConnection)
        With objReader
            intNewCount = CInt(.Item("Hit_Count")) + 1
        End With

        objReader.Close()

        '	|||||	Now Call the Update Hit Count
        Call Update_HitCount(intNewCount)
        '	|||||	Return the updated count
        Return intNewCount

    End Function

    Private Sub Update_HitCount(ByVal intValue As Int32)
        '	||||| Could have made this a class visible variable
        Dim MyConn As OleDbConnection = New OleDbConnection("Provider = Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\wwwroot\Northwind.mdb;User ID=Admin;Password=;")
        Dim strSQL As String
        Dim objCmd As OleDbCommand
        Dim intRecords As Integer
        strSQL = "UPDATE tblHits "
        strSQL &= "SET Hit_Count = " & CStr(intValue)   '	||||| Maybe you have it as an Integer in the DB
        strSQL *= " WHERE ID = 1" '	||||| The Primary Key to the Only row in table

        objCmd = New OleDbCommand(strSQL, MyConn)
        intRecords = objCmd.ExecuteNonQuery() '	|||||	Returns the number of rows affected
        MyConn.Close()

    End Sub

Probably not the most elegant way, but you get the point I am sure. :mrgreen:

Hope this helps dude!

:cool:

[Slade]

I have an alternative to this, will show you once I do it :P

Thanks heaps Paladine, you shed some light on a few things.

[Slade]

ok I want to make a hit counter, using a stored procedure I will be able to read and write to the table. The table includes columns:

ArtID
Title
Content
Hits
HitText
UserID

'Hits' is the hit counter column... can someone give me some sample code for me to make a hit counter for this?

Thanks in advance

Slade

[Paladine]

Hey Slade I am not sure what you are asking? Are you asking for the Stored Procedure Script or the ASP Code to execute it?

In either case can I make a suggestion. You have the HITS being stored in the same table as the User. Are you wanting to track not only the Hits the site as a whole, but the individual user hits? Because if the later is true the Hit Count for the site would be a Calculated column. No need to store it unless you really wanted to.

As for the script, I will reply later with a sample script I would use, as for right now the GYM is calling. Haha..
:cool:

[Slade]

nah sorry... I should have explained. I have about 4 or 5 people adding articles to the site, the content is the body of the page, or the article. I want to store the amount of hits each article gets, the userID is the user name of the user who added the article... a typical artical would be like this:

ArtID
1

Title
Authorization

Content
The article goes here in html format

Hits
100

HitText
People have read this

UserID
Slade

What I need is the stored procedure AND the asp .net code... :( If you can help that would be great.