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 .NET
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 .NET.
Keywordsdatabase, DataGridView, DataTable, DataAdapter, OleDbDataAdapter, data adapter, bind, DataSet, DataTable, Visual Basic .NET, VB.NET

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. The Form1_Load event handler calls the PrepareForm method, which gets the form ready for work.

' The connection object.
Private Conn As OleDbConnection

' The table's column names.
Private ColumnNames As New List(Of String)()
Private TableName As String = ""

' The query controls.
Private CboField(), CboOperator() As ComboBox
Private TxtValue() As TextBox
Private DataTypes As New List(Of Type)()

' Make a list of the table's fields.
Private Sub Form1_Load(ByVal sender As System.Object, ByVal _
    e As System.EventArgs) Handles MyBase.Load
    ' Compose the database file name.
    ' This assumes it's in the executable's directory.
    Dim db_name As String = Application.StartupPath & _

    ' Prepare the form for use.
    PrepareForm(db_name, "BookInfo")
End Sub

' Make a list of the table's field names and prepare the
' first ComboBox.
Private Sub PrepareForm(ByVal db_name As String, ByVal _
    table_name As String)
    TableName = table_name

    ' Make the connection object.
    Conn = New OleDbConnection( _
        "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & db_name & ";" & _
        "Mode=Share Deny None")

    ' Get the fields in the BookInfo table.
    ' Make a command object to represent the command.
    Dim cmd As New OleDbCommand()
    cmd.Connection = Conn
    cmd.CommandText = "SELECT TOP 1 * FROM " & table_name

    ' Open the connection and execute the command.
        ' Open the connection.

        ' Execute the query. The reader gives access to the
        ' results.
        Dim reader As OleDbDataReader = cmd.ExecuteReader()

        ' Get field information.
        Dim schema As DataTable = reader.GetSchemaTable()
        For Each schema_row As DataRow In schema.Rows
            ColumnNames.Add(schema_row.Field(Of _
            DataTypes.Add(schema_row.Field(Of _
            '@ Console.WriteLine(schema_row.Field(Of
            ' Type)("DataType").ToString())
        Next schema_row

        ' Initialize the field name ComboBoxes.
        CboField = New ComboBox() {cboField0, cboField1, _
            cboField2, cboField3}
        CboOperator = New ComboBox() {cboOperator0, _
            cboOperator1, cboOperator2, cboOperator3}
        TxtValue = New TextBox() {txtValue0, txtValue1, _
            txtValue2, txtValue3}
        For i As Integer = 0 To CboField.Length - 1
            CboField(i).Items.Add("")           ' Allow a
                ' blank field choice.
            For Each field_name As String In ColumnNames
            Next field_name
            CboField(i).SelectedIndex = 0       ' Select the
                ' blank choice.
            CboOperator(i).SelectedIndex = 0    ' Select the
                ' blank choice.
        Next i
    Catch ex As Exception
        MessageBox.Show("Error reading " & table_name & "" & _
            "column names." & vbCrLf & ex.Message)
        ' Close the connection whether we succeed or fail.
    End Try
End Sub
PrepareForm creates a database connection object. It then selects a record from the indicated database table so it can get information about that table. It saves the table's column names in the ColumnNames list and it saves the column data types in the DataTypes list. It then uses the ColumnNames list 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 btnQuery_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles btnQuery.Click
    Dim where_clause As String = ""
    For i As Integer = 0 To CboField.Length - 1
        ' See if the field and operator are non-blank.
        If ((CboField(i).SelectedIndex <= 0) OrElse _
            (CboOperator(i).SelectedIndex <= 0)) _
            ' Don't use this row. Clear it to prevent
            ' confusion.
            CboField(i).SelectedIndex = 0
            CboOperator(i).SelectedIndex = 0
            ' See what delimiter we need for this type of
            ' field.
            Dim delimiter As String = ""
            Dim value As String = TxtValue(i).Text
            Dim column_num As Integer = _
                CboField(i).SelectedIndex - 1
            If (DataTypes(column_num) Is _
                GetType(System.String)) Then
                delimiter = "'"
                value = value.Replace("'", "''")
            ElseIf (DataTypes(column_num) Is _
                GetType(System.DateTime)) Then
                ' Use # for Access, ' for SQL Server.
                delimiter = "#"
            End If

            ' Add the constraint to the WHERE clause.
            where_clause &= " AND " & _
                CboField(i).SelectedItem.ToString() & " " & _
                CboOperator(i).SelectedItem.ToString() & " " _
                    & _
                delimiter & value & delimiter
        End If  ' if field and operator are selected.
    Next i  ' For i As Integer = 0 To CboField.Length - 1

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

    ' Compose the query.
    Dim query As String = "SELECT * FROM " & TableName
    If (where_clause.Length > 0) Then query &= " WHERE " & _
    '@ Console.WriteLine("Query: " & query)

    ' Create a DataAdapter to load the data.
    Dim data_adapter As New OleDbDataAdapter(query, Conn)

    ' Create a DataTable.
    Dim data_table As New DataTable()
    Catch ex As Exception
        MessageBox.Show("Error executing query " & query & _
            vbCrLf & ex.Message)
    End Try

    ' Bind the DataGridView to the DataTable.
    dgvBookInfo.DataSource = data_table
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 and executes it. It uses a data adapter to load the results into a DataTable and sets the form's DataGridView control's DataSource property to the DataTable so the user can see the result.

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