Counter

[Bobs390]

Hi,

I have the following asp page that processes data submitted from links in other pages.

<%
'declare variables passed from links
  SiteURL = Request.QueryString("URL")
  site = Request.QueryString("site")
  city = Request.QueryString("city")
  If Len(SiteURL) > 0 Then
	Set objConn = CreateObject("ADODB.Connection")
	objConn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("clicks.mdb"))
	
	' If the URL is already in the database an error will be thrown and we perform an UPDATE
	' Instead of a INSERT.
	on error resume next
	objConn.Execute "INSERT INTO SiteClicks(site,city,thisdate,clicks) VALUES('" & site & "' , '" & city & "', '" & date & "', 1)"   
		If Err.Number <> 0 Then
		'objConn.Execute "UPDATE SiteClicks SET city = '" & city & "'"
		objConn.Execute "UPDATE SiteClicks SET clicks = clicks + 1 WHERE site='" & site & "' AND WHERE city='" & city & "'"
		'objConn.Execute "UPDATE SiteClicks SET thisdate = '" & date & "'" 
	End If
	objConn.Close
	Set objConn= Nothing
	Response.redirect SiteURL
  End If
%>

The point is to count the hits from the link on a particular page. It reads the city and site variable and adds the clicks in increments. However currently it wont add by city, only by site and even then it will only do it once. It also needs to check to see if the site and corresponding city are already in a row in the database, and if so just add 1 to the click.

Hope this kind of makes sense.

Thanks!

 

[Tarwn]

As long as the primary key for the table is site + city + date then you should be getting the results your looking for already.

If your primary key is anything else (site for instance) then a record will be inserted on the first hit, andfrom then on only work for that one site/city/date combination because the site a;lready exists as a key, causing other site/city/date combinations to fail.

The method of trying an insert and using failure to update is a hack to try and cut down on calls to the db, if it has instead been trying to select for that site, city, date (then decideing to either insert or update) itwould have cost you an extra call to the database, but you wouldn't behaving the problem you are now (if i called it right on that primary key thing).

-T

_{01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111}
Need an expensive ASP developer in the North Carolina area? Feel free to let me know.

 

[Nevermoor]

Instead of on error resume next, try

"SELECT * from SiteClicks WHERE (site ='"&site&"') AND (city = '"&city&"')

then test of EOF and do the appropriate step.

Also, as far as syntax goes:
"UPDATE SiteClicks SET clicks = clicks + 1, thisdate = '"&NOW()&"' WHERE (site='...') AND (city='...')"

If you're using SQL server, you should test your syntax on it before coding into an ASP page...

 

[Bobs390]

Hi,

I am using an access database by the way.

Tarwn,
I have checked the primary key and it was set to site. If I have no primary key all the hits are recorded, but individually. The ones that match the fields of site and city dont just increment the clicks value by one. As you said "As long as the primary key for the table is site + city + date" can a table have more than one primary key?

Nevermoor,
What you wrote make sense to me as well, but I am not sure how that translares into my code, what I have now is...

<%
'declare variables passed from links
  SiteURL = Request.QueryString("URL")
  site = Request.QueryString("site")
  city = Request.QueryString("city")
  If Len(SiteURL) > 0 Then
	Set objConn = CreateObject("ADODB.Connection")
	objConn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("clicks.mdb"))
	
	on error resume next
	objConn.Execute "INSERT INTO SiteClicks(site,city,thisdate,clicks) VALUES('" & site & "' , '" & city & "', '" & date & "', 1)"   
	If Err.Number <> 0 Then
				objConn.Execute "UPDATE SiteClicks SET clicks = clicks + 1 thisdate = '" & date & "' WHERE site='" & site & "' AND WHERE city='" & city & "'"
	End If
	objConn.Close
	Set objConn= Nothing
	Response.redirect SiteURL
  End If
%>

Thanks again.

 

[Bobs390]

I have just realised that my last post made little sense, basically what I need to do is:

1- Check records in table to see if same data for corresponding site and city are already there
2- If data exists as above then update the clicks +1 and stamp the new date on it
3- if no data exists insert the site, city date and a click of 1 into a new record
4- end

cheers

 

[Bobs390]

HOLD ON- I figured it out from other snippets of code, But there is an issue.

<% 
SiteURL = Request.QueryString("URL")
site = Request.QueryString("site")
city = Request.QueryString("city")

Set DataConn = Server.CreateObject("ADODB.Connection")
Set RS = Server.CreateObject("ADODB.RecordSet")
DataConn.Open "DBQ=" & Server.Mappath("clicks.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};"
Set rsInfo = DataConn.Execute("SELECT * from SiteClicks WHERE site = '" & site & "' AND city = '" & city & "'")

If rsInfo.EOF Then
     
  SQL = "INSERT INTO SiteClicks (site,city,thisdate,clicks) VALUES('" & site & "' , '" & city & "', '" & date & "', 1);"
  Set rsInfo = DataConn.Execute(SQL)
  Response.Write "<CENTER>Record Updated!<br>" 
   
Else
'"UPDATE SiteClicks SET clicks = clicks + 1, thisdate = '"&NOW()&"' WHERE (site='...') AND (city='...')"
  SQL = "UPDATE SiteClicks SET clicks = clicks + 1, thisdate = '"&date&"' WHERE site='" & site & "' AND city='" & city & "';"
  Set rsInfo = DataConn.Execute(SQL)

  Response.Write "<CENTER>2</CENTER>"
RS.Close
DataConn.Close
End If

%>

If I use the RS.Close at the end to close the recordset object it throws up an error

Operation is not allowed when the object is closed.
/click/clicks/redirect.asp, line 23

But if I delete the RS.Close the it works fine ?!?
Why does it do this and is it a problem if I just delete it?

Many Thanks again!

 

[Tarwn]

A table can multiple fields comprising it's primary key. If you need to have multiple records that will have the same site (ie, 5 sets of differant site/city pairs) then you will need to change the primary key to either the site+ city fields (click on both, then rt click and select primary key) or you will need to create a new autonumber field and use tat as your primary key. Otherwise, with site as your primary, your not going to be able to insert the next city/site pair tat comee's along if a site i already listed in your db with another city.

As I mentioned in my first post you would be better of doing a SELECT to check if a record already exists, something like:

1) SELECT for city/state pair
2) If resulting recordset is Not EOF Then do an UPDATE
3) Else do an INSERT

Those actually correspond to your numbers in your previous post, hope that helps,

-T

_{01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111}
Need an expensive ASP developer in the North Carolina area? Feel free to let me know.