[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Progressive search in DataGridView

Posted on 2009-12-23
8
Medium Priority
?
1,112 Views
Last Modified: 2012-05-08
In VB 2008, I have a DataGridView with a list of item numbers. I would like to implement a progressive search through the list, so that if the user types "L", the DataGridView immediately jumps to the first entry that starts with L. Then if the user types "I", it jumps to the first entry that starts with LI. And so on (if no match is found, the program beeps).

I've tried setting up code in the KeyPress event handler for the DataGridView using the Find method of the DataView that is the DataSource. However, Find only does an exact match, not a "begins with" match, so this only works with single-character key values--not very useful! I can't figure out how to do a "begins with" search and get the result selected in the DataGridView.

I've seen some code online that does progressive filtering of the list based on what you've typed (i.e., only the records that match are displayed). However, that's not what I'm looking for: I want the entire list displayed, but the first matching entry highlighted. (Ideally, I'd like to have the letters typed highlighted in the selected cell, but that's probably a separate topic.)
Private Sub dgvNumbers_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles dgvNumbers.KeyPress
        Dim intSearch As Integer
        Dim dgcCurrent As DataGridViewCell
        strSearch &= e.KeyChar
        intSearch = dvNumbers.Find(strSearch)
        If intSearch = -1 Then
            My.Computer.Audio.PlaySystemSound(Media.SystemSounds.Beep)  ' Play bell
            strSearch = Mid(strSearch, 1, Len(strSearch) - 1)           ' Delete that character from our search string
        Else
            dgGoTo.CurrentRowIndex = intSearch
            dgvNumbers.FirstDisplayedScrollingRowIndex = intSearch
            dgvNumbers.Rows(intSearch).Selected = True
            dgvNumbers.Rows(intCurIndex).Selected = False
            intCurIndex = intSearch
            dgcCurrent = dgvNumbers.CurrentCell
        End If
    End Sub

Open in new window

0
Comment
Question by:ElrondCT
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 42

Expert Comment

by:Meir Rivkin
ID: 26118865
>>so that if the user types "L", the DataGridView immediately jumps to the first entry that starts with L
by which column u wish to base your search?
0
 
LVL 42

Expert Comment

by:Meir Rivkin
ID: 26118951
ok, i've done a small project with datagridview and the following controls:
textbox - contains the search text u wish to apply on the datagridview
combobox - contains datagridview columns which user can select (to determine on which column to apply the search).

List<string> _values member is used as a cached data structure for easy iteration when looking for the first occurence of the search pattern (the index of the row we're looking for)
_lastSelectedIndex member is used to save the last selected row index in order to set it to false when new search text was entered and a new row index was found.

whenever user select different column from the combobox, first the datagridview is sorted by this column and the _values member is re-initialized.

then user type in the textbox and the gridviw is scrolled to the row index of the 1st occurence and select it.



Imports System.ComponentModel

Public Class Form1
    Inherits Form
    Private _values As New List(Of String)()
    Private _lastSelectedIndex As Integer = 0

    Public Sub New()
        InitializeComponent()
    End Sub

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load

        Dim ds As New DataSet()
        ds.ReadXml("C:\users\meirr\desktop\tickersmessages.xml")

        Dim dt As DataTable = ds.Tables(0)

        Dim bs As New BindingSource()
        bs.DataSource = dt
        DataGridView1.DataSource = bs
        ComboBox1.Items.AddRange(DataGridView1.Columns.Cast(Of DataGridViewColumn)().[Select](Function(n) DirectCast(n.Name, Object)).ToArray())
        ComboBox1.SelectedIndex = 0
    End Sub

    Private Sub textBox1_TextChanged(ByVal sender As Object, ByVal e As EventArgs) Handles TextBox1.TextChanged

        Dim search As String = TextBox1.Text

        Dim index As Integer = 0
        For i As Integer = 0 To _values.Count - 1
            If _values(i).StartsWith(search) Then
                index = i
                Exit For
            End If
        Next

        DataGridView1.Rows(_lastSelectedIndex).Selected = False
        DataGridView1.FirstDisplayedScrollingRowIndex = index
        DataGridView1.Rows(index).Selected = True
        _lastSelectedIndex = index
    End Sub

    Private Sub comboBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles ComboBox1.SelectedIndexChanged
        _values.Clear()
        DataGridView1.Sort(DataGridView1.Columns.Cast(Of DataGridViewColumn)().Where(Function(n) n.Name = ComboBox1.Text).FirstOrDefault(), ListSortDirection.Ascending)
        _values.AddRange(DataGridView1.Rows.Cast(Of DataGridViewRow)().Where(Function(n) n.Cells(ComboBox1.SelectedIndex) IsNot Nothing AndAlso n.Cells(ComboBox1.SelectedIndex).Value IsNot Nothing).[Select](Function(n) n.Cells(ComboBox1.SelectedIndex).Value.ToString()))
    End Sub
End Class

Open in new window

0
 
LVL 4

Expert Comment

by:me655321
ID: 26119504
Here's how I would do it. Although you need some methods that would trigger the search string to clear.
    Dim strSearch As String = ""
    Private Sub DataGridView1_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles DataGridView1.KeyPress
        strSearch &= e.KeyChar
        For i = 0 To DataGridView1.RowCount - 1
            If DataGridView1.Item(0, i).Value IsNot Nothing AndAlso DataGridView1.Item(0, i).Value.ToString.StartsWith(strSearch) Then
                DataGridView1.Rows(i).Selected = True
                DataGridView1.CurrentCell = DataGridView1.Item(0, i)
                If DataGridView1.Rows(i).Displayed = False Then
                    DataGridView1.FirstDisplayedScrollingRowIndex = i
                End If
                Exit Sub
            End If
        Next
        My.Computer.Audio.PlaySystemSound(Media.SystemSounds.Beep)  ' Play bell
        strSearch = Mid(strSearch, 1, Len(strSearch) - 1)           ' Delete that character from our search string
    End Sub

Open in new window

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 4

Expert Comment

by:me655321
ID: 26119508
By the way in my example I based it on column 0.
0
 
LVL 20

Author Comment

by:ElrondCT
ID: 26121379
Thanks, all, for your suggestions. Christmas is getting in the way of testing; I probably won't be able to get back with comments until Monday. I do have to say I'm disappointed that the code options listed both rely on a manual look through the entire list--I could have several thousand items to be looking through, and I would have thought there would be a way to do an indexed search. But perhaps I'm overestimating the time needed to scan the list.
0
 
LVL 20

Accepted Solution

by:
ElrondCT earned 0 total points
ID: 26175521
Sorry it took so long to get back; the holidays have been hectic. I've ended up with a rather different approach from what any of you suggested. I didn't like having to do a brute-force search through the entire table, and realized I could use DataTable.Select with the LIKE command to get a match on the first x characters, then take the first result and .Find it in the DataGridView. So I've now got the following code in the KeyPress event handler, plus the KeyDown event handler to handle Backspace and moving by other means (up & down arrow, page up/page down).

I wasn't able to find a way to highlight just the typed characters in a cell, so I've decided to display the search characters in a label (lblSearchText).

So, thanks for your suggestions, but I'm going to accept my own answer this time.
Private blnBackSpace as Boolean
    Private strSearch as String
    Private dvNumbers As DataView

    Private Sub dgvNumbers_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles dgvNumbers.KeyPress
        ' Progressive search. Add each character typed to the search string and look for a match
        Dim intSearch As Integer
        If blnBackSpace = True Then                                     ' Backspace is handled by KeyDown, but Handled property
            e.Handled = True                                            '  has to be set here for some reason
            blnBackSpace = False
            Return
        End If
        ' Add newly typed character to list. If it's a special character, we need to precede with a backslash 
        ' or it'll be treated as a wildcard.
        strSearch &= CStr(IIf(InStr("~()#\/=><+-*%&|^'""[]", e.KeyChar) > 0, "\", "")) & e.KeyChar
        Dim drFind() As ds.HeaderRow
        drFind = CType(dsA.Header.Select("fldKey LIKE '" & strSearch & "*'", "fldKey ASC"), ds.HeaderRow())
        If drFind.Count = 0 Then                                        ' No match
            My.Computer.Audio.PlaySystemSound(Media.SystemSounds.Beep)  ' Play bell
            strSearch = Mid(strSearch, 1, Len(strSearch) - 1)           ' Delete that character from our search string
        Else
            intSearch = dvNumbers.Find(drFind(0).fldKey)
            dgvNumbers.FirstDisplayedScrollingRowIndex = intSearch
            dgvNumbers.CurrentCell = dgvNumbers.Item(0, intSearch)
            lblSearch.Visible = True
            lblSearchText.Visible = True
        End If
        lblSearchText.Text = strSearch
    End Sub

    Private Sub dgvNumbers_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles dgvNumbers.KeyDown
        Dim intSearch As Integer
        Select Case e.KeyCode
            Case Keys.Back
                If Len(strSearch) = 0 Then
                    My.Computer.Audio.PlaySystemSound(Media.SystemSounds.Beep)  ' Play bell
                Else
                    strSearch = Mid(strSearch, 1, Len(strSearch) - 1)
                    ' If character deleted was an escaped special character, remove the escape character
                    If Len(strSearch) > 0 AndAlso Mid(strSearch, Len(strSearch)) = "\" Then
                        strSearch = Mid(strSearch, 1, Len(strSearch) - 1)
                    End If
                    If Len(strSearch) = 0 Then
                        intSearch = 0
                        lblSearch.Visible = False
                        lblSearchText.Visible = False
                    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.
                        Dim drFind() As ds.HeaderRow
                        drFind = CType(dsA.Header.Select("fldKey LIKE '" & strSearch & "*'", "fldKey ASC"), ds.HeaderRow())
                        intSearch = dvNumbers.Find(drFind(0).fldKey)
                    End If
                    dgvNumbers.FirstDisplayedScrollingRowIndex = intSearch
                    dgvNumbers.CurrentCell = dgvNumbers.Item(0, intSearch)
                    lblSearchText.Text = strSearch
                End If
                blnBackSpace = True
            Case Keys.Down, Keys.Up, Keys.PageDown, Keys.PageUp
                lblSearch.Visible = False
                lblSearchText.Visible = False
                strSearch = ""
        End Select
    End Sub

Open in new window

0
 
LVL 20

Author Comment

by:ElrondCT
ID: 26175937
Update: I realized that I was using the wrong escape character for protecting punctuation characters. The line with all the punctuation characters should be:

(I'll probably write this up as an article with a few more bells and whistles.)
strSearch &= CStr(IIf(InStr("~()#\/=><+-*%&|^'""[]", e.KeyChar) > 0, "[" & e.KeyChar & "]", e.KeyChar))

Open in new window

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question