Building & Managing Web Sites with Microsoft Technologies
Active X Data Objects and the N-Tier Model

The N-Tier Model:

View the Theory and Best Practices for Client/Server Computing Presentation.

The ADO Model:

Click on image above to see a detailed model.

Connection Object:

'The connection object manages the conversation between the client and the server. The server object may be created explicitly or implicitly in the case of a "firehose" cursor.

'Examples presume local variables of:
Dim RA 'as Long
Dim mySQL 'as String
Dim gsConnectString 'as String
Dim rs as 'new ADODB.Recordset
Dim gvEXE_DB 'as new ADODB.Connection

Common ADO Constants

Opening a Connection object to be used by all recordsets in program

'For connecting to SQL*Server with ODBC
gsConnectString = "DRIVER=SQL Server;SERVER=sqlserver;DATABASE=myDB;UID=Username;PWD=Password;"

'For Connecting to Access MDB with ODBC
gsConnectString = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=myMDB.mdb;}"

Set gvEXE_DB = Server.CreateObject("ADODB.Connection")

gvEXE_DB.ConnectionString = gsConnectString
gvEXE_DB.Open

'Once you have a connection object, you may send commands to the server as below:

mySQL = "Update TableX Set ColumnY = ValueV Where ID = 3"
gvEXE_DB.Execute mySQL, RA 'RA retunrs the number of records affected!

'Don't forget to dispose these variables before your page ends

gvEXE_DB.Close
Set gvEXE_DB = Nothing

Recordsets

'A recordset is a collection of rows, each row having the same columns (fields) They must be created explicitly as below:

Set rs = Server.CreateObject("ADODB.Recordset")

(Updatable) For  a full function recordset   (Roughly equivilent to a DAO DynaSet):

'Don't forget to close and dispose your recordset objects
rs.Close
set rs = Nothing

mySQL = "Select ..."

With rs
Set .ActiveConnection = gvEXE_DB
    .CursorType = adOpenStatic
    .LockType = adLockBatchOptimistic
    .Source = mySQL
    .Open
End With

' ... Use RecordSet Here

'Don't forget to close and dispose your recordset objects
rs.Close
set rs = Nothing

mySQL = "Select ..."

With rs
Set .ActiveConnection = gvEXE_DB
    .CursorType = adOpenForwardOnly
    .LockType = adLockReadOnly
    .cursorLocation = adUseServer
    .Source = mySQL
    .Open
End With

' ... Use RecordSet Here

'Don't forget to close and dispose your recordset objects
rs.Close
set rs = Nothing

'Alternately you can use the execute method to execute a sql command, stored procedure or a view. If it returns rows you can

mySQL = "{Call sp_GetCustomers(-1)}"  'Call the stored procedure sp_GetCustomers (made up example) and pass it the parameter of TRUE.
set rs = gvEXE_DB.Execute mySQL, RA  'Rows are now in the recordset

Navigating Recordsets

The methods below are used to navigate through a recordset. Recordsets have the notion of a current record. Only the current record can be manipulated at one time.

Fields

'The fields collection is used to hold information about the columns and the values in the columns

Common Field Types

'Typical Example use of a Recordset, Creating an HTML table (note use of Fields collection):

Response.Write("<table width='100%' Border='1'>")

'Write out Column Names
Response.Write("<tr>")
For i = 0 to rs.Fields.Count - 1
     Response.Write("<td>")
     S = Trim(cStr(rs.Fields(i).Name & ""))
     Response.Write("<b>" & S & "</b>")
     Response.Write("</td>")
Next
Response.Write("</tr>")

'Write out all rows values
While not rs.EOF
     Response.Write("<tr>")
     For i = 0 to rs.Fields.Count - 1
           Response.Write("<td>")

          S = Trim(cStr(rs.Fields(i).Value & ""))
          If len(S) = 0 Then S = "&nbsp;"
          Response.Write(S)

           Response.Write("</td>")
     Next
     Response.Write("</tr>")
     rs.Movenext
Wend
Response.Write("</table>")

Stuff to remember about ADO recordsets

1) Cursor Updatability is a function of which LOCKING you choose.
The LockType Property controls this, the two most common  choices are:
1. adLockReadOnly for readonly recordsets (faster cursor, no locks)
2. adLockOptimistic for updatable cusors

If you specify optimistic locking, you may not be able to update a recordset in general because of:
a. The recordset does not contain the primary key column or that column cannot be derived by the driver.
b. The recordset contains a non-updatable JOIN. (See SQL for more about this)
c. You do not have rights to update the table(s) in question
d. The server is no longer connected or accessable (for non-ADOR (RDS) recordsets)
e. You have tried to update record that has already been updated.

2) Performance can vary greatly with the choice of:
a. CursorType
b. Cursor Location
c. Cache Size
d. Lock Type
e. Provider (ADO 2.x and higher)
f. Number of Records Returned To Client (Fewer is better!)
These effects are combinitorial, some combinations are good others less so.

3) Not all cursor types support all functionality. Especially be careful of cursor movement and bookmark options.

4) Be wary of the RECORDCOUNT property. Only Test for = 0 or <> 0.

[Top]

CSC 96B  http://www.valtara.com/CSC96B/   
Copyright 1999, Valtara Data Design