Auotocomplete showing only name which starts in caps

Question

I am working on a application with C#, SQL Server CE, VS 2010 and in it I am giving option of searching records.

If I search data with capital character I am getting record but when I tries with small caps I am not getting any record.

My requirement is as above SQL query

SELECT top ({0}) name FROM Expenses WHERE name like '%{1}%'

Screenshot when providing input in small caps.

Screenshot when providing input in capital.

Code:

private void textBox1_TextChanged(object sender, EventArgs e)
{
    AutoCompleteStringCollection namesCollection = new AutoCompleteStringCollection();
    SqlCeConnection con = new SqlCeConnection(@"Data Source=|DataDirectory|\Database\Acadamy.sdf;Persist Security Info=False");

    con.Open();

    SqlCeCommand cmnd = con.CreateCommand();
    cmnd.CommandType = CommandType.Text;

    var fetchAmount = 10;
    var userInput = textBox1.Text.Trim();

    cmnd.CommandText = string.Format("SELECT top ({0}) name FROM Expenses WHERE name like '%{1}%'",
    fetchAmount, userInput);

    SqlCeDataReader dReader;
    dReader = cmnd.ExecuteReader();

    if (dReader.Read())
    {
       while (dReader.Read())
          namesCollection.Add(dReader["name"].ToString());
    }
    //else
    //{
    // MessageBox.Show(@"Data not found");
    //}
    dReader.Close();

    textBox1.AutoCompleteMode = AutoCompleteMode.Suggest;
    textBox1.AutoCompleteSource = AutoCompleteSource.CustomSource;
    textBox1.AutoCompleteCustomSource = namesCollection;    
 }

What would be wrong with code?

Answer 1

Try this

cmnd.CommandText = string.Format("SELECT top ({0}) name FROM Expenses WHERE name like '%{1}%'", fetchAmount, userInput).ToUpper();