Listing matching items in a combo box while entering a text?

Question

I have a ComboBox in a form and while the user typing some text in the box, if there are matching items in the database with the text typed, should be listed in the ComboBox. So that the user can be notified if the item being entered is already in the database. I'm trying to achieve this as follows.

When a text is entered in the combo it will fire an event. Listening to that event, Presenter will get matching user list from the database and then the list will be set as the DataSource of the ComboBox as follows...

But why it is not displayed in the combo? Please let me know the issue in this code!

Form

public partial class frmUser : Form
{
    // two seperate get and set properties are used to same combo box as a list will be set as the datasource.

    public string UserName 
    {
        get{return cmbUserName.Text;}
    }

    public IEnumerable<User> UserList
    {
        set { cmbUserName.DataSource = value; }
    }
    private void cmbUserName_TextChanged(object sender, EventArgs e)
    {
        OnChangeText(sender, e);
    }
}

Presenter

class UserPresenter
{       
    private void WireUpEvents()
    {
        _View.OnChangeText += new EventHandler(_View_OnChangeText);
    }

    private void _View_OnChangeText(object sender, EventArgs e)
    {
        ShowMatchingUsers();
    }

    private void ShowMatchingUsers()
    {
        var userList = _DataService.GetUsers(_View.UserName ); //edit
        _Veiew.UserList = userList; //edit
    }
}

DataService

    public IEnumerable<User> GetUsers(string userID)
    { 
        string selectStatement = "SELECT user_id from [user] WHERE user_id like '@userID %'";
        List<User> UserList = new List<User>();

        using (var sqlConnection = new SqlConnection(db.ConnectionString))

        using (var sqlCommand = new SqlCommand(selectStatement, sqlConnection))
        {
            sqlCommand.Parameters.Add("@userID", SqlDbType.VarChar).Value = userID ;
            sqlConnection.Open();

            using (SqlDataReader dataReader = sqlCommand.ExecuteReader())

                while (dataReader.Read())
                {
                    UserList.Add(
                    new User 
                    {
                        UserID = (string) dataReader["user_id"]
                    });
                }

            return UserList;

        }
    }

I did this in WPF with get / set and INPC. What I found was even for 20,000 items it was worth downloading all up front and using LINQ. Tab to get the first matching is also a nice feature. If you want to get into some more advanced and more responsive UI then I recommend WPF and MVVM. — paparazzo, Jun 08 '14 at 20:11
_the list will be set as the DataSource of the ComboBox_ : I don't see where that part is happening. Looks like the returning result set is thrown away.. But maybe I am missing something in your code..?? — TaW, Jun 08 '14 at 22:08
@TaW, Actually I was referring to this property in the view -> public IEnumerable UserList { set { cmbUserName.DataSource = value; } } — CAD, Jun 09 '14 at 03:41

score 1 · Answer 1 · answered Jun 09 '14 at 06:31

I can't tell you exactly how to change your code because I am missing quite a few details, like some events and classes and the possible nesting states of your classes and objects.

But the error is, like I noted, the fact that the resulting IEnumerable<User>which GetUsers brings back is thrown away.

You wrote that it should get used as the DataSource of the ComboBox via the Property UserList but in fact you are hiding this property by declaring a new

List<User> UserList = new List<User>();

locally in GetUsers. This would be OK, albeit a little confusing; (I suggest you call it something different like newUserlist ;) but you still need to set the real UserList to the returned result set somewhere!

When I patched this up like this (missing important info on your actual code)

private void cmbUserName_TextChanged(object sender, EventArgs e)
{
  UserList = _DataService.GetUsers("_View.UserName");

   ///OnChangeText(sender, e); re-instate your own wiring!
}

a dummy value showed up fine in the Combobox..

You should not use my code but stay with you set up! Changing

_DataService.GetUsers(_View.UserName );

to

Userlist = _DataService.GetUsers(_View.UserName );

seems to be the direct solution, but you probably will have to watch for a way to connect innner and outer class variables..

Thanks for the advice. I found an issue in my sql LIKE. when hard coded its working but when parameter is used its not. In my code there is an space between @userID and %. Even it is removed its not giving the desired output? — CAD, Jun 09 '14 at 13:06
You need to use a parameter: `MySqlCommand cmd = oldDb.GetSqlStringCommand(CommandType.Text,"SELECT * _ FROM users WHERE name LIKE @searchText"); MySqlParameter nameParameter= cmd.CreateParameter(); nameParameter.DbType = DbType.String; nameParameter.ParameterName = "@searchText" nameParameter.Value = "%John%";` taken from [CodeProject](http://www.codeproject.com/Tips/542184/MySqlCommand-MySqlParameter-and-LIKE-with-Percent) or look at [this post](http://stackoverflow.com/questions/652978/parameterized-query-for-mysql-with-c-sharp) — TaW, Jun 09 '14 at 13:32
Thanks now its ok. I should have given the value to the parameter as `userID+"%"` instead of `userID`. Now I have a problem with listing. The user list is not shown in list. I think I have to have a loop adding items to the list somewhere. — CAD, Jun 09 '14 at 13:59

score 1 · Answer 2 · answered Jul 16 '15 at 06:37

1

use visual studio, set combobox property as follows combobox autocompletemode = SuggestAppend & autocompletesource=ListItems

answered Jul 16 '15 at 06:37

Mohamed Yunus

73
4

score 0 · Accepted Answer · answered Jun 18 '14 at 05:15

0

I should have given the value to the parameter as userID+"%" instead of userID. With regard to user listing, I think I have to have a loop adding items to the list somewhere.

answered Jun 18 '14 at 05:15

CAD

4,112
6
29
47

Listing matching items in a combo box while entering a text?

3 Answers3