Visual Basic Concepts
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 run time. 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:
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
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
Dim DE as New MyDE
MyDE.Customers
Note This example assumes that there are no parameters associated with the 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
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.
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
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
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