C# AutoComplete

Question

I am trying to add an autocomplete feature to a textbox, the results are coming from a database. They come in the format of

[001] Last, First Middle

Currently you must type [001]... to get the entries to show. So the problem is that I want it to complete even if I type the firstname first. So if an entry was

[001] Smith, John D

if I started typing John then this entry should show up in the results for the auto complete.

Currently the code looks something like

AutoCompleteStringCollection acsc = new AutoCompleteStringCollection();
txtBox1.AutoCompleteCustomSource = acsc;
txtBox1.AutoCompleteMode = AutoCompleteMode.Suggest; 
txtBox1.AutoCompleteSource = AutoCompleteSource.CustomSource; 

....

if (results.Rows.Count > 0)
    for (int i = 0; i < results.Rows.Count && i < 10; i++) 
    {
        row = results.Rows[i];
        acsc.Add(row["Details"].ToString());
    }
}

results is a dataset containing the query results

The query is a simple search query using the like statement. The correct results are returned if we do not use the autocomplete and just toss the results into an array.

Any advice?

EDIT:

Here is the query that returns the results

SELECT Name from view_customers where Details LIKE '{0}'

With {0} being the placeholder for the searched string.

Answer 1

If you're running that query (with {0} being replaced by the string entered), you might need:

SELECT Name from view_customers where Details LIKE '%{0}%'

LIKE still needs the % character... And yes, you should use parameters rather than trusting the user's input :)

Also, you seem to be returning the Name column, but querying on the Details column. So if someone types in "John Smith", if that's not in the Details column you won't get what you want back.

Answer 2

If you decide to use a query that is based on user input make sure you use SqlParameters to avoid SQL Injection attacks

SqlCommand sqlCommand = new SqlCommand();
sqlCommand.CommandText = "SELECT Name from view_customers where Details LIKE '%" + @SearchParam + "%'";
sqlCommand.Parameters.AddWithValue("@SearchParam", searchParam);

Answer 3

The existing AutoComplete functionality only supports searching by prefix. There doesn't seem to be any decent way to override the behavior.

Some people have implemented their own autocomplete functions by overriding the OnTextChanged event. That's probably your best bet.

For example, you can add a ListBox just below the TextBox and set its default visibility to false. Then you can use the OnTextChanged event of the TextBox and the SelectedIndexChanged event of the ListBox to display and select items.

This seems to work pretty well as a rudimentary example:

public Form1()
{
    InitializeComponent();


    acsc = new AutoCompleteStringCollection();
    textBox1.AutoCompleteCustomSource = acsc;
    textBox1.AutoCompleteMode = AutoCompleteMode.None;
    textBox1.AutoCompleteSource = AutoCompleteSource.CustomSource;
}

private void button1_Click(object sender, EventArgs e)
{
    acsc.Add("[001] some kind of item");
    acsc.Add("[002] some other item");
    acsc.Add("[003] an orange");
    acsc.Add("[004] i like pickles");
}

void textBox1_TextChanged(object sender, System.EventArgs e)
{
    listBox1.Items.Clear();
    if (textBox1.Text.Length == 0)
    {
    hideResults();
    return;
    }

    foreach (String s in textBox1.AutoCompleteCustomSource)
    {
    if (s.Contains(textBox1.Text))
    {
        Console.WriteLine("Found text in: " + s);
        listBox1.Items.Add(s);
        listBox1.Visible = true;
    }
    }
}

void listBox1_SelectedIndexChanged(object sender, System.EventArgs e)
{
    textBox1.Text = listBox1.Items[listBox1.SelectedIndex].ToString();
    hideResults();
}

void listBox1_LostFocus(object sender, System.EventArgs e)
{
    hideResults();
}

void hideResults()
{
    listBox1.Visible = false;
}

There's a lot more you could do without too much effort: append text to the text box, capture additional keyboard commands, and so forth.