Progressive search in DataGridView

Published on
16,507 Points
3 Endorsements
Last Modified:
You've probably seen a table that allows the user to start typing and moves immediately to the row whose key field starts with the characters you've typed. A ComboBox does a very rudimentary form of this: you type the first character, and it moves to the first match. But a progressive search is more powerful; as you type, say, S, M, I, T, with a list of names, it'll go first (in my local phone directory) to Saab, then to Smale, then to Smialkowski, then to Smith. The full list of names is still displayed, so the user can go up or down from wherever he lands with the search (unlike versions of progressive searches that filter the rows). You can undo a letter by pressing Backspace.

While I've seen progressive searches in other languages that will highlight the letters entered in the list itself, there doesn't seem to be an easy way to highlight portions of text inside a DGV cell. So instead, this routine displays the text it's searching for in a separate label that is displayed elsewhere on the form (I put mine just above the key field of the DGV). One complication is that many punctuation characters must be enclosed in brackets, or they'll be considered to have special meanings, not to be characters to search for. The label display won't be necessarily the same as the internal search string.

This requires the DGV to use a DataSource that has the .Select method, such as a DataTable. Note that the search is case-insensitive, which is inherent in the use of LIKE in the Select statement.

The basic methodology is:

When the user enters a character, it is processed by the KeyPress event handler. Add it to the search string (with brackets around it if it's one of the punctuation characters that need it). Search the DataTable for records with fldKey that start with the letter(s) entered; if one or more records are found, move the current cell (which is highlighted on screen) to the first matching row. If no records are found, beep and ignore that character. If a Backspace is entered, remove the last character from the search string and find the first matching record of the shortened search string (if no search string is left, move to the first record). If the user moves via the up & down arrow, Page Up/Page Down, or clicking with the mouse, discard the search string and recognize the move.

This can't really be made into a generalized class. You will need to use the code shown and customize it for your particular situation. In particular, the following objects need to be properly renamed for your code:

dgv - the DataGridView
ds - the strongly typed DataSet that contains the DataTable dt
dsA - an instance of DataSet ds
dt - the DataTable of dsA which is the underlying source of data
dv - a DataView which is dgv's DataSource
fldKey - the field/column in DataTable dt on which to search; dgv needs to be sorted on this field
lblSearch - A label saying "Searching for:"
lblSearchDisplay - A label displaying the search string (without brackets on any punctuation)

This code is written for Visual Basic/Visual Studio 2005 or later. For VB 2002/2003, DataGrid (instead of DataGridView) operations are essentially the same for this purpose. I believe the only change needed is to replace




which probably won't sound quite the same. (To play a .wav file, see the VS documentation for "sounds, playing example.")
Public Class frm
    Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "
    ' This is omitted, because it'll be created when you set up the form
#End Region

    Private strSearch As String                         ' Search string, if typing in a lease number
    Private dv As DataView
    Private blnBackSpace As Boolean                     ' User typed a backspace to shorten search string

    Private Sub frm_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
        dv = dsA.dt.DefaultView
        dv.Sort = "fldKey"
        dgv.DataSource = dv
        blnBackSpace = False
    End Sub

    Private Sub dgv_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles dgv.KeyDown
        ' This event handler works with dgv_KeyPress to provide a progressive search in the DataGridView based on
        ' fldKey. This event handler responds to Backspace, up & down arrow, and Page Up/Page Down, which
        ' cannot be processed by the KeyPress event.
        Dim intSearch As Integer
        Select Case e.KeyCode
            Case Keys.Back
                If Len(strSearch) = 0 Then
                    My.Computer.Audio.PlaySystemSound(Media.SystemSounds.Beep)  ' Play error bell
                    ' If character deleted was an escaped special character, remove the brackets as well
                    If Mid(strSearch, Len(strSearch)) = "]" Then
                        strSearch = Mid(strSearch, 1, Len(strSearch) - 3)
                        strSearch = Mid(strSearch, 1, Len(strSearch) - 1)
                    End If
                    If Len(strSearch) = 0 Then
                        intSearch = 0
                        ' Find what's the first row that matches the new search string. We know there will be a match,
                        ' because this is a wider search than what's currently active.
        		intSearch = FindMatch()
                        lblSearchText.Text = Mid(lblSearchText.Text, 1, Len(lblSearchText.Text) - 1)
                    End If
                    dgv.CurrentCell = dgv.Item(0, intSearch)		' *** This assumes fldKey is the first column of the row
                End If
                blnBackSpace = True
            Case Keys.Down, Keys.Up, Keys.PageDown, Keys.PageUp
                ' If we use one of these keys, cancel the search process
        End Select
    End Sub

    Private Sub dgv_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles dgv.KeyPress
        ' Progressive search for lease number. Add each character typed to the search string and look for a match
        ' The current search string is displayed in lblSearchDisplay.
        Dim intSearch As Integer
        If blnBackSpace = True Then                                     ' Backspace is handled by KeyDown, but Handled property
            e.Handled = True                                            '  has to be set here 
            blnBackSpace = False
        End If
        Dim strSaveSearch As String = strSearch                         ' Save in case of error
        ' Add newly typed character to list. If it's a special character, we need to surround with brackets
        ' or it'll be treated as a wildcard or some other special meaning.
        strSearch &= CStr(IIf(InStr("~()#\/=><+-*%&|^'""[]", e.KeyChar) > 0, "[" & e.KeyChar & "]", e.KeyChar))
        intSearch = FindMatch()                                         ' Look for a match
        If intSearch = -1 Then                                          ' No match
            My.Computer.Audio.PlaySystemSound(Media.SystemSounds.Beep)  ' Play bell
            strSearch = strSaveSearch                                   ' Revert to prior search string
            dgv.CurrentCell = dgv.Item(0, intSearch)		' *** This assumes fldKey is the first column of the row
            lblSearch.Visible = True
            lblSearchText.Visible = True
            lblSearchText.Text &= e.KeyChar
        End If
    End Sub

    Private Sub dgv_MouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles dgv.MouseClick
        ' If user clicks on a row, cancel the search process. 
    End Sub

    Private Sub ClearSearch()
        lblSearch.Visible = False
        lblSearchText.Visible = False
        lblSearchText.Text = ""
        strSearch = ""
    End Sub

    Private Function FindMatch() As Integer
        Dim drFind() As ds.dtRow
        drFind = CType(dsA.dt.Select("fldKey LIKE '" & strSearch & "*'", "fldKey ASC"), ds.dtRow())
        If drFind.Count = 0 Then
            Return -1
            Return dv.Find(drFind(0).fldKey)
        End If
    End Function

End Class

Open in new window


Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Join & Write a Comment

This video tutorial shows you the steps to go through to set up what I believe to be the best email app on the android platform to read Exchange mail.  Get the app on your phone: The first step is to make sure you have the Samsung Email app on your …
A query can call a function, and a function can call Excel, even though we are in Access. This is Part 2, and steps you through the VBA that "wraps" Excel functionality so we can use its worksheet functions in Access. The declaration statement de…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month