VBScript Tutorials - Herong's Tutorial Notes
Dr. Herong Yang, Version 5.00

Interacting with External Applications - ActiveX Data Object (ADO)

<< IIS ASP Server Supporting VBScript

<< VBScript Tutorials - Herong's Tutorial Notes

This section provides a tutorial example on how to pass values from one page to another. This example is a very simple registration application with two ASP pages.

ADO (ActiveX Data Object): An application programming interface (API) to access relational database management systems.

  • ADO is provided as a DLL, and usually installed at "c:\program files\common files\system\ado\msadox.dll".
  • ADO can access databases connected through ODBC data source names (DSN).
  • ADO can also access MS Access database files.
  • ADO is built on top of OLE DB technology.
  • ADO API is very similar to DAO.

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>

Note that:

  • In this example, I didn't use ODBC DSN to open the connection. Instead, I used a special connection string to connect to the MS Access file directly.

Sections in This Chapter

What is ASP (Active Server Pages)?

Static, Client-Side and Server-Side Scripting Pages

Setting Up IIS to Run ASP Pages - asp.dll

ASP Objects: Request, Response, Session and Application

ASP Object Example - Passing Values between Pages

Interacting with External Applications - ActiveX Data Object (ADO)

Dr. Herong Yang, updated in 2008
Interacting with External Applications - ActiveX Data Object (ADO)