Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

Ajax AutoCompleteExtender example or sample without using webservice (or) Asp.Net AJAX AutoComplete Extender From Database without using webservice

May 13, 2011
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.


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

<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 RSS subscribe by email Subscribe by Email

113 comments :

Galvin said...

Thanks. Your code is very helpful for me. It is working fine.

bala said...

DO NOT FIRING SERVICE METHOD BOSS

djeevansingh said...

thanku very much... it works...

Raju G said...

thanks for your explanation...
you explained properties of autocompleteextender...
it is awesome

Anonymous said...

didnot firing process

Suresh Dasari said...

hi it's working fine i think you did mistake in your application

MITHUN G said...

Hi Suresh

Your code is super

thanks.

Anonymous said...

The most important thing for me: the code provided in this example actually works. Thank you!

Madhu Mangalagiri said...

Thank you very much for publish this code

Anonymous said...

your site is very useful for me thanks

Mohd. Rashid Siddique said...

Good Post.It Really helped me in understanding AutoCompleteExtender.

Thanks.

Anonymous said...

its not working. somethings is missing.

pls complete this........

Suresh Dasari said...

it's working fine please check your code without working how can i place demo..

Abs said...

Hi ur site is provide me good knowledge..

but this Example is not working. somethings is missing.

Anonymous said...

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.

san said...

suresh thank you.It helped me a lot.

mannu said...

Code in vb.net

Anonymous said...

Very nice post! Perfectly working. Thanks!

Anonymous said...

this site is very helpful

Anonymous said...

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!

Anonymous said...

this code return only contryname how to find contry code..

manas said...

thanks, really it is working...

Anonymous said...

i cant't see populate data

Anonymous said...

@suresh: your presentation very simple,easy to understand.. It really helped me a lot...

avikghosh22 said...

thank u sir....

Ashish Gupta said...

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

arun said...

thanks..i need one more help

this code return contryname how to find SELECTED contry code.(that is ID)

Anonymous said...

hi team, my breakpoint is not reaching service method it ends at public webservice() {}. Please help

Anonymous said...

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.

Ravi said...

Thanks for your code. Working fine. Thanks alot.

Unknown said...
This comment has been removed by the author.
Unknown said...
This comment has been removed by the author.
Unknown said...

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

Anonymous said...

your code is working like magic.
thank you.

Unknown said...

How can i retrieve ID and name, name is visible and ID is hidden, deal with them like a drop down list (DataValueFeild & DataTextFeild)

Anonymous said...

sorry...ur code is not working

Suresh Dasari said...

@Unknown.... Please check this link http://www.aspdotnet-suresh.com/2012/07/jquery-autocomplete-textbox-with.html

Suresh Dasari said...

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...

महेश तिवारी, वाराणसी, उत्तर प्रदेश, भारत said...

I used successfully.But List appear on textbox.I want to display list below textbox.

Anonymous said...


I can not understand the "dbconnection" from
ConfigurationManager.ConnectionStrings["dbconnection"].ToString()
by how i can get it

Suresh Dasari said...

ConfigurationManager.ConnectionStrings["dbconnection"].ToString() is your database connection string. Please check above code i added the that one also...

Ankit Jain said...

GREAT....................!!!!

Anonymous said...

it's really gr8....thnx yaar...

Rushikesh Pawar said...

hey...its working great......keep uploading such a new things.....

Anonymous said...

its super yarr... working excellent..

Unknown said...

Good day, a question as I can do to put more of an AutoCompleteExtender in a single website.

thanks.

Ajay said...

Thank you Darling

Narendran said...

THANKS A LOT, It helps me a lot with clear explanation.

Narendran

Unknown said...

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

Free Open Source Developers said...

dude this example is very helpful to me thank you...

javeed said...

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..........

Unknown said...

Hi i like this solution but i also have another problem actually i want to Autogenerate A specific Id number in which first two digits are year ,second are Month and last four are serial number like 12110001 12-year,11-month,0001 serial no. and when my month will change then serial id will again set to 0001 i.e 12120001 can you help me

Unknown said...

ThanQ sir

Unknown said...

thanks..

your post are very use full For ME.. Really Thanks

Anonymous said...

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 ?

Anonymous said...

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..

Mathan Sivanantham said...

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.,




Mathan Sivanantham said...
This comment has been removed by the author.
Mathan Sivanantham said...

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.,

Anonymous said...

Nice Article, Thanks...

hermes said...

Amazing summary bookmarked the site.

Anonymous said...

thanks.............

Anonymous said...

Your post says "without using webservice" yet you do use a webservice!!!

Ashish Pratap Singh said...

good one it really worked for me..........

Unknown said...

Thanks boss..it helped me

Anonymous said...

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.....

Anonymous said...

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

Dhruv Parikh said...

Awesome....Working Fine..Thanks a lot...

Anonymous said...

nice One Suresh

Anonymous said...

super useful. love this tutorial, bro.
(totally not spambot)

Suman's BLOG said...

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,

ghanikhan said...

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

home said...

That is great, thanks your explanation.

Anonymous said...

thnkz a lot....! it wrkd

Unknown said...

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.

Anonymous said...

nice article yaar..it helped a lot

Anonymous said...

how to use multiple autocompleteextender within same page for multiple textboxes. Please help....

Anonymous said...

Service method is not firing..

Gaurav Agarwal said...

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..?

Unknown said...

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....

Ravi said...

i tried with number it doesn't shows number instead it shows "undefined". Can you help???

venki said...

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;

}
}
}

Anonymous said...

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....

Anonymous said...

Hello Suresh,
I learn ajax through your code examples only. Thank you so much for the great help.

Techne Talk said...

Great !!!. Very helpful to me.

Unknown said...

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.

Unknown said...

thanks dear

Unknown said...

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...

Unknown said...
This comment has been removed by the author.
Unknown said...

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

}
}

Anonymous said...

Works fine with your code. But unable to succeed in my 3-Tier application. Any suggestions ??

Anonymous said...

Excellent coding! thank you.

Anonymous said...

lun

Anonymous said...

your code is not working..plz help me..

Anonymous said...

I did everything but it's loading but not firing..

praveen said...

it is not working in firefox but it's working fine in chrome.. why?

Anonymous said...

Nice Article....

Anonymous said...

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.

Anonymous said...

Thanks Suresh..Helping Me a lot

Anonymous said...

I used the code asit is and not getting the country list

Anonymous said...

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

Ramya B R said...

Code is working properly..those data am not able to display list below textbox

Unknown said...

autocomplete extender in textbox but i want to find the id without intract in database how can do please help me...

Unknown said...

Hi Suresh My requirement is to get gridview as autocomplete....or getting multiple column values as autofill..please help me in getting this

anand said...

hi suresh sir,
i am using it to search email id from database how can i..

SACHIN said...

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]

Anonymous said...

not working any place in webpage can call webservice method ?

Syed Shahid Ali said...

sir can we use session inside GetCountries()

Anonymous said...

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..!!

Unknown said...

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.

Unknown said...

I have done..

Unknown said...

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.............

Aasim said...

Alhamdulillah it works!!!

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.