Populate datagrid based upon filter & search criteria

Having a lot of trouble working with the datagrid control in VB.NET.

In the Form1_Load event I have the datagrid populating data from a table in Access. (no problems here).
Imports System.Data
Imports System.Data.OleDb
Public Class Form1
Private Const SELECT_STRING As String = "Select * From Students Order By LastName"
Private Const CONNECT_STRING As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\StudentAthletes.mdb"
Private m_DataSet As New DataSet

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim myConn As OleDbConnection = New OleDbConnection

        myConn.ConnectionString = CONNECT_STRING 'connString
        Dim data_adapter As OleDbDataAdapter = New OleDbDataAdapter(SELECT_STRING, myConn)
        data_adapter.Fill(m_DataSet, "Students")
        DataGridView1.DataSource = m_DataSet.Tables(0)
        lblCount.Text = DataGridView1.RowCount & " students"

        With cboData
            .SelectedIndex = 0
        End With
    End Sub
Now that the datagrid is completely populated, I want the user to select an item from a combobox that needs to be filtered. The items in the combobox will be fields from a table in Access.
Next I want the user to enter search criteria in a text box. Then click the 'search' button.

I want the datagrid to populate based on what the user selected.

Example: User selects "FirstName" from the combobox and keys "Jo" in the textbox. My code
should re-populate the datagrid control with anybody's "FirstName" that begins with "Jo".

Here is my code: (not working....actually it only works when they filter by LastName??)
Private Sub buttonSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles buttonSearch.Click
        Dim strField As String
        Dim strValue As String
        Dim strQuery As String
        Dim strConnection As String
        Dim myConn As OleDbConnection = New OleDbConnection

        Dim ds As DataSet = New DataSet
        Dim dt As New DataTable

        strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\StudentAthletes.mdb"

        strField = cboData.SelectedItem
        strValue = txtSearch.Text
        strQuery = "Select * From Students"
        strQuery = strQuery & " Where " & strField
        strQuery = strQuery & " Like " & "'" & strValue & "%'"

        Using Conn As New OleDbConnection(strConnection)
            Using Cmd As New OleDbCommand(strQuery, Conn)
                Using da As New OleDbDataAdapter(Cmd)
                End Using
            End Using
        End Using

        DataGridView1.DataSource = dt
        'myConn.ConnectionString = strConnection 'connString
        'Dim data_adapter As OleDbDataAdapter = New OleDbDataAdapter(strQuery, myConn)
        'data_adapter.Fill(ds, "Students")
        'DataGridView1.DataSource = ds.Tables(0)
        'lblCount.Text = DataGridView1.RowCount & " students"
   End Sub

I can't figure out how to get the datagrid to display each new query that might be ran.
I've tried troubleshooting many things. What am I missing?


Who is Participating?
vb_jonasConnect With a Mentor Commented:
Hi! Yes, I've tried that. If you pause the program on the row "Using Conn As New OleDbConnection(strConnection)" what is the value of strQuery then? "Select * FROM Students Where City Like 'D%'" ?

Also check the datatypes, are the fields perhaps of the memo type?
Hi! Your code should work, I tried it (against a dummy database). What's happening when you run it?

stltodaycomAuthor Commented:
When the Form Loads it populates the datagrid just fine.
If the user selects LastName from the dropdown box and wants to see all
Students with a last name beginning with 'D', they enter 'D' in the search text box,
and the datagrid will populate only the Students with a lastname that begins with 'D'. This works fine.

Now, if the user selects FirstName from the dropdown box and enters 'J' in the search box the datagrid
will NOT populate those Students with a first name beginning with J.

Same with the City and State. It will not filter if one of these values are choses. It only fitlers when the user select LastName from the dropdown box and searches by lastname in the text box.

Try and filter by FirstName in your mock database.

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

stltodaycomAuthor Commented:
When I put a breakpoint on line "Using Conn ..." the value of strQuery is always what it should be, because I am assigning the SQL string to strQuery before I establish a connection.

This just got more weird. I can filter by LastName and City, but I can't filter by FirstName or State. (Pay no attention to the other fields I am loading in the dropdown box.)

It's working as designed on LastName and City. When you try to run a filter/search against FirstName it returns zero records. Same thing with State.

Select * FROM Students Where FirstName Like 'D%'" : this displays zero records even though there are studetns whose First name begins with D.

Select * FROM Students Where State Like 'M%'": this also returns zero records even though there are many states beginning with M.

If I don't specify a search value when I select FirstName or State, it returns all of the records.
Select * FROM Students Where FirstName Like '%'" - returns all of the records that have a FirstName
Select * FROM Students Where State Like '%'" -  returns all of the records that have a State

The database fields all have the same datatype: Text / the length are all 255.

Can you filter by FirstName and/or State?

stltodaycomAuthor Commented:
...okay, problem solved. There was a freaken' space before all of the FirstName and State data.
When I did the initial import into Access of the customer's Excel spreadsheet I didn't even notice the [space] in the data.

Now everything is working as designed.

Thanks for your help with this!
stltodaycomAuthor Commented:
vb_jonas took the time to create a dummy database and used my script to help troubleshoot my question. Thanks for the effort.
Ah, good you found it, thanks for the points! Happy programming!
All Courses

From novice to tech pro — start learning today.