Command Object

A Command object is a definition of a specific command that you intend to execute against a data source.

Collections

Properties

ActiveConnection, CommandText, CommandTimeout, CommandType, Prepared

Methods

Remarks

You can create a Command object independently of a previously defined Connection object by setting its ActiveConnection property to a valid connection string. ADO still creates a Connection object, but it doesn't assign that object to an object variable. However, if you are associating multiple Command objects with the same connection, you should explicitly create and open a Connection object; this assigns the Connection object to an object variable. If you do not set the Command objects' ActiveConnection property to this object variable, ADO creates a new Connection object for each Command object, even if you use the same connection string.

Use Command objects to obtain records and create a Recordset object, to execute a bulk operation, or to manipulate the structure of a database. Depending on the functionality the provider exposes, some collections, methods, or properties of a Command object may generate an error when you call them.

With the collections, methods, and properties of a Command object, you can do the following:

· Associate an open connection and the Command object with the ActiveConnection property.

· Define the text version of the command (for example, an SQL statement) with the CommandText property.

· Set the number of seconds a provider will wait for a command to execute with the CommandTimeout property.

· Specify the type of command described in the CommandText property with the CommandType property prior to execution in order to optimize performance.

· Determine whether or not the provider saves a prepared (or compiled) version of the command prior to execution with the Prepared property.

· Manage command arguments passed to and from the provider with the Parameters collection.

· Execute a command and return a Recordset object if appropriate with the Execute method.

· Keyset cursor ¾ behaves like a dynamic cursor, except that it prevents you from seeing records that other users add, and prevents access to records that other users delete from your recordset; always allows bookmarks and therefore allows all types of movement through the Recordset. Data changes by other users will still be visible.

· Static cursor ¾ provides a static copy of a set of records for you to use to find data or generate reports; always allows bookmarks and therefore allows all types of movement through the Recordset. Additions, changes, or deletions by other users will not be visible.

· Forward-only cursor ¾ behaves identically to a static cursor except that it allows you to scroll only forward through records. This improves performance in situations where you need to make only a single pass through a recordset.

Set the CursorType property prior to opening the Recordset object to choose the cursor type of the Recordset object. You can also pass a CursorType argument with the Open method.

If you don't specify a cursor type, ADO opens a forward-only cursor by default.

You can create as many Recordset objects as needed. Different Recordset objects can access the same tables and fields without conflicting.

When you create a Recordset object, the current record is positioned to the first record (if any) and the BOF and EOF properties are set to False. If there are no records, the RecordCount property setting is 0, and the BOF and EOF property settings are True.

You can use the MoveFirst, MoveLast, MoveNext, and MovePrevious methods, as well as the Move method, and the AbsolutePosition, AbsolutePage, and Filter properties to reposition the current record, assuming the provider supports the relevant functionality. Forward-only Recordset objects support only the MoveNext method. When you use the Move methods to visit each record (or enumerate the Recordset), you can use the BOF and EOF properties to see if you've moved beyond the beginning or end of the Recordset object.

Recordset objects may support two types of updating: immediate and batched. In immediate updating, all changes to data are written immediately to the underlying data source once you call the Update method. You can also pass arrays of values as parameters with the AddNew and Update methods and simultaneously update several fields in a record.

If a provider supports batch updating, you can have the provider cache changes to more than one record and then transmit them in a single call to the database with the UpdateBatch method. This applies to changes made with the AddNew, Update, and Delete methods. After you call the UpdateBatch method, you can use the Status property to check for any data conflicts in order to resolve them.