Get the list

Sunday Jun 6th 1999 by ServerWatch Staff
Share:

Part 1 - Getting alphabetical ordered lists using a single ASP page.

Stefano Lanfranconi
Part 1 - Getting alphabetical ordered lists using a single ASP page.
  A simple way to get an interactive, alphabetically ordered list of customers, employees, columnists etc. is to write some server-side VBScript code rows.
No word is spent in this article about how to set up an IIS-ASP engine, or about how to set an ODBC connection. It is assumed that readers are experienced enough to manage that sort of problems. See related documentation for further widening.

The only page we need is ListColumnists.asp

<%@ Language="VBScript" %>

<%
      'Let's start setting some useful values.

 
      vConnName = "SwynkList"

      'Change this with your own ole db or odbc
      'data connection name.

 
      vTableName = "Columnists"

      'Change this with the actual table name hosting your data.

 
      vDetailColor = "DeepSkyBlue"

      'This sets the bgcolor for table detail tags.


      vLetterCase = 1

      'This set the case (upper or lower) in which linked letters will be displayed:
      'Upper Case = 1; Lower Case = 2. 
%>

     
When called by server-side script, the following function arranges two rows of 13 letters linked to ListColumnists.asp both at the top and the bottom of the page as shown in Figure 1. The two parameters vColor and vCase match vDetailColor and vLetterCase respectively.

<%
      Function CreateAlphabetInTableRows(vColor, vCase)


           'The starting Chr() function number depends on vCase value

           if vCase = 1 then chrCode = 65 'Chr(65) = 'A'
           if vCase = 2 then chrCode = 97 'Chr(97) = 'a'
          
           'Writes the first row of letters

           Response.Write("<tr>")
                'This cycles through the first thirteen english alphabet letters.
                For k = chrCode to chrCode + 12
                     Response.Write("<td align='middle' bgcolor='" & vColor & "'>")
                     Response.Write("<a href='ListColumnists.asp?letter=" & chr(k) & "'>")
                     Response.Write("<font color='black' face='Verdana, Arial' size='2'><b>" & chr(k) & "</b>")
                     Response.Write("</font></a></td>" & chr(10))
                Next
           Response.Write("</tr>")
          
           '...and writes the second one.

           Response.Write("<tr>")
                'This cycles from 14th to 26th letter.
                For k = chrCode + 13 to chrCode + 25
                     Response.Write("<td align='middle' bgcolor='" & vColor & "'>")
                     Response.Write("<a href='ListColumnists.asp?letter=" & chr(k) & "'>")
                     Response.Write("<font color='black' face='Verdana, Arial' size='2'><b>" & chr(k) & "</b>")
                     Response.Write("</font></a></td>" & chr(10))
                Next
           Response.Write("</tr>")
     
      End Function
%>

When clicked, each letter becames the 'letter' parameter used by VBScript to compose the consequent list of columnists.

Two alphabetical table rows created by code Figure 1
 
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"
      Else
            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) %>
    
</table>

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%>
           <tr>
                <td colspan="13"><font face="Verdana, Arial" size="2" color=red>
                <b>No columnist found!</b>
                </font>
                </td>
           </tr>
<%Else%>
          
           <%Do Until rs.EOF%>
           <tr>
                <td colspan="8"><font face="Verdana, Arial" size="2">
                <%  = rs("FirstName") & " " %>
                <b><%  = rs("Surname")%></b></font>
                </td>
                <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>
                <%Else%>
                          <img border="0" alt="no-email" WIDTH="20" HEIGHT="15">

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

           <%rs.MoveNext
           Loop%>
     
<%End IF%>  
     
<%Conn.Close%>  

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) %>
    
</table>

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
 
Share:
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved