An Introduction To ADO
K. Yue, copyright 2000
Created November 1, 2000

Introduction

• ADO (Active-X Database Object) allows applications to access and manipulate data in a database server through OLE DB providers.
• ADO provides a high level and easy-to-use interface to OLE dB, which provides the underlying data access.
• ADO is a set of Component Object Model (COM) interfaces.
• ADO can be used by various languages, such as Visual Basic, VBScript, C++ and C#.

VBScript

• By default, ADO enumerated constants are not defined in VBScript.
• To use ADO enumerated constants, include the file adovbs.inc.

<!--#include File="adovbs.inc"-->

• To create an ADO object in VBScript, the CreateObject function of the built-in Server object must be used.

Dim objResult
Set objResult = Server.CreateObject( "ADODB.Recordset" )

ADO Objects

• Some major ADO objects are:
  • Connection: represents an open connection to a data source.
  • Command: defines a command for execution against a data source. The most important commands are SQL commands.
  • RecordSet: represents the entire set of records from a base table or the results of an executed command. Takes on a more or less linked list structure and refers to a single record at a time.
  • Field: represents a column in a row.
  • Error: represents an ADO Error.
• The basic operational steps in using ADO are:
  • Open a database connection to a data source.
  • Perform operations against the database connection.
    • Prepare the SQL statements (or stored procedural calls, etc).
    • Execute the SQL statements against the database connection.
    • Access the SQL statement result through the return RecordSet.
  • Close the database connection.
• To keep the lecture notes short, only the Connection and RecordSet objects are covered in depth.

The Connection Object

• Use the createObject method to create a connection object.

set Conn = Server.CreateObject("ADODB.Connection")

• Use the open method to open a database connection.

' No password
Conn.open "Resume"
' With username yue and password sleeping.
Conn.open "Resume", "yue", "sleeping"
' Alternatively,
Conn.open "DSN=Resume; UID=yue; password=sleeping"

• The open method can also be used to open DSN-less connection by setting the property ConnectionString before opening.

conn.ConnectionString = "driver={SQL Server};" & _
    "server=voyager;uid=someid;pwd=somepwd;database=Voyager"
conn.ConnectionTimeout = 30
conn.Open

• The execute command can be used to execute a query: SQL statement, stored procedure, or provider-specific text. The command returns a RecordSet object.
• Limitations of the execute command:
  • The returned Recordset object is always a read-only, forward-only cursor.
  • Cannot use query parameters and the query cannot be persisted to be re-executed.

sql = "select * from Students"
Set ResultsRS = Conn.Execute(SQL)

• Use the the BeginTrans, CommitTrans, and RollbackTrans methods and the Attributes property to support transaction management, if the provider supports them.

Conn.BeginTrans ' Begin transaction.
SQL = "Delete * from Faculty where " & _
      "FacultyID = '100000000'"
Conn.Execute SQL, nRecords
TotalErrors = Conn.Errors.Count
SQL = "Delete * from FacultyReference " & _
      "where FacultyID = '100000000'"
Conn.Execute SQL, nRecords
TotalErrors = TotalErrors + Conn.Errors.Count

If TotalErrors = o Then
   Conn.CommitTrans
Else
   Conn.RollbackTrans
End If

The RecordSet Object

• ADO data is manipulated mainly through the RecordSet object.
• All Recordset objects consist of records (rows) and fields (columns).
• Use the ActiveConnection to set the connection object for the RecordSet.

Set rstSQ = Server.CreateObject("ADODB.Recordset")
Set rstSQ.ActiveConnection = conn

• A cursor is used to access the RecordSet object.
• Conceptually, a cursor is a pointer pointing to the record to be accessed within the RecordSet object.
• There are four kinds of cursors:
  • Dynamic cursor:
    • Cursor points to the actual records in the database.
    • Allows you to view additions, changes, and deletions by other users
  • Keyset cursor:
    • Cursor points to the actual records in the database.
    • Allows you to view changes by other users, but not additions and deletions.
  • Static cursor:
    • provides a static copy of a set of records.
    • allows all types of movement through the Recordset.
  • Forward-only cursor:
    • provides a static copy of a set of records.
    • Allows you to only scroll forward through the Recordset.
• Since most applications require only one-pass access to the RecordSet, forward-only cursors are the most common and are the only returned by the execute command of the Connection object.
• Use the cursorType property to set the cursor types of the RecordSet object. The four cursor type constants are adOpenDynamic, adOpenKeyset, adOpenStatic and adOpenForwardOnly

rstSQ.CursorType = adOpenStatic

• Use the open method to execute a query.

'   open the table Student
rstSQ.open = "Student"
'   execute a SQL query.
rstSQ.open "select * from Student"

Use Move, MoveNext and MoveFirst to move the cursor.

• For forward-only cursor, only the MoveNext method can be used.
• To test the location of the cursors, use the BOF and EOF methods.
  • BOF: Indicates that the current record position is before the first record in a Recordset object.
  • EOF: indicates that the current record position is after the last record in a Recordset object.
• Use the Fields collection of the RecordSet to access the fields (columns) of the current record pointed to by the cursor.
• The fields collection is the default collection of a RecordSet object.
• Each field in the column can be accessed through its column name as well as its positional value.

Example:

Visit Count for each visiting IP address. Assume that the table IPAddresses(IPAddress, Count) exists in the DSN IPAddresses.

<html>
<head>
<title>Visit Counts per ip addresses</title>
</head>
<body>

<%
'   Get Remote IP Address.
IPAddress = Request.ServerVariables("Remote_Addr")
'   Open database connection.
Set conn = Server.CreateObject("adodb.connection")
conn.open "IPAddresses"

'   Check to see the IP address exits.
'   Inefficient: to be modified.
sql = "select * from IPAddresses"
set result = conn.execute(sql)
found = false
Do while (not result.eof)
   if (IPAddress = result.fields.item("IPAddress")) then
      Count = Cint(result.fields.item("Count")) + 1
      found = true
     exit Do
   end if
   result.movenext
loop

if found then
   sql = "update IPAddresses set Count = " & Count & _
      " where IPAddress = '" & IPAddress & "'"
   conn.execute(sql)
else
   Count = 1
   sql = "insert into IPAddresses values('" & _
   IPAddress & "', " & Count & ")"
   conn.execute(sql)
end if
conn.close

'    Print result.
Response.write("IP Address " & IPAddress & " has visited " & _
"this page for " & count & " times.")

%>
</body>
</html>