Custom auto complete

[Minko]

Hello,

I have a Textbox and on the textchanged event it takes the users input and searches through a database and takes the items which are like the users input and puts them in a listbox which a user can select from. The problem with this method is that it has turned out to be noticeably slow.

So my question is, is there a faster way to do this and if you know one then can you point me in the right direction.

Thanks,

Minko

[deceptikon]

Well, for starters I wouldn't use the TextChanged event because that would result in a lot of hits on the database for intermediate text. For example, if the user types "foobar", you'd go back and do a fuzzy lookup on the database six times when you really only wanted one for the final string. Instead, consider using the Validated event, a KeyPress handler that looks for the enter key, or even a separate button so that you have more control over when the database is searched.

[TnTinMN]

Based on the fact that you currently have this working, It appears that the returned recordset for the first entered character is not to large to fit into the available memory.

The question is why are you querying the database for subsequently entered characters?

Load a datatable based on the first entered character and then use that table as the datasource for the listbox. On subsequent character entry, adjust the table's defaultview row filter.

Something like this:

   Private lbxSource As New DataTable
   Private LastLoadCharacter As Char = Nothing

   Private Sub tbSearchCriteria_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tbSearchCriteria.TextChanged

      Dim txtlen As Int32 = tbSearchCriteria.Text.Length
      If txtlen = 0 Then
         ListBox1.DataSource = Nothing
      Else
         If txtlen = 1 Then

            If LastLoadCharacter <> tbSearchCriteria.Text(0) Then
               LastLoadCharacter = tbSearchCriteria.Text(0)
               ' query database and load the table lbxSource with the results
            End If
            lbxSource.DefaultView.RowFilter = ""
            ListBox1.DataSource = lbxSource

         Else
            lbxSource.DefaultView.RowFilter = "[SearchField] Like '" & tbSearchCriteria.Text & "*'"
         End If
      End If

   End Sub

[Minko]

Hey,

I do not think I explained what I am trying to do very well, for which I apologize. I have a database file with item codes and I want it so that if you can only remember part of the item code then it will show the first five results which match the users input. But I would like it so that it updates as you type (auto suggest/complete). As I have said I have already got it working but it is very slow and there are only about 6 items in the database and I expect there will be 800+ items in the database eventually. This is has not actually been added to my official project, it is in a seperate project as I am just testing my idea. Hopefully you will be able help me find a better/faster way to do this.

I hope this helps clear up what I am trying to achieve, thanks for the input so far.

Minko

[deceptikon]

My answer remains the same. However, I've had the same task (on a much larger database than ~800 items) and simply exposed the LIKE wildcard syntax for users. If they wanted an exact match they just passed in the complete code. If they wanted an approximate match, they used LIKE syntax, such as "123%" for all codes starting with "123".

In that case the lookup was tied to a button and also a KeyPress handler because those folks were keyboard warriors, but as I mentioned before, you can use the Validated event as well.