[RESOLVED] Auto SuggestAppend issue for ComboBox

[BrailleSchool]

Hi all,

I have a ComboBox on my form and I am trying to create a SuggestAppend for AutoComplete as the end user types. However, I am getting an ArgumentOutOfRangeException when I press the first letter. Then System.Data.DataRowView automatically populates the ComboBox.

This is what I am doing:
modDBStuff

Public Sub AutoSuggestDropDownListFromDB(ByVal cboName As Elegant.Ui.ComboBox, ByVal TableName As String, ByVal ColumnName As String)
        'TODO
        Using SetDatabaseConnection As New SqlConnection(GetDatabaseConnectionString)
            'Specify an SQL query.
            Dim sSQL As String = "SELECT " & ColumnName & " FROM " & TableName
 
            'Assign the SQL query and database connection to the DataAdapter.
            Dim MyDataAdapter As New SqlDataAdapter(sSQL, SetDatabaseConnection)
            'Create a new instance of a DataSet.
            Dim MyDataSet As New DataSet
 
            MyDataSet.Locale = System.Globalization.CultureInfo.CurrentCulture
 
            Try
                'Clear the DataSet of any previously obtained database information.
                MyDataSet.Clear()
                'Refill the DataAdapter with new information obtained from the database.
                MyDataAdapter.Fill(MyDataSet, TableName)
 
                'Fill the ComboBox.
                With cboName
                    'Assign the database table to the DataSource property.
                    .DataSource = MyDataSet.Tables(TableName)
                    'Assign the database column name to the DisplayMember and ValueMember properties.
                    '.DisplayMember = ColumnName
                    '.ValueMember = ColumnName
                    .AutoCompleteMode = AutoCompleteMode.SuggestAppend
                    .AutoCompleteSource = AutoCompleteSource.ListItems
                End With
            Catch ex As SqlException
                'Show the end user a message if an error is generated.
                MessageBox.Show("There was an error retrieving data from table name " & TableName & " for the column " & ColumnName & "." & vbNewLine & vbNewLine & _
                                "We are going to prepopulate the dropdown menu with a default value; therefore, you can disregard the error." & vbNewLine & vbNewLine & _
                                "We highly suggest that you click on the plus sign next to the dropdown menu to add some options of your own." & vbNewLine & vbNewLine & _
                                "The reason why you got this error in the first place is:" & vbNewLine & _
                                "No data items in the database " & TableName & " to pull." & vbNewLine & vbNewLine & _
                                "The exception error is:" & vbNewLine & _
                                ex.Message, _
                                "Database Information Retrieval Issues", _
                                MessageBoxButtons.OK, _
                                MessageBoxIcon.Error, _
                                MessageBoxDefaultButton.Button1, _
                                MessageBoxOptions.DefaultDesktopOnly, False)
            Catch ex As ArgumentOutOfRangeException
                MessageBox.Show(ex.Message, _
                                "Argument Out Of Range Exception", _
                                MessageBoxButtons.OK, _
                                MessageBoxIcon.Error, _
                                MessageBoxDefaultButton.Button1, _
                                MessageBoxOptions.DefaultDesktopOnly, False)
            End Try
        End Using 'SetDatabaseConnection.
    End Sub

_TextChanged

Private Sub cboClientName_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboClientName.TextChanged
        'Change the content of the input to have the first character capitalized by default.
        'TitleCaseComboBox(cboClientName)
 
        If bNewCallSingle OrElse bNewCallMultiple Then
            'Add some text to the title bar.
            'I didn't add the company name because it shows in the title bar as:
            'ProductName - [Work Log Entry.]
            'ProductName = SupportSpace Call Tracker.
            Me.Text = cboClientName.Text.Trim & "'s Work Log Entry"
        End If
 
        Try
            'Autosuggest clients.
            AutoSuggestDropDownListFromDB(cboClientName, "ClientInformation", "ClientName")
        Catch ex As ArgumentOutOfRangeException
            MessageBox.Show(ex.Message, _
                            "Argument Out Of Range Exception", _
                            MessageBoxButtons.OK, _
                            MessageBoxIcon.Error, _
                            MessageBoxDefaultButton.Button1, _
                            MessageBoxOptions.DefaultDesktopOnly, False)
        End Try
    End Sub

Any suggestions?

[stanav]

Try to set your combobox properties (datasource, autocompletemode, autocompletesource... ) just once, i.e. at form load event and see what happens... Also, why are you commenting out the line that assigns the displaymember to your combobox? Without setting that property, your combobox is empty.

[BrailleSchool]

Try to set your combobox properties (datasource, autocompletemode, autocompletesource... ) just once, i.e. at form load event and see what happens... Also, why are you commenting out the line that assigns the displaymember to your combobox? Without setting that property, your combobox is empty.

Given up with coding it by hand. XSD here I come!

[BrailleSchool]

Now a few more years under my belt... the following code will work with DropDownList or DropDown. Pulls the information from a single column in a database table. The flags will handle both a DropDownList or a DropDown. Drop down for AutoComplete and SuggestAppend.

    Public Sub PopulateDropDownList(ByRef ControlName As ComboBox, _
                                    ByVal TableName As String, _
                                    ByVal ColumnName As String, _
                                    Optional ByVal DropDown As Boolean = False, _
                                    Optional ByVal DropDownList As Boolean = False)

        Using SetDatabaseConnection As New SqlConnection(MSSQLConnection)
            Dim Qry As String = "SELECT " & ColumnName & " FROM " & TableName
            Dim Da As New SqlDataAdapter(Qry, SetDatabaseConnection)
            Dim Ds As New DataSet

            Try
                Ds.Clear()
                Da.Fill(Ds, TableName)

                Dim AddBlankEntry As DataRow = Ds.Tables(TableName).NewRow
                AddBlankEntry(ColumnName) = vbNullString
                Ds.Tables(TableName).Rows.Add(AddBlankEntry)

                With ControlName
                    If DropDown = True Then
                        .AutoCompleteMode = AutoCompleteMode.SuggestAppend
                        .AutoCompleteSource = AutoCompleteSource.ListItems
                    Else
                        .AutoCompleteMode = AutoCompleteMode.None
                    End If

                    .DataSource = Ds.Tables(TableName)
                    .DisplayMember = ColumnName
                    .ValueMember = ColumnName
                    .SelectedItem = vbNullString
                End With
            Catch RangeEx As IndexOutOfRangeException
                MessageBox.Show(RangeEx.Message, _
                                "Index Out Of Range Exception", _
                                MessageBoxButtons.OK, _
                                MessageBoxIcon.Error, _
                                MessageBoxDefaultButton.Button1)
            Catch CastEx As InvalidCastException
                MessageBox.Show(CastEx.Message, _
                                "Invalid Cast Exception", _
                                MessageBoxButtons.OK, _
                                MessageBoxIcon.Error, _
                                MessageBoxDefaultButton.Button1)
            Catch ArgNullEx As ArgumentNullException
                MessageBox.Show(ArgNullEx.Message, _
                                "Invalid Cast Exception", _
                                MessageBoxButtons.OK, _
                                MessageBoxIcon.Error, _
                                MessageBoxDefaultButton.Button1)
            End Try
        End Using
    End Sub

To use the code, just call the following in your form load event and change the boolean flags accordingly.

PopulateDropDownList(DropDownMenuControlName, "DatabaseTableName", "ColumnName", False, True)