none
TextBox AutoComplete not working RRS feed

  • Question

  • Hi,

    I have the problem that I just can't make it work, no matter what I do.

    This is my code, it tries to search into database everytime a key is pressed and when the textbox's lenght is greater than 3, it works ok until the part that retreives the data but it never shows the results below the textbox, what am I doing wrong?, this was supposed to be an easy task. I am using VS2008 and .Net 3.5

    Public Class autocompleteTextbox
        Private SearchStr As String
        Private DataCollection As New AutoCompleteStringCollection
    
        Private Sub autocompleteTextbox_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            TextBox1.AutoCompleteMode = AutoCompleteMode.Suggest
            TextBox1.AutoCompleteSource = AutoCompleteSource.CustomSource
        End Sub
    
        Private Sub getData(ByVal dataCollection As AutoCompleteStringCollection)
            Dim connetionString As String = Nothing
            Dim connection As SqlConnection
            Dim command As SqlCommand
            Dim adapter As New SqlDataAdapter()
            Dim dt As New DataTable
            connetionString = "server=#######;initial catalog =#####;TrustServerCertificate=yes;Encrypt=yes;Connection Timeout=30;User=#####;Password=#####"
            Dim sql As String = "SELECT cve_cve+' - '+cve_desc as cve_desc FROM CATS where [cve_desc] like '%" & TextBox1.Text.Trim & "%'"
            connection = New SqlConnection(connetionString)
            Try
                connection.Open()
                command = New SqlCommand(sql, connection)
                adapter.SelectCommand = command
                adapter.Fill(dt)
                adapter.Dispose()
                command.Dispose()
                connection.Close()
                dataCollection.Clear()
                For Each row As DataRow In dt.Rows
                    dataCollection.Add(row(0).ToString())
                Next
            Catch ex As Exception
                MessageBox.Show("Can not open connection ! ")
            End Try
        End Sub
    
        Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged
            If TextBox1.Text.Length >= 3 Then
                getData(DataCollection)
                TextBox1.AutoCompleteCustomSource = DataCollection
            End If
    
        End Sub
    End Class


    G.Waters


    Friday, December 29, 2017 11:05 PM

All replies

  • I have a MSDN code sample you can look at which uses MS-Access but the same logic works for SQL-Server as both OleDb and SqlClient Data providers have the same objects and methods e.g. SqlDataAdapter is the same animal as OleDbDataAdapter etc.

    Snippets from the code sample where I use the absolute minimal code to do the auto-complete for a TextBox.

    ----

    On a side note, the auto-complete aspect of a control such as a TextBox is a closed system so if you want to peek at a the current search/filter you can't.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, December 29, 2017 11:30 PM
    Moderator
  • Hi

    Here is some simple code - no database so just added some random data for this example.

    I didn't follow some of your posted code so this is only an illustration of AutoComplete for a TextBox.

    ' Form1 with TextBox1
    Option Strict On
    Option Explicit On
    Public Class Form1
      Private Sub autocompleteTextbox_Load(ByVal sender As System.Object, ByVal e As EventArgs) Handles MyBase.Load
    	With TextBox1
    	  .AutoCompleteSource = AutoCompleteSource.CustomSource
    	  .AutoCompleteCustomSource = getData()
    	  .AutoCompleteMode = AutoCompleteMode.Suggest
    	End With
      End Sub
      Private Function getData() As AutoCompleteStringCollection
    	Dim DataCollection As New AutoCompleteStringCollection
    
    	Dim dt As New DataTable
    
    	' some random data in place of DataBase data
    	With dt
    	  .Columns.Add("Col1")
    
    	  .Rows.Add("ABCDE")
    	  .Rows.Add("ACDEF")
    	  .Rows.Add("ABDEF")
    	  .Rows.Add("ABCEF")
    	  .Rows.Add("ABCDF")
    
    	End With
    	DataCollection.Clear()
    	For Each row As DataRow In dt.Rows
    	  DataCollection.Add(row(0).ToString())
    	Next
    	Return DataCollection
      End Function
    End Class


    Regards Les, Livingston, Scotland

    Friday, December 29, 2017 11:44 PM
  • Thanks Karen, but using it in the Load Event does not work for me, I need to be able to search in database while the user is typing, that's why I limited user to write at least 3 characters, any other ideas?

    G.Waters

    Friday, December 29, 2017 11:44 PM
  • Thanks Karen, but using it in the Load Event does not work for me, I need to be able to search in database while the user is typing, that's why I limited user to write at least 3 characters, any other ideas?

    G.Waters

    Well how about using Keyup event. I'm currently in C# mode, not able to get into vb.net mode but you should be able to follow along. For the (TextBox) part use Ctype(sender,TextBox) etc.

    public Form1()
    {
        InitializeComponent();
        textBox1.KeyUp += TextBox1_KeyUp;
    }
    
    private void TextBox1_KeyUp(object sender, KeyEventArgs e)
    {
        if (((TextBox)sender).TextLength >= 3)
        {
            Console.WriteLine($"'{((TextBox)sender).Text}'");
        }
        else
        {
            Console.WriteLine($"'{((TextBox)sender).Text,10}'");
        }
    }

    So in the output above, the text aligned left is what you would be after and ignore the right aligned text, does this make sense?


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, December 30, 2017 12:41 AM
    Moderator
  • George,

    I would like to see the answer to this also. I have the same problem with my project I think. I cant get the autocomplete list to show below the textbox.

    I can make a one form example that works. But in my large project it does not. I have suspected it has something to do with being in a class that is referenced in the project???? Because it works when the textbox is not part of a class that is part of a larger project. But in my larger project, it does not work.

    Also the DataCollection is defined in the large project and referenced in the class.

    So if you don't solve your problem with the examples shown perhaps there is something special required for the class? I don't know. I have wanted to ask this question too. There are many details I suppose. Part of the reason I never asked is I cant make a simple example that reproduces the problem.

    I guess I am not helping but is there some special way required if the textbox is in a class like George's example? Hope I am making sense.

    PS George,

    Do you use any listener apis in your project or WndProc? I have also suspected these interacting with the showing of the dropdown list?

    Saturday, December 30, 2017 10:53 AM
  • I should have time this afternoon to put together a complete code sample if this is not resolved by then.  Since I only have VS2015 and VS2017 and if there is an interest in one or the other let me know.

    What I'm thinking of is, a class project that does data operations where a front end forms project utilizes the class project to obtain needed data for the auto-complete operation. I would create mocked data using Red Gate's SQL Data generator with a lot of data so we are not working with a small set of data. 


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, December 30, 2017 11:05 AM
    Moderator
  • George,

    Where the data comes from doesn't matter. I think you might be up against something that's been known for a while: The AutoComplete for a TextBox won't work if you have the TextBox set to multi-line.

    I've never found any documentation for this but it seems to be so:

    Public Class Form1
        Private Sub _
            Form1_Load(sender As System.Object, _
                       e As System.EventArgs) _
                       Handles MyBase.Load
    
            Dim acsc As New AutoCompleteStringCollection
    
            With acsc
                .Add("New York City, NY")
                .Add("Los Angeles, CA")
                .Add("Chicago, IL")
                .Add("Houston, TX")
                .Add("Philadelphia, PA")
                .Add("Phoenix, AZ")
                .Add("San Antonio, TX")
                .Add("San Diego, CA")
                .Add("San Antonio, TX")
                .Add("Dallas, TX")
                .Add("San Jose, CA")
            End With
    
            With TextBox1
                .AutoCompleteCustomSource = acsc
                .AutoCompleteMode = AutoCompleteMode.SuggestAppend
                .AutoCompleteSource = AutoCompleteSource.CustomSource
            End With
    
        End Sub
    End Class


    "A problem well stated is a problem half solved.” - Charles F. Kettering

    Saturday, December 30, 2017 7:57 PM