What's New
Q & A
Tip Jar
C# Helper...
Follow VBHelper on Twitter Follow VBHelper on Twitter
MSDN Visual Basic Community
TitleUse combo boxes and text boxes to let the user pick search criteria for a database query in Visual Basic 6
DescriptionThis example shows how to use combo boxes and text boxes to let the user pick search criteria for a database query in Visual Basic 6.
Keywordsdatabase, DataGridView, DataTable, DataAdapter, OleDbDataAdapter, data adapter, bind, DataSet, DataTable, Visual Basic 6, VB 6

The top of this program holds three columns of controls. The left column contains combo boxes holding the names of the fields in a database table. The middle column holds operators such as =, <, and >=. The right column holds text boxes. The user can use these controls to determine how the program queries the database. For example, if the user selects the Title field from the first combo box, the >= operator from the second, and enters R in the first text box, the program searches for records where the Title field has value >= R.

The following code executes when the form loads.

Private ConnectString As String
Private m_DBConnection As ADODB.Connection
Private m_FieldNames() As String
Private m_DataTypes() As Long

' Get the field names and data types.
Private Sub Form_Load()
Dim db_name As String
Dim rs As ADODB.Recordset
Dim num_cols As Integer
Dim c As Integer
Dim i As Integer

    db_name = App.Path & "\books.mdb"

    Set m_DBConnection = New ADODB.Connection
    ConnectString = _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & db_name & ";" & _
        "Persist Security Info=False"
    m_DBConnection.ConnectionString = ConnectString

    Set rs = m_DBConnection.Execute( _
        "SELECT TOP 1 * FROM Books", , adCmdText)

    num_cols = rs.Fields.Count
    ReDim m_FieldNames(0 To num_cols - 1)
    ReDim m_DataTypes(0 To num_cols - 1)
    For c = 0 To num_cols - 1
        m_FieldNames(c) = rs.Fields(c).Name
        m_DataTypes(c) = rs.Fields(c).Type
    Next c
    ' Add the field names to the field ComboBoxes.
    For i = cboField.LBound To cboField.UBound
        cboField(i).AddItem ""
        For c = 0 To num_cols - 1
            cboField(i).AddItem m_FieldNames(c)
        Next c
    Next i

End Sub
Form_Load creates a database connection object. It then selects a record from the Books database table so it can get information about that table. It saves the table's column names in the m_FieldNames array and it saves the column data types in the m_DataTypes array. It then uses the m_FieldNames array to initialize the left column of combo boxes that display the column names.

When the user makes selections and clicks Query, the following code executes.

' Build and execute the appropriate query.
Private Sub cmdQuery_Click()
Dim query As String
Dim where_clause As String
Dim i As Integer
Dim delimiter As String
Dim value As String
Dim column_num As Integer

    where_clause = ""
    For i = cboField.LBound To cboField.UBound
        ' See if the field and operator are non-blank.
        If ((cboField(i).ListIndex <= 0) Or _
            (cboOperator(i).ListIndex <= 0)) _
            ' Don't use this row. Clear it to prevent
            ' confusion.
            cboField(i).ListIndex = 0
            cboOperator(i).ListIndex = 0
            txtValue(i).Text = ""
            ' See what delimiter we need for this type of
            ' field.
            delimiter = ""
            value = txtValue(i).Text
            column_num = cboField(i).ListIndex - 1
            If (m_DataTypes(column_num) = adVarChar) Or _
               (m_DataTypes(column_num) = adChar) Or _
               (m_DataTypes(column_num) = adVarWChar) Or _
               (m_DataTypes(column_num) = adWChar) _
                delimiter = "'"
                value = Replace$(value, "'", "''")
            ElseIf (m_DataTypes(column_num) = adDate) Or _
                   (m_DataTypes(column_num) = adDBDate) Or _
                   (m_DataTypes(column_num) = adDBTime) _
                ' Use # for Access, ' for SQL Server.
                delimiter = "#"
            End If

            ' Add the constraint to the WHERE clause.
            where_clause = where_clause & " AND " & _
                cboField(i).List(cboField(i).ListIndex) & "" & _
                    "" & _
                cboOperator(i).List(cboOperator(i).ListIndex) _
                    & " " & _
                delimiter & value & delimiter
        End If  ' if field and operator are selected.
    Next i  ' cboField.LBound To cboField.UBound

    ' If where_clause is non-blank, remove the initial " AND
    ' ".
    If Len(where_clause) > 0 Then where_clause = _
        Mid$(where_clause, 6)

    ' Compose the query.
    query = "SELECT * FROM Books"
    If Len(where_clause) > 0 Then query = query & " WHERE " _
        & where_clause
    ' Debug.Print "Query: " & query

    ' Connect the ADODC to the database.
    adodcBooks.ConnectionString = ConnectString
    adodcBooks.RecordSource = query

    ' Bind the ADODC to the DataGrid.
    Set dgBooks.DataSource = adodcBooks
End Sub
This code loops through the combo boxes. If the user has selected a field name and a corresponding operator, the code adds a condition to the WHERE clause it is building. If the user leaves a field name or operator blank, the program blanks the other corresponding controls so it doesn't look like they may be contributing to the final WHERE clause.

When it builds each piece of the WHERE clause, the code uses delimiters for string and date values. It uses a single quote ' for strings, and it uses a # for dates. (Access databases require # delimiters for dates. SQL Server databases require ' delimiters for dates.)

After is has built the WHERE clause, the program composes a final query. It sets the ADO Data Control's RecordSource property to the query and refreshes the control. Finally it makes the DataGrid control use the ADO Data Control as its DataSource.

Copyright © 1997-2010 Rocky Mountain Computer Consulting, Inc.   All rights reserved.