The code we use presents a random tip by using ASP and a little trickery. In addition, our code never presents the same tip twice until all the tips have been presented.
Here’s how we do it…
First, let’s look at the structure of the SQL Server table that holds all the tips (KB).
ID int
Summary varchar(255)
FileName varchar(255)
Presented bit
ID is the primary key for the table and is used to reference each tip. The Summary column contains text that is used to summarize the tip and is what we show the user for a short description of the tip. FileName holds the actual file name of the page for the tip. The Presented column is either 1 or 0, indicating if the tip has already been presented.
The overall structure of the ASP code used to display the tips has several steps:
setup the db connection
’setup the db connection
Set dbTip = Server.CreateObject(“ADODB.Connection”)
dbTip.Open sDSN
Initialize the VBScript number randomizer
‘Init the number randomizer
Randomize
Get the total number of tips that have not been presented
‘get the total number of tips
sSQL = “SELECT Count(ID) AS CountOfTipID FROM KB WHERE Presented = 0″
Set rsTip = dbTip.Execute(sSQL)
iTipUpperBounds = rsTip(“CountOfTipID”)
If all the tips have been presented, reset the presented flag on all the tips
‘make sure we have records to present
If iTipUpperBounds = 0 Then
‘all the records have been presented…
‘we need to clear the dirty flag
sSQL = “UPDATE KB SET Presented = 0″
dbTip.Execute(sSQL)
‘get the total number of tips…again, now that we’ve cleared dirty flag
sSQL = “SELECT Count(ID) AS CountOfTipID FROM KB”
Set rsTip = dbTip.Execute(sSQL)
iTipUpperBounds = rsTip(“CountOfTipID”)
End
If
Pick a random number between 1 and the total number of tips that have not been presented
‘pick a random tip
iTipNumber = Int(((iTipUpperBounds -1) – 0 + 1) * Rnd + 0)
Get all the tips that have not been presented
‘get the tips
sSQL = “SELECT ID, Summary, FileName FROM KB WHERE Presented=0″
Set rsTip = dbTip.Execute(sSQL)
Move the cursor to the random number using the Move method.
’select the random tip
rsTip.Move iTipNumber
Read the record and present the data
” target=”_top”>Click
here for the details…
Update the record to indicate that it’s been presented
‘turn the presented flag on for the record
sSQL = “UPDATE KB SET Presented = 1 WHERE ID = ” & rsTip(“KBID”)
dbTip.Execute(sSQL)
Clean up
‘clean up recordset
rsTip.Close
Set rsTip = Nothing
dbTip.Close
Set dbTip = Nothing
Below, is a working example…referesh the page to see the summary of a new tip.
