Allora, io nel mio db sql server ho creato 2 tabelle:
citiesOfTheDay
cityID - int (sarebbe il numerico di Access)
today - varchar(8) (sarebbe testo di lunghezza 8 di Access)
cities
cityID - int Identity (sarebbe il contatore di Access)
cityName - varchar(100) (sarebbe il testo di Access)
' puoi mettere tutti gli altri campi qui di seguito
La tabella cities l'ho popolata così:
codice:
1 Roma
2 Milano
3 Napoli
4 Firenze
5 Venezia
6 Verona
7 Palermo
8 Cagliari
9 Torino
10 Genova
Nell'esempio, come ho detto prima, uso SQL Server.
Se usi ACCESS devi cambiare la riga in rosso mettendo la tua stringa di connessione e aggiungere un asterisco * dopo DELETE nelle riga in verde.
codice:
<%
session.LCID = 1040
set conn = server.createObject("ADODB.Connection")
conn.open "Provider=sqloledb; " &_
"Network Library=DBMSSOCN; " &_
"Encrypt=yes;" &_
"Data Source=localhost;" &_
"Initial Catalog=xxxxxx;" &_
"User Id=xxxxxx;" &_
"Password=xxxxxx;"
today = dateToString(date())
' today = dateToString("01/06/2007") ' per fare i test decommentare questa riga e modificare a mano la data tra le parentesi
maxValue = 4
function dateToString(theDate)
if not isDate(theDate) then theDate = date()
yyyy = datePart("yyyy",theDate)
mm = datePart("m",theDate)
if mm < 10 then mm = "0" & mm
dd = datePart("d",theDate)
if dd < 10 then dd = "0" & dd
dateToString = yyyy & mm & dd
end function
function citiesOfTheDayAlreadyExists(theDate)
sql = "SELECT cityID FROM citiesOfTheDay WHERE today = '" & theDate & "'"
set rs = conn.execute(sql)
if not rs.eof then
citiesOfTheDayAlreadyExists = true
else
citiesOfTheDayAlreadyExists = false
end if
rs.close
set rs = nothing
end function
function getCitiesOfTheDay(byRef theArrayID)
sql = "SELECT cityID FROM cities WHERE cityID NOT IN (SELECT cityID FROM citiesOfTheDay)"
set rs = conn.execute(sql)
if not rs.eof then
do until rs.eof
theArrayID = theArrayID & "," & rs("cityID")
rs.moveNext
loop
else
call initializeCitiesOfTheDay(theArrayID)
end if
rs.close
set rs = nothing
end function
function initializeCitiesOfTheDay(tempArrayID)
sql = "DELETE FROM citiesOfTheDay"
conn.execute(sql)
call getCitiesOfTheDay(tempArrayID)
end function
function randomizeCities(theArrayID,theMaxValue,theDate)
dim tempArray()
i = 0
reDim tempArray(i)
while not i = theMaxValue
idExists = false
randomize()
randomNumber = int(rnd * uBound(theArrayID) + 1)
tmp = theArrayID(randomNumber)
for x = 0 to uBound(tempArray)
if tmp = tempArray(x) then idExists = true: exit for
next
if not idExists then
reDim preserve tempArray(i)
tempArray(i) = tmp
i = i + 1
end if
wend
for y = o to uBound(tempArray)
call storeCitiesOfTheDay(tempArray(y),theDate)
next
end function
function storeCitiesOfTheDay(theCityID,theDate)
sql = "INSERT INTO citiesOfTheDay(cityID,Today) VALUES(" & theCityID & ", '" & theDate & "')"
conn.execute(sql)
end function
function main()
if not citiesOfTheDayAlreadyExists(today) then
call getCitiesOfTheDay(arrayID)
if inStr(arrayID,",") > 0 then arrayID = split(arrayID,",")
if isArray(arrayID) then
if uBound(arrayID) < maxValue then
for z = 1 to uBound(arrayID)
call storeCitiesOfTheDay(arrayID(z),today)
next
else
call randomizeCities(arrayID,maxValue,today)
end if
end if
end if
end function
main()
%>
<html>
<head>
<title>Titolo Pagina</title>
</head>
<body>
<%
sql = "SELECT * FROM cities A INNER JOIN citiesOfTheDay B ON B.cityID = A.cityID WHERE B.Today = '" & today & "'"
set rs = conn.execute(sql)
if not rs.eof then
do until rs.eof
response.write rs("cityName") & "
"
rs.moveNext
loop
end if
rs.close
set rs = nothing
%>
</body>
</html>
<%
conn.close
set conn = nothing
%>
Rispondi quotando