CODEWITHSUNDEEP
c#autocomplete
ImonBayazid
ImonBayazid

Reputation: 1096

set autocomplete in a textbox

I have a textbox(named textbox1) in windows form application.I have a database named nn.sdf and i want to use this as a source of auto-complete.Whenever a user gives a input into textbox1,it will show a suggestion from the database, matching with the input text given by user .So i put my code into textBox1_TextChanged property.my code is here:

 private void textBox1_TextChanged(object sender, EventArgs e)
    {
        AutoCompleteStringCollection namesCollection = new AutoCompleteStringCollection();
        SqlCeConnection con = new SqlCeConnection(@"Data Source=C:\Users\Imon-Bayazid\Documents\nn.sdf");
        con.Open();
        SqlCeCommand cmnd = con.CreateCommand();
        cmnd.CommandType = CommandType.Text;
        cmnd.CommandText = "SELECT top(10)  english FROM dic";        
        SqlCeDataReader dReader;
        dReader = cmnd.ExecuteReader();

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

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

But it shows only the first 10 data.i know i have problem in the line 

  cmnd.CommandText = "SELECT top(10)  english FROM dic";// english is my column name and dic is my table name

I don't know what should be the cmnd.CommandText.I want the autosuggestion whenever a user input anything into the textbox1. How can i do this???

Upvotes: 1

Views: 7205

Answers (1)

gunr2171
gunr2171

Reputation: 17512

As you know, CommandText should (or could) be an SQL statement. Try the following

int fetchAmount = 10;
string userInput = "abc";
cmnd.CommandText = string.Format("SELECT top ({0}) english FROM dic WHERE english like '{1}%'",
    fetchAmount.ToString(), userInput);

LIKE is an SQL command that compares text. So in your case, you want all results where the text starts with what the user has typed in.

Now before someone gets on my case, I know this is not the best way to do this. Entering in values directly into a SQL statement leaves you wide open to SQL Injection. I would HIGHLY suggest that you learn and implement Stored Procedures to do any interactions with a database.

Upvotes: 1

Related Questions