Developing Applications with the Microsoft Visual Basic 6.0 Data Object Wizard

Article
07/12/2006

Microsoft Corporation

December 1998

The Visual Basic® Data Object Wizard (DOW) is a Visual Basic Add In Wizard. The DOW significantly reduces development time of data components.

You can use the DOW to create data Classes and User Controls that display and manipulate data. You can add these components to Visual Basic forms to create a data application.

Before you can use the DOW, you must create one or more commands with the Visual Basic DataEnvironment Designer (DED). One DED command specifies the source of data. Other optional DED commands specify lookup and data modification commands.

A DOW data Class contains a RecordSet for each DED data source, lookup, and data modification command. Data consumers can either bind to the Class or use manual techniques to access the data from the Class.

A DOW User Control can be a DataGrid, Single Record, ComboBox, or ListBox. These User Controls bind to DOW data Classes.

Several complex design issues are addressed by the DOW, allowing it to create data components that are useful in serious enterprise client/server applications.

Design Issues
DOW Commands
The DataEnvironment Designer
Creating a Data Class
Creating Data-Bound User Controls
Using DOW Objects in an Application
Appendix

Design Issues

Stored Procedures

A generally accepted standard for client/server development is to have all database access accomplished through a secure application programming interface (API). An example of this is the use of Stored Procedures with SQL Server™. Most serious applications need the security and performance enhancements offered by this secure API.

The DOW allows separate DED commands for "Select" (main data source), "Lookup," "Update," "Insert," and "Delete" operations.

Update Timing

Few serious client/server applications allow the user instant control over updates. Generally, the user is allowed to make local changes, then access a control (often a "Save" button) to update the Data Source.

Many binding mechanisms change the Data Source immediately when the user leaves a cell or record. (Some binding mechanisms have a "batch" update mode that allows the developer to control the moment of update.)

DOW components use either "batch" or "immediate" update mode to allow the application user to freely make local changes, then either "Cancel" or "Save" the changes to the Data Source.

Lookup (Foreign Key) Relationships

Most code generators have no provision for displaying meaningful text for Lookup options. Usually, only the (often) cryptic Lookup values are shown.

For example, a PersonID, like "28282," might be typically displayed instead of an actual name ("Joe Smith") for a Person as a Lookup in a table.

These relationships are often defined by multiple fields of different data types (not just one field of integer datatype).

For each Lookup relationship, the DOW replaces multiple fields of multiple data types with one field, displaying a validation list of meaningful text descriptions (generally in ComboBoxes).

Primary Key Generation

There are so many schemes for generating Primary Keys (identity constraints, max(value) +1, manually generated schemes, and so on), that it would be impractical to attempt to generate code for them.

The DOW will rely on either (1) the User's "Insert" command to automatically generate the Primary Key values(s) or (2) allowing direct user input of primary key values.

If the DED "Insert" command has Input/Output parameter(s) that feed back system generated Primary Key values to the DED command, the DOW data Class stores these values, which allows immediate Updates or Deletes of the newly inserted Record.

Null Values

Many real client/server applications require that Null data be clearly identified to users. In many cases, users need to be able to see a difference between values that are Null and values that are blanks (zero length strings), for example.

Null values are often displayed as meaningful text, such as "(None)," "(Null)," or "(Nothing)," and so on.

The DOW displays meaningful text for a null value.

Command Parameters

Supplying command parameter values should not be an afterthought in the design process. Most commands in serious applications have parameters. These values need to be provided to the commands before they can be executed.

The DOW creates a data Class Property for each Parameter of each DOW Select and Lookup command. These Properties can either be set on the Property Sheet of the DOW User Control at design time or manually by code at run time.

DOW Commands

Before you use the DOW, you need to use the DED to create one Select command, the main data source. Then, depending on your application requirements, you will create Lookup commands and Insert, Update, and Delete commands.

One Select Command

You must have one Select command. The Select command returns the basic data you want to access. Many applications will require that the Select command have Parameters. The Select command will also need to have one or more fields as its Primary Key.

Zero or More Lookup Commands

If your Select command has fields whose values are obtained from a finite set (domain), then you can create a Lookup command that will retrieve this set. Data from this Lookup command will then be displayed in a DOW User Control's ComboBox so that the user can only choose valid values.

**Note **The Lookup command display field must be of String data type, since it will be displayed as text in ComboBoxes and the Lookup column in a DataGrid.

The DOW maps Lookup Fields in the Select command to Primary Key fields in the Lookup command(s).

Zero or One Insert Command

If you want to be able to add records to the set defined by the Select command, create an Insert command. The Insert command must have Parameters for each Not Null field defined by the Select command.

The DOW maps Fields in the Select command to Parameters in the Insert command.

If the Primary Key value(s) are system-generated by the Insert command and the Insert command returns these values through input/output Parameter(s), the data Class generated by the DOW will store these values and associate them with the newly Inserted record. Updates and Deletes can then be immediately performed using these values.

Zero or One Update Command

If you want to be able to Edit records, create an Update command.

The DOW maps Fields in the Select command to Parameters in the Update command.

The DOW allows you to use the same command for Insert and Update if that command provides for both functionalities.

Zero or One Delete Command

If you want to be able to Delete records, create a Delete command.

The DOW maps Primary Key Fields in the Select command to Parameters in the Delete command.

The DataEnvironment Designer

Before you can use the DOW, you must have created commands with the DataEnvironment Designer (DED).

Your VB project can have only one DataEnvironment Designer.

Figure 1. DataEnvironment Designer

Creating a Data Class

Select Data Environment Command

The DOW reads your DED and displays all of its commands.

Choose the DED command that will be your primary source of data. This is the DOW "Select" command.

This command must have Primary Key field(s) included as well as all Not Null fields if you want to perform Inserts and Updates.

This command must have Primary Key fields(s) included if you want to perform Deletes.

Figure 2. Select Data Environment command

Define Class Field Information

Since it is not always possible to get field nullability and Primary Key information from all types of commands, that is, stored procedures, the user must supply this information.

If a field is a Primary Key, it is not necessarily a Not Null field from the DOW's perspective. An example of this is a Select command that provides system-generated Primary Key values through input/output parameter(s). In this case, set the "Nullable?" column to "Yes" so that the DOW will not require a value be entered in this field for Inserts and Updates.

Figure 3. Define class field information

Define Lookup Table Information

This step defines Lookup relationships for the Select command.

Each relationship has one or more (Foreign Key) fields in the Select command. These fields need to be mapped to the Lookup command's Primary Key fields.

For example, in the sample entity relationship diagram (ERD) in the appendix, there are three Lookup relationships:

Person CountyID and StateCode map to County CountyID and StateCode, respectively.
Person CompanyCarID maps to CompanyCar CompanyCarID.
Person ManagerID maps to Person PersonID.

For each relationship, choose a Source (Select) field, Lookup command, Lookup field to display, and the Primary Key fields in the Lookup command that will be mapped in the next step.

After all information has been entered for the relationship, press the "Add" button to include the relationship in the "Selected lookup commands."

The screen below shows the relationship information entered for the first relationship:

Figure 4. Define the first Lookup relationship

Information for the second relationship:

Figure 5. Define the second Lookup relationship

Information for the third relationship:

Figure 6. Define the third Lookup relationship

Map Lookup Fields

Since there can be more than one field in each Lookup relationship, there is a "Map Lookup Fields" screen for each relationship.

The DOW makes an attempt to match Select command field names with Lookup command field names, displaying these matches in a grid. If the mapping is incorrect or other mapping rows need to be added, click the appropriate cell and choose the correct Select field name from the "Source Command Fields" ComboBox and the correct Lookup field name from the "Lookup Command Fields" ComboBox.

If a mapping row exists that should not be there, choose "(None)" from the ComboBox for both the Select and Lookup field names.

Figure 7. Map Lookup Fields

Figure 8. Map Lookup Fields

The default mapping for the third relationship is not correct and needs to be modified:

Figure 9. Incorrect Lookup mapping

Figure 10. Correcting Lookup mapping

Define and Map Insert Data Command

If you want to be able to add records to your data source, you will need to specify the DED Insert command and mapping between the DOW Select Command fields and the DED Insert command parameters. The DOW attempts to do this for you, mapping fields to parameters with the same names.

This screen is functionally similar to the "Map Lookup Fields" screen. If the default mapping is not correct, you can change it by clicking on the incorrect cell and selecting the correct values from the ComboBoxes.

Figure 11. Define and Map Insert Data Command

Define and Map Update Data Command

This is similar to the "Map Insert Data Command" screen, except that if you are using the same command, you can just check "Use Insert command for Update."

Figure 12. Define and Map Update Data Command

If you want to be able to delete records from your data source, you will need to specify the DED Delete command and mapping between the DOW Select Command fields and the DED Delete command parameters. The DOW attempts to do this for you, mapping fields to parameters with the same names.

Figure 13. Define and Map Delete Data Command

Complete the process by choosing a name for your DOW data Class. The DOW will notify you if the Class already exists, in which case you can choose a new name or overwrite the existing Class.

**Warning **You will lose any unsaved work on an existing Class if you choose to overwrite it.

Figure 14. Naming the data Class

Creating Data-Bound User Controls

To display and manipulate data in a DOW data Class, you can generate a DataGrid, Single Record, ComboBox, or ListBox User Control. Select command lookup fields are replaced with a ComboBox displaying the Lookup "display" field.

A DOW DataGrid User Control displays several rows of data in grid format.

A Single Record User Control displays one row of data in TextBoxes, ComboBoxes, and CheckBoxes, depending on the data type.

Combo/ListBox User Controls display data in either a ComboBox or ListBox, respectively.

A DOW-generated DataGrid User Control:

Figure 15. DataGrid User Control

A DOW-generated Single Record User Control:

Figure 16. Single Record User Control

A DOW-generated ComboBox User Control:

Figure 17. ComboBox User Control

A DOW-generated ListBox User Control:

Figure 18. ListBox User Control

DataGrid

Select the data Class to use

Choose a DOW-created data Class as a source of data for your DataGrid. This screen only shows Classes created by the DOW.

Figure 19. Select data Class

Select User Control type

Select the type of User control you would like to create, in this case a DataGrid.

Figure 20. Select User Control type

Map Class Properties to a Control type

You can now choose the type of control to display data for each field:

(None)—Don't show the field in the User Control.
ComboBox—If the field is a Lookup field in the Select command, you may choose to show the "display" field of the Lookup command in this field, instead of the actual Lookup fields. In the example below, even though "StateCode" and "CountyID" are listed, they will not be shown in the DataGrid. Instead, they will be replaced by "CountyDescr" from the GetCounty command, which will be shown in a ComboBox when the user clicks on a DataGrid cell in that column.
TextBox—This shows the value of the field as it exists in the Select command field.

If the field is a Primary Key, the DOW defaults to "(None)," assuming that in most applications, Primary Key values are system generated and not supplied by the user. If the field is a Lookup field, a ComboBox is shown by default. ComboBoxes are only intended for Lookup fields and are therefore ignored if the field is a non-Lookup field, in which case a TextBox is displayed.

Figure 21. Map Class Properties to a Control type

Enter User Control name

Complete the process by choosing a name for your DataGrid User Control. The DOW will notify you if the User Control already exists, in which case you can choose a new name or overwrite the existing User Control.

**Warning **You will lose any unsaved work on an existing User Control if you choose to overwrite it.

Figure 22. Enter User Control Name

Single Record

The Single Record User Control generation is the same as the DataGrid User Control.

Combo/ListBox

The Combo/ListBox User Controls are generated in the same way as the DataGrid User Control, except that all Lookup , Insert, Update, and Delete command information in the data Class is ignored, since Lookup and data manipulation operations do not apply to these two User Controls.

The field displayed in the Combo/ListBox controls is the first field in the Select command that is displayed with a TextBox. If you would like to have another field displayed in the ComboBox, see the comments in the "Public Function Init…" in the generated Combo/ListBox code.

Using DOW Objects in an Application

DataEnvironment Commands

The Select command must have a Primary Key field if Update and Delete operations are to be performed with DOW generated objects.

Lookup commands typically consist of one or more Primary Key fields and a Description field. The Lookup command can be based on a single table or it might be a join of several parent tables, with the Description field being concatenated from description fields in the parent tables.

**Note **The Description field must be of String data type.

There must be one Lookup (Foreign Key) field in the Select command for each Primary Key field in the Lookup command. These field names do not need to be the same, the DOW FK Mapping screens will allow you to map fields that have different names.

If the system is generating Primary Key values for Inserted records, create Input/Output command parameters for these Primary Keys. Have the Insert command set these parameters with the system generated values. The DOW data Class will retrieve these values so that immediate Updates and Inserts can be performed on newly Inserted records.

Data Class

Events

Certain methods raise the ClassError event. The DataGrid and Single Record User Controls both sink this event and display the error description from this event.

Event rsMoveComplete is useful in a DataGrid/SingleRecord Master/Detail scenario. When the user changes record focus in the DataGrid, the SingleRecord can refresh its data based on that record by sinking this event.

DeleteRecordComplete notifies the User Control that the record has been successfully completed.

rsUpdateEvent fires when the method rsUpdate is complete.

Data binding

There are two kinds of data binding—simple bound and complex bound.

Simple binding can be applied to controls that are not "binding aware." Simple binding requires the BindingCollection object. Simple binding is really a "property setter"—it sets a property of another control to a value of a field in a RecordSet. For example, you can set the Text Property of a TextBox to the value of a particular field in a RecordSet. The TextBoxes and CheckBoxes of the DOW SingleRecord User Control use simple binding.

Complex binding refers to data controls that are "binding aware." These controls have functionality similar to the BindingCollection built into the control. The DOW DataGrid is "complex" bound to the Select RecordSet in the data Class.

RecordSets

Lookup command "display" fields do not exist in the Select command. In order for them to be shown in a bound DataGrid, these fields must be "added" to the Select command RecordSet.

Therefore, for any Select command that has Lookup relationships, a new RecordSet is created that includes a Lookup "display" field for each relationship. Select commands without Lookup relationships use the DED Select command directly.

The Select RecordSet is disconnected from its data source either because it is a locally created RecordSet or the DED Select command DataSource is set to Nothing. This allows substituting other DED commands for Update, Insert and Delete operations.

Methods

The Init… methods return the data Class object and have an optional parameter which is a data Class object. Therefore, the same instance of the data Class can be used by more than one User Control. An example of this is a Master Detail Form, where the Master could be a DataGrid which contains a few fields of a RecordSet. The Detail could be a SingleRecord User Control which contains all the fields of the same RecordSet. In this case the DataGrid User Control would instantiate the data Class (returned from the Init… method), then pass it as a parameter of the SingleRecord Init method).

System-generated Primary Key values

If your Insert command generates Primary Key values from the system and this command has parameters for each of these Primary Keys, then these values are returned to the data Class. This allows immediate Updates or Deletes following an Insert.

In this case, set the "nullability" of the Primary Key fields to "Yes," so that the DOW generated data class does not require value(s) for Primary Key fields.

User Controls

Auto Initializing

Auto Initializing allows the generated User Controls to run and be filled with data by only (1) placing the User Control on a Form and (2) setting its Select command parameter Properties.

By default, the Property ManualInitialize is False. If the Select command parameter Properties on the Property Sheet (for the User Control on the Form) have been specified, the User Control will load with data upon initialization.

If the parameter Property values are missing or of the wrong data type, and so on, the Form will load but the User Control will have no data.

Manual Initializing

ManualInitialize = True is the normal mode of initialization in most applications.

If ManualInitialize = True, then the values for the parameter Properties are set in Code, usually in the form that contains the User Control. The Init… method is run (generally from the Form) to Initialize the User Control and load data.

If ManualInitialize = False, then parameter Property values are read from the Property Sheet for the User Control.

Appendix

Class

Properties

Select Command Field Properties: For each field in the Select command, there are corresponding Property Lets and Gets that allow reading and writing to the current Select command record. These properties have the same name as the field in the Select command.

Select Command Parameter Properties: For each parameter in the Select command, there are corresponding Properties that allow setting the Select command parameter values. These Properties have names that are concatenations of the Select command name and the Select parameter name.

Lookup Commands Parameter Properties: For each parameter in each Lookup command, there are corresponding Properties that allow setting the Lookup command parameter values. These Properties have names that are concatenations of the Lookup command name and the Lookup parameter name.

Lookup Commands RecordSet Properties: For each Lookup command, there is a corresponding Public RecordSet Property. These RecordSets are named with the Lookup command name preceded by "rs."

AbsolutePosition As Long: Returns the Select RecordSet.AbsolutePosition.

BOF As Boolean: Returns the Select RecordSet.BOF value.

EOF As Boolean: Returns the Select RecordSet.EOF value.

SaveMode As EnumSaveMode:

AdImmediate—Sets LockType to Optimistic. Automatically saves when moving to a new current record in the RecordSet.
AdBatch—Sets LockType to BatchOptimistic. Saves current record manually with "Update" method. Saves all dirty records manually with "UpdateBatch" method.

Methods

Sub AddRecord(): Adds a blank record to the Select RecordSet.

Sub Delete(): Deletes the current Select RecordSet record and runs the DED Delete command.

Sub Move(lRows As Long): The record lRows from the current Select record becomes the current record.

Sub MoveFirst(): The first record in the Select RecordSet becomes the current record.

Sub MoveLast(): The last record in the Select RecordSet becomes the current record.

Sub MoveNext(): The next record in the Select RecordSet becomes the current record.

Sub MovePrevious(): The previous record in the Select RecordSet becomes the current record.

Function MoveToPk(Value1,…,ValueN) As Boolean: Moves to the record in the Select RecordSet having the Primary Key values Value1,…,ValueN.

Sub rsUpdate(vFieldName): Updates field vFieldName with the correct "display" Lookup command field value for the Select command Lookup fields.

Sub Update(): Saves the all dirty records (SaveMode = adBatch), running the DED Update command.

Sub UpdateBatch(): Saves the current record (SaveMode = adBatch), running the DED update command.

Function ValidateData() As Boolean: Validates the data values in the current Record.

Events

ClassError(sProcedureName As String, oErr As ErrObject): Occurs for certain application errors. Returns the Procedure name, sProcedureName, and the Err object.

DeleteRecordComplete(): Occurs after a record has been deleted from the Select RecordSet.

rsMoveComplete(): Occurs after the Select RecordSet has completed a move to a new current record.

rsUpdateEvent(vFieldName): Occurs after the Lookup display fields have been updated for field vFieldName.

User Controls: DataGrid

Properties

Select Command Parameter Properties: For each parameter in the Select command, there are corresponding Properties that allow setting the data Class Select command parameter values. These Properties have names that are concatenations of the Select command name and the Select parameter name.

Lookup Commands Parameter Properties: For each parameter in each Lookup command, there are corresponding Properties that allow setting the data Class Lookup command parameter values. These Properties have names that are concatenations of the Lookup command name and the Lookup parameter name.

GridEditable As Boolean: Set to True to enable data modifications to the DataGrid and enabling the Lookup command ComboBoxes on the Lookup "display" fields.

ManualInitialize As Boolean: Set to False to enable filling the DataGrid with data when the User Control is initialized. This allows the control to be fully operational after the DataGrid User Control is (1) generated, (2) any Command property parameter values are entered on the Property Sheet, and (3) the User Control is placed on a Form.

When set to True, parameter values are not read from the PropertySheet. Select and Lookup command parameter property values are set from code in the Form containing the User Control.

SaveMode As EnumSaveMode:

AdImmediate—Sets data Class LockType to Optimistic. Automatically saves when moving to a new current record in the RecordSet.
AdBatch—Sets data Class LockType to BatchOptimistic. Saves current record manually with "Update" method. Saves all dirty records manually with "UpdateBatch" method.

Methods

Function(s) UpdateFK{FKCommand}(nSurrogateKey As Integer): For each Lookup command, sets the Lookup fields for the current {FKCommand} record to the Lookup RecordSet record fields having a Surrogate Key of nSurrogateKey.

Function Init({DataClass}) As Object: Initializes the DataGrid by setting command parameters, setting the DataSource of the DataGrid to the data Class, setting data updatability according to GridEditable, hiding Lookup fields, and so on.

DataClass is an optional parameter. If passed, this instance of the data Class is used as the DataGrid DataSource. Otherwise, a new instance of the data Class is created.

The Object returned is the data Class used as the DataGrid DataSource.

Update(): Runs data Class method, Update, saving the all dirty records (SaveMode = adBatch), using the DED Update command.

UpdateBatch(): Runs the data Class method, UpdateBatch, Saving the current record (SaveMode = adBatch), using the DED update command.

Events

MoveComplete(oDataSource As Object): Sinks the data Class rsMove_Complete event. Returns the DataSource data Class.

User Controls: Single Record

Properties

ManualInitialize As Boolean: Set to False to enable filling the SingleRecord User Control with data when the User Control is initialized. This allows the control to be fully operational after the SingleRecord User Control is (1) generated, (2) any Command parameter property values are entered on the Property Sheet, and (3) the User Control is placed on a Form.

When set to True, parameter values are not read from the PropertySheet. Select and Lookup command parameter property values are set from code in the Form containing the User Control.

SaveMode As EnumSaveMode:

AdImmediate—Sets data Class LockType to Optimistic. Automatically saves when moving to a new current record in the RecordSet.
AdBatch—Sets data Class LockType to BatchOptimistic. Saves current record manually with "Update" method. Saves all dirty records manually with "UpdateBatch" method.

Methods

AddRecord(): Runs the data Class AddRecord method.

Delete(): Runs the data Class Delete method.

Function Init({DataClass}) As Object: Initializes the SingleRecord User Control by setting command parameters, setting the DataSource of the User Control to the data Class, hiding Lookup fields, and so on.

DataClass is an optional parameter. If passed, this instance of the data Class is used as the User Control's DataSource. Otherwise, a new instance of the data Class is created.

The Object returned is the data Class used as the SingleRecord DataSource.

Sub Move(lRows As Long): Runs the data Class Move method—the record lRows from the current Select record becomes the current record.

Sub MoveFirst(): Runs the data Class MoveFirst method—the first record in the Select RecordSet becomes the current record.

Sub MoveLast(): Runs the data Class MoveLast method—the last record in the Select RecordSet becomes the current record.

Sub MoveNext(): Runs the data Class MoveNext method—the next record in the Select RecordSet becomes the current record.

Sub MovePrevious(): Runs the data Class MovePrevious method—the previous record in the Select RecordSet becomes the current record.

Function MoveToPk(Value1,…,ValueN) As Boolean: Runs the data Class MoveToPk method—moves to the record in the Select RecordSet having the Primary Key values Value1,…,ValueN.

Update(): Runs data Class method, Update, saving the all dirty records (SaveMode = adBatch) using the DED Update command.

UpdateBatch(): Runs the data Class method, UpdateBatch, saving the current record (SaveMode = adBatch) using the DED update command.

Events

MoveComplete(oDataSource As Object): Sinks the data Class rsMove_Complete event. Returns the SingleRecord data Class.

User Controls: Combo/ListBox

Properties

ManualInitialize As Boolean: Set to False to enable filling the Combo/ListBox User Control with data when the User Control is initialized. This allows the control to be fully operational after the Combo/ListBox User Control is (1) generated, (2) any Command parameter property values are entered on the Property Sheet, and (3) the User Control is placed on a Form.

When set to True, parameter values are not read from the PropertySheet. Select and Lookup command parameter property values are set from code in the Form containing the User Control.

NoneFirst As Boolean: If true, loads "(None)" in the first row of the Combo/ListBox. This is used in relationships that allow nulls in the Foreign Key side of the relationship.

Methods

Function Init({DataClass}) As Object: Initializes the Combo/ListBox User Control by setting command parameters, setting the DataSource of the User Control to the data Class, and so on.

DataClass is an optional parameter. If passed, this instance of the data Class is used as the User Control's DataSource. Otherwise, a new instance of the data Class is created.

The Object returned is the data Class used as the Combo/ListBox DataSource.

Events

Combo/ListBoxClick(PKVal1,…,PKValN)

Occurs when a user clicks on a Combo/ListBox row. This event supplies Primary Key values for the record, PKVal1 through PkValN.

clsDow

When a DOW data Class is generated, if the class clsDow is not in the VB Project, it is added. clsDow contains the definition for EnumSaveMode.

Typical Example

A Sample Entity Relationship Diagram (ERD)

Below is a sample ERD that illustrates building a DOW data Class and User Controls.

Figure 23. Entity Relationship Diagram

Sample Command Stored Procedures

GetPerson (Select Command):

create procedure GetPerson5
  @PersonID int=0
as
select p.PersonID,p.FirstName,p.LastName,p.StateCode,p.CountyID,
       p.CompanyCarID,
       p.HireDate,p.ManagerID,p.Salary,p.LaborClassCode,MailCheck,Comment
from Person p
where p.PersonID>@PersonID
order by p.LastName,p.FirstName

GetCounty (Lookup Command):

create procedure GetCounty5
  @StateCode char(2)="  ",@CountyID int=0
as
  select c.StateCode,c.CountyID,s.Descr+' '+c.Descr CountyDescr
    from County c,State s
    where c.StateCode=s.StateCode
      and c.StateCode>@StateCode
      and c.CountyID>@CountyID
    order by CountyDescr

GetCompanyCar (Lookup Command):

create procedure GetCompanyCar5
  @CompanyCarID int=0
as
  select CompanyCarID,Descr CompanyCarDescr
    from CompanyCar
    where CompanyCarID>@CompanyCarID
    order by CompanyCarDescr

GetManager (Lookup Command):

create procedure GetManager5
  @ManagerID int=0
as
  select PersonID,FirstName+' '+LastName ManagerName
    from Person
    where PersonID>@ManagerID
    order by LastName,FirstName

PutPerson (Insert, Update Command):

create procedure PutPerson5
  @PersonID int out,
  @FirstName varchar(30),
  @LastName varchar(30),
  @StateCode char(2),
  @CountyID int,
  @CompanyCarID int,
  @HireDate datetime,
  @ManagerID int,
  @Salary money,
  @LaborClassCode decimal(3,3),
  @MailCheck bit=1,
  @Comment text
as

if not exists (select *
             from Person
             where PersonID=@PersonID)
  begin
    select @personID=isnull(max(personID),0)+1
      from Person

    /* insert Person */
    insert Person(PersonID,CompanyCarID,FirstName,LastName,CountyID,StateCode,
                         HireDate,ManagerID,Salary,LaborClassCode,MailCheck,Comment)
      values(@PersonID,@CompanyCarID,@FirstName,@LastName,@CountyID,@StateCode,
                         @HireDate,@ManagerID,@Salary,@LaborClassCode,@MailCheck,@Comment)

    /* Check if record saved successfully. If not, then return false. */
    if (select  @@rowcount) <> 1
        return (-1)
    else
        return
  end
else
  begin
    /* update Person */
    update Person set
      CompanyCarID=@CompanyCarID,
      FirstName=@FirstName,
      LastName=@LastName,
      CountyID=@CountyID,
      StateCode=@StateCode,
      HireDate=@HireDate,
      ManagerID=@ManagerID,
      Salary=@Salary,
      LaborClassCode=@LaborClassCode,
      MailCheck=@MailCheck,
      Comment=@Comment
      where PersonID=@PersonID

    /* Check if record saved successfully> If not, then return false. */
    if (select  @@rowcount) <> 1
        return (-1)
    else
      return
  end

DeletePerson (Delete Command):

create procedure DeletePerson5
    @PersonID int
as
  delete Person
    where PersonID=@PersonID

Developing Applications with the Microsoft Visual Basic 6.0 Data Object Wizard

Contents

Design Issues

Stored Procedures

Update Timing

Lookup (Foreign Key) Relationships

Primary Key Generation

Null Values

Command Parameters

DOW Commands

One Select Command

Zero or More Lookup Commands

Zero or One Insert Command

Zero or One Update Command

Zero or One Delete Command

The DataEnvironment Designer

Creating a Data Class

Select Data Environment Command

Define Class Field Information

Define Lookup Table Information

Map Lookup Fields

Define and Map Insert Data Command

Define and Map Update Data Command

Creating Data-Bound User Controls

DataGrid

Select the data Class to use

Select User Control type

Map Class Properties to a Control type

Enter User Control name

Single Record

Combo/ListBox

Using DOW Objects in an Application

DataEnvironment Commands

Data Class

Events

Data binding

RecordSets

Methods

System-generated Primary Key values

User Controls

Auto Initializing

Manual Initializing

Appendix

Class

Properties

Methods

Events

User Controls: DataGrid

Properties

Methods

Events

User Controls: Single Record

Properties

Methods

Events

User Controls: Combo/ListBox

Properties

Methods

clsDow

Typical Example

A Sample Entity Relationship Diagram (ERD)

Sample Command Stored Procedures

Additional resources