Interacting with External Applications - ActiveX Data Object (ADO)

VBScript Tutorials - Herong's Tutorial Examples - Version 5.20, by Dr. Herong Yang

VBScript Tutorials - Herong's Tutorial Examples

∟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.

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
'- 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: