Database Usage

Now that you have a database set up, and you have fully documented it, you are ready to use it. But how? Microsoft has recognized that most Visual Basic applications are data-centric, and has provided multiple object-based methods for accessing the data you need. Database vendors have chimed in as well, supplying their own object models for data management and database administration purposes. Data connection objects have become an alphabet soup of communication methods, including DAO (Data Access Objects), ADO (ActiveX Data Objects), RDO (Remote Data Objects), ODBC (Open Database Connectivity), Jet (Microsoft Access), SQL-DMO (SQL Server Distributed Management Objects), ISAM (Indexed Sequential Access Method), and OO4O (Oracle Objects for OLE), to name just a few. Which of the many methods do you pick?

To be honest, the data transport you select is not as important as the presentation of the data given to the user of your application. Microsoft recommends that you use ADO and OLE DB as your primary means of accessing all aspects of a database, especially for Internet-based development. While this is a good recommendation, you may still require the functionality or compatibility provided by another of the object collections. Use the database connectivity protocol that best meets your needs. Just be sure to document the features and expectations of the connectivity tool in your technical documentation set.

What is needed in presentation of data to the user is the element of control. In this case, "control" applies both to the use of Visual Basic user interface "controls," and to the amount of control you as the programmer exert over the user's experience with the application. In general, you should take the highest level of control onto yourself, and limit the control of the user as much as possible. This may seem strange, since it is often the user who is also the owner of the data, not you. However, if users really needed full control of the data, they would write the program themselves, and you would be out of a job.

There are several traditional methods by which Visual Basic programmers have presented data to the user. For simplicity of description, I call them "Spreadsheet,""Bound Field," and "Form Object" presentations. In each of the presentations, we will use the baseball-related tables shown in Figure 12-1.

Spreadsheet Presentation

The spreadsheet method of presenting and collecting data is one of the quickest to implement, assuming that you have the right tools. One tool that uses this interface as its default method of data presentation is Microsoft Access, and its Datasheet view. An Access view of the Team table appears in Figure 12-2.

The Spreadsheet presentation gives the user almost full and direct access to the data. In Visual Basic, you can employ a variation of this presentation using one of the available grid controls to allow the user to interact with the underlying table or view data.

While the Spreadsheet presentation is easy to achieve, it leaves much to be desired. Complex and fully normalized data relationships are not easily managed through such an interface. The method by which changes are canceled is not immediately obvious, if available at all. Also, the visual presentation of the Spreadsheet provides no indication of the relationships or priorities of the columns in the grid. These are some of the reasons why I recommended in Chapter 11 that grids be used in a limited fashion for data display, and seldom, if ever, for data entry. Avoid the Spreadsheet presentation of data within your Visual Basic application.

Bound Field Presentation

The Bound Field presentation of data is also popular in Microsoft Access, and it is often employed in standard Visual Basic applications as well. The Bound Field presentation has two primary traits. The first is an absolute dependence on the use of "data bound" controls. Most, if not all, of the controls displayed on each form take advantage of the data connection features available within many Visual Basic controls. The second trait of this mode of presentation is the active interaction between related tables. In Microsoft Access, such interactions are accomplished through the use of sub-forms, as shown in Figure 12-3.

The Bound Field presentation is much more advanced than the Spreadsheet presentation. Priorities and relationships between the different fields are much clearer on this form. The complex and normalized relationship between the Team and the Player tables is easily managed. Also, this presentation is not too difficult to implement in Visual Basic because of the tools supplied with the development environment. In the latest versions of Visual Basic, almost every control supplied with the system can be bound to a data source. Data sources themselves can be visually designed with drag-and-drop methods.

While there are many advantages to the Bound Field presentation, it still is not adequate for professional Visual Basic applications. It is still not obvious how data changes are canceled. Also, the transition from one instance of the major entity (in this case, the Team) to another gives no clear indication of the committal of the changes to the database. In this particular example, the two rows of VCR buttons can be confusing. While they could be replaced with more intuitive movement methods, that change alone would still leave the vagueness of the transition between two entities. Also, there is no clear way to move a Player from one team to another.

The biggest problem with the Bound Field presentation is that it removes control of the data from both the programmer and the user. The logic supplied through the data bound features of each control and connection object form the basis of data management. While this makes it easy to implement data solutions quickly and with little need for the actual writing of Visual Basic code, it also makes it easy to incorrectly handle legitimate errors that may occur in the communication and processing of the data. Avoid the use of the Bound Field presentation, and in general avoid all data bound control features.

Form Object Presentation

The third, and preferred, form of data presentation is the Form Object presentation, so named because a form contains an object's worth of data. The object may represent a single table entity, or it may be built up from complex database relationships. However, to the user each form is a single unit. The programmer controls the access to the elements and construction of the object, while the user has limited access to only those object elements made available by the programmer. Figures 12-4, 12-5, and 12-6 show the three primary forms involved in managing the Team and Player tables.

In this presentation, the maintenance of each object (basically the table entities in this example) is now performed by separate forms. This divide-and-conquer method reduces the complexity of the data presented to the user, reserving to each form the management of a single thing, a data object. It is now clear from these forms how you cancel changes made to individual fields—simply click on the Cancel button. The relationships and priorities between the objects and fields are clear. Still, the number-one benefit of this method of presentation is that the user's control of the data is limited to just what the programmer desires, and the programmer's control of the data is maximized. This is true at the source code level as well. In fact, the only access to the fields on the Player Properties form from other forms is through a public function defined for the purpose of editing new and existing players. Consider the following DAO-based code associated with the Player Properties form.

' ----- Baseball Application
'       Written by Tim Patrick
'       Player.frm - Player Properties
Option Explicit

Private mnResult As Integer
Private mlPlayerID As Long

Public Function EditPlayer(lPlayerID As Long) As Long
    ' ----- Edit a new or existing player.  lPlayerID
    '       is the player's ID, or -1 to add a new
    '       player.  Returns player ID on success,
    '       or -1 on cancel.
    mlPlayerID = lPlayerID
    Me.Show vbModal
    If (mnResult = vbCancel) Then
        EditPlayer = -1
    Else
        EditPlayer = mlPlayerID
    End If
    Unload Me
End Function

Private Sub LoadData()
    ' ----- Load in the data for the existing
    '       player record
    Dim sSQL As String
    Dim rsInfo As Recordset

    On Error GoTo ErrorHandler

    ' ----- Retrieve the player's data
    sSQL = "SELECT * FROM Player WHERE ID = " & mlPlayerID
    Set rsInfo = gdbData.OpenRecordset(sSQL, _
        dbOpenSnapshot)
    txtLastName.Text = rsInfo!LastName & ""
    txtFirstName.Text = rsInfo!FirstName & ""
    cboPosition.ListIndex = ListByItem(cboPosition, _
        rsInfo!Position)
    rsInfo.Close
    Exit Sub

ErrorHandler:
    GeneralError "frmPlayer.LoadData", Err, Error$
    Resume Next
End Sub

Private Function SaveData() As Boolean
    ' ----- Save the user's data.  This routine
    '       assumes that the form data is valid.
    '       Return True on success and set mlPlayerID.
    Dim lNewID As Long

    On Error GoTo ErrorHandler
    SaveData = False

    If (mlPlayerID = -1) Then
        ' ----- Add a new player.  First get a
        '       new primary key.
        lNewID = GetTableID("Player")
        If (lNewID = -1) Then Exit Function
        ' ----- Now add the player's record
        sSQL = "INSERT INTO Player (ID, LastName, " & _
            "FirstName, Position) VALUES (" & lNewID & _
            ", " & CStrDB(Trim$(txtLastName.Text)) & _
            ", " & CStrDB(Trim$(txtFirstName.Text)) & _
            ", " & cboPosition.ItemData( _
            cboPosition.ListIndex) & ")"
        gdbData.Execute sSQL
        mlPositionID = lNewID
    Else
        ' ----- Update an existing player
        sSQL = "UPDATE Player SET LastName = " & _
            CStrDB(Trim$(txtLastName.Text)) & _
            ", FirstName = " & _
            CStrDB(Trim$(txtFirstName.Text)) & _
            ", Position = " & cboPosition.ItemData( _
            cboPosition.ListIndex) & " WHERE ID = " & _
            mlPositionID
        gdbData.Execute sSQL
    End If

    ' ----- Success!
    SaveData = True
    Exit Function

ErrorHandler:
    GeneralError "frmPlayer.SaveData", Err, Error$
    Exit Function
End Function

Private Function VerifyData() As Boolean
    ' ----- Check the user's data entry.  Return
    '       True if all data is valid.
    VerifyData = False

    ' ----- Last name is required.
    If (Trim$(txtLastName.Text) = "") Then
        MsgBox "Last name is required.", _
            vbOKOnly + vbExclamation, PROGRAM_TITLE
        txtLastName.SetFocus
        Exit Function
    End If

    ' ----- First name is required.
    If (Trim$(txtFirstName.Text) = "") Then
        MsgBox "First name is required.", _
            vbOKOnly + vbExclamation, PROGRAM_TITLE
        txtFirstName.SetFocus
        Exit Function
    End If

    ' ----- Position is required.
    If (cboPosition.ListIndex = -1) Then
        MsgBox "Position is required.", _
            vbOKOnly + vbExclamation, PROGRAM_TITLE
        cboPosition.SetFocus
        Exit Function
    End If

    ' ----- Success!
    VerifyData = True
End Function

Private Sub cmdCancel_Click()
    ' ----- Cancel changes made to the player.
    mnResult = vbCancel
    Me.Hide
    ' ----- Continue with EditPlayer()
End Sub

Private Sub cmdOK_Click()
    ' ----- Save changes made to the player.
    If (VerifyData() = False) Then Exit Sub
    If (SaveData() = False) Then Exit Sub
    mnResult = vbOK
    Me.Hide
    ' ----- Continue with EditPlayer()
End Sub

Private Sub Form_Load()
    ' ----- Prepare the form.
    Dim sSQL As String
    Dim rsInfo As Recordset

    On Error GoTo ErrorHandler

    ' ----- Fill in the list of positions.
    sSQL = "SELECT ID, FullName FROM CodePosition " & _
        "ORDER BY FullName"
    Set rsInfo = gdbData.OpenRecordset(sSQL, _
        dbOpenSnapshot)
    Do While Not (rsInfo.EOF)
        ' ----- Add this position to the list.
        cboPosition.AddItem rsInfo!FullName
        cboPosition.ItemData(cboPosition.NewIndex) = _
            rsInfo!ID
        rsInfo.MoveNext
    Loop
    rsInfo.Close
    ' ----- If we will edit an existing player, load
    '       in that player's data now.
    If (mlPlayerID <> -1) Then LoadData
    Exit Sub

ErrorHandler:
    GeneralError "frmPlayer.Form_Load", Err, Error$
    Resume Next
End Sub

Private Sub txtFirstName_GotFocus()
    ' ----- Highlight the entire text.
    SelectText txtFirstName
End Sub

Private Sub txtLastName_GotFocus()
    ' ----- Highlight the entire text.
    SelectText txtLastName
End Sub

This code assumes the existence of some other functions defined elsewhere.

• ListByItem. This function is defined in Chapter 11, in the Combo Box Control entry.

• SelectText. This function is defined in Chapter 11, in the Text Box Control entry.

• GeneralError. This function is defined in Chapter 2, in the Order Of Code section.

• GetTableID. This function is not defined in this book, but it obtains the next primary key value for a table by using a database sequence or other similar platform-specific mechanism.

• CStrDB. This routine converts a string into a form suitable for insertion in an SQL statement.

Public Function CStrDB(sOrig As String) As String
    ' ----- Prepare a string for an SQL statement.
    If (Trim$(sOrig) = "") Then
        CStrDB = "NULL"
    Else
        CStrDB = "'" & Replace$(sOrig, "'", "''") & "'"
    End If
End Function

The Team Properties form initiates a call to the Player Properties form when the user clicks on either the Add or Edit button.

Private Sub cmdAdd_Click()
    ' ----- Add a new player
    If (frmPlayer.EditPlayer(-1) <> -1) Then
        RefreshPlayerList
    End If
End Sub

Private Sub cmdEdit_Click()
    ' ----- Add a new player
    Dim lPlayerID As Long

    If (lstPlayers.ListIndex = -1) Then Exit Sub
    lPlayerID = lstPlayers.ItemData(lstPlayers.ListIndex)
    If (frmPlayer.EditPlayer(lPlayerID) <> -1) Then
        RefreshPlayerList
    End If
End Sub

I can promise you now, this method of data presentation will take longer to program, you will have more forms in your finished application, and your user documentation needs will expand. When you consider the code to populate the data fields, verify all data supplied by the user, save the user's changes, and transition between the different forms, the amount of coding goes from basically no lines to several hundred lines. However, professional programming is not about meeting the needs of the programmer, but about meeting the needs of the user. And I can guarantee you, if you generate professional-quality applications for your users, you will receive the accolades and rewards that you deserve.