Counting visits to productpages with database countfield

Could anyone help me with the following problem?

In order to collect the hits to different Productpages I inserted a
field (hitCount) in a table (Pagina) of a database (Kris.mdb)

Unfortunately the code doesn't seem to make a connection with the
database. The two records in the body cannot be found. If I remove
those It says that it cannot close a recordset that isn't open... I
figured it out that it must be the SQL line

rsCount.Source = "UPDATE Pagina SET hitCount = hitCount + 1 WHERE
PaginaId = " & Request("paginaId")

If I change that to:

rsCount.Source ="SELECT * FROM Pagina"

It shows some records in the body. So what goes wrong?

<%@LANGUAGE="VBSCRIPT"%>
<%Response.Buffer=true%>
<%
set rsCount=Server.CreateObject("ADODB.recordset")
rsCount.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=" & Server.Mappath("fpdb/kris.mdb")
rsCount.Source = "UPDATE Pagina SET hitCount = hitCount + 1 WHERE
PaginaId = " & Request("paginaId")
rsCount.CursorLocation = 2
rsCount.CursorType = 3
rsCount.LockType = 3
rsCount.Open()
rsCount_numRows = 0

%>

hitcount <%=(rsCount.Fields.Item("hitCount").Value)%>

paginaId <%=(rsCount.Fields.Item("ProductId").Value)%>

Re: Counting visits to productpages with database countfield

Jeroen wrote:
> Could anyone help me with the following problem?
>
> In order to collect the hits to different Productpages I inserted a
> field (hitCount) in a table (Pagina) of a database (Kris.mdb)
>
> Unfortunately the code doesn't seem to make a connection with the
> database. The two records in the body cannot be found. If I remove
> those It says that it cannot close a recordset that isn't open... I
> figured it out that it must be the SQL line
>
> rsCount.Source = "UPDATE Pagina SET hitCount = hitCount + 1 WHERE
> PaginaId = " & Request("paginaId")

1. Do not use a recordset to execute a query that does not return records
(only SELECT statements return records. DML statements - UPDATE, INSERT and
DELETE - do not return records). Use Execute instead. Either the
connection.execute statement or the coimmand.execute statement (see below)

2. Do not use dynamic sql, especially when you perform no data validation.
You open the door for hackers
(http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23) as well as
increasing the difficulty of actually writing your code, since you need to
correctly handle delimiters, etc. I prefer saved parameter queries
(http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&se lm=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl),
but if you have some reason to avoid them, you can use a Command object to
parameterize your sql strings
(http://groups-beta.google.com/group/microsoft.public.inetse rver.asp.db/msg/72e36562fee7804e)

> set rsCount=Server.CreateObject("ADODB.recordset")
> rsCount.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data
> Source=" & Server.Mappath("fpdb/kris.mdb")

Do not use a connection string to open a recordset. Doing so disables
connection pooling. Always use an explicit connection object.


Here is how I would accomplish your task. I would open your database in
Access and create a saved parameter query called "qUpdPageCount" using this
sql:
UPDATE Pagina SET hitCount = hitCount + 1
WHERE PaginaId = [pPage]

I would then create another saved query called "qGetPageCount" using this
sql (avoid using "Select *" in production code -
http://www.aspfaq.com/show.asp?id=2096):
SELECT ProductId, hitCount
FROM Pagina
WHERE PaginaId = [pPage]

Then, in ASP, I would do this:

<%
dim cn, rs, arData, hits, product

'first, validate that Request("paginaId") contains what you expect
'it to contain. Then:

set cn=createobject("adodb.connection")
cn.open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=" & Server.Mappath("fpdb/kris.mdb")

cn.qUpdPageCount Request("paginaId")

set rs=CreateObject("ADODB.recordset")
cn.qGetPageCount Request("paginaId"), rs
if not rs.EOF then arData = rs.getrows

rs.close:set rs=nothing
cn.close:set cn=nothing

if isarray(arData) then
product=arData(0,0)
hits=arData(1,0)
erase arData
end if
%>

hitcount <%=hits%>

paginaId <%= product %>