Member Avatar for Sevyt

I am writing a small program to quickly search through a archive.
But i cannot figure out how to get it to filter.

I got my regular fields like firstname,lastname, gender, birthyear, street, city, zip, phone.
Which i can search through by just entering one of these fields.

Then i got a dropdown box which i was hoping to use a filter on.
Every file is in a cabinet, but since those are somewhat alphabetically i have more then 1 cabinet for certain letters.
This is why i wanted to use a filter to get a list of all people in a certain cabinet.

So i want my dropdownbox to automatically show the cabinets that are in the database(for example A...Z).
If i choose B from the dropdownbox i want to see all the names that are in that cabinet in my datagridview.


Is there any way to do this?

Hope i am making somewhat sense.

  • 2 Contributors
  • 5 Replies
  • 254 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by Reverend Jim

Dani AI

Generated

Short practical plan (two common approaches) that builds on ’s suggestion and fits an Access backend.

Server-side (requery) — best for large tables or when you want always-current results:

  • Populate the combobox from the database using a DISTINCT query on the cabinet column, then bind it (DisplayMember/ValueMember).
  • On combobox selection, run a parameterized query that returns only rows for that cabinet and bind the result to the DataGridView. Use OleDb with parameter placeholders (order matters) and dispose connections with Using blocks.
' get distinct cabinets
Using cn As New OleDb.OleDbConnection(connString)
  Dim da As New OleDb.OleDbDataAdapter("SELECT DISTINCT [Cabinet] FROM [People] ORDER BY [Cabinet]", cn)
  Dim dt As New DataTable : da.Fill(dt)
  cmbCabinet.DisplayMember = "Cabinet" : cmbCabinet.ValueMember = "Cabinet" : cmbCabinet.DataSource = dt
End Using

' on selection change: parameterized requery
Using cn As New OleDb.OleDbConnection(connString)
  Dim cmd As New OleDb.OleDbCommand("SELECT * FROM [People] WHERE [Cabinet] = ?", cn)
  cmd.Parameters.AddWithValue("?", cmbCabinet.SelectedValue)
  Dim da As New OleDb.OleDbDataAdapter(cmd) : Dim dt As New DataTable : da.Fill(dt)
  dgvResults.DataSource = dt
End Using

Client-side (load once, then filter) — good for smaller datasets and snappier UI:

  • Load all records into a DataTable once, bind a BindingSource (or DefaultView) to the DataGridView, and build the combobox list from the table’s distinct cabinet values (DataTable.DefaultView.ToTable(True,"Cabinet")).
  • Filter locally on selection with BindingSource.Filter or DataView.RowFilter (remember to escape single quotes in values).

Troubleshooting / tips:

  • For Access .accdb use the ACE provider; for old .mdb use Jet. Make sure the Cabinet field type (text vs numeric) matches your filter syntax. Trim whitespace or normalize case if values vary. Add an “All” entry to the combobox to clear filters. Avoid string concatenation in SQL—use parameters to prevent unexpected behavior.

Recommended Answers

All 5 Replies

Member Avatar for Reverend Jim

Are you searching an archive, a file hierarchy or a database? You seem to be referencing all three. And I am not sure what you mean by a cabinet? What, exactly, is the format of the stuff (for lack of a more precise term) that you want to search?

Member Avatar for Sevyt

Sorry i made it a bit confusing i noticed.

I want to search/filter through a access database.

With cabinet i mean a actual physical archiving cabinet.
So the program is used to make life a bit easier to find whatever file i need.

The access database has all the relevant information.
Including a cabinet number.

But i would like to make a filter, so that when for example i choose cabinet E in my dropdown box.
I will get a list of all the people in that cabinet.

Member Avatar for Reverend Jim

Here is one way.

Private Sub cmbLetters_TextChanged(sender As System.Object, e As System.EventArgs) Handles cmbLetters.TextChanged

    Dim conn As New ADODB.Connection
    Dim rset As New ADODB.Recordset
    Dim init As String = DirectCast(sender, ComboBox).Text

    conn.Open("Driver={SQL Server};Server=jim-pc\sqlexpress;Database=NorthWind;Trusted_Connection=yes;")
    rset.Open("select CustomerID from Customers where CustomerID like '" & init & "%'", conn, ADODB.CursorTypeEnum.adOpenForwardOnly)

    cmbNames.Items.Clear()

    Do Until rset.EOF
        cmbNames.Items.Add(rset(0).Value)
        rset.MoveNext()
    Loop

    rset.Close()
    conn.Close()

End Sub

cmbLetters contains the letters A to Z. Replace NorthWind (MS sample DB) and CustomerID with your specific items. There are likely other ways to do this by linking the control directly to a datatable or whatever but I believe this is the most straightforward. If your names are not in alphabetical order you can add "order by CustomerID ASC" to the end of the query.

Edited by Reverend Jim because: n/a
Member Avatar for Sevyt

alright thanks a lot, gonna give this a try.

Member Avatar for Reverend Jim

I'll add to my earli
er caveat, most of my database code was written in vbScript so I leaned heavily on ADO (which was the technology Microsoft was pushing at the time). Now there are other methods available to VB users that may be more flexible. I tend to stay with what I am familiar. I understand that even streams use recordsets as their underlying access, their main advantage being that they allow you to process records immediately instead of having to wait for the entire recordset to be retrieved.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.