glossary.asp - ADO Example Script

ASP Tutorials - Herong's Tutorial Examples

∟glossary.asp - ADO Example Script

This section provides a tutorial example on how to use ADO classes to query and display result from a simple glossary database.

Here is a simple example of using ADO to search for records from a glossary database, written in MS Access.

<script language="vbscript" runat="server">
'
' glossary.asp
'
' 11-Aug-2002 Herong Yang: finished the first draft
'
' Copyright 2002 Herong Yang
' --------------------------
   Dim oConn, oRs, oF
   Dim filePath      
   Dim term, abbreviation, definition
   Dim key_word, submit
   
   key_word = Request.Form.Item("key_word")
   submit = Request.Form.Item("submit")

   call displayHeader("Glossary")

   if len(submit) = 0 or submit = "Search" then
      Response.write("<form method=post>")
      Response.write("<hr width=640 align=left>")
      Response.write("<input type=text name=key_word value=" _ 
         & key_word & ">")
      Response.write("&nbsp;<input type=submit name=submit" _
         & " value=Search>")
      Response.write("&nbsp;<input type=submit name=submit" _
         & " value=Print>")
      Response.write("<hr width=640 align=left>")
      Response.write("</form>")
   end if

   dim sql
   sql = " SELECT * FROM glossary"
   if len(key_word) > 0 then
      sql = sql & " WHERE abbreviation LIKE '%" & key_word & "%'" _
              & " OR term LIKE '%" & key_word & "%'" _
              & " OR definition LIKE '%" & key_word & "%'" 
   end if
   sql = sql & " ORDER BY term"
   
   call displayResult()
   call displayFooter()     
   
Sub displayResult()
   filePath = Server.MapPath(".\glossary.mdb")
   Set oConn = Server.CreateObject("ADODB.Connection")
   oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filePath
   Set oRs = oConn.Execute(sql)

   Response.write("<TABLE border=0 width=640>")
   Response.write("<tr><td>")

   Do while (Not oRs.eof)
      set oF = oRS.Fields.Item("term")
      if not IsNull(oF) then
         term = oF.Value

         set oF = oRS.Fields.Item("abbreviation")
         abbreviation = ""
         if not IsNull(oF) then
            abbreviation = " (" & oF.Value & ")"
         end if
      
         set oF = oRS.Fields.Item("definition")
         definition = ""
         if not IsNull(oF) then
            definition = oF.Value
         end if
      
         Response.Write("<p><b>" & term & abbreviation & "</b> " _
            & definition & vbNewLine)
      end if
      oRs.MoveNext 
   Loop 
   Response.write("</td></tr>" & vbNewLine)
   Response.write("</TABLE>")

   oRs.close
   oConn.close 
End sub   

sub displayHeader(title)
   Response.write("<HTML>")
   Response.write("<HEAD>")
   Response.write("<TITLE>" & title & "</TITLE>")
   Response.write("</HEAD>" & vbNewLine)
   Response.write("<BODY BGCOLOR=White topmargin=10 leftmargin=10>")
   Response.write("<p><font size=+2><b>" & title & "</b></font> - " _
      & Date())
   Response.write("<p>" & vbNewLine)
end sub

sub displayFooter()
   Response.Write("<hr width=320 align=left>")
   Response.write("Copyright &copy; 2002 Herong Yang," _
      & " herong_yang@yahoo.com")
   Response.write("<br>Suggestions are welcome." & vbNewLine)
   Response.Write("</BODY>")
   Response.Write("</HTML>")
end sub
</script>