Searching the Inventory

Duwamish Books Catalog, Phase 1

Kevin Marzec
Microsoft Developer Network

May 1998

Summary: Describes the Catalog application. (8 printed pages). Knowledge of Visual Basic and data-access programming will be helpful. Discusses:

• Creating user-friendly database search functionality
  
  
• Using ActiveX controls on a search form
  
  
• Code samples

The Catalog application of the Duwamish Books sample was built with Microsoft® Visual Basic® version 5.0. It contains all the functionality for performing searches (with multiple parameters, such as International Standard Book Number (ISBN), keywords, author, date, and price), creating new items, and editing existing items. The goal was to model an application that a small retail bookstore might use. It was specifically designed to demonstrate the migration path from a simple two-tier application to an n-tier, fully scalable and distributed application. It shares the database with the other Duwamish applications, but its data access application programming interface (API) is its own. In subsequent phases, as the Catalog application is moved closer to an enterprise-scale application, the data access API and the workflow layer will be shared among all the components in the system.

Application Overview

Figure 1. Initial Search form

The main Search form (Figure 1) is displayed immediately when the application starts up. This form displays the search parameter controls for title, author, supplier, item ID, ISBN, and keyword(s) by default, and provides an Advanced button that expands the form and displays additional parameter controls: price, year, publisher, and inventory. There is also a button on the main form for creating a new item, which will only appear if the user has permission to do so. Upon search execution the relevant record data is displayed in a ListView control (named Results) at the bottom of the form.

After the search is performed, the user may double-click any of the matching items to view their details. If the user is allowed to edit the items, each field in the item details will be modifiable by the user. Otherwise, the user will only be able to view the static data, and possibly add the item to an order.

The Data Flow

The data mechanism uses ADO (ActiveX® Data Objects) to talk to a Microsoft Access database. The recordset object is retrieved via the GetRecordset function. GetRecordset takes two parameters, one required and an optional Boolean. The first parameter is the SQL query string (for example, "Select * From Items"). The query is constructed so as to retrieve the relevant data from a combination of tables and fields to populate the results box. Next, the records in the database are examined one by one to see if all the criteria match. If they do, they are added to the results box.

The second parameter, if true, will return an updatable recordset. Otherwise, by default the returned recordsets are static and nonupdatable. The application itself calls the function with a true or a false. When only retrieving data, the application will call the GetRecordset() function with a value of FALSE. However, if the user is editing an existing item, the application will call GetRecordset() with a value of TRUE. See the "Conclusion" section of this article for a discussion of some of the migration issues involved with this model.

For more information on the database and its schema, see Steve Kirk's article, "Designing a Simple Retail-Oriented Database: Duwamish Books Database, Phase 1." This article will assume a rudimentary understanding of the layout of the database schema.

Searching

All the search category elements are implemented as an ActiveX control (called SearchItem). They are self-contained UI elements designed to encapsulate the search parameters for a particular criterion. Table 1 lists and describes the SearchItem properties.

Table 1. SearchItem Properties

Each control is initialized with various settings, such as name, and a flag designating whether the control reflects a simple text-entry search item or a multiple-button option search item (which is initialized with the text for each option as well). The control will raise a Click event when the check box is clicked and a KeyPress event when the user presses ENTER while in the text field. (This event allows the user to type in their search text and simply click ENTER to start a search.) The control contains methods for retrieving the user-entered data before the search is performed. Putting all of the UI logic into a separate control makes sense, because in doing so the client application only needs to worry about the control location and initialization. The following code snippet illustrates the simplicity obtained through this model:

 ' Set location of control
MyCtrl.Left = nLeft
MyCtrl.Height = . . .
.
.
.
MyCtrl.SetCtrlType(2)   ' 0 = no option buttons 
' 1 = 2 option buttons 
' 2 = 3 option buttons

MyCtrl.SetCheckTitle("Keyword")
MyCtrl.SetOption1Text("Exact Match")
MyCtrl.SetOption2Text("Any word")
MyCtrl.SetOption3Text("All words")

. . . 

MyCtrl.GetCheck()   ' Returns the check state – true / false
MyCtrl.GetOption()' Returns which option button is selected (1-3)
MyCtrl.GetText()   ' Returns the user-entered text

Note   The client application using the Search form has the option of passing in some parameters that will define how the Search form works (see Table 2). First, it may call SetEditable(True/False). This will enable the creation and modification of items to take place. If this is not called, or if it is called with a false parameter, the user will not be able to create items, and the Item Detail form will disable all the text controls.

The second optional parameter is SetOrderable(True/False). If this is set to True, the Item Detail form will allow the user to add the current item to his or her "shopping cart." Then, after the form exits, the client application may call GetOrderCount() and GetOrder(int) to retrieve each of the item IDs and quantities that the user ordered.

The final optional parameter is SetSupplierID(int). If this is called, the supplier search item control is "set" to the given ID passed in. The control is disabled, and in this way acts as a "filter." This idea could be taken further to include all the search item controls, but, for the scope of this sample, the only control that needed this functionality was the supplier field, because it is used by the Order Entry application to place orders to a selected supplier.

Table 2. Search Form Properties

Displaying Item Details

Figure 2. Item Detail form

The Item Detail form (Figure 2) is displayed when the user either double-clicks one of the items in the search results listview or when the user clicks the Create New button (if it is available). The Item Detail form is responsible for displaying all relevant item data, and for providing editing capabilities if necessary. The form contains display controls for the following fields: Title, Item Type, ISBN, Price, Author, Inventory, Reorder level, Description, Publication Date, Item Graphic File, Publisher, Supplier, and Keywords. The following code is called upon receipt of a Double-click event in the results box:

' The Item ID for each item is stored as a tag.
ItemDetail.SetItemID (liItem.Tag)

' Setting the quantity to zero causes the "Add to Order" button to appear.
' Setting the quantity to a non-zero value sets the default quantity.   

If mbOrderable Then ItemDetail.SetQuantity (0)
ItemDetail.SetAbleToEdit mbEditable
ItemDetail.Show vbModal
  
' Check to see if the item changed; if so, update the list view.
If ItemDetail.ItemChanged = True Then

    ' Update the item.
    liItem.Text = ItemDetail.GetTitle
    liItem.SubItems(1) = ItemDetail.GetItemType
    liItem.SubItems(2) = ItemDetail.GetAuthor
    liItem.SubItems(3) = ItemDetail.GetYear
    liItem.SubItems(4) = ItemDetail.GetPrice
    liItem.SubItems(5) = ItemDetail.GetInventory
    liItem.SubItems(6) = ItemDetail.GetISBN
    
End If
  
' Check for an order.
If ItemDetail.GetQuantity() > 0 Then

    ' Set up the order detail object.
    Set cDetail = New cOrderDetail
    cDetail.ItemId = liItem.Tag
    cDetail.Quantity = ItemDetail.GetQuantity
    cDetail.UnitPrice = ItemDetail.GetPrice

    ' mcolOrderDetails is a collection of order detail objects –-
    ' if it doesn't exist yet, we need to create it.
    If mcolOrderDetails Is Nothing Then
      Set mcolOrderDetails = New Collection
    End If
    
    ' Add this new order to the collection.
    mcolOrderDetails.Add cDetail

End If
  

Creating and Modifying Items

The form must support the ability to create and modify item types, authors, suppliers, and publishers, and must provide checking for duplicate entries. Not all of these fields are required to be filled in for creation (or modification) of a record. To make editing easy, the Update (or Create) button is initially disabled and an event handler is attached to each control that can be modified (with edit controls, this is done by handling Change events). In these handlers, we check to see whether all the required fields are filled in, and if so, we enable the Update button. The following code illustrates this process:

Private Sub ItemDesc_Change()
  CheckUpdateButton
End Sub

Private Sub CheckUpdateButton()
If ItemTitle<> vbNullString And ItemInventory <> vbNullString And _
  ItemReorder <> vbNullString And ItemYear <> vbNullString And 
  ItemPrice <> vbNullString Then
  If CLng(ItemInventory) >= 0 And CLng(ItemReorder) >= 0 And 
  CLng(ItemYear) >= 0 And CDbl(ItemPrice) >= 0# Then
    UpdateButton.Enabled = True
  Else
    UpdateButton.Enabled = False
  End If
Else
  UpdateButton.Enabled = False
End If

The Item Detail form contains a flag, "mbEditable", to keep track of whether modifications may be made to this item. When the user clicks the Publisher or the Supplier buttons, the Contact Detail form is launched and initialized with a call to ContactDetail.SetEditable(mbEditable). This way the editing permissions are passed all the way from the original client to the Search form, to the Item Detail form, and finally to the Contact Detail form.

The Keyword Field

Items may have associated keywords, whose combined length may not exceed 255 characters (since we're using a Text field in Microsoft Access). We want to provide the ability for users to easily add and remove keywords to an item, and ideally, to make it as easy as possible to add the same keywords to multiple items. To accomplish this, a Keyword combo box is provided along with Add and Remove buttons. Users may enter a single keyword and click Add to add the keyword to the combo box (checking is done for duplicate keywords and for excessive length). Users may also select an existing keyword from the combo box and click Remove to remove it. To make adding the same keywords to multiple items easier, and rather than having to add each keyword individually and click Add each time, users are able to add an entire keyword string, which is a list of keywords separated by commas (for example, "Fiction, Mystery, Murder"). The application then cycles through each keyword in turn, checking for a duplicate and checking the total keyword length. If everything is working properly it then adds the keyword, stripping out the commas and any leading or trailing white space.

The Contact Detail Form

The Contact Detail form is quite simple and straightforward. It is initialized with a Boolean from the client (the Item Detail form) that specifies whether the data is modifiable, as well as a ContactID that specifies where the data is to be found in the Contacts table. It then displays the relevant contact data fields in the dialog box. Again, all of the EditControl_Click events are handled and then all the data is checked for validity and to make sure that all required fields contain data. If data exists in all of the required fields, the Update button is enabled. Likewise, if any of the required fields lack data, the Update button is disabled. For the creation of new publishers or suppliers, the Update button contains the "Create" caption and no ID is passed in, so no data is initially displayed.

Figure 3. Contact Detail form

Conclusion

This application presents an interface and encapsulated functionality for a searching and cataloguing process in a simple bookstore. We will evolve this model from a simple system involving separate "cooperating" applications (cooperating in the sense that they share a database and a few forms) to eventually form a fully distributed, scalable system. We will do this gradually through a process of pulling out portions of the API (notably the data access and some of the workflow layer) that are easily shared between the separate interfaces, improving them as we go.

For example, the current data model, where updatable recordsets are opened, passed off, and kept open by the client until the client does a Recordset.Update call, will become problematic as Duwamish Books expands to more than one store. Instead, we could keep the returned recordsets disconnected from the database, and simply update the recordset by calling another function, something like PutRecordset. This function would then update the recordset in the database, allowing everything to stay disconnected so that another user of the system won't be locked out of a record if a different user retrieved an updatable recordset and failed to close it.

In Phase 2, we will implement a data access COM object, and disconnected recordsets, for use across many Duwamish Book stores. This first step toward scalability allows two separate offices using the same Duwamish applications to share a single data access API and not have to worry about synchronizing separate databases.