textbox search like in google

I am using C#.net Windows form , and I need to create a search textbox which will display combo box values (similar to google search); The values displayed in the combo box will be values from the SQL 2005 database (example the user is searching on FirstName, the combobox will display all firstnames, which get filtered as the user types in more letters.... if user is searching on LastName, the combo box displays all LastName values in the database.. etc)

when I am doing the above task

I have written the sql query like this

SELECT  distinct(person_Firstname+''+person_Lastname) 
AS 
name FROM persondetails 
WHERE name Like '%'+@name+'%'

when I am executing this query it gives error like this --- must declare a scalar varaible

my aim is when i am entering first letter in textbox it will display all names starting with that letter like in google...

can any one correct this ....

private void tbautocomplete_TextChanged(object sender, EventArgs e)
{
    AutoCompleteStringCollection namecollection = new AutoCompleteStringCollection();
    SqlConnection con = new SqlConnection(@"Data Source=88888;Initial Catalog=contrynames;Integrated Security=True");
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = con;
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = "SELECT  distinct(person_Firstname+''+person_Lastname) AS name FROM persondetails WHERE name Like '%'+@name+'%'";
    con.Open();
    SqlDataReader rea = cmd.ExecuteReader();
    if (rea.HasRows == true)
    {
        while (rea.Read())
        namecollection.Add(rea["name"].ToString());            
    }
    rea.Close();

    tbautocomplete.AutoComplet开发者_运维知识库eMode = AutoCompleteMode.Suggest;
    tbautocomplete.AutoCompleteSource = AutoCompleteSource.CustomSource;
    tbautocomplete.AutoCompleteCustomSource = namecollection;

It sounds like you're trying to build an AutoComplete feature in your app. You're only missing the parameter on your SqlCommand. Try this:

string searchFor = "%" + txtName.Text + "%"; //the string the user entered.

cmd.CommandText = @"SELECT  distinct(person_Firstname+''+person_Lastname) AS name
                    FROM persondetails 
                    WHERE person_Lastname Like @name
                    OR person_Firstname LIKE @name";

cmd.Parameters.AddWithValue("@name", searchFor);

Your WHERE clause must use the column name in your table. It sounds as if you want to match either the first or last name columns with your search token.

 WHERE person_Lastname LIKE @name
    OR person_Firstname LIKE @name

The functionality you're looking for is called AutoComplete. I'm not familiar with an AutoComplete control in Windows forms, but if there isn't a built in one, there will certainly be third-party controls that do this.

The AutoComplete control will likely provide an event callback where you can put your SQL query to provide the possible completions.

As for your SQL error, it looks like it might be an issue with column names, but it is difficult to tell without your schema.

Edit:

I see you're using an AutoComplete contol already. The problem in your SQL is that your @name parameter is in your query, but you haven't added the parameter to the cmd object, so it doesn't know what value to put there.