Objectives of the Week

• Understand MORE Database Terminology
• Use additional database aware controls
• Understand Database Search Techniques
• Be able to write basic SQL statements
• Database programming WITHOUT a Data Control
• Understand the use of ODBC Data Sources

More on Databases

Microsoft supplies the BIBLIO database with Visual Studio and Access. The earlier version is smaller and easily transportable on a diskette. The later version has the same structure, it just has lots more records. Both are available for download in case the database is not on your workstation. Both versions here are in the Access 97 format. This database will be used for all of the examples for this week.

     BIBLIO.MDB zipped:                                      Small Version                                Large Version

The structure of BIBLIO.MDB

Four Tables

The Biblio Database also contains a Query. A query represents a recordset that selects specific columns that meet a particular criteria. This recordset may be drawn from one or more tables. Here is the Graphical Representation of the query as provided by Access.

Access will provide the SQL (Structured Query Language) representation of the query also.

While it is beyond the scope of this course and this class to explore into the depths of database design, it is important to know some of the terminology and how it related to Visual Basic. The VB perspective on queries is that they are just like any other table.

Referential Integrity is an important database design concept. As is briefly demonstrated by the tables and the queries in the Biblio database, there are relationships between many of the tables. The query diagram reflects these relationships. Referential Integrity represents the enforcement of the rules related to these relationships.

For Example:

    What if the Titles Table contains a row with a PubID that has no entry in the Publishers Table?

    What if the Title Author has a row with an ISBN, that is not in Titles, or an AuthID that is not in Authors?

    If I delete a row from the Authors Table, do I have to delete all of the Titles related to the author?

Referential Integrity can be built into many table relationships within a database. It can deny an Insert into Title Author when the Author is not in the Authors Table or the Title is not in the Titles Table. When an Author is Deleted from the Authors Table, Referential Integrity can force a Delete of every Title Author row for that Author. The benefit is that the Database and not the Application Program assumes the responsibility for enforcing the rules established for Table Relationships.

An attempt to delete a row from the Authors Table from within Access will result in the following message:

A similar error will be generated from within VB.

Indexes can be created on columns that are often processed in sequential order. There is additional overhead in terms of space (A Table of Pointers is created and maintained by the Database Management System), but the increased performance with respect to data retrieval is often worth the additional overhead. Having multiple indexes built on a table allows the application to easily select the order in which to process the rows without the performance hit of having the Database Management System sort the rows.

Finally, we will cover the coding of some basic SQL statements. Entire courses are dedicated to Structured Query Language. The discussion here will be associated with using the Select statement and the concept of Joining two tables to retrieve a set of records to be returned for application processing. 

Grid Control

The Data Control and the binding of TextBoxes provided easy access to the information in a table with very little or no coding. There are controls available in VB or from third parties that provide this capability on a recordset level as opposed to handing back a row at a time as was the case last week.  

The DBGrid Control is packaged with Visual Basic and provides full navigation and maintenance to a table. Records can be added, changed or deleted depending on the properties that are set at design time. As with No Code and Bound Controls, the DBGrid can be limited, but you may also customize the features by programming the events that the grid exposes. To use this control, you must select it from Project | Components:

Select the ToolBox Icon and add the control to the form as with any VB control

Now set the properties for the DBGrid control  

Download the Example

An important feature is to warn the user before deleting a row. The BeforeDelete event is modified to ask the user if they are sure, and cancel the operation if the user changes their mind. Additional error checking is added to provide an appropriate message depending on the result of the Delete. Recall that Referential Integrity will prevent certain operations base on the rules established when the tables were created.

Dim blnCancel As Boolean

Private Sub dbgPublishers_BeforeDelete(Cancel As Integer)
    Dim rc As Integer

    rc = MsgBox("Are you sure?", vbYesNo, "Delete Row?")
    If rc = vbNo Then
        Cancel = True
        blnCancel = True
    End If

End Sub

Private Sub dbgPublishers_Error(ByVal DataError As Integer, Response As Integer)
    Dim strMessage As String

    If DataError = 16389 Then
        If blnCancel = True Then
            strMessage = "Delete was cancelled"
        Else
            strMessage = dbgPublishers.ErrorText
        End If

        Response = 0
        blnCancel = False

        MsgBox strMessage, vbInformation + vbOKOnly, "Data Grid Message"

        Exit Sub
    End If

End Sub

Searching

This example revisits many of the topics discussed last week, but adds some search logic and some additional Validation. The Button Logic is similar to last week. The importance of the Button Logic is to PREVENT user errors by disabling options that currently don't apply to the requested operation. For example. when the user requests an Add operation, this request must be completed or cancelled prior to browsing to the next record. The button logic will disable the Browse options until the add process is completed.

Validation can be enforced at the control level. This example demonstrates that the PubId is validated when a change is made and the control looses focus.

Private Sub txtPublID_Validate(Cancel As Boolean)
    Dim strMessage As String

    If Val(txtPublID.Text) <= 0 Then
        strMessage = "Publisher ID must be numeric"
        MsgBox strMessage, vbExclamation, "Data Entry Error"
        With txtPublID
            .SelStart = 0
            .SelLength = Len(.Text)
        End With
        Cancel = True
    End If

End Sub   

Do not let the user continue if the PubID is not numeric. Additional checks will be made when the row is updated, but this logic provides immediate an response to the user if the PubID is not a number.

The FindFirst method will reposition to a new table row based on a search criteria. The parameter for find first describes a match condition. If this condition is satisfied by a table row, that row is made the current row and the bound controls are updated on the screen. If there is no match, an error message is displayed, and the previous row (save using the Bookmark) is redisplayed on the form.

In this example, the user can search base on one of two criteria. The search can take place on the common company name or the full company name. Note the syntax when referring to a column name that contains a space. Use the square bracket characters to prevent the space in the column name from being interpreted as a delimiter.

Private Sub cmdSearch_Click()
    Dim vntBookmark As Variant

    With dtaPublisher.Recordset
        vntBookmark = .Bookmark

        If optName.Value = True Then
            .FindFirst "Name Like '" & txtSearch.Text & "*'"
        Else
            .FindFirst "[Company Name] Like '" & txtSearch.Text & "*'"
        End If

        If .NoMatch Then
            MsgBox "No record was found.", vbInformation, "No Match"
            .Bookmark = vntBookmark
        End If

    End With

End Sub

Download the Example

Indexes and Seeking

Creating an index on a table provides very fast sequential access in the order of the selected index. There can be many indexes built on a table. There is additional space required to support each index as an index is really a system maintained table. In addition to the extra space needed, the adds and deletes will be impacted as the index must be updated in addition to the table being used. The Recordset has an Index property that holds the current index. To modify the access to use another index, change the Index property to the value of the new index. You may specify the name of the index or code "PrimaryKey" to restore the default access sequence.

The user interface allows the changing of the index by selecting the option button for the desired processing sequence. This program also has a search feature that will use the selected index to quickly locate the requested row.

Private Sub SetIndex(strIndex As String)
    Dim vBookmark As Variant

    vBookmark = dtaTitles.Recordset.Bookmark
    dtaTitles.Recordset.Index = strIndex
    dtaTitles.Recordset.Bookmark = vBookmark

End Sub

If the position in the table is not maintained using the BookMark, the next Browse operation will first reposition you back to the beginning of the table based on the record order specified by the newly selected index.

The Seek Method compares the value of the indexed column with the search criteria.   This differs from the Find method because you must specify a column name and a value for the find, where the Seek will search the currently selected indexed column. Because the PubID is a number, the search criteria must be translated into a number.   .

Private Sub cmdSearch_Click()
    Dim vntBookmark As Variant

    With dtaTitles.Recordset
        vntBookmark = .Bookmark

        .Seek ">=", IIf(optPub.Value, Val(txtSearch.Text), txtSearch.Text)

        If .NoMatch Then
            MsgBox "No record was found.", vbInformation, "No Match"
            .Bookmark = vntBookmark
         End If

    End With

End Sub

Download the Example

Joins and More Data Bound Controls

This example has a user interface consisting of two forms. The first will display data from both the Titles and Publishers Tables. This will require an SQL statement. The SQL statement will perform a JOIN on the two tables, linking the rows that match PubIDs. The columns from the rows from both tables will be returned to the application when the match criteria is met.

Select * from Titles, Publishers Where Titles.PubID = Publishers.PubID

Recall the structure of each of these tables

Title, ISBN, and Year Published come from Titles PubID could come from either Company Name and City come from Publishers   Visual Basic looks at the result of query the same way that it looks at a table

The Data Control RecordSource Property is set to the SQL Statement in Form Load, and the result is the same as if a table name were supplied at design time. The SQL statement allows you to limit or be selective about the data you request instead of working with the contents of an entire table.

The second for allows for additional selection refinement. The Publishers Form constructs an SQL statement that selects all rows from the two tables where the PubIDs match, but in addition, match a single PubID.  This form builds the SQL statement based on the users selection criteria, but it also builds the Data Bound ListBox using only properties set at design time. First the SQL statement.

strSQL = "Select * from Titles, Publishers " & _                     "Where Titles.PubID = " & _                         dblPublishers.BoundText & _                             " And Titles.PubID = Publishers.PubID" This SQL statement used the BoundText property, which contains the PubID value for the selected publisher. This is detailed in the next section. This string is passed back to the main form and used to reset the Data Control RecordSource, and the result is that the main form will display only the rows for the selected publisher.

The ListBox on the Publishers Form is not an ordinary ListBox. It is a DBListBox or a Data Bound Listbox and in order to use it, you must put it in the ToolBox using the Project | Components selection.

You get both a Data Bound ListBox and a Data Bound ComboBox

The Design Time properties that must be set are first a reference to the Data Control. Because the Data Control has a RecordSource refereing to the Publishers Table, this DBListBox has effectivly been bound to that Table. The ListField Property refers to the Name column of the Publishers Table. This is what is displayed in the ListBox. The BoundColumn is the PubId and this is what is stored in the BoundText propert whenever an entry in the ListBox is selected. This means that when a Publisher is selected, the PubID is store in the Bound Text Property, and this is the value used to build the query.

Private Sub cmdOK_Click()
    blnSelectionMade = False
    If dblPublishers.Text = "" Then
        Me.Hide
        Exit Sub
    End If

    strSQL = "Select * from Titles, Publishers " & _
                        "Where Titles.PubID = " & _
                            dblPublishers.BoundText & _
                            " And Titles.PubID = Publishers.PubID"

    blnSelectionMade = True
    Me.Hide

End Sub

The only code required to populate the DBListBox is to set the DatabaseName using the Database selected from App.Path. This takes place in frmMain. Th DBListBox has no ListIndex property, so to de-select an entry in the List, the Text property is set to Null.

Private Sub Form_Load()
        dtaPublishers.DatabaseName = frmMain.dtaTitles.DatabaseName
        dblPublishers.ReFill
End Sub

Private Sub Form_Activate()
        dblPublishers.Text = ""
End Sub

Download the Example

Using the DBCombo Control

The DBCombo Control function much the same as the Dropdown Combo, but has additional database support built in.

When designing a database, one design criteria is to eliminate the redundancy of the information stored. In this example, the database contains two tables that are logically related. The Customer Table contains information related to the customer, but also includes the customer's selected shipping method. This information is coded in the database in order to reduce the amount of space required and to eliminate redundant information. The Shipping Table contains the shipping code and a description of what the code means. The two tables and their relationship is detailed below.

The Customer Table

The Shipping Table

Customer / Shipping Relationship

There is no reason to store a relatively large Shipping Description with each of many Customer Rows so the Customer Table contains a code that refers to an entry in the Shipping Table.

The DBCombo Control allows the combo box to display the Shipping Description while making available to the program the associated Shipping Code. The RowSource must reference a Data Control that points to the Shipping Table. The two additional properties that are required to complete this relationship is the BoundColumn, which is set to point to the Shipping Code, and the ListField which is set to point to the Shipping Description. Note that the Customer Table is not referenced.

The program, which makes use of Unbound Controls, will set or reference the BoundText property of the DBCombo Box at run time. Note that the Shipping Table is not referenced by the program.

The code below is from a modified version of Example 4 from Week 12

Private Sub ScreenToDB()
    dtaCustomers.Recordset.Fields("Name").Value = txtName.Text
    dtaCustomers.Recordset.Fields("Address").Value = txtAddress.Text
    dtaCustomers.Recordset.Fields("CSZ").Value = txtCSZ.Text
    dtaCustomers.Recordset.Fields("ShippingMethod").Value = dbcShipping.BoundText

End Sub

Private Sub DBToScreen()
    blnUpdating = True

    txtName.Text = GetField("Name")
    txtAddress.Text = GetField("Address")
    txtCSZ.Text = GetField("CSZ")
    dbcShipping.BoundText = GetField("ShippingMethod")

    blnUpdating = False

    NoChange

End Sub

Download the Example

No Data Control

This example covers two important topics. The first demonstrates how to write an application that will access data from database tables and NOT use a Data Control. The second is the use of ODBC, which stands for Open Database Connectivity. This example also separates the User Interface and the Database Access. The Database Access is all hidden inside a class module that will be compiled into a .DLL

ADO or Active Data Objects is a Microsoft Class used to access and manage relational databases. It makes use of recordsets in a way that is similar to using a Data Control Recordset. It's details are beyond the scope of this course.

The User Interface is of little interest in this eaxmple. It implements the exposed methods of the class and nothing more.

The Class object must be added to the project using Project | References,  an instance of the class must be declared.and the programmer must know that MoveNext and MovePrev are the only exposed methods.

ODBC Data Sources

It will not always be possible to place the Database in the application directory. ODBC Data Source Management allow the Network Administrator to control the location of the Databases. From Start | Settings | Control Panel | ODBC Data Sources, select the User DSN tab and Add a data source called ADO. This will be a Microsoft Access Database, so select the appropriate driver, the select the database, and click on OK.

From within the class module, in Class_Initialize, make the connection to the Database. Open the Recordset using an SQL statement, and process as you did using the Data Control with Unbound Controls.

Const ConnectString = "DSN=ADO"

Dim adoConn As ADODB.Connection
Dim adoRS As New ADODB.Recordset

Private Sub Class_Initialize()
        Set adoConn = New ADODB.Connection

        With adoConn
            .ConnectionString = ConnectString
            .ConnectionTimeout = 30
            .Mode = adModeReadWrite
            .CursorLocation = adUseServer
            .Open
            End With

        Set adoRS = New ADODB.Recordset

        adoRS.Open "Select * from Address", adoConn, adOpenKeyset
        adoRS.MoveFirst

End Sub

The client will access Get Properties for every column in the table in order to gain access to each field individually.

Download the Example

There is one more example that you can walk through on your own. It is similar to the ADO example above. It uses ODBC and a Data Bound Grid. It's features are a collection of many of the topics discussed here today. It is an example that ties many of these concepts together in a single application. It is the application used to support the ECC Faculty Web Page directory.

Download the Example

Assignment

Reading Assignment

• Read Chapter 13 of the text
• Review the web material for Week 14

Programming Assignment

As Always: The assignment is due before the start of class next week

Week 13 Assignment

Here is a list of the Program Grading Criteria