Add 1 hit everytime a person with a username logs on my site

[chris_angell]

Hello I am very stuck


I am trying to make a input box that shows how many times a person with a username from my Database has logged on my site. (they have to get a username before entering my site) How can I have a field in a database that adds 1 each time a person logs on to a page for that specific person,


ie they have a ID named 16 and they use 16 to log in how can I make a field in the database say hits 1 2 3 .. ans so on.when people enter it adds one..

help because I can't think of a way to do it.. I am stuck ?? this is all new to me..

thanks chris

[oracleguy]

In the same table that the usernames are stored, add a new field called "hits" or something.

Then when the person logs in, just run a SQL query to add one to their count.

The query would be something like this:

"UPDATE <tablename> SET hits=hits+1 WHERE ID=" & <USERID> & ";"

And since it would be in the same table, you can grab it when you do your select query that would come before this one, and use the number in that one and add one so you can display the number of log ins if you'd like.

[chris_angell]

I have tried but I am failing...

I have this but I am not to sure where I am trying to place the info.. I have made a field called Hits.. but I am only used to silmpe asp ??? hmmm

here is my code for login on.. where can I add "UPDATE <tablename> SET hits=hits+1 WHERE ID=" & <USERID> & ";"

my code

<% @LANGUAGE = VBScript %>
<!-- #INCLUDE FILE="admin/dbconnection.asp" -->
<%

username=request.form("UserName")
password=request.form("Password")
companyname=request.form("CompanyName")%>

<%strSQL = "SELECT * FROM tblClients WHERE tblClients.UserName = '" & username & "' AND tblClients.Password = '" & password & "' AND tblClients.CompanyName = '" & companyname & "'"
rst2.open strSQL, strConnect, ado_Openkeyset, ado_LockOptimistic
if not rst2.eof then%>
<%if rst2("SalesOrTech")=false then
id=rst2("RefID")
rst2.close
set rst2=nothing
response.redirect "sales/sales.asp?u=" & username & "&p=" & password & "&id=" & id
else
id=rst2("RefID")
rst2.close
set rst2=nothing
response.redirect "technical/tech.asp?u=" & username & "&p=" & password & "&id=" & id
end if
end if
rst2.close
set rst2=nothing
response.redirect "pages/no_login_f.htm"
%>




I hope this helps :)

[oracleguy]

Originally posted by chris_angell

<% @LANGUAGE = VBScript %>
<!-- #INCLUDE FILE="admin/dbconnection.asp" -->
<%

username=request.form("UserName")
password=request.form("Password")
companyname=request.form("CompanyName")%>

<%strSQL = "SELECT * FROM tblClients WHERE tblClients.UserName = '" & username & "' AND tblClients.Password = '" & password & "' AND tblClients.CompanyName = '" & companyname & "'"
rst2.open strSQL, strConnect, ado_Openkeyset, ado_LockOptimistic
if not rst2.eof then%>
<%if rst2("SalesOrTech")=false then
id=rst2("RefID")
rst2.close
set rst2=nothing
response.redirect "sales/sales.asp?u=" & username & "&p=" & password & "&id=" & id
else
id=rst2("RefID")
rst2.close
set rst2=nothing
response.redirect "technical/tech.asp?u=" & username & "&p=" & password & "&id=" & id
end if

strConnect.Execute("UPDATE tblClients SET hits=hits+1 WHERE RefID=" & id & ";")

end if
rst2.close
set rst2=nothing
response.redirect "pages/no_login_f.htm"
%>


Also I noticed you are passing the username and more importantly the password in the url... this isn't a good idea. Because then the password is out in the open. You should use something like session variables instead.

[whammy]

The more I mess with hidden fields, querystrings, form input, etc. the more I realize just how insecure a lot of websites are to some fairly simple attacks!

If you're passing ANYTHING in a querystring (or hidden field, etc.) it behooves you to make sure you're receiving exactly the kind of data you expect server-side, BEFORE using it in a SQL statement. This isn't just for passwords, etc. but goes for just about anything.

You have to consider "cross-site scripting", SQL injection attacks, etc....

Otherwise, someone might be able to do some pretty nasty things to your database...

This article doesn't cover the last aspect, but should give you an idea of one of the milder things that could be accomplished without paying close attention to these types of concerns:

http://tiberi.us/view_article.aspx?article_id=35

[chris_angell]

thanks very much for your help.. first of all.

but, there is always a but.. but I have put the code into place

---------------------------------------------------

strConnect.Execute("UPDATE tblClients SET hits=hits+1 WHERE RefID=" & id & ";")

---------------------------------------------------

but the serve is bringing back an error.....

---------------------------------------------------

Object required: 'provider=Microsoft.J'

---------------------------------------------------


I take it the server I am using is old or doesn't have certain objects it can create ????


what can I do to over come this.. all i want is to add 1 to a number field in the database to a specific ID.... HMMM


any more ideas... cheers

[oracleguy]

It might be a problem with your database connection code. Posting it might help... Whammy might have some more ideas.

[chris_angell]

here is my connection string.. I don't know if this will help.. I have had a look to see If i could see anything.. but my knowledge is minimal in asp... you guys are the true asp warriors

<%
'Variables
Dim rst 'Recordset
Dim strSQL 'The sql string
dim strConnect 'the connection string

'connection variables
Dim ado_Openkeyset
dim ado_lockOptimistic

'For the recordset arguments
ado_Openkeyset=1
ado_lockOptimistic=3

'assign the connection string, to connect to the database
strConnect="provider=Microsoft.Jet.OLEDB.4.0; data source=" & server.mappath("db/logins97.mdb")
'strConnect="Driver={Microsoft Access Driver (*.mdb)}; DBQ=D: \Inetpub\wwwroot\www root\Temp\admin\ansa\db\logins.mdb"

'set up the recordset
Set rst=server.createobject("ADODB.RECORDSET")
Set rst2=server.createobject("ADODB.RECORDSET")
%>

[whammy]

You need:

Set strConnect="provider=Microsoft.Jet.OLEDB.4.0; data source=" & server.mappath("db/logins97.mdb")

... that should solve that specific error...

[whammy]

P.S. If that doesn't work, (but it should), this works on every server I have tried it on (so far):

sConnString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
Mid(Server.MapPath("\"), 1, InStrRev(Server.MapPath("\"),"\")-1) & "\db\login.mdb;" & _
"Persist Security Info=False;"

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open sConnString

The reason for mapping the path like that, is I prefer to store my database files (when I use access, anyway) above my root directory, so they cannot be directly downloaded. A little trick I learned back when I signed up for my first brinkster.com account...