ASP 101 - Active Server Pages 101 - Web06
The Place ASP Developers Go!

Please visit our partners

Windows Technology Windows Technology
15 Seconds
ASP 101
ASP Wire
VB Forums
VB Wire
internet.commerce internet.commerce
Partners & Affiliates

ASP 101 is an site
ASP 101 is an site
Internet News
Small Business
Personal Technology

Corporate Info
Tech Jobs
E-mail Offers

ASP 101 News Flash ASP 101 News Flash

 Top ASP 101 Stories Top ASP 101 Stories
Migrating to ASP.NET
Getting Scripts to Run on a Schedule
The Top 10 ASP Links @

Use VBScript's TypeName to resolve type mismatch errors
Show All Tips >>
ASP 101 RSS Feed ASP 101 Updates

Analyzing the Opinions You Collect

We've now got our visitor's opinions safely stored away in a table in our database. As it's likely that someone is going to want to know the results at some point, we need to find a way of extracting and using them.

In Chapter 10, we'll show you a way that we can manage and use the email addresses that we collect this way, but in the meantime, the following page (showopinions.asp) is just one solution for analyzing the results. It shows the percentage of times that each of the six checkboxes was ticked by the users that expressed at least one opinion.

Building the showopinions.asp Page

The showopinions.asp page is quite a bit more complex than most of the ASP/ADO pages we've so far seen which extract and display data from a database. The reason is that we have to do quite a lot of work to get the results we want from the records.

Each record contains six numeric fields holding the results of our user's opinions. Between one and six of these fields can have the value -1, with the remainder being zero. We want to find the number of times that each of the six checkboxes was ticked (i.e. has the value -1), as a percentage of the total number of visitors who provided opinions-in other words the percentage who agreed with each of the opinion statements in the page. We also want to present the results by week, rather than as an overall total, so that we can see any changes and trends.

To do this means we need to summarize the results for each week in turn, starting from this week and going backwards to the first week that we have opinions for. Getting a meaningful result for a single week can be achieved by summing the values for each 'opinion' field for that week's records. This works because each of the six fields will have either zero or -1 in each record, so the result of adding the values together and reversing the sign will be the number of 'yes' answers for that field.

Timeout, Connection and Parameters

Our page starts off with the customary code to extend the script timeout and insert the data connection string. Then we check the value of the site parameter (if supplied) to see which of the sites we host the listing is for:

<% Server.ScriptTimeOut = 600 %>
<!-- #include virtual="/connect/" -->
QUOT = Chr(34)
CRLF = Chr(13) & Chr(10)
strSite = Request("site") 'site to select opinions for
Select Case strSite
Case "wd" 'Web-Developer site
strSiteName = "Web-Developer"
strWhere = " WHERE THostIP = '' "
Case "cd" 'COMDeveloper site
strSiteName = "COMDeveloper"
strWhere = " WHERE THostIP = '' "
Case "wa" 'World Of ATL site
strSiteName = "World Of ATL"
strWhere = " WHERE THostIP = '' "
Case Else
strSiteName = "(all sites)"
strWhere = ""
End Select
Summary of opinions expressed for the <% = strSiteName %> site: <P>
Getting the Date of the First Opinions

Now we can get the date of the first record using a SQL statement that includes the MIN function, and store it in our dFirstDate variable. We're going to use a different SQL statement to create the recordsets later in our page, so we can destroy this recordset once we've got the value we need:

On Error Resume Next
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open strConnect
'---------- get date of oldest record ----------------
strSQL = "SELECT FirstDate=MIN(dOpinionDate) FROM Opinions" & strWhere
Set oRs = oConn.Execute(strSQL)
If (oRs.EOF) Or (Err.Number > 0) Then
Response.Write "<FONT FACE=" & QUOT & "Arial" & QUOT & " SIZE=3>" _
& "<B>Sorry, the database cannot be accessed.</B></FONT></BODY></HTML>"
End If
dFirstDate = oRs("FirstDate")
Set oRs = Nothing
Getting the Opinions for Each Week

To select records from our Opinions table by week, we need to specify the week and year number in the SQL statement. We can calculate these values for any given date using the VBScript DatePart function. As we're starting with this week and working backwards, we first get the values for this week and year:

intYear = DatePart("yyyy",Now())
intWeek = DatePart("ww",Now())
Now comes the loop that we'll use to extract and display each week's results. The condition makes sure that the week and year we're currently processing are greater than or equal to the first week and year in our Opinions records:

'---------- loop while this week >= first date ----------------
Do While ((DatePart("ww",dFirstDate) <= intWeek)
And (DatePart("yyyy",dFirstDate) = intYear))
Or (DatePart("yyyy",dFirstDate) < intYear)
SQL Server provides us with a DATEPART function that works in a similar way to the VBScript equivalent, but with a different syntax for the first argument. Instead of a series of code letters in quotation marks, we use a word without quotation marks. With this function, it's easy to create a WHERE clause that selects just the required week's records. We add this clause onto the end of the existing WHERE clause that selects the site we're interested in:

If Len(strWhere) > 0 Then
strQuery = strWhere & " AND DATEPART(week,dOpinionDate)=" & intWeek _
& " AND DATEPART(year,dOpinionDate)=" & intYear
strQuery = " WHERE DATEPART(week,dOpinionDate)=" & intWeek _
& " AND DATEPART(year,dOpinionDate)=" & intYear
End If
Now we can execute our SQL statement to collect the results for the week. We COUNT the total number of values in one of the opinion fields to get the total number of opinion records (this field will never be null, because we always place either zero or -1 in it). Then we SUM the six opinion fields:

strSQL = "SELECT NumOpinions=COUNT(bUseful), Useful=SUM(bUseful), " _
& "Design=SUM(bDesign), Visit=SUM(bVisit), Buy=SUM(bBuy), " _
& "MailList=SUM(bMailList), Comment=SUM(bComment) FROM Opinions" _
& strQuery
Set oRs = oConn.Execute(strSQL)
If (oRs.EOF) Or (Err.Number > 0) Then
Response.Write "Week <B>" & intWeek & " : " & intYear _
& "</B>. No opinions recorded.<P>" & CRLF
Now we have all the totals, we can calculate the percentages for the week in question, remembering to take the absolute value of the result to remove the minus sign:

intCount = oRs("NumOpinions")
pcUseful = FormatPercent(Abs(oRs("Useful")) / intCount ,0)
pcDesign = FormatPercent(Abs(oRs("Design")) / intCount ,0)
pcVisit = FormatPercent(Abs(oRs("Visit")) / intCount ,0)
pcBuy = FormatPercent(Abs(oRs("Buy")) / intCount ,0)
pcMailList = FormatPercent(Abs(oRs("MailList")) / intCount ,0)
pcComment = FormatPercent(Abs(oRs("Comment")) / intCount ,0)
Response.Write "Week <B>" & intWeek & " : " & intYear & "</B>. " _
& "Number of opinions recorded: <B>" & intCount & "</B><BR>" & CRLF
Displaying the Opinions for Each Week

And, finally, we can display the results. We create a separate table for each week, which means that the user will see each week's results while the page is still downloading. If we used one big table, they would have to wait for all the results to be calculated:

<td align="center">  Found   <br>  Useful  </td>
<td align="center">  Like   <br>  Design  </td>
<td align="center">  Regular   <br>  Visitor  </td>
<td align="center">  Intend   <br>  to Buy  </td>
<td align="center">  Add to   <br>  MailList  </td>
<td align="center">  Emailed   <br>  Comment  </td>
<td align="center" nowrap><b><% = pcUseful %></b></td>
<td align="center" nowrap><b><% = pcDesign %></b></td>
<td align="center" nowrap><b><% = pcVisit %></b></td>
<td align="center" nowrap><b><% = pcBuy %></b></td>
<td align="center" nowrap><b><% = pcMailList %></b></td>
<td align="center" nowrap><b><% = pcComment %></b></td>
<% End If
intWeek = intWeek - 1
If intWeek = 0 Then
intYear = intYear - 1
intWeek = 53
End If
Set oRs = Nothing
Set oConn = Nothing
At the end of the page, as shown in the code above, we just decrement the week number (and year number if we've got to the start of the year), then go back to do the previous week. Here's the result:


We've covered a lot of ground in this chapter looking at what first seemed to be a very narrow topic-preventing errors appearing on your site. As you've seen, it's something that really needs to be approached from several angles. You need to try and prevent errors arising in the first place, and then try and catch those that do creep through.

A couple of ways of preventing errors are by using a commercial Web site construction package that checks and updates links (even if you don't actually use it to build the pages), and being aware of how links to your pages from other sites and search engines can be controlled, checked, and corrected.

Techniques for catching errors that do occur include those that you implement to do the job automatically (such as checking that the sites you link to still exist), and providing custom error pages that pick up 'lost' visitors and help them on their way.

We also looked at the other way you can find out how your site is really performing; by collecting user's opinions. This is particularly useful for catching errors that prevent it working properly on the less popular browsers, or on other operating systems.

Overall, in this chapter, we looked at:

Ways of preventing errors and broken links appearing on your site
How we can create custom error pages to catch broken links or other errors
How we can log inter-site navigation and other errors, when they do occur
Ways of checking that other sites we provide links to are still available
How we can provide feedback and collect opinions from our visitors
Opinions are fine, but they're not the kind of 'hard data' you need to convince your chairman that the Web site is producing results, and that you really have earned a pay raise. Instead, you need to be able to measure things like the number of visitors, the popularity of various resources on your site, and the kinds of people that are attracted to it. This is the task of the next two chapters.

1998 Wrox Press Limited, US and UK.