Get the list Page 2

Stefano Lanfranconi

Now we are ready to set our data connection up and to excute a query against the database. But first, since this is the very first time our Active Server Page is called out, we have to set the letter we want to start from. Most lists would start with letter 'A', but unfortunately no columnist's surname at Swynk site begins with that letter; so let me start with 'L', the first letter of my own surname :).

If the letter value of the Request.QueryString collection is blank, meaning that the page is loaded for the first time, after checking the case value we set the relative starting letter, 'L' or 'l'. Whereas, if Request.QueryString('letter') returns a valid letter, meaning the user clicked on it, we set the starting letter to the value itself.

      If Request.QueryString("letter") = "" Then
            if vLetterCase = 1 then vLetter = "L"
            if vLetterCase = 2 then vLetter = "l"
            vLetter = Request.QueryString("letter")
      End If

Let's set our data connection up and excute the query!
According to the vLetter value, the query returns the consequent recordset containing the columinists whose first letter surname matches with the selected value.
      Set Conn = Server.CreateObject("ADODB.Connection")
      Conn.Open vConnName
      vSql = "SELECT FirstName, Surname, Email, Topic FROM " & vTableName & " WHERE (((Surname) LIKE '" & vLetter & "%')) ORDER BY Surname;"
      set rs = Conn.Execute(vSql)

Let's give users some output as shown in Figure 2.
After writing HTML table tags, we ask the code to build the rows within them.
<table WIDTH="85%" ALIGN="center" BORDER="0" CELLSPACING="1" CELLPADDING="1">
         <%  = CreateAlphabetInTableRows(vDetailColor, vLetterCase) %>

The HTML table that actually contains the requested list (click here to download complete code), is also made up with some line of script we are now going to analyse.
This first line returns the value of the choosen letter.
<u><em>Lists columnists with surname starting with '<b> <%  = vLetter %> </b>'</em></u>

These others lines check if the query gives back any records; if it does, the code loops trough the recordset, otherwise it returns back users a warning message. If the columnist has got an email address, it will be shown by an icon close by his/her name.

<%If rs.EOF Or rs.BOF Then%>
                <td colspan="13"><font face="Verdana, Arial" size="2" color=red>
                <b>No columnist found!</b>
           <%Do Until rs.EOF%>
                <td colspan="8"><font face="Verdana, Arial" size="2">
                <%  = rs("FirstName") & " " %>
                <b><%  = rs("Surname")%></b></font>
                <td align="center" colspan="1">
                <%If Not rs("Email") ="" then%>
                          <a href="mailto:<%=rs("Email")%>"><img border="0" alt="email" src="images/email.gif" WIDTH="20" HEIGHT="15"></a>
                          <img border="0" alt="no-email" WIDTH="20" HEIGHT="15">

                     <%End If%>
                <td align="right"><font color="blue" face="Verdana, Arial" size="2">
<%  = rs("Topic")%></b></font>

<%End IF%>  
Finally let's ask the code to build another couple of rows within HTML table tags to allow users more comfortable interaction with our dynamic list.
<table WIDTH="85%" ALIGN="center" BORDER="0" CELLSPACING="1" CELLPADDING="1">
         <%  = CreateAlphabetInTableRows(vDetailColor, vLetterCase) %>

This ends both our ListColumnists.zip and our lesson.

Happy computing!

The figure below shows the server answer when the page is loaded for the first time or when the user clicks on 'L'. Figure 2

This article was originally published on Jun 7, 1999
Page 2 of 2

Thanks for your registration, follow us on our social networks to keep up-to-date