CODEWITHSUNDEEP
c#
asifa
asifa

Reputation: 771

autocomplete textbox from the database

I need my textbox to autocomplete when the user types. The value should come from the database. I am using the textchange property of the textbox.

protected void autocomplete(object sender, EventArgs e)
{
    string query = "Select area,codes from tbl_pincode";
    SqlConnection conn = new SqlConnection("Data Source=win2008-2;Initial       Catalog=h1tm11;User ID=sa;Password=#1cub3123*;Persist Security Info=True;");
    SqlCommand com = new SqlCommand(query, conn);
    conn.Open();
    SqlDataReader dr = com.ExecuteReader();
    while (dr.Read())
    {
        zipcode.Text = dr.GetValue(0).ToString();
    }
    conn.Close();
}

But i m not getting the desired result. Any ideas how to go about it?

Upvotes: 6

Views: 26043

Answers (4)

Strillo
Strillo

Reputation: 2972

  1. Never put your connection strings on ANY forum
  2. Use AJAX otherwise you'll have to postback the page every time the user types a character. JQuery & JQueryUI provide easy support for autocomplete features.
  3. use Telerik RadCombo (but you need to buy a license)

EDIT:

If you choose to use the JQueryUI autocomplete, I'd start from the remote JSONP example. You can point the url property of the ajax call inside the autocomplete's source function to a WebMethod in your page. This will receive an object containing the filter (data in the example) and return the required values from your database in JSON format (e.g. see this example)

Upvotes: 1

Ɖiamond ǤeezeƦ
Ɖiamond ǤeezeƦ

Reputation: 3331

You are selecting all rows from the tbl_pincode table, not just those that match the character(s) the user has input. You are then assigning the value from the area column for each row to zipcode.Text.

Maybe try:

protected void autocomplete(object sender, EventArgs e)
{
    if (string.IsNullOrEmpty(zipcode.Text))
        return;

    string query = @"
        SELECT area FROM tbl_pincode WHERE area LIKE @Value
        UNION 
        SELECT codes FROM tbl_pincode WHERE codes LIKE @Value";

    SqlConnection conn = null;
    SqlCommand com = null;
    SqlDataReader dr = null;
    try
    {
        conn = new SqlConnection("Data Source=win2008-2;Initial       Catalog=h1tm11;User ID=sa;Password=#1cub3123*;Persist Security Info=True;");
        com = new SqlCommand(query, conn);
        string value = string.Format("{0}%", zipcode.Text);
        com.Parameters.AddWithValue("@Value", value);
        conn.Open();
        dr = com.ExecuteReader();
        if (dr.Read())
        {
            zipcode.Text = dr.GetValue(0).ToString();
        }
    }
    finally
    {
        if (conn != null) conn.Dispose();
        if (dr != null) dr.Dispose();
        if (com != null) com.Dispose();
    }
}

The SQL selects area and codes that start with the character(s) the user has input so far. which is what I think based on your comments to other answers. I've also change the while loop to and if block as I can see why you would want to assign and then re-assign values to the zipcode.Text property (I could understand if you were appending to a List control).

Upvotes: 0

Fabio
Fabio

Reputation: 3120

You can use jQuery UI for autocomplete: http://www.dotnetcurry.com/ShowArticle.aspx?ID=515

Another option for ASP.NET autocomplete is AjaxControlToolkit: http://www.asp.net/ajaxLibrary/AjaxControlToolkitSampleSite/AutoComplete/AutoComplete.aspx

Upvotes: 1

mbx-mbx
mbx-mbx

Reputation: 1775

Have you looked at using the jquery ui autocomplete component? You can hook this up to a remote datasource

http://jqueryui.com/demos/autocomplete/

Upvotes: 2

Related Questions