Visual Basic Concepts

Command Objects

Command objects define specific detailed information about what data is retrieved from a database connection. Command objects can be based on either a database object (such as a table, view, stored procedure or synonym) or a Structured Query Language (SQL) query. You can also create relationships between Command objects to retrieve a set of related data in the form of a hierarchy (see Command Hierarchies).

Note   To be valid, a Command object must be associated with a Connection object.

If a Command object returns data, it is "recordset returning," and the results can be accessed using a Recordset object available from the DataEnvironment object. However, if a Command object does not return data (for example, stored procedures or SQL text that performs an update), it is "non-recordset returning." The Data Environment Designer automatically identifies whether the Command is recordset returning. You can override this setting by using the Recordset Returning check box on the Advanced tab of the Command Properties dialog box.

At run time, how you access the Command object depends on whether the Command object is recordset returning. If the Command object is recordset returning, you can access the Command object as either a property or method from the DataEnvironment object. If it is non-recordset returning, your Command object is only accessible as a method. See Using a Data Environment with Your Application for more information.

Creating a Command Object

The Add Command function is available at all times and is independent of the existence of other objects. However, a Command object that is not associated with a Connection object is invalid.

If a Connection object can be identified from the current focus during the add process, the ActiveConnection property of the Command object is set to that Connection object. If a Connection object is not identified, the Command object is invalid until you associate it with a connection.

To add a Command object

To specify Command object properties

  1. Right-click the Connection object and choose Properties to access the Command Properties dialog box.

  2. Click the General tab, and set the following:
    Item Purpose
    Command Name Change the default Command Name to a more meaningful name for your database object. For example, you may wish to change Command1 to "Customers" if the Command object is based on a table called "customers."
    Connection If the Command object was created from a Connection object's shortcut menu, the Connection name is automatically set. However, you can change this connection.

    Note   To be valid, each Command object must be associated with a Connection object.
    Database Object Select the type of database object from the drop-down list. This can be a stored procedure, synonym, table, or view.
    Object Name Select an object name from the drop-down list. The listed objects are from the connection and match the selected Database Object type.
    –or–  
    SQL Statement If this is selected as your data source, type an SQL query that is valid for your database in the SQL Statement box.

    -or–

    To build the query, click SQL Builder to launch the query designer.

  3. If the Command object is based on a parameterized query or a stored procedure, it may have a parameters collection. To set the parameter properties, click the Parameters tab in the Command Properties dialog box.

  4. Use the Relation, Grouping, and Aggregates tabs to define relationships and shape the data included in the Recordset. For more information, see Command Hierarchies.

  5. Click the Advanced tab in the Command Properties dialog box to set the properties that change how the data is retrieved or manipulated at run time. On this tab, set the advanced properties that provide your Data Environment control over the Command object properties and its resulting Recordset object.

  6. Click OK to apply the properties to the new Command object and close the dialog box.

    If a recordset-returning Command object was successfully created, you can click the expand (+) bitmap from the Data Environment designer's outline view to see a list of fields. If no fields are shown, the cause could be an empty Recordset, an invalid Command object, or an invalid connection. If you are sure you have a valid connection, right-click the DataEnvironment icon, and make sure the Show Fields menu command is checked.

Customizing the Parameter Objects of a Command Object

If a Command object is based on a parameterized query or a stored procedure with parameters, the Command object has a Parameters collection. You may want to customize the Parameter objects contained in the collection by changing the data type or making the name more descriptive. For more information, see ADO Parameters.

Changing the Properties of Associated Parameter Objects

The following procedure describes how you can change the properties of Parameter objects that are associated with a Command object.

To change a Command object's associated Parameter object properties

  1. Right-click the Command object that you wish to customize, and then select Properties from the shortcut menu.

  2. From the Parameters tab, select a Parameter object from the Parameter list box, and then set the following properties:
    Item Purpose
    Name Provide a unique, meaningful name for the selected Parameter object.
    Direction Specify whether this is an input or output parameter, or both, or if the parameter is the return value from the procedure.
    DataType Specify the data type to which the Parameter object is converted.
    Precision Specify the maximum size, in bytes.
    Scale Specify the maximum number of digits to the right of the decimal point.
    Size Specify the maximum size, in bytes.
    Host Data Type Specify the data type used when this Parameter object is referenced by the host application. Changing this setting affects the data types used in building the type library information for the host.
    Required Specify whether the parameter value is required when the Command object is executed.

    Note   If a required parameter is not set when the Command object is executed, the command will fail.
    Value Specify the default value that is used at run time (unless a value is provided programmatically), and if necessary, at design time, if the Command object must be executed to obtain the field information.

  3. Click OK to apply the parameter properties to the selected Command object and exit the dialog box.

For more information, see ADO Parameters.

Dragging From a Data View to Your Data Environment

You can automatically create Command objects by dragging from the Data View window to your Data Environment designer. This is an easy and efficient way to create Command objects from tables, views, or stored procedures that are listed in your Data View. If the connection associated with the Command object being dropped doesn't already exist in the Data Environment, a Connection object is automatically created.

Creating Multiple Command Objects from Stored Procedures

You can create multiple Command objects in your Data Environment designer from stored procedures using the Insert Stored Procedures dialog box.

To insert multiple stored procedures

  1. Click Insert Stored Procedures in the Data Environment designer toolbar.

    -or–

    Right-click a DataEnvironment or Connection object and choose Insert Stored Procedures from the shortcut menu.

  2. In the Insert Stored Procedures dialog box, move one or more stored procedures from the Available list to the Add list using the arrows.

    Use > to move the stored procedures to the Add list one at a time, or use >> to move all stored procedures at once. Use < to remove the stored procedures from the Add list one at a time, or use << to remove all stored procedures at once.

  3. Once the stored procedures are in the Add list, click Insert to add them to your Data Environment. A new Command object is created for each stored procedure.

    Note   The name of the Command object defaults to the name of the stored procedure.

  4. Click Close to exit the dialog box.