AutoComplete Combo Box having data from Excel which has two columns

[hawkz86]

Hi

I am a fresher to VB and now stuck with a code for my project.
Project Description : I have a .xlsx Excel File which has two Coulmns "Key" and "Summary", I have populated combobox on the form
using DataSet as shown below :

        Dim dt As New System.Data.DataTable()
        dt = cn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, Nothing)
        Dim sheetname As String = dt.Rows(0)("table_name").ToString()
        Dim da As New OleDbDataAdapter("SELECT Key + ' ' + Summary as Details FROM [" & sheetname & "]", cn)
        da.Fill(ds, sheetname)
        da.Dispose()
        cn.Close()

        If dt IsNot Nothing OrElse dt.Rows.Count > 0 Then

            Try
                ' For Loop to add data to the List of DropDown

                For i = 0 To ds.Tables(0).Rows.Count - 1

                    Me.ComboBox1.Items.Add(ds.Tables(0).Rows(i).Item(0))

                Next

Now I have to give ComboBox1 a AutoComplete feature so that user needn't have to scroll a huge list.

However I unable to do the same, I tried various ways but none worked. Would really appreciate any help.

[Jx_Man]

try this :

    Public Sub AutoComplete(ByVal cmb As ComboBox, ByVal e As KeyEventArgs)
        Dim sTypedText As String
        Dim iFoundIndex As Integer
        Dim oFoundItem As Object
        Dim sFoundText As String
        Dim sAppendText As String

        Select Case e.KeyCode
            Case Keys.Back, Keys.Left, Keys.Right, Keys.Up, Keys.Delete, Keys.Down
                Return
        End Select

        sTypedText = cmb.Text
        iFoundIndex = cmb.FindString(sTypedText)

        If iFoundIndex >= 0 Then
            oFoundItem = cmb.Items(iFoundIndex)
            sFoundText = cmb.GetItemText(oFoundItem)
            sAppendText = sFoundText.Substring(sTypedText.Length)
            cmb.Text = sTypedText & sAppendText

            cmb.SelectionStart = sTypedText.Length
            cmb.SelectionLength = sAppendText.Length
        End If

    End Sub

    Private Sub ComboBox1_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles ComboBox1.KeyUp
        AutoComplete(ComboBox1, e)
    End Sub

[hawkz86]

Hi

Thanks for the reply, I tried the above changes however it did not work, I am getting the Index iFoundIndex as -1. Can you please guide if I am missing something, I think the problem here is being caused because I am combining two columns from my Excel to be displayed in the ComboBox , cause if I do it with a single column it works fine.

[hawkz86]

Hi Jx

I just figured that the code is actually only appending after the fist Word , what i mean is if My drop down list has a value "EN-234 Business upgrade required"
and if I enter/write B or u nothing happens however if I write E it just appends the entire sentence.
I was lookind for just suggest option and not append and also to find any letter/word in between the value.
I tried a bit and found that i was able to solve it partially,cause what I want is to look for any substring in the sentence, it just looks for the first letter and not for any letter in the String :

                ComboBox1.AutoCompleteCustomSource.Clear()
                Dim col As New AutoCompleteStringCollection
                Dim comp As Integer
                For comp = 0 To ds.Tables(0).Rows.Count - 1
                    col.Add(ds.Tables(0).Rows(comp).Item(0))
                Next

                ComboBox1.AutoCompleteSource = AutoCompleteSource.CustomSource
                ComboBox1.AutoCompleteCustomSource = col
                ComboBox1.AutoCompleteMode = AutoCompleteMode.Suggest

The above code i have inserted just after my For Loop , however still stuck with finding any string/letter from the entire sentence.

Thanks in advance :)

[hawkz86]

Hi Jx
Can you please guide, I am just stuck, please bail me out.

[Jx_Man]

if I enter/write B or u nothing happens however if I write E it just appends the entire sentence.

Autocomplete works with knowing the "First Letter" in combobox items.
e.g (three items on combobox)
Austria
Brazil
Canada

So, If you type "c" it wil be Canada but when you type "n" there is nothing to show.

If you want to show items suggestion, you can set Combobox AutoCompleteMode = SuggestAppend and AutoCompleteSource = ListItems

[Jx_Man]

This might help

[hawkz86]

Thanks

I also had a look at the link you gave , the example is in C# I converted it in VB and tried to use the logic however failed :(

I will keep trying in case if i am able to figure out the logic , I will post it here . Thansk once again.