|
ADO Objects ADO Collections ADO Properties ADO Methods ADO Events ADO Dynamic Properties ADO Enumerated Constants ADO Programming Examples |
What is ADO?
● ADO stands for ActiveX Data Objects.
● ADO is a Microsoft Active-X component and built on top of OLE DB technology.
● ADO is automatically installed with Microsoft IIS.
● ADO is provided as a DLL, and usually installed at "c:\program
files\common files\system\ado\msadox.dll".
●
ADO is an application programming interface (API) to access
databases connected through ODBC data source names (DSN).
The common way to access a database from inside an ASP page is to:
1. Create an ADO connection to a database
2. Open the database connection
3. Create an ADO recordset
4. Open the recordset
5. Extract the data you need from the recordset
6. Close the recordset
7. Close the connection
When ADO 2.5 was introduced, two new items to the ADO Object model were added. While the earlier versions of ADO addressed the operations dealing with relational databases, new necessities drove the enlargement of the applicability of ADO to non-relational, hierarchical data like those in web sites and folder/file systems. Record and Stream objects were born out of these necessities. These additions considerably extended ADO's applicability beyond the well known databases such as MS SQL Server and Oracle, and provided access for looking into web site directories, file structures with directories, nested subdirectories, file systems and so on. In the broader areas beyond Microsoft Access, ADO has largely been replaced by the quite different ADO.NET library.
In my site, you will learn how to display data from a database, and how to edit, add and delete the data with ADO. After that you will learn ADOX and SQL.
Overview of ADO Objects, Collections, Properties, Methods and Events
|
ADO Objects ADO Command
ADO DataTypes
ADO Methods
ADO Events
|
ADO
Collections Errors Collection (ADO) Fields Collection (ADO) Parameters Collection (ADO) Properties Collection (ADO) A DO PropertiesAbsolutePage Property (ADO) AbsolutePosition Property (ADO) ActiveCommand Property (ADO) ActiveConnection Property (ADO Command) ActiveConnection Property (ADO Record) ActiveConnection Property (ADO Recordset) ActualSize Property (ADO) Attributes Property (ADO) BOF, EOF Properties (ADO) Bookmark Property (ADO) CacheSize Property (ADO) Charset Property (ADO) CommandStream Property (ADO) CommandText Property (ADO) CommandTimeout Property (ADO) CommandType Property (ADO) ConnectionString Property (ADO) ConnectionTimeout Property (ADO) Count Property (ADO) CursorLocation Property (ADO) CursorType Property (ADO) DataMember Property DataSource Property (ADO) DefaultDatabase Property DefinedSize Property (ADO) Description Property Dialect Property Direction Property EditMode Property EOS Property Filter Property HelpContext Property HelpFile Property Index Property IsolationLevel Property Item Property (ADO) LineSeparator Property (ADO) LockType Property (ADO) MarshalOptions Property (ADO) MaxRecords Property (ADO) Mode Property (ADO) Name Property (ADO) NamedParameters Property (ADO) NativeError Property (ADO) Number Property (ADO) NumericScale Property (ADO) OriginalValue Property (ADO) PageCount Property (ADO) PageSize Property (ADO) ParentRow Property (ADO) ParentURL Property (ADO) Position Property (ADO) Precision Property (ADO) Prepared Property (ADO) Provider Property (ADO) RecordCount Property (ADO) RecordType Property (ADO) Row Property (ADO) RowPosition Property (ADO) Rowset Property (ADO) Size Property (ADO Parameter) Size Property (ADO Stream) Sort Property Source Property (ADO Error) Source Property (ADO Record) Source Property (ADO Recordset) SQLState Property State Property (ADO) Status Property (ADO Field) Status Property (ADO Recordset) StayInSync Property Type Property (ADO Stream) Type Property (ADO) UnderlyingValue Property Value Property (ADO) Version Property (ADO) ADO Dynamic Properties ADO Dynamic Property Index Optimize Property-Dynamic (ADO) Prompt Property-Dynamic (ADO) Reshape Name Property-Dynamic (ADO) Resync Command Property-Dynamic (ADO) Update Resync Property-Dynamic (ADO) Unique Table, Unique Schema, Unique Catalog Properties-Dynamic (ADO) |
ADO Objects:
ADO provides two objects for managing connections with data sources (Connection and
Command), two objects for managing the data returned from a data source (Field and Recordset) and three secondary objects (Parameters, Properties, and Errors) for managing information about ADO.ADO Command Object
The ADO Command object is used to execute a single query against a database. The query can perform actions like creating, adding, retrieving, deleting or updating records.
If the query is used to retrieve data, the data will be returned as a RecordSet object. This means that the retrieved data can be manipulated by properties, collections, methods, and events of the Recordset object.
The major feature of the Command object is the ability to use stored queries and procedures with parameters.
Properties
| Property |
Description |
|---|---|
| ActiveConnection | Sets or returns a definition for a connection if the connection is closed, or the current Connection object if the connection is open |
| CommandText | Sets or returns a provider command |
| CommandTimeout | Sets or returns the number of seconds to wait while attempting to execute a command |
| CommandType | Sets or returns the type of a Command object |
| Name | Sets or returns the name of a Command object |
| Prepared | Sets or returns a Boolean value that, if set to True, indicates that the command should save a prepared version of the query before the first execution |
| State | Returns a value that describes if the Command object is open, closed, connecting, executing or retrieving data |
Methods
| Method |
Description |
|---|---|
| Cancel | Cancels an execution of a method |
| CreateParameter | Creates a new Parameter object |
| Execute | Executes the query, SQL statement or procedure in the CommandText property |
Collections
| Collection |
Description |
|---|---|
| Parameters | Contains all the Parameter objects of a Command Object |
| Properties | Contains all the Property objects of a Command Object |
ADO Connection Object
The ADO Connection Object is used to create an open connection to a data source. Through this connection, you can access and manipulate a database.
If you want to access a database multiple times, you should establish a connection using the Connection object. You can also make a connection to a database by passing a connection string via a Command or Recordset object. However, this type of connection is only good for one specific, single query.
Properties
| Property |
Description |
|---|---|
| Attributes | Sets or returns the attributes of a Connection object |
| CommandTimeout | Sets or returns the number of seconds to wait while attempting to execute a command |
| ConnectionString | Sets or returns the details used to create a connection to a data source |
| ConnectionTimeout | Sets or returns the number of seconds to wait for a connection to open |
| CursorLocation | Sets or returns the location of the cursor service |
| DefaultDatabase | Sets or returns the default database name |
| IsolationLevel | Sets or returns the isolation level |
| Mode | Sets or returns the provider access permission |
| Provider | Sets or returns the provider name |
| State | Returns a value describing if the connection is open or closed |
| Version | Returns the ADO version number |
Methods
| Method |
Description |
|---|---|
| BeginTrans | Begins a new transaction |
| Cancel | Cancels an execution |
| Close | Closes a connection |
| CommitTrans | Saves any changes and ends the current transaction |
| Execute | Executes a query, statement, procedure or provider specific text |
| Open | Opens a connection |
| OpenSchema | Returns schema information from the provider about the data source |
| RollbackTrans | Cancels any changes in the current transaction and ends the transaction |
Events
Note: You can only use Visual Basic, Visual C++, and Visual J++ languages to handle events.
| Event |
Description |
|---|---|
| BeginTransComplete | Triggered after the BeginTrans operation |
| CommitTransComplete | Triggered after the CommitTrans operation |
| ConnectComplete | Triggered after a connection starts |
| Disconnect | Triggered after a connection ends |
| ExecuteComplete | Triggered after a command has finished executing |
| InfoMessage | Triggered if a warning occurs during a ConnectionEvent operation |
| RollbackTransComplete | Triggered after the RollbackTrans operation |
| WillConnect | Triggered before a connection starts |
| WillExecute | Triggered before a command is executed |
Collections
| Collection |
Description |
|---|---|
| Errors | Contains all the Error objects of the Connection object |
| Properties | Contains all the Property objects of the Connection object |
ADO Error Object
The ADO Error object contains details about data access errors that have been generated during a single operation.
ADO generates one Error object for each error. Each Error object contains details of the specific error, and are stored in the Errors collection. To access the errors, you must refer to a specific connection.
Properties
| Property |
Description |
|---|---|
| Description | Returns an error description |
| HelpContext | Returns the context ID of a topic in the Microsoft Windows help system |
| HelpFile | Returns the full path of the help file in the Microsoft Windows help system |
| NativeError | Returns an error code from the provider or the data source |
| Number | Returns a unique number that identifies the error |
| Source | Returns the name of the object or application that generated the error |
| SQLState | Returns a 5-character SQL error code |
Methods
| Method |
Description |
|---|---|
| Clear | Removes all the Error objects from the Errors collection. |
ADO Field Object
The ADO Field object contains information about a column in a Recordset object. There is one Field object for each column in the Recordset.
Properties
| Property |
Description |
|---|---|
| ActualSize | Returns the actual length of a field's value |
| Attributes | Sets or returns the attributes of a Field object |
| DefinedSize | Returns the defined size of a field |
| Name | Sets or returns the name of a Field object |
| NumericScale | Sets or returns the number of decimal places allowed for numeric values in a Field object |
| OriginalValue | Returns the original value of a field |
| Precision | Sets or returns the maximum number of digits allowed when representing numeric values in a Field object |
| Status | Returns the status of a Field object |
| Type | Sets or returns the type of a Field object |
| UnderlyingValue | Returns the current value of a field |
| Value | Sets or returns the value of a Field object |
Methods
| Method |
Description |
|---|---|
| Append | Add (append) a Field object to the Fields Collection. |
| AppendChunk | Appends long binary or character data to a Field object |
| CancelUpdate | Cancels any changes made to the Fields collection of a Record object, before calling the Update method. |
| GetChunk | Returns all or a part of the contents of a large text or binary data Field object |
Collections
| Collection |
Description |
|---|---|
| Properties | Contains all the Property objects for a Field object |
ADO Parameter Object
The ADO Parameter object provides information about a single parameter used in a stored procedure or query.
A Parameter object is added to the Parameters Collection when it is created. The Parameters Collection is associated with a specific Command object, which uses the Collection to pass parameters in and out of stored procedures and queries.
Parameters can be used to create Parameterized Commands. These commands are (after they have been defined and stored) using parameters to alter some details of the command before it is executed. For example, an SQL SELECT statement could use a parameter to define the criteria of a WHERE clause.
There are four types of parameters: input parameters, output parameters, input/output parameters and return parameters.
Properties
| Property |
Description |
|---|---|
| Attributes | Sets or returns the attributes of a Parameter object |
| Direction | Sets or returns how a parameter is passed to or from a procedure |
| Name | Sets or returns the name of a Parameter object |
| NumericScale | Sets or returns the number of digits stored to the right side of the decimal point for a numeric value of a Parameter object |
| Precision | Sets or returns the maximum number of digits allowed when representing numeric values in a Parameter |
| Size | Sets or returns the maximum size in bytes or characters of a value in a Parameter object |
| Type | Sets or returns the type of a Parameter object |
| Value | Sets or returns the value of a Parameter object |
Methods
| Method |
Description |
|---|---|
| Append | Add (append) a Parameter object to the Parameters Collection. |
| AppendChunk | Appends long binary or character data to a Parameter object |
| Delete | Deletes an object from the Parameters Collection |
ADO Property Object
The ADO Property object represents a dynamic characteristic of an ADO object that is defined by the provider.
Each provider that talks with ADO has different ways of interacting with ADO. Therefore, ADO needs to store information about the provider in some way. The solution is that the provider gives specific information (dynamic properties) to ADO. ADO stores each provider property in a Property object that is again stored in the Properties Collection. The Collection is assigned to either a Command object, Connection object, Field object, or a Recordset object.
Properties
| Property |
Description |
|---|---|
| Attributes | Returns the attributes of a Property object |
| Name | Sets or returns the name of a Property object |
| Type | Returns the type of a Property object |
| Value | Sets or returns the value of a Property object |
ADO Record Object
The ADO Record object is used to hold a row in a Recordset, a directory, or a file from a file system.
Only structured databases could be accessed by ADO in versions prior 2.5. In a structured database, each table has the exact same number of columns in each row, and each column is composed of the same data type.
The Record object allows access to data-sets where the number of columns and/or the data type can be different from row to row.
Properties
| Property |
Description |
|---|---|
| ActiveConnection | Sets or returns which Connection object a Record object belongs to |
| Mode | Sets or returns the permission for modifying data in a Record object |
| ParentURL | Returns the absolute URL of the parent Record |
| RecordType | Returns the type of a Record object |
| Source | Sets or returns the src parameter of the Open method of a Record object |
| State | Returns the status of a Record object |
Methods
| Method |
Description |
|---|---|
| Cancel | Cancels an execution of a CopyRecord, DeleteRecord, MoveRecord, or Open call |
| Close | Closes a Record object |
| CopyRecord | Copies a file or directory to another location |
| DeleteRecord | Deletes a file or directory |
| GetChildren | Returns a Recordset object where each row represents the files in the directory |
| MoveRecord | Moves a file or a directory to another location |
| Open | Opens an existing Record object or creates a new file or directory |
Collections
| Collection |
Description |
|---|---|
| Properties | A collection of provider-specific properties |
| Fields | Contains all the Field objects in the Record object |
The Fields Collection's Properties
| Property |
Description |
|---|---|
| Count | Returns the number of items in the fields
collection. Starts at zero. Example: countfields = rec.Fields.Count |
| Item(named_item/number) | Returns a specified item in the fields
collection. Example: itemfields = rec.Fields.Item(1) or itemfields = rec.Fields.Item("Name") |
ADO Recordset Object
The ADO Recordset object is used to hold a set of records from a database table. A Recordset object consist of records and columns (fields).
In ADO, this object is the most important and the one used most often to manipulate data from a database.
When you first open a Recordset, the current record pointer will point to the first record and the BOF and EOF properties are False. If there are no records, the BOF and EOF property are True.Recordset objects can support two types of updating:
- Immediate updating - all changes are written immediately to the database once you call the Update method.
- Batch updating - the provider will cache multiple changes and then send them to the database with the UpdateBatch method.
In ADO there are 4 different cursor types defined:
- Dynamic cursor - Allows you to see additions, changes, and deletions by other users.
- Keyset cursor - Like a dynamic cursor, except that you cannot see additions by other users, and it prevents access to records that other users have deleted. Data changes by other users will still be visible.
- Static cursor - Provides a static copy of a recordset for you to use to find data or generate reports. Additions, changes, or deletions by other users will not be visible. This is the only type of cursor allowed when you open a client-side Recordset object.
- Forward-only cursor - Allows you to only scroll forward through the Recordset. Additions, changes, or deletions by other users will not be visible.
The cursor type can be set by the CursorType property or by the CursorType parameter in the Open method.
Note: Not all providers support all methods or properties of the Recordset object.
Properties
| Property |
Description |
|---|---|
| AbsolutePage | Sets or returns a value that specifies the page number in the Recordset object |
| AbsolutePosition | Sets or returns a value that specifies the ordinal position of the current record in the Recordset object |
| ActiveCommand | Returns the Command object associated with the Recordset |
| ActiveConnection | Sets or returns a definition for a connection if the connection is closed, or the current Connection object if the connection is open |
| BOF | Returns true if the current record position is before the first record, otherwise false |
| Bookmark | Sets or returns a bookmark. The bookmark saves the position of the current record |
| CacheSize | Sets or returns the number of records that can be cached |
| CursorLocation | Sets or returns the location of the cursor service |
| CursorType | Sets or returns the cursor type of a Recordset object |
| DataMember | Sets or returns the name of the data member that will be retrieved from the object referenced by the DataSource property |
| DataSource | Specifies an object containing data to be represented as a Recordset object |
| EditMode | Returns the editing status of the current record |
| EOF | Returns true if the current record position is after the last record, otherwise false |
| Filter | Sets or returns a filter for the data in a Recordset object |
| Index | Sets or returns the name of the current index for a Recordset object |
| LockType | Sets or returns a value that specifies the type of locking when editing a record in a Recordset |
| MarshalOptions | Sets or returns a value that specifies which records are to be returned to the server |
| MaxRecords | Sets or returns the maximum number of records to return to a Recordset object from a query |
| PageCount | Returns the number of pages with data in a Recordset object |
| PageSize | Sets or returns the maximum number of records allowed on a single page of a Recordset object |
| RecordCount | Returns the number of records in a Recordset object |
| Sort | Sets or returns the field names in the Recordset to sort on |
| Source | Sets a string value or a Command object reference, or returns a String value that indicates the data source of the Recordset object |
| State | Returns a value that describes if the Recordset object is open, closed, connecting, executing or retrieving data |
| Status | Returns the status of the current record with regard to batch updates or other bulk operations |
| StayInSync | Sets or returns whether the reference to the child records will change when the parent record position changes |
Methods
| Method |
Description |
|---|---|
| AddNew | Creates a new record |
| Cancel | Cancels an execution |
| CancelBatch | Cancels a batch update |
| CancelUpdate | Cancels changes made to a record of a Recordset object, before calling the Update method. |
| Clone | Creates a duplicate of an existing Recordset |
| Close | Closes a Recordset |
| CompareBookmarks | Compares two bookmarks |
| Delete | Deletes a record or a group of records |
| Find | Searches for a record in a Recordset that satisfies a specified criteria |
| GetRows | Copies multiple records from a Recordset object into a two-dimensional array |
| GetString | Returns a Recordset as a string |
| Move | Moves the record pointer in a Recordset object |
| MoveFirst | Moves the record pointer to the first record |
| MoveLast | Moves the record pointer to the last record |
| MoveNext | Moves the record pointer to the next record |
| MovePrevious | Moves the record pointer to the previous record |
| NextRecordset | Clears the current Recordset object and returns the next Recordset object by looping through a series of commands |
| Open | Opens a database element that gives you access to records in a table, the results of a query, or to a saved Recordset |
| Requery | Updates the data in a Recordset by re-executing the query that made the original Recordset |
| Resync | Refreshes the data in the current Recordset from the original database |
| Save | Saves a Recordset object to a file or a Stream object |
| Seek | Searches the index of a Recordset to find a record that matches the specified values |
| Supports | Returns a boolean value that defines whether or not a Recordset object supports a specific type of functionality |
| Update | Saves all changes made to a single record in a Recordset object |
| UpdateBatch | Saves all changes in a Recordset to the database. Used when working in batch update mode |
Events
Note: You cannot handle events using VBScript or JScript (only Visual Basic, Visual C++, and Visual J++ languages can handle events).
| Event |
Description |
|---|---|
| EndOfRecordset | Triggered when you try to move to a record after the last record |
| FetchComplete | Triggered after all records in an asynchronous operation have been fetched |
| FetchProgress | Triggered periodically in an asynchronous operation, to state how many more records that have been fetched |
| FieldChangeComplete | Triggered after the value of a Field object change |
| MoveComplete | Triggered after the current position in the Recordset has changed |
| RecordChangeComplete | Triggered after a record has changed |
| RecordsetChangeComplete | Triggered after the Recordset has changed |
| WillChangeField | Triggered before the value of a Field object change |
| WillChangeRecord | Triggered before a record change |
| WillChangeRecordset | Triggered before a Recordset change |
| WillMove | Triggered before the current position in the Recordset changes |
Collections
| Collection |
Description |
|---|---|
| Fields | Indicates the number of Field objects in the Recordset object |
| Properties | Contains all the Property objects in the Recordset object |
ADO Stream Object
The ADO Stream Object is used to read, write, and manage a stream of binary data or text.
A Stream object can be obtained in three ways:
- From a URL pointing to a document, a folder, or a Record object
- By instantiating a Stream object to store data for your application
- By opening the default Stream object associated with a Record object
Properties
| Property |
Description |
|---|---|
| CharSet | Sets or returns a value that specifies into which character set the contents are to be translated. This property is only used with text Stream objects (type is adTypeText) |
| EOS | Returns whether the current position is at the end of the stream or not |
| LineSeparator | Sets or returns the line separator character used in a text Stream object |
| Mode | Sets or returns the available permissions for modifying data |
| Position | Sets or returns the current position (in bytes) from the beginning of a Stream object |
| Size | Returns the size of an open Stream object |
| State | Returns a value describing if the Stream object is open or closed |
| Type | Sets or returns the type of data in a Stream object |
Methods
| Method |
Description |
|---|---|
| Cancel | Cancels an execution of an Open call on a Stream object |
| Close | Closes a Stream object |
| CopyTo | Copies a specified number of characters/bytes from one Stream object into another Stream object |
| Flush | Sends the contents of the Stream buffer to the associated underlying object |
| LoadFromFile | Loads the contents of a file into a Stream object |
| Open | Opens a Stream object |
| Read | Reads the entire stream or a specified number of bytes from a binary Stream object |
| ReadText | Reads the entire stream, a line, or a specified number of characters from a text Stream object |
| SaveToFile | Saves the binary contents of a Stream object to a file |
| SetEOS | Sets the current position to be the end of the stream (EOS) |
| SkipLine | Skips a line when reading a text Stream |
| Write | Writes binary data to a binary Stream object |
| WriteText | Writes character data to a text Stream object |
ADO Data Types
The table below shows the ADO Data Type mapping in MS Access
and
Visual Basic
6
|
ADO DataType Enum |
ADO DataType Enum Value |
Access |
Visual Basic 6.0 |
|---|---|---|---|
| adBigInt | 20 | Variant | |
| adBinary | 128 |
Binary LongBinary |
Variant |
| adBoolean | 11 |
Bit YesNo |
Boolean |
| adBSTR | 8 | ||
| adChapter | 136 | ||
| adChar | 129 | String | |
| adCurrency | 6 | Currency | Currency |
| adDate | 7 | DateTime | Date |
| adDBDate | 133 | ||
| adDBFileTime | 137 | ||
| adDBTime | 134 | ||
| adDBTimeStamp | 135 | DateTime | Date |
| adDecimal | 14 | Variant | |
| adDouble | 5 | Double | Double |
| adEmpty | 0 | ||
| adError | 10 | ||
| adFileTime | 64 | ||
| adGUID | 72 |
Guid ReplicationID |
Variant |
| adIDispatch | 9 | ||
| adInteger | 3 |
Counter AutoNumber LongInteger |
Long |
| adIUnknown | 13 | ||
| adLongVarBinary | 205 | OLEObject | Variant |
| adLongVarChar | 201 |
Memo Hyperlink |
String |
| adLongVarWChar | 203 |
Memo Hyperlink |
String |
| adNumeric | 131 | Decimal | Variant |
| adPropVariant | 138 | ||
| adSingle | 4 | Single | Single |
| adSmallInt | 2 |
Integer Short |
Integer |
| adTinyInt | 16 | ||
| adUnsignedBigInt | 21 | ||
| adUnsignedInt | 19 | ||
| adUnsignedSmallInt | 18 | ||
| adUnsignedTinyInt | 17 | Byte | Byte |
| adUserDefined | 132 | ||
| adVarBinary | 204 | ReplicationID | Variant |
| adVarChar | 200 |
Text LongText |
String |
| adVariant | 12 | Variant | |
| adVarNumeric | 139 | ||
| adVarWChar | 202 | Text | String |
| adWChar | 130 | String |
