Storing 'hit' in MDB

[SteveH]

Hello

I have the following script which shows a simple hit to a site:

<%
Set FSO = Server.CreateObject("Scripting.FileSystemObject")
Readfile = Server.MapPath ("count.txt")
Set File = FSO.OpenTextFile (Readfile, 1,true)
On Error Resume Next
Read = File.Readall
Count = Read
Count = Count + 1 'increments
File.Close
Writefile = Readfile
Set File = FSO.OpenTextFile (Writefile, 2)
File.Write("" & Count & "")
File.Close
Response.write("counter=" & Count & "")
Set FSO = Nothing
%>

If possible, I would like to store these hits to a database using something like the following, but I am unsure where to place it:

<%

Dim sql,conn

set conn=Server.CreateObject("ADODB.Connection")
conn.Open "driver={Microsoft Access Driver (*.mdb)};DBQ=D:\business\form.mdb;"

sql="INSERT INTO tblHitCount (HitNbr)"

conn.Execute sql
conn.close
%>

And advice would be appreciated.

Thanks

Steve

[Spudhead]

If you've got a database table with one integer field and one row, then you could do:

UPDATE tblHits SET intCount = (intCount+1)

I think, anyway :D And you'd probably want to check with a SQL expert on just how efficient it is.

Then put:

set conn = nothing

on the bottom of your code above, save it as hitcounter.asp and include it on every ASP page with:

<!--#INCLUDE FILE="hitcounter.asp"-->

Mind, doing a write to the database on every page load... that's gonna be some performance hit. Can't you just put the hitcount in an application variable and write it periodically to a file/database?

[SteveH]

Hello Spudhead

Thanks for your post.

So I don't use 'Insert'? OK, I'll try it. Do I put this code between <%code here%> in the ASP file I already have?

What if I later wanted to insert an IP address to correspond to that 'hit'? That is, if I had two field columns in my MS database, one for the 'hit' and another for the IP address (so that I can associate hit 1 with such and such an IP address, hit 2 with another IP address). Would I still use:

UPDATE tblHits SET intCount = (intCount+1)

set conn = nothing

Many thanks again.

Steve

[Spudhead]

Well.. the file-writing code you posted just overwrites a value in a file with the current hit count number. The database code I put above will do the same, but using a database instead of a file. And yes, for that you'd be updating a single record rather than inserting a new one. But what you're talking about here is logging individual hits rather than just keeping a count, and for that you would need an INSERT. You wouldn't even need a count column for that - you could just count the number of rows in your table.

But I really should stress that (a) what you're doing is probably going to increase the load on your server quite a lot, and (b) it's probably not the best way of getting the data you want: there are lots of stats packages available (like Google Analytics, for example) that will tell you how many people hit which pages and... all sorts of other things... not to mention your own server logs. Unless there's some application-specific reason that you need to be logging/counting these hits, I'd really advise you to save yourself the effort - and your server the load - and use something ready-built.