none
VB.NET Query database from a combo-box?

    Question

  • Hi all,

    I'm new to programming.

    PROBLEM: I want to only show specific database records based on serveral combo-box inputs from a user.

    I'm looking to query an Access database from within a form I've created for viewing simple Jpegs of CAD files and pointcloud data.

    I've never worked with table adapters, SQL statements, or much in the way of databases before and I'm lost.

    • Do I add a new query and do what I want from a configuration wizard somehow? That method gave me a toolstrip button with no combo-box.
    • Should the combo-box be bound to the table or should it stand alone?
    • Can this be done without the use of a wizard (purely in an event)

    I've been told what I'm looking to do is rather simple, I'm just unable to find syntax that has worked for me.

    Any help is appreciated,

    cdebruin

    Tuesday, May 29, 2012 3:57 PM

Answers

  • One method would be to load ComboBox controls with distinct values for specific columns from the database table you want to query against then have the user select from these ComboBox controls and build a SQL statement. The example below is simple, provide distinct values for two columns in a table in the form load event. In the Button1 click event we check the user selections and build a SQL statement followed by executing the statement against the backend database. Granted there are many different ideas on how to append parts to the select statement in regards to the WHERE clause, this is one. In the end a DataGridView displays the data from out select statement.

    Public Class Form1
        Private Builder As New OleDb.OleDbConnectionStringBuilder With
            {
                .Provider = "Microsoft.ACE.OLEDB.12.0",
                .DataSource = IO.Path.Combine(Application.StartupPath, "Database1.accdb")
            }
        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            Using cn As New OleDb.OleDbConnection With
                    {
                        .ConnectionString = Builder.ConnectionString
                    }
                Using cmd As New OleDb.OleDbCommand With {.Connection = cn}
                    cmd.CommandText = "SELECT DISTINCT ContactTitle FROM Customer ORDER BY ContactTitle"
                    Dim dt As New DataTable
                    cn.Open()
                    dt.Load(cmd.ExecuteReader)
                    cboContactType.DisplayMember = "ContactTitle"
                    cboContactType.DataSource = dt
                    dt = New DataTable
                    cmd.CommandText = "SELECT DISTINCT Country FROM Customer ORDER BY Country"
                    dt.Load(cmd.ExecuteReader)
                    cboCountry.DisplayMember = "Country"
                    cboCountry.DataSource = dt
                    cboContactType.SelectedIndex = -1
                    cboCountry.SelectedIndex = -1
                End Using
            End Using
        End Sub
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim SelectStatement As String = "SELECT * FROM Customer "
            If cboContactType.SelectedIndex <> -1 Then
                SelectStatement &= "WHERE ContactTitle = '" & cboContactType.Text & "'"
            End If
            If cboCountry.SelectedIndex <> -1 Then
                If SelectStatement.Contains("WHERE ContactTitle") Then
                    SelectStatement &= " AND Country = '" & cboCountry.Text & "'"
                Else
                    SelectStatement &= " WHERE Country = '" & cboCountry.Text & "'"
                End If
            End If
            Console.WriteLine("[{0}]", SelectStatement)
            Using cn As New OleDb.OleDbConnection With
                    {
                        .ConnectionString = Builder.ConnectionString
                    }
                Using cmd As New OleDb.OleDbCommand With {.Connection = cn}
                    cmd.CommandText = SelectStatement
                    Dim dt As New DataTable
                    cn.Open()
                    dt.Load(cmd.ExecuteReader)
                    DataGridView1.DataSource = Nothing
                    DataGridView1.DataSource = dt
                End Using
            End Using
        End Sub
    End Class
    Another idea is to simply load the entire table then by using DataTable Select or assigning the DataTable to a BindingSource use the Filter method of the BindingSource to filter data based on the ComboBox selections as done in the above example but instead of running select statements against the physical database do it against the DataSource of the DataGridView which can be cast from type Object to what you set the DataSource too i.e. DataTable or BindingSource in this case.


    KSG

    • Marked as answer by cdebruin Wednesday, May 30, 2012 6:24 PM
    Tuesday, May 29, 2012 4:24 PM
    Moderator

All replies

  • You have never worked with it, but want to do it in one time,

    Like telling that you want to fly an Airbus 380 but never have driven a scooter (in Dutch an step before you misunderstand).

    Try first to get an image visible using a database and do then the next step.

    Here a sample on our website, but there are endless samples for that.

     http://www.vb-tips.com/ExecuteScalarImage.ASPX


    Success
    Cor



    Tuesday, May 29, 2012 4:17 PM
  • One method would be to load ComboBox controls with distinct values for specific columns from the database table you want to query against then have the user select from these ComboBox controls and build a SQL statement. The example below is simple, provide distinct values for two columns in a table in the form load event. In the Button1 click event we check the user selections and build a SQL statement followed by executing the statement against the backend database. Granted there are many different ideas on how to append parts to the select statement in regards to the WHERE clause, this is one. In the end a DataGridView displays the data from out select statement.

    Public Class Form1
        Private Builder As New OleDb.OleDbConnectionStringBuilder With
            {
                .Provider = "Microsoft.ACE.OLEDB.12.0",
                .DataSource = IO.Path.Combine(Application.StartupPath, "Database1.accdb")
            }
        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            Using cn As New OleDb.OleDbConnection With
                    {
                        .ConnectionString = Builder.ConnectionString
                    }
                Using cmd As New OleDb.OleDbCommand With {.Connection = cn}
                    cmd.CommandText = "SELECT DISTINCT ContactTitle FROM Customer ORDER BY ContactTitle"
                    Dim dt As New DataTable
                    cn.Open()
                    dt.Load(cmd.ExecuteReader)
                    cboContactType.DisplayMember = "ContactTitle"
                    cboContactType.DataSource = dt
                    dt = New DataTable
                    cmd.CommandText = "SELECT DISTINCT Country FROM Customer ORDER BY Country"
                    dt.Load(cmd.ExecuteReader)
                    cboCountry.DisplayMember = "Country"
                    cboCountry.DataSource = dt
                    cboContactType.SelectedIndex = -1
                    cboCountry.SelectedIndex = -1
                End Using
            End Using
        End Sub
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim SelectStatement As String = "SELECT * FROM Customer "
            If cboContactType.SelectedIndex <> -1 Then
                SelectStatement &= "WHERE ContactTitle = '" & cboContactType.Text & "'"
            End If
            If cboCountry.SelectedIndex <> -1 Then
                If SelectStatement.Contains("WHERE ContactTitle") Then
                    SelectStatement &= " AND Country = '" & cboCountry.Text & "'"
                Else
                    SelectStatement &= " WHERE Country = '" & cboCountry.Text & "'"
                End If
            End If
            Console.WriteLine("[{0}]", SelectStatement)
            Using cn As New OleDb.OleDbConnection With
                    {
                        .ConnectionString = Builder.ConnectionString
                    }
                Using cmd As New OleDb.OleDbCommand With {.Connection = cn}
                    cmd.CommandText = SelectStatement
                    Dim dt As New DataTable
                    cn.Open()
                    dt.Load(cmd.ExecuteReader)
                    DataGridView1.DataSource = Nothing
                    DataGridView1.DataSource = dt
                End Using
            End Using
        End Sub
    End Class
    Another idea is to simply load the entire table then by using DataTable Select or assigning the DataTable to a BindingSource use the Filter method of the BindingSource to filter data based on the ComboBox selections as done in the above example but instead of running select statements against the physical database do it against the DataSource of the DataGridView which can be cast from type Object to what you set the DataSource too i.e. DataTable or BindingSource in this case.


    KSG

    • Marked as answer by cdebruin Wednesday, May 30, 2012 6:24 PM
    Tuesday, May 29, 2012 4:24 PM
    Moderator
  • What comes to my mind first is using LINQ. It's odd to work with but quite powerful.

    Please call me Frank :)

    Tuesday, May 29, 2012 4:25 PM
  • I agree this is a great option, with that in mind here is one way to create a select statement and append a where to the select of a LINQ statement. Note you can add many conditions using the same logic method shown below.

    Imports System.Data.OleDb
    Public Class Form1
        Private dtCustomers As New DataTable
        Private Sub cmdCloseForm_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdCloseForm.Click
            Close()
        End Sub
        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            Dim SQL = _
                <SQL>
                    SELECT 
                        CompanyName, 
                        ContactName, 
                        ContactTitle, 
                        Country 
                    FROM 
                        Customer
                </SQL>
            Using cn As New OleDbConnection(My.Settings.ConnectionString)
                cn.Open()
                With New OleDbDataAdapter(New OleDbCommand(SQL.Value, cn)).Fill(dtCustomers) : End With
            End Using
            cboCountries.Items.AddRange( _
                ( _
                    From Cust In dtCustomers.AsEnumerable _
                    Select Country = Cust.Field(Of String)("Country") _
                    Distinct _
                ).ToArray _
            )
            cboCountries.Sorted = True
        End Sub
        Private Sub cboCountries_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboCountries.SelectedIndexChanged
            cmdWhere.Enabled = cboCountries.Text.Length > 0
        End Sub
        Private Sub cmdWhere_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdWhere.Click
            DataGridView1.DataSource = Nothing
            Dim Query = From Cust In dtCustomers.AsEnumerable _
                    Select New With { _
                            .CustName = Cust.Field(Of String)("CompanyName"), _
                            .ContactName = Cust.Field(Of String)("ContactName"), _
                            .Country = Cust.Field(Of String)("Country")}
            ' Use original LINQ above and append Where via Lambda
            Query = Query.Where(Function(c) c.Country = cboCountries.Text)
            DataGridView1.DataSource = Query.ToList
        End Sub
    End Class


    KSG

    Tuesday, May 29, 2012 4:41 PM
    Moderator
  • Thanks so much for the help.

    "Another idea is to simply load the entire table then by using DataTable Select or assigning the DataTable to a BindingSource use the Filter method of the BindingSource to filter data based on the ComboBox selections as done in the above example but instead of running select statements against the physical database do it against the DataSource of the DataGridView which can be cast from type Object to what you set the DataSource too i.e. DataTable or BindingSource in this case."

    I wound up using a bastardized version of this. I guess flying that Airbus wasn't so difficult after all...

    Wednesday, May 30, 2012 6:27 PM
  • Thanks so much for the help.

    "Another idea is to simply load the entire table then by using DataTable Select or assigning the DataTable to a BindingSource use the Filter method of the BindingSource to filter data based on the ComboBox selections as done in the above example but instead of running select statements against the physical database do it against the DataSource of the DataGridView which can be cast from type Object to what you set the DataSource too i.e. DataTable or BindingSource in this case."

    I wound up using a bastardized version of this. I guess flying that Airbus wasn't so difficult after all...

    Since you want this route take a look at the following article at Code Project

    http://www.codeproject.com/Articles/33786/DataGridView-Filter-Popup


    KSG

    Wednesday, May 30, 2012 6:40 PM
    Moderator