Tame the Data Environment

by Sean Davidson

Reprinted with permission from Visual Basic Programmer's Journal, July 1999, Volume 9, Issue 7, Copyright 1999, Fawcette Technical Publications, Palo Alto, CA, USA. To subscribe, call 1-800-848-5523, 650-833-7100, visit www.vbpj.com, or visit The Development Exchange at www.devx.com.

What you need: Visual Basic 6.0 SQL Server 6.0 or higher SQL Server Pubs Database

To demonstrate DED techniques you can use in real-world applications, I'll show you how to build a small front-end application on top of the SQL Server Pubs database. With one or two exceptions, the techniques apply equally well over Microsoft Access or other ODBC-compliant databases. Although this application contains only two forms, my approach assumes thousands of records are in the database, so there's more code than you'd expect.

Test the Water First
As with anything new, DED has a number of bugs and behaviors ready to trip up the unwary developer. The impact of these behaviors, coupled with learning a new environment, has led some seasoned developers to convert their data environment projects back to more traditional development techniques. For this reason, don't leap into using DED—test the water first. A small pilot project, or even a few days of experimentation, will save you hours of frustration.

Open VB6 and select the Standard EXE template to create a new VB project. Add a data environment to the project by clicking on More ActiveX Designers | Data Environment from the Project menu. Change its name from the default DataEnvironment1 to deMain, then change the default connection name to conPubs in the standard Properties window.

Because larger projects contain a high number of objects within the data environment, it's important to set a naming standard early in your development. Avoid prefixing command objects with rs, as the data environment prefixes rs automatically to the recordset objects it creates from the command objects at run time.

Although you can edit the conPubs connection properties directly in the Properties window, right-clicking on conPubs and selecting Properties invokes a wizard-like dialog that makes this process easier. The wizard's first step requires you to choose the OLE DB driver to connect to the database. Although I've found the OLE DB driver for ODBC to be the most reliable, choose the OLE DB Provider for SQL Server for this example project. Which driver you choose isn't important, because changing the OLE DB driver later is a simple task and, in the case of SQL Server, has little effect on your application. Choosing the OLE provider for ODBC is also advantageous because the data source name configuration Control Panel utility is already written for you. If you choose a different driver, you'll likely have to write this functionality yourself.

Click on Next and enter the server name, user name, and password parameters for your SQL Server. You shouldn't use the Allow Password Saving option in any serious application. If you store this information within the executable, you must recompile and redistribute your application each time the database user name or password details change. Finally, set the database setting to the sample Pubs database, then click on the Test Connection button to ensure everything is working. When you have a successful connection, click on OK to save the details. Because you'll be changing this database, you might want to make a backup of it now using the SQL Enterprise Manager.

Now save the entire VB project. Some installations of the Visual Basic Environment (VBE) often crash shortly after you modify items in the data environment. This problem gets worse as your projects get larger, particularly if you bind controls inside a user control to the data environment. The VBE can also crash immediately after autosaving changes before running your project.

Open a Database Connection at Run Time
Now that you have a connection within the data environment, write some code to open the connection at run time. The best place for this type of initialization code is in the Sub Main() procedure. Rename the current project's default form1 to frmEmployee. Create a new module called Startup in your project and add this connection code:

Option Explicit

Sub Main()
    
   deMain.conPubs.Open _
      "Provider=SQLOLEDB.1;" & _
      "User ID=sa;PWD=;" & _
      "Initial Catalog=Pubs;" & _
      "Data Source=CATASTROPHE"
   frmEmployee.Show

End Sub

The code uses the Open method of the conPubs connection object to open the data environment connection at run time. This takes a connection string parameter, which you can construct by copying the conPubs ConnectionString property at design time and adding the user name and password parameters. In most applications, you'd prompt users for user name and password information with your own login form.

Sub Main() is also a good place to put splash screen and other database initialization code. Add error-trapping routines in case users can't establish a connection to the database to complete this sub. Select Project Properties from the Project menu and set the Startup object to Sub Main() to have this code execute instead of immediately loading the default form.

Next, open DED by double-clicking on it in the Project window. Then open the Data View window from the View menu to display the content and structure of the Pubs database. Unless you have a high-resolution monitor, viewing these windows at the same time requires some careful juggling. Expand the conPubs connection object in the Data View window so you can see the list of tables, views, and stored procedures.

Under the Employee table, a trigger named employee_insupd prevents data binding from working correctly in certain conditions. It raises an error and rolls back changes DED makes. Delete it by right-clicking on it and selecting Delete.

Now, drag and drop the employee, jobs, and pub_info tables from the database window into DED, then close the Data View window. Dragging and dropping table objects into the data environment creates corresponding table command objects there. Although this capability appears impressive, in most cases it isn't that useful. Real-world applications rarely use plain "table" command object types, as they provide few record-ordering and limitation mechanisms. In addition, they're network- and memory-intensive, and displaying more than 100 unordered records at a time confuses most users.

For your sample application, change these command objects to SQL statements. Right-click on the employee command object in DED, select Properties, and select the SQL Statement radio button on the General tab. Enter this SQL text into the SQL Statement box:

SELECT * FROM Employee WHERE 1=0

The 1=0 WHERE condition prevents this statement from returning any records. This is a great technique to use in search-type screens because it returns no records, but still allows access to information about the table structure. Next, choose the correct cursor location, type, and locking mechanism. This is particularly important in applications with a large number of users and/or records, and can significantly affect application performance. You can find these options on the Advanced tab of the command object Properties dialog.

Choose the Correct Cursor Type
A few basics for choosing the correct cursor type: Cursors can be located on either the server (server-side) or the user's PC (client-side). A server-side cursor reduces network traffic but increases the server's workload by maintaining "current record" information on the server. Conversely, client-side cursors are maintained on the user's PC, which means some record navigation operations might be faster.

To choose the correct option, you need to know what types of cursors are available. There are four main types: forward-only, keyset, dynamic, and static. For data environment development, you rarely use dynamic or forward-only cursors (see Table 1).

Locking type is the third setting for the cursor. Locking type determines what happens when the data environment attempts to make changes to a record. The default is read-only; this means record editing isn't supported. If you get strange messages when your application attempts to edit a record, you should check locking type first. To allow edits, set the locking type to Optimistic, which locks the record for editing by other users only while changes are being written to the database. The other options, Pessimistic and Batch Optimistic locking, are only important if you have many users attempting to edit the same records at the same time. Pessimistic locking locks the record from the moment the user begins to edit the record until the changes are written or discarded. Use Batch Optimistic locking with the ADO UpdateBatch method to perform batches of records at one time.

For the employee command object example, set the cursor to a server-side, keyset cursor with optimistic locking. As you'll only use them in drop-down combo boxes, set both the Pub_Info and Jobs command objects to client-side, static cursors with read-only locking. Change the Pub_Info table command object to a SQL statement with this SQL text:

SELECT * FROM Pub_Info ORDER BY Pub_id

Likewise, change the Jobs command object to a SQL statement with this SQL text that sorts the results alphabetically:

SELECT * FROM Jobs ORDER BY job_desc

Now close the DED and open the frmEmployee form. Right-click on the toolbox palette, select Components, and check the Microsoft DataGrid Control 6.0 and Microsoft DataListControls 6.0 libraries. Both libraries should have the text (OLEDB) beside them. Don't confuse them with the older VB5 controls shipped with VB6. Those controls require a data control to bind to and aren't compatible with the data environment.

Add a data grid control to the left side of the form, bind its DataSource property to deMain, and set its DataMember property to Employee. Create two columns using the data grid property pages and set them to display the fname and lname fields, respectively. I like to format the grid so it looks like a listbox, but to do this convincingly, you need a third-party grid control.

Make it Easy to Hide or Show Records
To create labels and textboxes for each field in the command object, place a frame control to the right of the data grid control, then drag and drop the employee command object from DED onto it. Placing the controls in a frame makes it easy to hide or show all fields at one time by altering the frame's Visible property. Set the Enabled property of all the controls in the frame to False. This prevents users from editing records in the sample application.

Delete the textboxes and labels for Pub_ID. Replace the Job_ID textbox with a DataCombo control bound to Job_ID, but with its RowSource set to deMain, RowMember property set to Jobs, and ListField set to job_desc. If these settings are correct, users can select an employee's job from the jobs table's drop-down list of jobs.

	Figure 1 Filter the List of Employees. Click here

Add a textbox, name it txtSearchName, and label it and position it above the data grid. Place a small command button to the right of this textbox and name it cmdSearch. Finally, add New and Delete command buttons below the data grid control (see Figure 1). With this form, users can enter an employee surname, click on the "..." button, and the list of employees is filtered to show only those with similar surnames. The beauty of this arrangement is it scales up to thousands of records, provided you set a minimum number of characters allowable for the surname criteria.

To make the form work this way, you must dynamically create and execute a SQL statement to filter the records based on a surname entered by the user. Use the SQL Like statement to achieve this. If you use DAO or a data control, this is all that's required.

Unfortunately, in this case, when the database is requeried, a new recordset containing the filtered records is created, but the data-bound controls are left bound to the original recordset. Without manually rebinding the data-bound controls to the new recordset, users can't see the new search results. In fact, when users click on the search button, it looks deceptively as though nothing has happened.

To work around this difference in behavior, I created a semi-generic Rebind method that iterates through the form controls collection and resets the DataSource property, effectively rebinding each data-bound control to the new recordset. Calling this method immediately after requerying the database allows users to see the requery results. (See Listing 1 for both the cmdSearch_Click event and the Rebind() method.)

I used the Replace function on the user criteria while constructing the SQL statement. This prevents SQL errors if a user enters an apostrophe character as part of the surname criteria. As with the double quote delimiter used with VB strings, replacing one quote with two fixes the problem. Although it's an easy error to prevent, it's often missed in the rush of the development process.

You can find another useful technique at the end of the Rebind method. This code hides the frame containing employee details if no records are returned—particularly useful if you don't want to give users the ability to create a new record except though a separate Add New type of window. Combining add and edit screens into one is confusing for users; avoid it whenever possible.

Create an Add New Form
DED's global nature lets you bind controls on two or more forms to the same recordset. When I first saw this capability, I was excited by the prospect of a significant reduction in code, but soon discovered a number of problems with binding forms.

Having two different sets of controls bound to the same recordset on different forms prevents the Update and CancelUpdate methods from working properly, returning only a cryptic "Canceled During Notification" message. This is because the two sets of controls see user edits at slightly different times, and this lag causes the second set of controls to sometimes edit record values in the middle of an update or event on the first form. The Data control supplied with VB6 is particularly susceptible to this problem.

One solution is to create another command object in the data environment and use a different command object for each form. However, a simpler way of getting around the problem is to use the rebinding behavior mentioned earlier. By closing and opening the recordset, and not rebinding before opening it, the Add New form gets a new Recordset object while the original form remains bound to the old recordset. When users add the record and close the new form, the main form can be rebound using the Rebind method and will see the newly added record. Here's the relevant code for the cmdNew_Click event on the frmEmployee form:

Private Sub cmdNew_Click()
   Dim TempEmployee_ID As String

   ' creates a new recordset
   deMain.rsEmployee.Close
   deMain.rsEmployee.Open
    
   TempEmployee_ID = frmNewEmployee.AddNew
    
      Call Rebind
   
   deMain.rsEmployee.MoveFirst
   deMain.rsEmployee.Find "Emp_ID='"& _
      TempEmployee_ID & "'"
 
End Sub

Add a new form to the project and name it frmNewEmployee. Cut and paste the data-bound controls and labels from within the frame in frmEmployee onto the new form. Delete the Emp_ID field, then set the Enabled property equal to True for each of the remaining controls. Add OK and Cancel command buttons to the bottom of the form, naming them cmdOK and cmdCancel, respectively.

Add the Microsoft Windows Common Controls-2 6.0 library to your toolbox palette through the Components window. Replace the Hire Date textbox with a DTPicker control and set its DataSource, DataMember, and DataField properties to deMain, employee, and hire date. The DTPicker control makes date selection easy and is preferable to using a textbox, but doesn't bind properly to fields containing Null values. For this reason, it's important to set default values when a new record is created.

Now add field and record validation, set new record defaults, and allow users to create and save or cancel new records (see Listing A). In Listing A, a public AddNew method displays the form modally, then returns the newly created employee's ID. This is a much cleaner and more object-oriented technique for calling a form with a specific purpose than using the Show method. It also eliminates the need for any global variables to pass newly created ID values to other parts of the program.

Reduce Validation Code
The AddNew method also contains code to set the MaxLength property of each data-bound textbox to match the maximum length of text the bound-to field can accept. It also sets default values for some of the database fields. This not only helps users, but it also reduces the amount of validation code you need.

Finally, add some basic "preventive" validation code to the form. The idea behind this: Don't allow users to click on the OK button until they've completed all required fields. The two main types of validation are field-level and record-level.

The KeyPress event is a good place to put field-level validation. In the txtjob_lvl_KeyPress event, I've used the InStr function as an easy way to allow only certain characters to be pressed. Changing which characters are allowed is a simple matter of changing the search string parameter of the InStr function.

Record-level validation enables and disables the OK button depending on whether the user has filled in all required fields. A good way to implement this type of checking is to create a ValidateRecord() method and call this from the KeyUp event of textboxes requiring validation. This leaves only basic checking for things such as duplicate keys required on the OK button.

Now look closely at the cmdOK_Click event, where you'd normally expect to find an Update method. I used Move(0) instead because if a user changes only DataCombo or DataList values, the Update method doesn't work. You probably won't see this bug in your example application, because the validation requires modification of other field values, but the bug results in data loss if not addressed.

This bug is difficult to detect, because in most cases users will move the cursor to another record, which saves the record as normal. Data loss only occurs if users close the recordset without moving off the edited record. Although there is no patch to date for this problem, Microsoft has created an article describing the Move(0) workaround.

One last task remains to complete your sample application. The employee table has a SQL server constraint that checks the format of the emp_id field before allowing a record to be saved correctly. To generate this ID, I've written a function whose code is placed within the data environment.

Collect All Code in One Place
DED is not only a designer, but it can also contain code in much the same way as a VB class module. This useful feature lets you collect all database-related code under one roof and access it as methods of the data environment (see Listing 2; add this code to deMain the same way you would add code to a class).

You can use this technique to wrap up calls to stored procedures. Although you can just drag and drop stored procedures into the data environment, wrapping stored procedures this way allows greater flexibility in how the stored procedure is called and how it returns values.

Now you've successfully built a small application with the data environment using techniques you can apply in the real world. Understanding the differences in the way the data environment operates, its limitations, and bug workarounds means you'll be better equipped if you decide to implement your next project with DED. The question that needs to be answered: whether the extra rebinding and workaround code required in data environment development is any quicker than traditional development techniques.


Sean Davidson is a consultant for Optimation New Zealand Ltd. He is a Microsoft Certified Systems Engineer, Microsoft Certified Solution Developer, Microsoft Sales Specialist, and avid coffee drinker. Reach him by e-mail at sean.davidson@clear.net.nz.