Interacting with External Applications - ActiveX Data Object (ADO)

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.

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
'- Copyright (c) 2015, HerongYang.com, All Rights Reserved.
'
   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; 2015 Herong Yang," _
      & " herong_yang@yahoo.com")
   Response.write("<br>Suggestions are welcome." & vbNewLine)
   Response.Write("</BODY>")
   Response.Write("</HTML>")
end sub
</script>

Note that:

Table of Contents

 About This Book

 Introduction of VBScript - Visual Basic Scripting Edition

 Variant Data Type, Subtypes, and Literals

 Arithmetic Operations

 Numeric Comparison Operations and Logical Operations

 String Operations - Concatenation and Comparison

 Variable Declaration and Assignment Statement

 Expression and Order of Operation Precedence

 Statement Syntax and Statement Types

 Array Data Type and Related Statements

 Array References and Array Assignment Statements

 Conditional Statements - "If ... Then" and "Select Case"

 Loop Statements - "For", "While", and "Do"

 "Function" and "Sub" Procedures

 Built-in Functions

 Inspecting Variables Received in Procedures

 Error Handling Flag and the "Err" Object

 Regular Expression Pattern Match and Replacement

 scrrun.dll - Scripting Runtime DLL Library

 Creating Your Own Classes

 IE Web Browser Supporting VBScript

IIS ASP Server Supporting VBScript

 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)

 WSH (Windows Script Host)

 References

 PDF Printing Version