Autocomplete text field from database

Question

I need my text block to be autocomplete when the user types. The value must come from the database. I am using the textchange property of a text field.

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 do not get the desired result. Any ideas how to do this?

Answer 1

You can use jQuery UI to 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

Answer 2

Have you considered using the jquery ui autocomplete component? You can connect this to a remote data source.

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

Answer 3

• Never put your connection strings on ANY forum
• Use AJAX, otherwise you will have to send the page every time the user enters a character. JQuery and JQueryUI provide easy support for autocomplete features.
• use Telerik RadCombo (but you need to buy a license)

EDIT:

If you decide to use jQueryUI autocomplete, I would start with the remote JSONP example. You can specify the url property of the ajax call inside the autocomplete function source for this example )

Answer 4

You select all rows from the tbl_pincode table, and not just those that correspond to the characters (s) that the user enters. Then you assign a value from the area column for each row to zipcode.Text .

Perhaps 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(); } } 

SQL selects the region and codes that begin with the characters that the user types so far. what I think based on your comments on other answers. I also modify the while and if while , as I see why you want to assign and then reassign the values ​​for the zipcode.Text property (I could understand if you were adding to the List control).