|
 |
asp_databases thread: Please answer this count-problem!
Message #1 by "SD-Studios" <info@s...> on Thu, 8 Nov 2001 13:31:33 +0100
|
|
Could someone please answer this?
The code is suppose to count hits per day. But instead it uses addnew to
insert a bunch of inserts with the same date showing that 1 person have
downloaded. How can I fix this so it will autoupdate the downloads-field if
the date is the same as todays date and addnew if it's tomorrow's date?
Please help me..!
' Beginning overall download count
Set objCon2 = Server.CreateObject("ADODB.Connection")
objCon2.Open "Driver={Microsoft Access Driver (*.mdb)};dbq=" &
Server.MapPath("database.mdb")
Set rsCounter2 = Server.CreateObject("ADODB.Recordset")
SQL3 = "SELECT * FROM downloads ORDER BY datum DESC"
rsCounter2.Open SQL3, objCon2
datum = rsCounter2("datum")
If datum = date Then
Set rsCounter3 = Server.CreateObject("ADODB.Recordset")
Set objCon3 = Server.CreateObject("ADODB.Connection")
objCon3.Open "Driver={Microsoft Access Driver (*.mdb)};dbq=" &
Server.MapPath("database.mdb")
SQL4 = "SELECT * FROM downloads WHERE datum = '" & date & "'"
rsCounter3.Open SQL4, objCon3, adOpenKeyset, adLockPessimistic
iCount2 = rsCounter2("downloads")
rsCounter3.Fields("downloads") = iCount2 + 1
rsCounter3.Update
rsCounter3.Close
objCon3.Close
Else
Set rsCounter4 = Server.CreateObject("ADODB.Recordset")
Set objCon4 = Server.CreateObject("ADODB.Connection")
objCon4.Open "Driver={Microsoft Access Driver (*.mdb)};dbq=" &
Server.MapPath("database.mdb")
SQL5 = "SELECT * FROM downloads"
rsCounter4.Open SQL5, objCon4, adOpenKeyset, adLockPessimistic
iCount3 = 0
With rsCounter4
.AddNew
.Fields("downloads") = iCount3 + 1
.Fields("datum") = Date
.Update
.Close
End With
objCon4.Close
End If
rsCounter2.Close
objCon2.Close
' End overall download count
How should I do to make it + 1 where the date is the same instead?
--
Martin Johansson
Message #2 by "Ken Schaefer" <ken@a...> on Thu, 8 Nov 2001 23:45:52 +1100
|
|
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "SD-Studios" <info@s...>
Subject: [asp_databases] Please answer this count-problem!
: The code is suppose to count hits per day. But instead it uses addnew to
: insert a bunch of inserts with the same date showing that 1 person have
: downloaded. How can I fix this so it will autoupdate the downloads-field
if
: the date is the same as todays date and addnew if it's tomorrow's date?
: Please help me..!
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
I'd change your code around completely!
What you need is:
a) Test database to see if there is a record for today
b) If there is a record, then update that record, else insert a new record
with today's date, and a download value of 1
' Access has problems with Date criteria:
' www.adopenstatic.com/faq/dateswithaccess.asp
dteDate = Year(Date()) & "/" & Month(Date()) & "/" & Day(Date())
strSQL = _
"SELECT Downloads " & _
"FROM Downloads " & _
"WHERE Datum = #" & dteDate & "#"
objRS.Open strSQL, objConn, adOpenForwardOnly, adLockReadOnly, adCmdText
If objRS.EOF then
' No record for today, insert a new record
strSQL = _
"INSERT INTO Downloads " & _
"(Datum, Downloads) " & _
"VALUES(Date(), 1)"
Else
' Update existing record
strSQL = _
"UPDATE Downloads " & _
"SET Downloads = Downloads + 1 " & _
"WHERE Datum = #" & dteDate & "#"
End If
objConn.Execute strSQL,,adCmdText+adExecuteNoRecords
objRS.close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
Cheers
Ken
Message #3 by Steve Carter <Steve.Carter@t...> on Thu, 8 Nov 2001 13:02:44 -0000
|
|
Try this instead:
When you write the hit count, always insert todays date.
SQL4 = "INSERT INTO downloads (datum) VALUES ('" & date & "')"
objCon3.execute SQL4
Then when you want to see the results, use a query to read the table, adding
up the hits per day
SELECT datum, count(datum) TheCount FROM downloads GROUP BY datum
So, e.g. your table with be
ID datum
1 01/01/2001
2 01/01/2001
3 01/01/2001
4 01/01/2001
5 01/01/2001
6 01/01/2001
7 02/01/2001
8 02/01/2001
9 02/01/2001
10 02/01/2001
11 03/01/2001
12 03/01/2001
13 03/01/2001
and the results of the SELECT above will be
datum TheCount
01/01/2001 6
02/01/2001 4
03/01/2001 3
> -----Original Message-----
> From: SD-Studios [mailto:info@s...]
> Sent: 08 November 2001 12:32
> To: ASP Databases
> Subject: [asp_databases] Please answer this count-problem!
>
>
> Could someone please answer this?
>
> The code is suppose to count hits per day. But instead it
> uses addnew to
> insert a bunch of inserts with the same date showing that 1
> person have
> downloaded. How can I fix this so it will autoupdate the
> downloads-field if
> the date is the same as todays date and addnew if it's
> tomorrow's date?
> Please help me..!
>
> ' Beginning overall download count
> Set objCon2 = Server.CreateObject("ADODB.Connection")
> objCon2.Open "Driver={Microsoft Access Driver (*.mdb)};dbq=" &
> Server.MapPath("database.mdb")
> Set rsCounter2 = Server.CreateObject("ADODB.Recordset")
> SQL3 = "SELECT * FROM downloads ORDER BY datum DESC"
> rsCounter2.Open SQL3, objCon2
>
> datum = rsCounter2("datum")
>
> If datum = date Then
>
> Set rsCounter3 = Server.CreateObject("ADODB.Recordset")
> Set objCon3 = Server.CreateObject("ADODB.Connection")
> objCon3.Open "Driver={Microsoft Access Driver (*.mdb)};dbq=" &
> Server.MapPath("database.mdb")
>
> SQL4 = "SELECT * FROM downloads WHERE datum = '" & date & "'"
> rsCounter3.Open SQL4, objCon3, adOpenKeyset, adLockPessimistic
>
> iCount2 = rsCounter2("downloads")
>
> rsCounter3.Fields("downloads") = iCount2 + 1
> rsCounter3.Update
> rsCounter3.Close
>
> objCon3.Close
>
> Else
>
> Set rsCounter4 = Server.CreateObject("ADODB.Recordset")
> Set objCon4 = Server.CreateObject("ADODB.Connection")
> objCon4.Open "Driver={Microsoft Access Driver (*.mdb)};dbq=" &
> Server.MapPath("database.mdb")
>
> SQL5 = "SELECT * FROM downloads"
> rsCounter4.Open SQL5, objCon4, adOpenKeyset, adLockPessimistic
>
> iCount3 = 0
>
> With rsCounter4
> .AddNew
> .Fields("downloads") = iCount3 + 1
> .Fields("datum") = Date
> .Update
> .Close
> End With
>
> objCon4.Close
>
> End If
>
> rsCounter2.Close
> objCon2.Close
> ' End overall download count
>
> How should I do to make it + 1 where the date is the same instead?
> --
> Martin Johansson
>
>
>
> steve.carter@t...
> $subst('Email.Unsub')
>
|
|
|
View Cart
