Using Data Environment Events

How to trap actions at the data level

The new Data Environment in Visual Basic, a wrapper around ADO, exposes many ADO features in a graphical manner, including the graphical Data Environment Designer and ADO events. ADO 2.0 includes events that let you isolate code pertaining to particular actions that occur within ADO. Isolating database code in events lets you trap the occurrence of actions at the data level—right before or after the occurrence hits the database.

ADO 2.0 allows two types of events. ConnectionEvent events execute (fire) when a connection prepares to start, connects, or disconnects, or a command prepares to execute or executes.

The second type of ADO 2.0 event is the RecordsetEvent. Such events execute as the cursor moves through a recordset, as fields in the recordset are changed or are about to be changed, as a row in the recordset is changed, or as a fetch operation status change occurs.

The Data Environment also has two events. The Initialize event fires when the Data Environment starts, and the Terminate event fires when the Data Environment terminates. In my tests, the Terminate event never fired.

The events are tied to the Data Environment, connections, and commands you create in the Data Environment Designer. You can access the code for an object's events by double-clicking the connection or command. This action opens the code editor window for that object. Then you can select an event from the event list and add your code.

Using the Events

To demonstrate using events, I used the CustomerInfo.vbp project, which I introduced in my April column. This application lets users view customer demographics by selecting a customer from a list. I modified the project so that the user selects the same list to update the customer records.

First, let's look at the application's modifications to let users update records. To let users update the database, you first create or modify a data command. I used the cmdCustomer-ByID data command from last month's code and changed its Lock Type to Optimistic for updates to the recordset. You can make this change to a data command by opening its properties in the Data Environment Designer and changing the Lock Type on the Advanced property page. After you've changed this setting, the recordset is updateable.

Next, add the UpdateCustomer function to Module 1, which you see in Listing 1, page 68. This function updates the records. The code checks each parameter to ensure the incoming data is not blank or null. This check forces some error checking on incoming parameters but eliminates actions such as forcing the CustomerID to uppercase. At this point, I used one of the ADO events to test for this condition.

Next, I added an event handler for the Will-ChangeField event. This event fires whenever an action attempts to change a field in the recordset. This point in the code is a handy place to insert code that needs to check fields for particular values or to perform other field-level operations.

My first attempt at this task failed miserably. I decided to add code in the event handler that would force the CustomerID to uppercase. This code is simple and easy to implement. The problem is that when the code executes each time I change the value of the field to uppercase, it fires the WillChangeField event again. This phenomenon is known as cascading events and occurs when one action triggers recursive events.

Listing 2 shows the code I finally settled on. When the WillChangeField event fires, it passes several arguments into the event. cFields is of type Long and contains the number of fields to be changed. Fields is a variant array. Each element of the array contains a field object for a field in the recordset. You can use cFields to iterate through all the fields in Fields (if you're changing more than one field). The adStatus parameter contains the current status of the operation. This parameter will contain a value of adStatusOK (1) if the operation is successful. The last parameter contains a reference to the recordset containing the field that will change.

The Set statement creates a reference to the Fields object in the first element of Fields(0). This is the field we want because we are changing only one field at a time. By putting a handler in this event, you can centralize field handling to check for any field in the recordset that could be changed.

After you've obtained the reference to the Field object, you can work with the field. The Select statement checks the field's Name property. If the current field is the CustomerID and the new value is not uppercase, the following statement

adStatus = adStatusCancel

sets adStatus to 4, which cancels the operation. This action triggers a run-time error that the code that requested the change to the record set must trap. If the WillChangeField event cancels the operation, the err.description returned will be: "The change was canceled during notification; no columns are changed." and the value in Err.number will be -2147217842.

One way to trap for this error is to add an error check just after the statement that sets the recordset property in the UpdateCustomer function, as in Listing 3. In this example, you can use the UCase function in the first If statement and not worry about it in the second. The purpose is to illustrate how you can trap for errors, not to use UCase.

You can tweak this code more. You can move the If statement that traps for 0 length or Null values into the WillChangeField event. This move has two advantages—it cleans up the code that sets the fields, and it centralizes the code to check for proper values in the field. This latter advantage becomes more important when you reuse the same data command in several places. You have to write the code to check the field length and null status only once, and you will not have to pay later for forgetting to write it in a new routine. If you make this change to the WillChangeField, the code looks like Listing 4.

Another area that often provides a challenge for developers concerns database connections. How do you handle usernames and passwords, and where do you put the code? You can solve this problem by using the events for a data connection. The WillConnect event occurs when a connection is about to execute, but before it executes. If no code is in this event procedure, the connection proceeds. This approach gives you a chance to change the parameters for the connection at runtime.

The WillConnect event has six parameters: ConnectionString for connection information, UserID for the current userid, Password for the current password, Options to identify the type of connection (same as the CommandType property for an ADO command or recordset), adStatus for the status value (same as other Will events status), and pConnection for the connection object.

You can modify the CustomerInfo project to take advantage of the WillConnect event. First, add a form to the project and name it frmLogin. Configure the form with two text boxes and two buttons. The first text box is named txtLoginName, and the second textbox is txtPassword. The buttons are cmdLogin and cmdCancel. Next, add this code in the Login buttons click event:

Private Sub cmdLogin_Click()   Me.HideEnd Sub

Now, open the event code for the conNorthwind connection object, select the WillConnect event, and add the code in Listing 5. This code displays frmLogin as a modal form, then retrieves the login name and password from the form. Next, the code unloads frmLogin. Then the Select Case statement determines the SQL Server login name and password to use and sets the UserID and Password parameters accordingly.

Options

This code focuses on using events in a two-tier client/server application. You can use the same approach with the events in an n-tier application. The only changes you might make are to remove the user interface portions of the code (the forms) and use the events to notify the calling application instead of the user. Next month, I will explore more features of the Data Environment.