Browse All Articles > Progressive search in DataGridView
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.")
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.PlaySyst
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
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.
Comments (0)