Introduction:
Here I will explain how to use Ajax AutoCompleteExtender without using webservice to display the words begin with prefix typed into the textbox using asp.net
Description:
In previous article I explained clearly how to implement AjaxAutoCompleteExtender with webservice. We can attach Ajax autocomplete exteneder to any textbox to implement this and after assign autocomplete extender to textbox and type more content than the specified minimum word length, a popup will show words or phrases starting with that value. So the user can choose exact word from the popup panel. Here we are implementing autoCompleteextender to fetch data from the database without using Webservice.
Here I will explain how to use Ajax AutoCompleteExtender without using webservice to display the words begin with prefix typed into the textbox using asp.net
Description:
In previous article I explained clearly how to implement AjaxAutoCompleteExtender with webservice. We can attach Ajax autocomplete exteneder to any textbox to implement this and after assign autocomplete extender to textbox and type more content than the specified minimum word length, a popup will show words or phrases starting with that value. So the user can choose exact word from the popup panel. Here we are implementing autoCompleteextender to fetch data from the database without using Webservice.
First design table in your database like this
Column Name
|
Data Type
|
Allow Nulls
|
ID
|
Int(set identity property=true)
|
No
|
CountryName
|
Varchar(50)
|
Yes
|
After completion of design table in database add AjaxControlToolkit reference to your application after that add
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="ajax" %>
|
Our aspx page code like this
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="ajax" %>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Ajax AutoCompleteExtender without Webservice</title>
</head>
<body>
<form id="form1" runat="server">
<ajax:ToolkitScriptManager ID="ScriptManager1" runat="server"/>
<div>
<asp:TextBox ID="txtCountry" runat="server"></asp:TextBox>
<ajax:AutoCompleteExtender ID="AutoCompleteExtender1" runat="server" TargetControlID="txtCountry"
MinimumPrefixLength="1" EnableCaching="true" CompletionSetCount="1" CompletionInterval="1000" ServiceMethod="GetCountries" >
</ajax:AutoCompleteExtender>
</div>
</form>
</body>
</html>
|
Here if you observe above code for autocompleteextender I declared different properties now I will explain each property clearly
TargetControlID - The TextBox control where the user types content to be automatically completed.
EnableCaching- Caching is turned on, so typing the same prefix multiple times results in only one call to the web service.
MinimumPrefixLength- Minimum number of characters that must be entered before getting suggestions from the web service.
CompletionInterval - Time in milliseconds when the timer will kick in to get suggestions using the web service.
CompletionSetCount - Number of suggestions to be retrieved from the web service.
Don’t get confuse I explained all the properties details only it’s very simple to implement auto completion textbox after completion of your aspx page design add following namcespaces in your code behind page
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Configuration;
|
After completion of adding namespaces write the following code in codebehind
[System.Web.Script.Services.ScriptMethod()]
[System.Web.Services.WebMethod]
public static List<string> GetCountries(string prefixText)
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["dbconnection"].ToString());
con.Open();
SqlCommand cmd = new SqlCommand("select * from Country where CountryName like @Name+'%'", con);
cmd.Parameters.AddWithValue("@Name", prefixText);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
List<string> CountryNames = new List<string>();
for (int i = 0; i < dt.Rows.Count; i++)
{
CountryNames.Add(dt.Rows[i][1].ToString());
}
return CountryNames;
}
|
Note: Use same parameter name string prefixText in List<string> GetCountries(string prefixText) method.
After that set your database connection in web.config like this
Demo
After that set your database connection in web.config like this
<connectionStrings>
<add name="dbconnection" connectionString="Data Source=SureshDasari;Integrated Security=true;Initial
Catalog=MySampleDB"/>
</connectionStrings >
|
Demo
 |
Download sample code attached
|
If you enjoyed this post, please support the blog below. It's FREE! Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email. |
|||
Subscribe by RSS
Subscribe by Email
|
|||
112 comments :
Thanks. Your code is very helpful for me. It is working fine.
DO NOT FIRING SERVICE METHOD BOSS
thanku very much... it works...
thanks for your explanation...
you explained properties of autocompleteextender...
it is awesome
didnot firing process
hi it's working fine i think you did mistake in your application
Hi Suresh
Your code is super
thanks.
The most important thing for me: the code provided in this example actually works. Thank you!
Thank you very much for publish this code
your site is very useful for me thanks
Good Post.It Really helped me in understanding AutoCompleteExtender.
Thanks.
its not working. somethings is missing.
pls complete this........
it's working fine please check your code without working how can i place demo..
Hi ur site is provide me good knowledge..
but this Example is not working. somethings is missing.
protected void Page_Load(object sender, EventArgs e)
{
}
[System.Web.Script.Services.ScriptMethod()]
[System.Web.Services.WebMethod]
public static List GetCountries(string prefixText)
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["AppConnection"].ToString());
con.Open();
SqlCommand cmd = new SqlCommand("select * from Country where CountryName like @Name+'%'", con);
cmd.Parameters.AddWithValue("@Name", prefixText);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
List CountryNames = new List();
for (int i = 0; i < dt.Rows.Count; i++)
{
CountryNames.Add(dt.Rows[i][0].ToString());
}
return CountryNames;
}
this is my codebehind code.
design page is same as ur page.
suresh thank you.It helped me a lot.
Code in vb.net
Very nice post! Perfectly working. Thanks!
this site is very helpful
Thank you Suresh. Your code is working perfectly fine when i use it in an aspx page.
If i put the same code in an user control AutoCompleteExtender is not working.
Any idea why it is not working ?
Thanks!
this code return only contryname how to find contry code..
thanks, really it is working...
i cant't see populate data
@suresh: your presentation very simple,easy to understand.. It really helped me a lot...
thank u sir....
Hi, you are posting always great post and they are help alot but your this code is not working I was trying many times even download your code and do that but not working
thanks..i need one more help
this code return contryname how to find SELECTED contry code.(that is ID)
hi team, my breakpoint is not reaching service method it ends at public webservice() {}. Please help
Great code, thanks a lot. It is working with an Access database (.accdb), but I can't get it to work with a sql server compact database (.sdf). Can you please help, please.
Thanks for your code. Working fine. Thanks alot.
hi Suresh Dasari, ur code is working great, can u tell me how to select multiple text ex:- india, indonesia, china using AutoComplete Extender .. appreciate ur help..
Prasad MSR
your code is working like magic.
thank you.
How can i retrieve ID and name, name is visible and ID is hidden, deal with them like a drop down list (DataValueFeild & DataTextFeild)
sorry...ur code is not working
@Unknown.... Please check this link http://www.aspdotnet-suresh.com/2012/07/jquery-autocomplete-textbox-with.html
This code is working fine please check your code i hope you did mistake in your code like forgot add to WebMethod attribute or sql connection problems etc...
I used successfully.But List appear on textbox.I want to display list below textbox.
I can not understand the "dbconnection" from
ConfigurationManager.ConnectionStrings["dbconnection"].ToString()
by how i can get it
ConfigurationManager.ConnectionStrings["dbconnection"].ToString() is your database connection string. Please check above code i added the that one also...
GREAT....................!!!!
it's really gr8....thnx yaar...
hey...its working great......keep uploading such a new things.....
its super yarr... working excellent..
Good day, a question as I can do to put more of an AutoCompleteExtender in a single website.
thanks.
Thank you Darling
THANKS A LOT, It helps me a lot with clear explanation.
Narendran
HI I got same requirement but bit change in requirement.I need to retrieve not one column i need to auto fill multiple columns with good design..please provide solution
Please click on below link for clarification
http://4.bp.blogspot.com/-o3RNiuUvqSw/UDSZ3WDY8GI/AAAAAAAAAO4/cuujP1m9_u0/s1600/dgviewmulitcol.jpg
dude this example is very helpful to me thank you...
hi..this is javeed.........
I am having the requirement of more than one textbox. how to add intellisense for that......can u help me please..........
ThanQ sir
thanks..
your post are very use full For ME.. Really Thanks
How to restrict the multiple data with same name displayed in autocpmplete textbox...I only want display distinct name of each company from my database..? Please help me ?
How to restrict the multiple data with same name displayed in autocpmplete textbox...I only want display distinct name of each company from my database..? Please help me ? Help me sir I really need this..
I tried this code with mysql database. It's not firing. Can anybody help to solve this problem.,
My code is.,
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using MySql.Data.MySqlClient;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Web.Configuration;
using System.Web.Services;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
[System.Web.Script.Services.ScriptMethod()]
[System.Web.Services.WebMethod]
public static List GetCountries(string prefixText)
{
// protected string con = WebConfigurationManager.ConnectionStrings["LocalMySqlServer"].ConnectionString;
MySqlConnection con = new MySqlConnection(WebConfigurationManager.ConnectionStrings["dbconnection"].ToString());
con.Open();
MySqlCommand cmd = new MySqlCommand("select name from district where name like @Name+'%'", con);
cmd.Parameters.AddWithValue("@Name", prefixText);
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
List CountryNames = new List();
for (int i = 0; i < dt.Rows.Count; i++)
{
CountryNames.Add(dt.Rows[i][1].ToString());
}
return CountryNames;
}
}
Connection string in webconfig file.,
AND I TRIED ALL TYPE OF AUTO COMPLETE TEXTBOX OPTIONS WITH MYSQL, WHICH YOU HAVE GIVEN. EVERYTHING IS TELLING ERROR OR NOT FIRING.,
PLEASE HELP ME TO CLEAR THIS PROBLEM.,
Nice Article, Thanks...
Amazing summary bookmarked the site.
thanks.............
Your post says "without using webservice" yet you do use a webservice!!!
good one it really worked for me..........
Thanks boss..it helped me
To get it work when deployed into iis is ...
Try...this
go to start>run>inetmgr>
In the connections sidepane..select application pools
select the application pool that u hav assigned when deployed that project into iis(to check that go to sites>in connections pane...and right click on website that u have deployed and select manage website >advanced settings then at the top u wil able to see application pool)
then comeback and select that particular application pool in application pools in connections pane right click on that particular application pool and select advanced settings..find out process model in that and select identity and browse through it and goto built in account and select local system.......then click ok...and get out of it...
I think it will work..it worked for me.....
It's actually very difficult in this busy life to listen news on Television, therefore I simply use the web for that purpose, and obtain the newest news.
Review my weblog; move next
Awesome....Working Fine..Thanks a lot...
nice One Suresh
super useful. love this tutorial, bro.
(totally not spambot)
Hi Suresh,
You may be right, it works, But some how event is not firing, I kept break points in VS2010, but they never hit, But on the same page i am using ajax control toolkit casecdeDropdownlist, it works well,
hi sir,
if i'm using two tables in sql database. how to search all the columns in the two tables using auto complete extender control in ajax. i need query for searching all the columns in the two tables..and retrieve data with their prefix string.. matching in all columns data.please help me.. thanks in advance
That is great, thanks your explanation.
thnkz a lot....! it wrkd
i used the same code. my webmethod countries
return all countryname start with the given character. But, in Textbox didn't fire the servicemethod.
Pls help me where i did mistake.
nice article yaar..it helped a lot
how to use multiple autocompleteextender within same page for multiple textboxes. Please help....
Service method is not firing..
Hey Suresh Thanx..!
But I want it for multiple text boxes, i.e. in 1 there are country and in other 1 there are related states! how can i achieve that..?
Hi Suresh,
This is working fine in with out master pages.
then i implemented it in content page. there it's not working....
i give the service path and method name... nothing is working..
Can you please help me....
i tried with number it doesn't shows number instead it shows "undefined". Can you help???
Its not working for me. My code is as follows:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace autocomplete2
{
public partial class WebForm1 : System.Web.UI.Page
{
[System.Web.Script.Services.ScriptMethod()]
[System.Web.Services.WebMethod]
public static List GetName(string txt)
{
SqlConnection con = new SqlConnection("Data Source=192.169.12.72\\jgjg;Initial Catalog=Olox;Persist Security Info=True;User");
con.Open();
SqlCommand cmd = new SqlCommand("select Name from birthday where name like @Name+'%'", con);
cmd.Parameters.AddWithValue("@Name", txt);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
List Name = new List();
for (int i = 0; i < dt.Rows.Count; i++)
{
Name.Add(dt.Rows[i][0].ToString());
}
return Name;
}
}
}
I wanted to use this AutoCompleteExtender tool withthe dynamically created textboxes. so how can i send the id of these textboxes to the script?? can u pls help.... m trying from last 6 days....
Hello Suresh,
I learn ajax through your code examples only. Thank you so much for the great help.
Great !!!. Very helpful to me.
Hey.... thanks for this useful post.
am a bit confuse where ti put this code means on page load or on textbox i.e. protected void txtOEM_TextChanged(object sender, EventArgs e){}
?
Please help me out.
thanks dear
How to search multiple values in a single textbox from database??
Please help me.. for example a textbox have data like this [USA UAE ....]. who to make more than one value searchable in a single textbox from database...
my friend, I need help as would do to show more values in the result, for example I have a client that manages the table name, identity card number and others but I want to show the name concatenated with the identity card number, and would do ... I LEAVE MY SOURCE CODE FOR you to check and help me ... THANKS IN ADVANCE
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Paises.BC;
using Arqhys.BE;
namespace Paises.Web
{
public partial class Catalogo : System.Web.UI.Page
{
ProductoBC pro = new ProductoBC();
protected void Page_Load(object sender, EventArgs e)
{
}
public void loadProductos(int pOpcion, String pCriterio)
{
GridView1.DataSource =
pro.getProductosByCategoriaOrNombre(pOpcion, pCriterio);
GridView1.DataBind();
}
protected void ImageButton1_Click(object sender, ImageClickEventArgs e)
{
if (RadioButton1.Checked)
loadProductos(1, DropDownList1.SelectedValue);
else
loadProductos(2, TextBox1.Text);
}
}
}
Works fine with your code. But unable to succeed in my 3-Tier application. Any suggestions ??
Excellent coding! thank you.
lun
your code is not working..plz help me..
I did everything but it's loading but not firing..
it is not working in firefox but it's working fine in chrome.. why?
Nice Article....
I have spent all day trying to figure out how to do this, I have searched through 4 pages on Google and Youtube and nothing helped me but this. THANK YOU SO MUCH.
Thanks Suresh..Helping Me a lot
I used the code asit is and not getting the country list
i want first name and last in one textbox which are the different columns in database same using this example.. menas in my database first name , middle name and lastname there are three cloumns.. i want this with full name display.. i am trying like your way but displays only matching middle name not full name
Code is working properly..those data am not able to display list below textbox
autocomplete extender in textbox but i want to find the id without intract in database how can do please help me...
Hi Suresh My requirement is to get gridview as autocomplete....or getting multiple column values as autofill..please help me in getting this
hi suresh sir,
i am using it to search email id from database how can i..
why it is not working after i change parameter name from string prefixText to other ?
and why this 2 are use it it is without web service
[System.Web.Script.Services.ScriptMethod()]
[System.Web.Services.WebMethod]
not working any place in webpage can call webservice method ?
sir can we use session inside GetCountries()
Thank you so much!!! understood how it works.. its very important to understand how it works..!!!
Worked fine, just changed the table name, column name in the query and the column number in the for loop... Worked the way i wanted..!!
I want, On selection of any list item textbox fill with selected text and after textbox fill page will automatically redirect on another aspx page with selected item text.
I have done..
Hi Suresh ,
Thanks.
My application is working successfully , But textbox showing only first two letters. After two letters that record is hiding . What is the problem . Help me Thanks lot.............
Alhamdulillah it works!!!
Note: Only a member of this blog may post a comment.