Visual Basic Concepts

Article
01/04/2007

Programmatically Accessing Objects in Your Data Environment Designer

Once Connection or Command objects are created, you can programmatically access and manipulate these ADO Command, Connection, and Recordset objects as though you had created them directly through ADO. This makes it possible for you to programmatically bind data-aware controls to a Command object or to fields within a Command object at . You can also dynamically set properties on ADO Connection or Recordset objects while they are closed, and set parameter values before data binding.

In addition, when you programmatically access data exposed by the Data Environment, you have greater control over execution options and can create multiple instances of a DataEnvironment object.

When a Command object is executed using a method or data binding, the following occurs:

ADO objects are created for the associated Connection and Command objects, and the resulting Recordset. Also, the design-time properties from the Data Environment's objects transfer to the ADO objects.
The ADO Connection opens.
The ADO Command opens.

You can also programmatically create instances of your Data Environment by declaring a variable within a procedure using the DIM statement. For example:

Dim DE as New MyDE

Programmatically Manipulating Objects within Your Data Environment Designer

Each Command object that you create is exposed programmatically as a method from the DataEnvironment object. Therefore, to execute the Command object, you can execute the method from the Data Environment.

In addition to exposing a method for each Command object, a Recordset object is also exposed, depending on the Recordset Returning property setting. Since it is not possible to surface both a method and a property of the same name, the Recordsets are exposed as the name of the Command preceded by "rs."

Upon creation, the Recordset object is closed. When you execute the method off the Data Environment, the Recordset opens. In addition, you can manipulate the Recordset before and after it is opened. For example, once the Recordset is opened, you can navigate to the next record using the MoveNext method.

All ADO Recordset methods and properties are available from the Recordset that is associated with the DataEnvironment object.

To programmatically access your Data Environment from a Visual Basic form

Drop a Command button on your form. The code attached to this button is used to step through each record within the Command object in your DataEnvironment object.
In the Click event of the button, add the following line of code in the Code****window.
```
Dim DE as New MyDE
    MyDE.Customers
```
Note This example assumes that there are no parameters associated with the Command object.
The records are available from the Recordset associated with the Data Environment. This has the same name as the Command object, preceded by "rs". Add the following code to move to the first record in the Customers Command object.
```
DE.rsCustomers.MoveFirst
```
At this point, all methods associated with an ADO Recordset are available for DE.rsCustomers, such as the methods used to add, update, delete, and step through records. For example, the following code uses ADO methods to loop through all records in the recordset.
```
Do While DE.rsCustomers.EOF = False
    Debug.Print DE.rsCustomers.Fields(1).Value
    DE.rsCustomers.MoveNext
Loop
```

Programmatically Executing Objects

The following are examples of executing Command objects using the method and Recordset objects associated with the DataEnvironment object. The examples use a Command object that is based on a recordset-returning stored procedure with two input parameters.

Executing a Command Object with Multiple Parameters

If the Command object has multiple parameters, and you want to pass selected parameters, you must manually set the value of the parameters using the Parameters collection. Then, you must use the Open method from the Command object. For example, the Command object "InsertCustomer" contains the parameter's identification, first name, and nickname. To execute this method and include all parameters, you can use the following method:

MyDE.InsertCustomer "34","Fred","Freddy"

However, to only include the identification and first name, you would use the following code:

MyDE.Commands("InsertCustomer").Parameters("ID").value = "34"
MyDE.Commands("InsertCustomer").Parameters("Name").value = "Fred
MyDE.Commands("InsertCustomer").Execute

Executing a Command Object with Parameters

The following example shows recordset-returning stored procedures with two Parameter objects.

Dim MyDE As DataEnvironment1
Dim nRecords As Integer
Dim nSum As Long

MyDE.SalesTotalByCityState "Seattle","WA"
MyDE.RSSalesByCityState.MoveFirst
For nRecords = 1 To MyDE.RSSalesByCityState.RecordCount
   nSum = nSum + MyDE.RSSalesByCityState.Fields("Invoice_Amt")
   MyDE.RSSalesByCityState.MoveNext
Next nRecords
Debug.Print nSum

Following is a more complicated example that shows a Command object that is based on a stored procedure that returns both a Recordset object and a return value. The example also contains an input and an output Parameter object.

Dim MyDE As DataEnvironment1
Dim sOutStatus As String, nNumRecords As Long
nNumRecords = MyDE.OrdersByEmployee("SMITH", sOutStatus)
If sOutStatus = "Succeeded" Then
   MyDE.OrdersByEmployee.MoveFirst
   While Not MyDE.RSOrdersByEmployee.EOF
      Debug.Print MyDE.RSOrdersByEmployee.Fields("OrderDate")
      MyDE.RSOrdersByEmployee.MoveNext
   End
End If

Executing a Non-Recordset Returning Command Object

The following example shows a non-recordset-returning stored procedure with a return value and Parameter object. A common scenario is to use stored procedures to insert, update and delete records. These stored procedures do not return Recordset objects, but do use input and output Parameter objects.

Dim MyDE As New DataEnvironment1
Dim sOutStatus As String, nNumRecordsAffected As Long
nNumRecords = MyDE.DeleteEmployee("34", sOutStatus)
If sOutStatus = "Succeeded" Then
   Debug.Print nNumRecords + " employee(s) were deleted."
Else
   Debug.Print "Delete was not successful."
End If

Share via