Access DB, using combobox to filter

[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.

[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?

[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.

[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.

[Sevyt]

alright thanks a lot, gonna give this a try.

[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.