|
 |
asp_databases thread: SV: RE: Please answer this count-problem!
Message #1 by "SD-Studios" <info@s...> on Thu, 8 Nov 2001 14:11:25 +0100
|
|
But then the database will be pretty large in filesize =)
--
Martin Johansson
-----Ursprungligt meddelande-----
Från: Steve Carter [mailto:Steve.Carter@t...]
Skickat: den 8 november 2001 14:03
Till: ASP Databases
Ämne: [asp_databases] RE: Please answer this count-problem!
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')
>
Message #2 by Steve Carter <Steve.Carter@t...> on Thu, 8 Nov 2001 13:22:58 -0000
|
|
Yes, although if you are expecting THAT much traffic then maybe you
should
be using SQL server :-)
The tricky bit is that AFAIK there is no way to express in SQL "Insert
if
it's not there, otherwise update". Ah, here you go
SQL =3D"SELECT datum, hits FROM downloads WHERE datum =3D '" & date &
"'"
oRs.open SQL, oConn, , adOpenKeyset, adLockPessimistic
' check the positions of these parameters!
' I'm typing straight into outlook here!
With oRs
if .BOF and .EOF ' There are no records
.AddNew
.Fields("datum") =3D Format(Now,"dd/mm/yyyy")
.Fields("hits") =3D 1
else
.Fields("hits") =3D .Fields("hits") + 1
end if
.Update
.Close
End With
> -----Original Message-----
> From: SD-Studios [mailto:info@s...]
> Sent: 08 November 2001 13:11
> To: ASP Databases
> Subject: [asp_databases] SV: RE: Please answer this count-problem!
>
>
> But then the database will be pretty large in filesize =3D)
> --
> Martin Johansson
>
> -----Ursprungligt meddelande-----
> Fr=E5n: Steve Carter [mailto:Steve.Carter@t...]
> Skickat: den 8 november 2001 14:03
> Till: ASP Databases
> =C4mne: [asp_databases] RE: Please answer this count-problem!
>
>
> Try this instead:
>
> When you write the hit count, always insert todays date.
>
> SQL4 =3D "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 =3D Server.CreateObject("ADODB.Connection")
> > objCon2.Open "Driver=3D{Microsoft Access Driver (*.mdb)};dbq=3D" &
> > Server.MapPath("database.mdb")
> > Set rsCounter2 =3D Server.CreateObject("ADODB.Recordset")
> > SQL3 =3D "SELECT * FROM downloads ORDER BY datum DESC"
> > rsCounter2.Open SQL3, objCon2
> >
> > datum =3D rsCounter2("datum")
> >
> > If datum =3D date Then
> >
> > Set rsCounter3 =3D Server.CreateObject("ADODB.Recordset")
> > Set objCon3 =3D Server.CreateObject("ADODB.Connection")
> > objCon3.Open "Driver=3D{Microsoft Access Driver (*.mdb)};dbq=3D" &
> > Server.MapPath("database.mdb")
> >
> > SQL4 =3D "SELECT * FROM downloads WHERE datum =3D '" & date & "'"
> > rsCounter3.Open SQL4, objCon3, adOpenKeyset, adLockPessimistic
> >
> > iCount2 =3D rsCounter2("downloads")
> >
> > rsCounter3.Fields("downloads") =3D iCount2 + 1
> > rsCounter3.Update
> > rsCounter3.Close
> >
> > objCon3.Close
> >
> > Else
> >
> > Set rsCounter4 =3D Server.CreateObject("ADODB.Recordset")
> > Set objCon4 =3D Server.CreateObject("ADODB.Connection")
> > objCon4.Open "Driver=3D{Microsoft Access Driver (*.mdb)};dbq=3D" &
> > Server.MapPath("database.mdb")
> >
> > SQL5 =3D "SELECT * FROM downloads"
> > rsCounter4.Open SQL5, objCon4, adOpenKeyset, adLockPessimistic
> >
> > iCount3 =3D 0
> >
> > With rsCounter4
> > .AddNew
> > .Fields("downloads") =3D iCount3 + 1
> > .Fields("datum") =3D 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')
> >
>
>
> $subst('Email.Unsub')
>
>
>
>
> steve.carter@t...
> $subst('Email.Unsub')
>
Message #3 by "SD-Studios" <info@s...> on Thu, 8 Nov 2001 14:39:19 +0100
|
|
Okay..thanks! =)
--
Martin Johansson
-----Ursprungligt meddelande-----
Från: Ken Schaefer [mailto:ken@a...]
Skickat: den 8 november 2001 13:46
Till: ASP Databases
Ämne: [asp_databases] Re: Please answer this count-problem!
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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
|
|
|
View Cart
