Query By Example

Like this article? We recommend

Prototyping with Visual Basic

Learn More Buy

Like this article? We recommend

Like this article? We recommend 

Prototyping with Visual Basic

Learn More Buy

How It Works

Using the Selector form is easy from the user's point of view. For many applications, you can use it as it is written with little or no modification.

The form is also easy to add to your application. That lets you show it to customers in an early prototype so you can get feedback right away.

To make query by example easy for the user and developer, the Selector form has to do a lot of work. Its four main tasks are gathering information about what it needs to do, initializing itself, performing queries, and letting the user select a record.

Gathering Information

Listing 3 shows the code the Selector form uses to gather information about the fields it will display. It uses the private collections m_FieldDBName, m_FieldDisplayName, m_FieldAlignment, and m_FieldDBFormat to hold information about the fields it will manipulate. These collections are described in Table 1.

Table 1 Field Information Collections

The form's AddField method saves information about a field. It simply adds the information describing the new field to the field information collections.

Listing 3 The Selector Form Uses this Code to Store Field Information.

' Configuration parameters.
Private m_Connection As ADODB.Connection
Private m_BaseQuery As String
Private m_FromClause As String
Private m_JoinStatements As String
Private m_OrderByClause As String
Private m_NumVisibleFields As Integer

' Field description collections.
Private m_FieldDBName As Collection
Private m_FieldDisplayName As Collection
Private m_FieldAlignment As Collection
Private m_FieldDBFormat As Collection

' Add entries for a field.
Public Sub AddField(ByVal field_db_name As String, _
  ByVal field_display_name As String, _
  Optional ByVal field_db_format _
    As SelectFormat = format_String, _
  Optional ByVal field_alignment _
    As MSFlexGridLib.AlignmentSettings = flexAlignLeftTop)

  ' Allocate the field collections if necessary.
  If m_FieldDBName Is Nothing Then
    Set m_FieldDBName = New Collection
    Set m_FieldDisplayName = New Collection
    Set m_FieldAlignment = New Collection
    Set m_FieldDBFormat = New Collection
  End If

  ' Save the new values.
  m_FieldDBName.Add field_db_name
  m_FieldDisplayName.Add field_display_name
  m_FieldAlignment.Add field_alignment
  m_FieldDBFormat.Add field_db_format
End Sub

Initialization

Listing 4 shows the Select form code that prepares the form for work. Most of the work is done in the Initialize subroutine. Initialize is long but straightforward.

Initialize first does a little error-checking to make sure its parameters make sense. For example, it makes sure that it has been passed a database connection and that at least one field has been defined.

The routine then saves the database connection, FROM clause, join statements, and ORDER BY clause. It checks the clauses to ensure they begin with FROM, WHERE, and ORDER BY, respectively.

Next, Initialize concatenates the field names into a basic SELECT statement of the following form:

SELECT field1, field2, ...

Initialize loops through the field display names to see how many are non-blank. Later, that number determines the number of columns in the result grid.

The subroutine then loads the controls it needs to display in the form's Query Values area. As it loads the controls, it sets their positions and TabIndex properties. After it has loaded the new controls, Initialize sizes the Query Values frame to fit the controls it contains.

The routine gives each field name combo box a blank item so the user can select nothing for that field. It then loops through the field names shown to the user and adds them to the field name combo boxes. As it does this, the routine keeps track of the field name that takes up the most room.

When it has finished loading the combo boxes, the Initialize subroutine makes them all wide enough to display the biggest field name. It also positions the operator combo boxes and the value text boxes.

Finally, subroutine Initialize calls DisplayColumnHeaders to set the grid control's column headers. DisplayColumnHeaders loops through the collection of field display names and places each in the first row of the corresponding column. It sets the column's width so it is wide enough to display the header value and sets the column's alignment using the value specified in the call to AddField.

Listing 4 The Selector Form Uses this Code to Initialize Itself.

' Configuration parameters.
Private m_Connection As ADODB.Connection
Private m_BaseQuery As String
Private m_FromClause As String
Private m_JoinStatements As String
Private m_OrderByClause As String
Private m_NumVisibleFields As Integer

' The data selected.
Private m_DataSelected As Variant

' Save values used to configure the form.
Public Sub Initialize(ByVal db_connection As ADODB.Connection, _
  ByVal from_clause As String, _
  Optional ByVal join_statements As String = "", _
  Optional ByVal order_by_clause As String = "", _
  Optional ByVal num_conditions As Integer = 4)
Const GAP = 30

Dim i As Integer
Dim j As Integer
Dim frame_height As Single
Dim ctl As Control
Dim field_name As String
Dim name_width As Single
Dim max_name_width As Single
Dim operator_left As Single
Dim value_left As Single
Dim tab_index As Integer

  ' *** Validate the parameters ***
  ' Make sure we have not already done this.
  If Not (m_Connection Is Nothing) Then
    Err.Raise err_AlreadyInitialized, _
      "frmSelector.Initialize", _
      "Selector already initialized"
  End If

  ' Make sure the parameters make some sense.
  If db_connection Is Nothing Then
    Err.Raise err_NoConnection, _
      "frmSelector.Initialize", _
      "Connection is Nothing"
  End If

  ' Make sure we have fields defined.
  If m_FieldDBName.Count < 1 Then
    Err.Raise err_NoFields, _
      "frmSelector.Load", _
      "No fields defined"
  End If

  ' *** Save the connection and query clauses ***
  Set m_Connection = db_connection

  m_FromClause = Trim$(from_clause)
  If UCase$(Left$(m_FromClause, 5)) <> "FROM " Then
    m_FromClause = "FROM " & m_FromClause
  End If

  m_JoinStatements = Trim$(join_statements)
  If UCase$(Left$(m_JoinStatements, 6)) <> "WHERE " Then
    m_JoinStatements = "WHERE " & m_JoinStatements
  End If

  m_OrderByClause = Trim$(order_by_clause)
  If Len(m_OrderByClause) > 0 Then
    If UCase$(Left$(m_OrderByClause, 9)) <> "ORDER BY " Then
      m_OrderByClause = "ORDER BY " & m_OrderByClause
    End If
  End If

  ' Build the SELECT clause.
  For i = 0 To m_FieldDBName.Count - 1
    m_BaseQuery = m_BaseQuery & ", " & m_FieldDBName(i + 1)
  Next i
  m_BaseQuery = "SELECT " & Mid$(m_BaseQuery, 3)

  ' See how many columns are visible.
  m_NumVisibleFields = 0
  For i = 0 To m_FieldDisplayName.Count - 1
    If Len(m_FieldDisplayName(i + 1)) > 0 Then _
      m_NumVisibleFields = m_NumVisibleFields + 1
  Next i
  If m_NumVisibleFields < 1 Then
    Err.Raise err_NoVisibleFields, _
      "frmSelector.Load", _
      "No visible fields"
  End If

  ' Set the tab index for the first three controls.
  cboFieldName(0).TabIndex = 0
  cboOperator(0).TabIndex = 1
  txtFieldValue(0).TabIndex = 2
  tab_index = 3

  ' *** Load the selection controls ***
  Me.txtFieldValue(0).Height = cboFieldName(0).Height
  For i = 1 To num_conditions - 1
    Load cboFieldName(i)
    With cboFieldName(i)
      Set .Container = fraQueryValues
      .Top = cboFieldName(i - 1).Top + _
         cboFieldName(i - 1).Height + 2 * GAP
      .Visible = True
      .TabIndex = tab_index
      tab_index = tab_index + 1
    End With

    Load cboOperator(i)
    With cboOperator(i)
      Set .Container = fraQueryValues
      .Top = cboFieldName(i).Top
      .Visible = True
      For j = 0 To cboOperator(0).ListCount - 1
        .AddItem cboOperator(0).List(j)
      Next j
      .TabIndex = tab_index
      tab_index = tab_index + 1
    End With

    Load txtFieldValue(i)
    With txtFieldValue(i)
      Set .Container = fraQueryValues
      .Top = cboFieldName(i).Top
      .Visible = True
      .TabIndex = tab_index
      tab_index = tab_index + 1
    End With
  Next i

  ' Set the remaining tab indexes.
  cmdList.Default = True
  cmdList.TabIndex = tab_index
  tab_index = tab_index + 1
  flxResults.TabIndex = tab_index
  tab_index = tab_index + 1

  ' Make the value area as big as we can.
  frame_height = _
    txtFieldValue(txtFieldValue.UBound).Top + _
    txtFieldValue(0).Height + 120
  fraQueryValues.Move 0, 0, ScaleWidth, frame_height

  ' *** Arrange the selection controls ***
  ' Start the field name ComboBoxes with a blank entry.
  For Each ctl In cboFieldName
    ctl.AddItem ""
  Next ctl

  ' Add the field names to the field name ComboBoxes.
  For i = 0 To m_FieldDisplayName.Count - 1
    ' See if the display name is blank.
    field_name = m_FieldDisplayName(i + 1)
    If Len(field_name) > 0 Then
      For Each ctl In cboFieldName
        ctl.AddItem field_name
      Next ctl

      ' See how wide the field name is.
      name_width = TextWidth(field_name)

      ' Save the widest field name's width.
      If max_name_width < name_width Then
        max_name_width = name_width
      End If
    End If
  Next i

  ' Calculate the necessary positions of the controls.
  name_width = max_name_width + 480
  operator_left = cboFieldName(0).Left + name_width + GAP
  value_left = operator_left + cboOperator(0).Width + GAP

  ' Size the controls so there is room for the
  ' column names. We don't bother to size the
  ' value fields because we need to do that when
  ' the form resizes anyway.
  For Each ctl In cboFieldName
    ctl.Width = name_width
  Next ctl
  For Each ctl In cboOperator
    ctl.Left = operator_left
  Next ctl
  For Each ctl In txtFieldValue
    ctl.Left = value_left
  Next ctl

  ' Display the column headers.
  DisplayColumnHeaders
  flxResults.Visible = True
End Sub

' Display the column headers in the MSFlexGrid.
Private Sub DisplayColumnHeaders()
Dim i As Integer
Dim c As Integer
Dim field_name As String

  ' Copy the results into flxResults.
  flxResults.Rows = 2
  flxResults.FixedRows = 1
  flxResults.Rows = 1
  flxResults.Cols = m_NumVisibleFields
  flxResults.FixedCols = 0

  ' Display the column headings.
  For i = 0 To m_FieldDisplayName.Count - 1
    field_name = m_FieldDisplayName(i + 1)
    If Len(field_name) > 0 Then
      flxResults.TextMatrix(0, c) = field_name
      flxResults.ColWidth(c) = TextWidth(field_name) + 120
      flxResults.ColAlignment(c) = m_FieldAlignment(i + 1)
      c = c + 1
    End If
  Next i
End Sub

The other initialization-related task that the form performs is positioning its controls. The form's Resize event handler, shown in Listing 5, begins by making the Query Values area as wide as the form. It makes the value text boxes as wide as possible while still fitting within that area.

The Resize event handler then centers the List button beneath the Query Values area, and sizes the result grid to fill the rest of the form.

Listing 5 When the Form Resizes, the Resize Event Handler Rearranges Its Controls.

' Arrange the form's controls.
Private Sub Form_Resize()
Const GAP = 60

Dim i As Integer
Dim value_width As Single
Dim grid_top As Single
Dim grid_height As Single

  ' Make fraQueryValues fill the width of the form.
  fraQueryValues.Width = ScaleWidth

  ' See how wide we can make the value fields.
  value_width = fraQueryValues.Width - txtFieldValue(0).Left - 120
  If value_width < 120 Then value_width = 120

  ' Size the values fields.
  For i = cboFieldName.LBound To cboFieldName.UBound
    txtFieldValue(i).Width = value_width
  Next i

  ' Put the List button under fraQueryValues.
  cmdList.Move (ScaleWidth - cmdList.Width) / 2, _
    fraQueryValues.Top + fraQueryValues.Height + GAP

  ' Fill the rest of the form with the results grid.
  grid_top = cmdList.Top + cmdList.Height + GAP
  grid_height = ScaleHeight - grid_top
  If grid_height < 120 Then grid_height = 120
  flxResults.Move 0, grid_top, ScaleWidth, grid_height
End Sub

Performing Queries

The most interesting part of the Selector form's code, shown in Listing 6, deals with composing and executing queries, and displaying the results. When the user clicks the List button, its event handler uses the ComposeQuery routine to create the query it must execute. It then calls DisplayResults to execute the query and display the results.

ComposeQuery starts with the form's basic join statement. If the program specified a join statement in its call to the form's Initialize method, this will have the form:

WHERE table1.field1 = table2.field2

If the call to initialize did not specify a join condition, the join clause is initially the word WHERE.

Next, ComposeQuery loops through the field name combo boxes. When it finds a non-blank selection, the routine calls AddToWhereClause to add that field's information to the WHERE clause.

When it has added all of the fields to the WHERE clause, the program determines whether the clause contains more than the word WHERE. If the clause contains nothing but WHERE, the routine empties it entirely. That lets ComposeQuery add the blank clause to the final query safely.

Finally, ComposeQuery concatenates the basic SELECT, FROM, and ORDER BY clauses with the newly constructed WHERE clause to build the final query.

Subroutine AddToWhereClause adds a field name, operator, and value to the query's WHERE clause. First, the routine determines whether the field name or operator is blank. If either of these is blank, there is not enough information to add to the WHERE clause so the routine exits.

If the field's value is blank, the operator must be one that does not require a value. If the operator is not IS NULL or IS NOT NULL, the routine exits.

If the WHERE clause already contains anything, the routine adds AND to the clause. It then tacks on the new field's name and the operator.

If the operator is IS NULL or IS NOT NULL, nothing else is needed, so the routine exits. If the operator is something else, the routine must also add the field's value. AddToWhereClause uses the field's format to determine whether the value should be formatted as a string, number, or date. It adds the value accordingly and returns the result.

After the List button's event handler builds the query with the ComposeQuery subroutine, it calls DisplayResults to execute the query and show the records selected. DisplayResults uses the database connection's Execute method to execute the query.

If Execute returns any rows, the routine uses the returned Recordset's GetRows method to copy the data into the Variant array m_DataSelected. DisplayResults then loops through the array, copying the results into the form's MSFlexGrid control.

As it displays values, the routine checks the values' data type to see which are stored as Currency data in the database. It displays Currency values with two digits of precision. If it did not take this extra step, the values 12.00 and 34.50 would be displayed as 12 and 34.5, not looking much like currency values.

After it has copied all the data into the grid control, DisplayResults sizes the columns. The routine makes each column wide enough to display the widest value it contains.

Listing 6 The Selector Form Uses this Code to Perform Queries and Display Results.

' The data selected.
Private m_DataSelected As Variant

' Compose and execute the query.
Private Sub cmdList_Click()
Dim query As String

  ' Compose the query.
  query = ComposeQuery()

  ' Execute the query and display the results.
  DisplayResults query
End Sub

' Build the SELECT statement.
Private Function ComposeQuery() As String
Dim i As Integer
Dim where_clause As String
Dim query As String

  ' Start with the join conditions.
  where_clause = m_JoinStatements

  ' Add the conditions specified by the user.
  For i = cboFieldName.LBound To cboFieldName.UBound
    ' Make sure the field name is not blank.
    If cboFieldName(i).ListIndex >= 0 Then
      AddToWhereClause where_clause, _
        m_FieldDBName(cboFieldName(i).ListIndex + 1), _
        cboOperator(i).Text, _
        Replace(txtFieldValue(i).Text, "'", "''"), _
        m_FieldDBFormat(cboFieldName(i).ListIndex + 1)
    End If
  Next i

  ' See if the WHERE clause is more than "WHERE "
  If Len(where_clause) <= 6 Then where_clause = ""

  ' Build the query.
  ComposeQuery = _
    m_BaseQuery & " " & _
    m_FromClause & " " & _
    where_clause & " " & _
    m_OrderByClause
End Function

' Add this entry to the WHERE clause if appropriate.
Private Sub AddToWhereClause(ByRef where_clause As String, _
  ByVal field_name As String, ByVal operator As String, _
  ByVal field_value As String, _
  ByVal field_db_format As SelectFormat)
Const DATE_FORMAT = "MM/DD/YYYY"

  ' If the field name or operator is blank, do nothing.
  If Len(field_name) = 0 Or Len(operator) = 0 Then Exit Sub

  ' If the value is blank, make sure the operator is
  ' IS NULL or IS NOT NULL.
  If Len(field_value) = 0 Then
    If (operator <> "IS NULL") And _
      (operator <> "IS NOT NULL") _
        Then Exit Sub
  End If

  ' If the clause is more than "WHERE ", add an AND.
  If Len(where_clause) > 6 _
    Then where_clause = where_clause & " AND"

  ' Compose the condition.
  where_clause = where_clause & " " & _
    field_name & " " & _
    operator & " "

  ' If the operator is IS NULL or IS NOT NULL, we are done.
  If (operator = "IS NULL") Or _
    (operator = "IS NOT NULL") _
      Then Exit Sub

  ' Add quotes if necessary.
  Select Case field_db_format
    Case format_String
      ' Add quotes.
      field_value = "'" & field_value & "'"
    Case format_Numeric
      ' Add nothing.
      field_value = field_value
    Case format_Date
      ' Format for a date.
      field_value = "TO_DATE('" & _
        Format$(field_value, DATE_FORMAT) & _
        "', '" & DATE_FORMAT & "')"
  End Select

  ' Add the value.
  where_clause = where_clause & field_value
End Sub

' Execute the query and display the results in
' the MSFlexGrid.
Private Sub DisplayResults(ByVal query As String)
Dim rs As ADODB.Recordset
Dim max_col As Integer
Dim r As Integer
Dim c As Integer
Dim i As Integer
Dim fld As ADODB.Field
Dim text_width As Single
Dim max_width As Single

  ' Execute the query.
  Set rs = m_Connection.Execute(query, , adCmdText)

  ' Hide flxResults while we rebuild it.
  flxResults.Visible = False
  flxResults.Refresh

  ' Copy the results into flxResults.
  flxResults.Rows = 1
  max_col = rs.Fields.Count - 1

  ' Save the results in an array.
  If rs.EOF Then
    m_DataSelected = Null
  Else
    m_DataSelected = rs.GetRows()

    ' Display the rows.
    rs.MoveFirst
    r = 1
    Do While Not rs.EOF
      ' Display this row.
      flxResults.Rows = r + 1
      c = 0
      For i = 0 To max_col
        If Len(m_FieldDisplayName(i + 1)) > 0 Then
          Set fld = rs.Fields(i)
          If fld.Type = adCurrency Then
            flxResults.TextMatrix(r, c) = _
              Format$("" & fld.Value, "0.00")
          Else
            flxResults.TextMatrix(r, c) = "" & fld.Value
          End If
          c = c + 1
        End If
      Next i

      ' Save this record's index.
      flxResults.RowData(r) = r - 1

      ' Get the next record.
      r = r + 1
      rs.MoveNext
    Loop
  End If
  rs.Close

  ' Size the columns.
  For c = 0 To flxResults.Cols - 1
    max_width = 0
    For r = 0 To flxResults.Rows - 1
      text_width = TextWidth(flxResults.TextMatrix(r, c))
      If max_width < text_width Then max_width = text_width
    Next r
    flxResults.ColWidth(c) = max_width + 120
  Next c

  ' Display the FlexGrid.
  flxResults.Visible = True
End Sub

Selecting Records

The last task the Selector form performs is notifying the main program when the user selects a record. When the user double-clicks on a row in the result grid, the grid's DblClick event handler shown in Listing 7 event fires. The event handler copies the data for the selected row from the m_DataSelected array into a new one-dimensional Variant array. It then raises the RecordSelected event, passing it the new array. The main program can use the values in this array to see which record was selected.

Note that the program copies all the data for this record into the new array, not just the fields that should be visible to the user. This lets the program see fields that are selected by the query but hidden from the user.

Listing 7 When the User Double-clicks a Record, this Program Raises the Recordselected Event.

' We raise this event when the user selects a record.
Public Event RecordSelected(ByVal values As Variant)

' Raise the RecordSelected event.
Private Sub flxResults_DblClick()
Dim i As Integer
Dim r As Integer
Dim values As Variant

  ' Make sure we selected data.
  If IsNull(m_DataSelected) Then Exit Sub

  ' Copy the selected data into the values variable.
  ReDim values(LBound(m_DataSelected, 1) To _
         UBound(m_DataSelected, 1))
  r = flxResults.RowData(flxResults.Row)
  For i = LBound(values) To UBound(values)
    values(i) = m_DataSelected(i, r)
  Next i

  ' Raise the event.
  RaiseEvent RecordSelected(values)
End Sub