Hi,

Thinking to add a feature in my new site, how many users read the articles.

Is there any tutorial I can follow, how can I track the visitors?

Using

DWMX, ASP, VBScript, Access DB

Please advice

Regards

Babu

Are your news items held in a database or are they just static pages?

Regards

Vince Baker
<strong>DMX Zone Manager</strong>

[VBScript | ASP | HTML | CSS | SQL | Oracle | AS400 | ERP Logic | Hosting]

Hi Thanks

Using Access Database

Culum: ID Autonumber
Article_Name:
Image_1:


Look forward from you

Babu

add another table to your db to hold the article read (you can also get the users details if you have a login system and the datetime).

On the viewing page, you can add a manual sql insert action that will record the article id and i suggest the datetime....

Your table would be as follows:

tbl_article_stats

Columns

article_stat_id (autonumber)
article_id_fk (number - your article unique id)
article_stat_datetime (datetime - default value - now())

you can obviously add more to this if you want.

THen, at the top of your page just after the recordset getting your article info add the following (change table names, column names accordingly etc)

&lt;%
sql ="INSERT INTO tbl_article_stats (article_id) VALUES (" & yourrecordsetname("your_unique_article_id_field_name" & ""
%&gt;

&lt;%
set insertstat = Server.CreateObject("ADODB.Command"
insertstat .ActiveConnection = MM_enter your connection name here_STRING
insertstat .CommandText = sql
insertstat .CommandType = 1
insertstat .CommandTimeout = 0
insertstat .Prepared = true
insertstat .Execute()
%&gt;

This will automatically take the article unique id and place it into the table for your stats....

post back if you have any questions.


Regards

Vince Baker
&lt;strong&gt;DMX Zone Manager&lt;/strong&gt;

[VBScript | ASP | HTML | CSS | SQL | Oracle | AS400 | ERP Logic | Hosting]

Thanks again


I am going to try it now...lets see what result I can get.

I will be here again.

Thanks a lot

Babu

Edited by - babui on 03 Mar 2005 15:39:35

Hi Vince

Working on the page now, just little bit confuse....

In the BODY Section Where I showing the Read Stats
In a table &lt;td&gt; 0 Users read it&lt;/td?

Which option shall I copy hear '0' position.

Look forward from you

Thanks

Babu

You need to add a recordset to the page that will count up the number or records (rows) filtered on the article unique id,

for example


Select COUNT article_id_fk AS ReadNumber
from tbl_article_stats
Where article_id_fk = varID

define your variable: (in advanced mode in the recordset builder page)

Name: varID
Default value: (anything here.....long strange number is always good)
Runtime value: Request.Querystring("name of your article id field in the url"

Then, add to the body:

&lt;td&gt; &lt;%=recordsetname("ReadNumber"%&gt; Users read it&lt;/td&gt;





Regards

Vince Baker
<strong>DMX Zone Manager</strong>

[VBScript | ASP | HTML | CSS | SQL | Oracle | AS400 | ERP Logic | Hosting]

Hi Vince

I am getting an error on the page:

Microsoft VBScript runtime (0x800A01F9)
Invalid or unqualified reference
/House/london_lets.asp, line 53


OK, here I can explain What I have done so far:

New Table:

Table Name: tbl_article_stats

Culumns: article_stat_id, Property_ID, article_stat_datetime

Table:
myLet:
Culums: Property_ID, Title

Recordsets:

&lt;%
Dim rsLdn_lets__MMCOLParam
rsLdn_lets__MMCOLParam = "%"
If (Request.Form("key" &lt;&gt; "" Then
rsLdn_lets__MMCOLParam = Request.Form("key"
End If
%&gt;
&lt;%
Dim rsLdn_lets
Dim rsLdn_lets_numRows

Set rsLdn_lets = Server.CreateObject("ADODB.Recordset"
rsLdn_lets.ActiveConnection = MM_HouseConnection_STRING
rsLdn_lets.Source = "SELECT Region, Title, Short_Description, Type, Bedrooms, Per_week, Property_ID, Imain FROM myLet WHERE Title LIKE '" + Replace(rsLdn_lets__MMCOLParam, "'", "''" + "' ORDER BY Per_week ASC"
rsLdn_lets.CursorType = 2
rsLdn_lets.CursorLocation = 3
rsLdn_lets.LockType = 1
rsLdn_lets.Open()

rsLdn_lets_numRows = 0
%&gt;
&lt;%
Dim rsCount__varID
rsCount__varID = "1"
If (Request.QueryString("article_stat_id" &lt;&gt; "" Then
rsCount__varID = Request.QueryString("article_stat_id"
End If
%&gt;
&lt;%
Dim rsCount
Dim rsCount_numRows

Set rsCount = Server.CreateObject("ADODB.Recordset"
rsCount.ActiveConnection = MM_HouseConnection_STRING
rsCount.Source = "SELECT Property_ID AS ReadNumber FROM tbl_article_stats WHERE article_stat_id = " + Replace(rsCount__varID, "'", "''" + ""
rsCount.CursorType = 0
rsCount.CursorLocation = 2
rsCount.LockType = 1
rsCount.Open()

rsCount_numRows = 0
%&gt;
&lt;%
sql ="INSERT INTO tbl_article_stats (article_stat_id) VALUES (" & rsLdn_lets.Fields.Item("Title" & ""
%&gt;
&lt;%
set insertstat = Server.CreateObject("ADODB.Command"
insertstat .ActiveConnection = MM_HouseConnection_STRING [Line 53: error]
insertstat .CommandText = sql
insertstat .CommandType = 1
insertstat .CommandTimeout = 0
insertstat .Prepared = true
insertstat .Execute()
%&gt;

Body:

&lt;%=rsLdn_lets.Fields("ReadNumber"%&gt;

I think I got something wrong, probably I didn't understand properly....

Any idea - how I can get it right?

Many thanks

Babu

two things,

in the sql line:

&lt;%
sql ="INSERT INTO tbl_article_stats (article_stat_id) VALUES (" & rsLdn_lets.Fields.Item("Title" & ""
%&gt;

you are missing .value i.e.

&lt;%
sql ="INSERT INTO tbl_article_stats (article_stat_id) VALUES (" & rsLdn_lets.Fields.Item("Title".value & ""
%&gt;

Try that, but also, do you have the connection line at the top of your page?

Are you using an ODBC connection with a DSN name?


Regards

Vince Baker
<strong>DMX Zone Manager</strong>

[VBScript | ASP | HTML | CSS | SQL | Oracle | AS400 | ERP Logic | Hosting]

HI Vince

Just checked

Found nothing with the SQL

&lt;%
sql ="INSERT INTO tbl_article_stats (article_stat_id) VALUES (" & rsLdn_lets.Fields.Item("Title".value & ""
%&gt;

Yeh I am using ODBC Connection
and My is included with the

&lt;!--#include file="Connections/HouseConnection.asp" --&gt;

Look forward from you

Babu

Just looking at what you posted back,

insertstat .ActiveConnection = MM_HouseConnection_STRING [Line 53: error]
insertstat .CommandText = sql
insertstat .CommandType = 1
insertstat .CommandTimeout = 0
insertstat .Prepared = true
insertstat .Execute()

There appears to be a space after the insertstat and the dot (.) is this so on your page?

Regards

Vince Baker
<strong>DMX Zone Manager</strong>

[VBScript | ASP | HTML | CSS | SQL | Oracle | AS400 | ERP Logic | Hosting]

Hi Vince

Yes I just checked it, there is a space somehouse came in but I didn't type it.........

Now fixed it......


Got another error:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Seyssel Street'.
/House/london_lets.asp, line 58

Line 58 is: insertstat.Execute()



Babu

Place a ' character infront of that line and add a line underneath:

Response.write(sql)

This will write the sql command to the page....can you then copy and post that line here as something is going wrong with the sql line.

We will get there!

Regards

Vince Baker
<strong>DMX Zone Manager</strong>

[VBScript | ASP | HTML | CSS | SQL | Oracle | AS400 | ERP Logic | Hosting]

Here is SQL

&lt;%
sql ="INSERT INTO tbl_article_stats (article_stat_id) VALUES (" & rsLdn_lets.Fields.Item("Title".value & ""
%&gt;
&lt;%
set insertstat = Server.CreateObject("ADODB.Command"
insertstat.ActiveConnection = MM_HouseConnection_STRING
insertstat.CommandText = sql
insertstat.CommandType = 1
insertstat.CommandTimeout = 0
insertstat.Prepared = true
'insertstat.Execute()
Response.write(sql)
%&gt;


Another Error:

Error Type:
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.
/House/london_lets.asp, line 645


Line 645: &lt;%=rsLdn_lets.Fields("ReadNumber"%&gt;

Babu

Hi Vince

I wonder I am not good in SQL, but a little bit confuse in this line here...have a look....is this ok?

&lt;%
sql ="INSERT INTO tbl_article_stats (article_stat_id) VALUES (" & rsLdn_lets.Fields.Item("Title".value & ""
%&gt;

Here I used ("Title" from table myLet.
But in the
tbl_article_stats hasn't got any culumn like this, I mean no corresponding....

Does it mean anything? May be I am wrong......I am not sure..

Babu

Hi Vince

Found another descrepency

I mentioned previously that

My table contains - myLet

Culum: ID Autonumber
Article_Name:
Image_1:

ID: is not a auto number & not even number, its a text
You added:

article_stat_id (autonumber)
article_id_fk (number - your article unique id)
article_stat_datetime (datetime - default value - now())

article_id_fk
which became as: Property_ID

Does it make any difference?

Babu

Yes, when writing the sql string a text field must have single quotes around it...that could be our problem...

try this line:

&lt;%
sql ="INSERT INTO tbl_article_stats (article_stat_id) VALUES ('" & rsLdn_lets.Fields.Item("Title".value & "')"
%&gt;

see what happens with that in place of the old sql line.

Regards

Vince Baker
<strong>DMX Zone Manager</strong>

[VBScript | ASP | HTML | CSS | SQL | Oracle | AS400 | ERP Logic | Hosting]

Also, in your recordset to get the count you have missed some thing out:

rsCount.Source = "SELECT Property_ID AS ReadNumber FROM tbl_article_stats WHERE article_stat_id = " + Replace(rsCount__varID, "'", "''" + ""

THat is the line you have, you need to have

rsCount.Source = "SELECT Count(Property_ID) AS ReadNumber FROM tbl_article_stats WHERE article_stat_id = " + Replace(rsCount__varID, "'", "''" + ""

So you get a count of how many rows are returned rather than just the property id itself.

Regards

Vince Baker
<strong>DMX Zone Manager</strong>

[VBScript | ASP | HTML | CSS | SQL | Oracle | AS400 | ERP Logic | Hosting]

Hi Vince

Shall remove

'insertstat.Execute()
Response.write(sql)

to previous stat?

Babu

yes, hopefully all will then work.

Regards

Vince Baker
<strong>DMX Zone Manager</strong>

[VBScript | ASP | HTML | CSS | SQL | Oracle | AS400 | ERP Logic | Hosting]

Hi Vince

I just changed at previous stat

and got new error here

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query.
/House/london_lets.asp, line 58


I think it is for the Inetpub permission.

Is that right?

Babu

you must give the internet guest account rights to write in the folder where the database is kept....

Regards

Vince Baker
<strong>DMX Zone Manager</strong>

[VBScript | ASP | HTML | CSS | SQL | Oracle | AS400 | ERP Logic | Hosting]

Vince

I changed the directory attribute but no change

Error:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
/House/london_lets.asp, line 58


Babu

add the

'insertstat.Execute()
Response.write(sql)

lines back in and run the page then look and see what is written in the page in the browser

Regards

Vince Baker
<strong>DMX Zone Manager</strong>

[VBScript | ASP | HTML | CSS | SQL | Oracle | AS400 | ERP Logic | Hosting]

Hi

Final SQL And Code stands like:


&lt;%
Dim rsCount__varID
rsCount__varID = "1"
If (Request.QueryString("article_stat_id" &lt;&gt; "" Then
rsCount__varID = Request.QueryString("article_stat_id"
End If
%&gt;
&lt;%
Dim rsCount
Dim rsCount_numRows

Set rsCount = Server.CreateObject("ADODB.Recordset"
rsCount.ActiveConnection = MM_HouseConnection_STRING
rsCount.Source = "SELECT Count(Property_ID) AS ReadNumber FROM tbl_article_stats WHERE article_stat_id = " + Replace(rsCount__varID, "'", "''" + ""
rsCount.CursorType = 0
rsCount.CursorLocation = 2
rsCount.LockType = 1
rsCount.Open()

rsCount_numRows = 0
%&gt;
&lt;%
sql ="INSERT INTO tbl_article_stats (article_stat_id) VALUES ('" & rsLdn_lets.Fields.Item("Title".value & "')"
%&gt;
&lt;%
set insertstat = Server.CreateObject("ADODB.Command"
insertstat.ActiveConnection = MM_HouseConnection_STRING
insertstat.CommandText = sql
insertstat.CommandType = 1
insertstat.CommandTimeout = 0
insertstat.Prepared = true
insertstat.Execute()
%&gt;

Body: &lt;%=rsLdn_lets.Fields("ReadNumber"%&gt;

Thanks

Babu

HI

Checked that

URL: localhost/House/london_lets.asp

Error:

Error Type:
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.
/House/london_lets.asp, line 645

Babu