Visual Basic Concepts

Article
01/04/2007

Data Environment Programming Guidelines

The Data Environment designer allows the quick and easy building of hierarchical recordsets at design time. Programming the resulting DataEnvironment object, however, requires a knowledge of the ADO programming model combined with characteristics of hierarchical recordsets. Below are the major points to visit when programming the DataEnvironment object.

****Note ****The phrase "Data Environment designer" refers to the designer and its features. The phrase "DataEnvironment object" refers to the resulting programming object of the Data Environment designer.

Requerying the Recordset

Requerying a DataEnvironment-built recordset presents a few issues to be aware of. If you are using controls that are bound to the DataEnvironment, you must rebind those controls after requerying the recordset. You can rebind the controls on a form to the DataEnvironment object by setting the DataMember, DataSource, and DataField properties wherever applicable.

Rebind After Using Requery Method

Whenever you query the database using the Requery method, you must rebind the controls to the appropriate recordset objects, as shown in the example below:

deNwind.rsCustomers.Requery
' Rebind a TextBox control named txtCustomer and a DataGrid
' control named grdData.
With txtCustomer
   Set .DataSource = deNwind
   .DataMember = "Customers"
   .DataField = "CompanyName"
End With
With grdData
   .DataMember = "customers"
   Set .DataSource = deNwind
End With

****Note ****The Data Environment designer automatically prepends references to Command objects with "rs"—thusCustomersbecomes rsCustomers. Also notice that the DataEnvironment object contains a "Customers" method. By invoking the method, you can open its recordset.

Closing and Reopening the Recordset

When you close a recordset, the controls bound to that recordset no longer listen for events broadcasted by that recordset to notify them of changes. If you reopen the recordset, the data for that recordset will not appear in the controls until you rebind them.

Creating a Parameterized Query

The example below demonstrates how to create a parameterized query with the Data Environment designer.

To create a parameterized query

Create a new project and add a Data Environment designer to the project.
On the Data Environment designer, create a Connection object and configure it to connect to the SQL Server Pubs database.
Right-click the Connection object and click Add Command.
Rename the Command object CommandQuery.
Right-click the Command object and click Properties.
On the General tab, click SQL Statement and type the following parameterized query:
```
SELECT * FROM Authors Where Au_ID = ?
```
Click the Parameters tab. (A message will appear informing you that not enough information has been supplied. Click OK.)

A new Input parameter will have been created for you. You can create further parameters, if needed, or set properties of the parameter. If you are using the Access OLE DB Provider or ODBC Driver, see the section below entitled "Paramaterized Queries and the Access OLE DB Provider and ODBC Driver."
Click OK to close the dialog box.
On the default form, draw two TextBox controls and a CommandButton control.

Add the code below to the Form object's code window:

Option Explicit
Private Sub Command1_Click()
    ' You must close the recordset before changing the parameter.
    If DataEnvironment1.rsCommandQuery.State = adStateOpen Then
        DataEnvironment1.rsCommandQuery.Close
    End If
    ' Reopen the recordset with the input parameter supplied by
    ' the TextBox control.
    DataEnvironment1.CommandQuery Text1.Text
    With Text2
        .DataField = "AU_LName"
        .DataMember = "CommandQuery"
        Set .DataSource = DataEnvironment1
    End With
End Sub

Private Sub Form_Load()
    ' Supply a default value.
    Text1.Text = "172-32-1172"
    ' Change the CommandButton caption.
    Command1.Caption = "Run Query"
End Sub

Run the project and click the button. Change the text in Text1 to run another query.

Parameterized Queries At Run Time

It's possible to build a parameterized query with the Data Environment designer and have controls bound to that DataMember. That query will run when the form is loaded. If you don't have the value of the parameter set ahead of time, the query will fail. You'll have a closed recordset and you'll need to re-bind your controls before explicitly running the query. To avoid this, at design time be sure to supply a default value for the parameter (on the Parameters tab). Alternatively, at run time, simply make sure you call the Command explicitly before displaying a control bound to that Command.

Paramaterized Queries and the Access OLE DB Provider and ODBC Driver

Due to limitations of the Microsoft® Access OLE DB provider and ODBC driver, parameterized queries are not fully supported in the Data Environment when connecting to an Access database, because the driver and provider do not return information about parameters (like data type and direction) in the query.

Setting the Required Property for Stored Procedure Parameters

When running stored procedures from the DataEnvironment, how the Required property of a parameter is set depends on whether or not the stored procedure command is a child command in a hierarchy.

Stored Procedure as Child Commands: Optional Parameters Only

Although it's possible to insert a stored procedure into a hierarchy as a child command, there are two conditions that must be met:

The stored procedure must not have any required parameters.
After inserting the stored procedure into a hierarchy, you must set the Required check box for any parameters to False.

To create a stored procedure as a child command

Right-click an existing Command object and click Add Child Command.
Right-click the new Command object and click Properties.
By default, the Database Object is set to Stored Procedure. Click the Object Name box to see a list of stored procedures and select one.
Click the Parameters tab.
Select any parameter except RETURN_VALUE.
Set the Required property to False.

Setting Required Parameters for Inserted Stored Procedures with SQLServer Required

When using SQLServer and stored procedures, it's possible to create stored procedures that have optional parameters. However, if the stored procedure is not a child object in a hierarchy, optional parameters are not accepted, and instead must be supplied by your code.

An example of a stored procedure with an optional parameter (@idCol) is shown below:

CREATE PROCEDURE OptionalParametersNotAllowed
   (@IDCol smallint = NULL)
   AS
   IF @IDCol = NULL
      select count(SupplierID) from Products
   ELSE
      select count(SupplierID) from Products where SupplierID= @IDCol

With the Data Environment designer, you can insert the stored procedure by right-clicking a Connection object and clicking Insert Stored Procedures. As shown above, you can set properties of the stored procedure's parameters by right-clicking the resulting Command object, clicking Properties, then clicking the Parameters tab. While it's possible to set the Required option for the parameter to False, the DataEnvironment object will generate an error message ("Argument not optional") unless a value is supplied. To work around this situation, be sure to supply a value as shown in the code below:

Private Sub Command1_Click()
   ' Open the connection.
   DE.Connection1.Open , "sa"
   ' Run the stored procedure with a value.
   DE.dbo_ OptionalParametersNotAllowed 8
   Debug.Print DE.rsdbo_OptionalParametersNotAllowed.Fields(0).Value
   ' Free resources
   DE.Connection1.Close
   Set DE = Nothing
End Sub

Enabling the MTS SetAbort Method

If you use your DataEnvironment object in an MTS component with its default settings, the work done by the DataEnvironment is not in the MTS transaction. Thus the SetAbort method will not roll back the work done by the DataEnvironment object. To ensure that the SetAbort method will roll back the DataEnvironment work, there are two methods:

At design time, set the the RunPromptBehavior of the Connection object to 4-adPromptNever. (Select the Connection object and press F4 to display the Properties window.)
At run time, set the dynamic prompt property of your connection to adPromptNever prior to opening your connection, as shown below:
```
DataEnvironment1.Connection1.Properties("Prompt") = adPromptNever
DataEnvironment1.Connection1.Open
```

Important Not all OLE DB Providers and ODBC Drivers support suppressing the prompt if login fails. And not all database management systems support the two-phase commit protocol required to support an MTS transaction.

Using the DataReport with the DataEnvironment Object

When the DataReport is displayed for the first time, it must read the contents of the recordset. Once it has read the contents of the recordset, it leaves the recordset at BOF. Since this work is done asynchronously, you can't simply reset the bookmark after invoking the Show method. There is no event to signify when the DataReport has finished reading the recordset. This situation also affects the DataEnviroment because you may want to display the same data on a form and in a DataReport. If you display the data in textboxes on the form and then have a command button to display/print the data via the DataReport, the textboxes will be blank because the textboxes are using the same recordset as the DataReport whose bookmark is currently at BOF. See the Knowledge Base article Q190607 for more information.

Using the HFlexGrid Control with the DataEnvironment Object

The behavior of the Hierarchical FlexGrid differs from that of the DataGrid. While both are data-aware controls, the HFlexGrid control does not allow the user to edit data and propagate the changes back to the database. Instead the data is read into the grid once. Once the data is displayed, the control no longer acts like a bound control. Navigating through the recordset does not affect the active row in the MSHFlexGrid, and vice versa. Changes made to the recordset (insertions, updates, deletions, requeries) do not affect the MSHFlexGrid.

Working with Shape Commands

There are two distinct methods for requerying a database:

Requery using a Shape command.
Requery using a SQL statement.

The differences may account for some confusion when trying to program the DataEnvironment object.

Shape Commands Described

When you create a hierarchical recordset (adding Child commands and using the Relationship tab of the Properties dialog box to associate them), you are actually creating several recordsets. The resultant family of recordsets cannot easily be duplicated by using a standard SQL statement. In fact, the Data Environment designer uses a different language syntax called the Shape command. You can see the Shape command of any hierarchical recordset by checking the Source property either at run time or at design time:

Right-click the top-most Command object in a hierarchy.
Click Hierarchy Info.

By default, the Shape command is visible. An example is shown below:

SHAPE {SELECT * FROM `Customers`}  AS Customers APPEND (( SHAPE {SELECT * FROM `Orders`}  AS Orders APPEND ({SELECT * FROM 'Order Details`}  AS OrderDetails RELATE 'OrderID' TO 'OrderID') AS OrderDetails) AS Orders RELATE 'CustomerID' TO 'CustomerID') AS Orders

You can reproduce the hierarchical recordset by assigning the Source property to the Shape command. However, as shown above, you must rebind the controls to the proper recordset objects. An example is shown below:

With grdData
   ' Be sure to reset the DataMember property to the name of the
   ' Command object you want to bind to.
   .DataMember = "Orders"
   Set .DataSource = deNwind
End With

With txtCustomer
   .DataMember = "Customers"
   .DataField = "CompanyName"
   Set .DataSource = deNwind
End With

Walking Through the Command Shape

A hierarchical recordset consists of several recordsets presented in a stair-like, parent-child structure. In the Data Environment, each recordset is created by a child Command. To "walk" the structure in code, you must declare an ADO Recordset object variable for each child recordset and assign a reference to it. The reference must be to the Value property of a Field object that links two tables. In that case, note that the Value property returns an object reference to the recordset:

Dim rsTop As ADODB.Recordset
Dim rsChild1 As ADODB.Recordset, rsChild2 As ADODB.Recordset
' Open the recordset.
DataEnvironment1.titleauthors
' Set rsTop to the topmost Command object.
Set rsTop = DataEnvironment1.rstitleauthors
' Set the first child object variable to the Value property of the
' Field named authors.
Set rsChild1 = rsX.Fields("authors").Value
' Set the second (grandchild) object variable.
' Note that we use the reference to the child recordset (rsChild1).
Set rsChild2 = rsChild1.Fields("titles").Value

You can also determine if the Field object's Value property returns a reference to a child recordset by checking if its Type property returns adChapter, as shown in the example below:

Dim f As Field, f2 As Field
Dim rs2 As Recordset
DataEnvironment1.Command1
Set rs2 = DataEnvironment1.rsCommand1.Fields("Command2").Value
For Each f In rs2.Fields
   Debug.Print f.Name
   If f.Type = adChapter Then
      For Each f2 In f.Value.Fields
         Debug.Print , f2.Name
      Next f2
   End If
Next f

After assigning references to the child recordsets to the object variables, you can then bind controls to the recordset objects.

Handling Events

After configuring a DataEnvironment object as a hierarchical recordset, it's logical to examine the code editor and check for the events for each of the objects in the hierarchy. However you will find that only the topmost Command object has a set of ADO Recordset object events (WillMove, WillChangeField, MoveComplete, etc.). The question then becomes how to program the events for objects which don't exist.

Instead of declaring the object variables within the procedure (as shown above), declare them in the Declarations section of the code editor using the Private (or Public) and WithEvents keywords. Consequently the events for the ADO Recordset object will become available for programming, as shown below:

Option Explicit
Private WithEvents rsChild1 As Recordset
Private WithEvents rsChild2 As Recordset

Private Sub BindRecordsets()
   DataEnvironment1.titleauthors ' Open recordset.
   Set rsTop = DataEnvironment1.rstitleauthors
   Set rsChild1 = rsX.Fields("authors").Value
   Set rsChild2 = rsChild1.Fields("titles").Value
   ' Bind the child recordsets to two DataGrid controls.
   Set DataGrid1.DataSource = rsChild1
   Set DataGrid1.DataSource = rsChild2
End Sub

Private rsChild1_WillMove(ByVal adReason As ADODB.EventReasonEnum, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
   Debug.Print pRecordset!OrderDate
End Sub