Logging page hits to a database 2

[etoucan]

Because I'm less than competent with ASP, I'm counting on you guys helping me with my counting problem. Any solution is very gratefully received.

I have a poetry-based website (

http://www.limestonemag.net

to get the flavour) that has a number of poems stored in an Access database and I want to find out which poems (say, the top 5) my readers are viewing the most. This means logging hits to a table in the db as and when they occur, depending on the poem ID they select. I can understand how to perform a db query & display the results, but I'm a total numbskull when it comes to writing to a db. So, what I need is to know how to write the ID to the table (i.e. the bit that comes after the ? in the URL). Please can anyone point me in the right direction or, even better chuck out some quick code? If I haven't explained this too cleverly, just shout.

 

[Lothario]

This might give you some idea...

http://www.aspemporium.com/aspapps.asp?eid=1

-L

 

[etoucan]

Lothario - Many thanks for that.

It looks interesting and I downloaded the example to test. But, the numbskulls prevailed and I couldn't get it to work. However, from what I could gather it appears to be code to get the popularity of links from a db. I need some simple code to add a record when a page is accessed - I think I could write the ASP code to read and display the most popular links once a table had been populated.

Any other ideas?

 

[Lothario]

Also try these links:

http://www.scit.wlv.ac.uk/~jphb/sst/asp/asp4.html

http://www.15seconds.com/issue/980805.htm

-L

 

[Genimuse]

Here's how I'd do it in ASP, with this code on the page where the visitor views the poem.

This assumes that your table is called Sample and that you have added a field to it called Views, with a default value of 0, and that your identifying field is called ID. It also assumes you have a querystring variable called ID with the ID of the poem. Obviously you'll need to change these things to meet your needs. The blue parts are the important code -- the rest is just the connection stuff, which you probably already have.

[COLOR=gray]'Create the database connection -- use your own before closing it[/color]
Dim objConn, strSQL
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open [i]YourConnectionStringHere[/i]

[COLOR=gray]'Create the SQL for incrementing a field[/color]
[COLOR=blue]strSQL = "UPDATE Sample SET Views = Views + 1 WHERE ID = " & Request.QueryString("ID")[/color]
[COLOR=gray]'Execute it[/color]
[COLOR=blue]objConn.Execute strSQL[/color]

[COLOR=gray]'Close the connection and get rid of it[/color]
objConn.Close
Set objConn = Nothing

(That first blue line is all one thing, ending with the querystring ID -- it just wraps here on screen.)

Then on your statistics page you just want the TOP 5 records from the table ORDERed BY Views.

Hope that helps.

 

[Genimuse]

ORDERed BY Views DESC, that is.

 

[etoucan]

Thanks for that Genimuse. I think this is the way to go and I can follow the logic. However, when I execute the following code

<%
'Create the database connection
Dim objConn, strSQLView
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open MM_Limestone2_STRING

'Create the SQL for incrementing a field
strSQLView = "UPDATE tblWorks SET fldViews = fldViews + 1_ WHERE fldPoemsID = " + Request.QueryString("fldPoemsID")

'Execute it
objConn.Execute strSQLView

'Close the connection and get rid of it
objConn.Close
Set objConn = Nothing
%>

I get this error pointing to the Execute command: Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query.

Any ideas?

 

[etoucan]

Ignore the undercore in the strSQLView variable.

 

[Genimuse]

Hmm. I'm puzzled. I don't see anything wrong with it. I'd guess that maybe your querystring variable is wrong? What happens if you add

Response.Write("<h3>" & strSQLView & "</h3>")
Response.Flush

after the creation of the string but before the execution? I'm sure it will still throw an error, but does the SQL look right?

 

[AnnYH]

If you use access as your database, before you run the page make sure you access database is closed. otherwise, it will show the error msg that you have got.

 

[etoucan]

Genimuse's solution worked perfectly once I'd managed to sort out the correct database write permissions in various places. So, many thanks - I now have it working the way it should.