Help Needed on MS Access Database

[lindajames]

Can anyone plzzzz help me with the following:

i have an asp script that detects a users country and if the user is from USA or UK it takes them to a certain page and if they are from any other country it logs the country they are in into a database table called country and field called s_country. and then it redirects into a default page. As you may know this will just create thousands of new records (rows) in the database. I do not want to create this many records.

Basically, i want to create 2 fields. 1 called CountryName and the other called TotalHits or something. And that way when a visitor comes to the page, if they are not from UK or USA the script locates their country name in the CountryName field and it basically adds 1 number to the HitsTotal of that country (e.g. 124 will become 125)

Can anyone plzzzzzzzzzzzzz help me and tell me.

Below is the current code:

select case strCountryName
case "United States"
esponse.redirect("usa/index.html")
case "United Kingdom"
response.redirect("uk/index.html")
case else
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " &server.mappath("logs.mdb")
oConn.open
sSQL = "insert into country (s_country) VALUES ('" &strCountryName &"')"
set oRS = oConn.execute(sSQL)
oConn.close
end select

I really appreciate anyones help

Many Thanks

Cheers
Linda

[lindajames]

hello! can anyone help me plzzz? im goin nutz waiting for someone to reply.

[txbakers]

instead of writing a record every time, why not keep a running count?

Then, you wouldn't insert every time, just update.

select max(field)as numcountry from countries group by country.

numcountry = numcountry + 1

update countries set field = numcountry where country = "United States"

****WARNING**** This is just pseudo code - not perfect syntax. But it should get you going in the right direction.

[chris_f]

Hi lindajames,

TxBakers has the right idea. You want to split it into 3 commands.

1. Get the current counter
GetCounterSQL = "SELECT totalhits FROM Country WHERE CountryName = '" & StrCountryName & "'"

2. Increase the counter
Dim NewTotalHits
NewTotalHits = GetCounterSQL("totalhits") + 1

3. Update the database with the new counter value
UpdateCounterSQL = "UPDATE Country SET totalhits = " & NewTotalHits & " WHERE CountryName = '" & StrCountryName & "'"

Something like the above should be sufficent.
You'll have to code the rest.

Chris