Progressive search in DataGridView

ElrondCT
CERTIFIED EXPERT
Published:
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

My.Computer.Audio.PlaySystemSound(Media.SystemSounds.Beep)

with

Microsoft.VisualBasic.Beep

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
                              dgv.Select()
                      	ClearSearch()
                              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
                                      Else
                                          ' 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)
                                          Else
                                              strSearch = Mid(strSearch, 1, Len(strSearch) - 1)
                                          End If
                                          If Len(strSearch) = 0 Then
                                              intSearch = 0
                                              ClearSearch()
                                          Else
                                              ' 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
                                      ClearSearch()
                              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
                                  Return
                              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
                              Else
                                  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. 
                              ClearSearch()
                          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
                              Else
                                  Return dv.Find(drFind(0).fldKey)
                              End If
                          End Function
                      
                      End Class
                      

Open in new window

3
9,851 Views
ElrondCT
CERTIFIED EXPERT

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.