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

JQuery UI autocomplete textbox with database in asp.net

Mar 26, 2012
Introduction:

In this article I will explain how to implement Asp.net autocomplete textbox with database using JQuery.


Description:
  
In previous articles I explained Ajax autocomplete extender example and JQuery autocomplete textbox with images in asp.net. Now I will explain another article relating to autocomplete textbox with JQuery UI in asp.net.
To implement this concept first we need to design table in database to save user details in database.

Column Name
Data Type
Allow Nulls
UserId
int(set identity property=true)
No
UserName
varchar(50)
Yes
Location
nvarchar(max)
Yes
After completion table design enter some of user details in database to work for our sample.
Write the following code in your aspx page

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>AutoComplete Box with jQuery</title>
<link href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.1/themes/base/jquery-ui.css" rel="stylesheet" type="text/css"/>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js"></script>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.1/jquery-ui.min.js"></script> 
<script type="text/javascript">
$(document).ready(function() {
SearchText();
});
function SearchText() {
$(".autosuggest").autocomplete({
source: function(request, response) {
$.ajax({
type: "POST",
contentType: "application/json; charset=utf-8",
url: "Default.aspx/GetAutoCompleteData",
data: "{'username':'" + document.getElementById('txtSearch').value + "'}",
dataType: "json",
success: function(data) {
response(data.d);
},
error: function(result) {
alert("Error");
}
});
}
});
}
</script>
</head>
<body>
<form id="form1" runat="server">
<div class="demo">
<div class="ui-widget">
<label for="tbAuto">Enter UserName: </label>
<input type="text" id="txtSearch" class="autosuggest" />
</div>
</form>
</body>
</html>
If you observe above code in header section I added some of script and css files by using those files we have a chance to display auto complete text with css style. To get those files just add those urls in in your application.

Another thing here we need to know is script function in header section

$(".autosuggest").autocomplete({
source: function(request, response) {
$.ajax({
type: "POST",
contentType: "application/json; charset=utf-8",
url: "Default.aspx/GetAutoCompleteData",
data: "{'username':'" + document.getElementById('txtSearch').value + "'}",
dataType: "json",
success: function(data) {
response(data.d);
},
error: function(result) {
alert("Error");
}
});
}
});
This is the function declaration of JSON format we are using this JSON function to call web methods using JQuery $.ajax() whenever we need to make Ajax call with JQuery then we will use JSON functions like as we mentioned in above format. Here type, ContentType and dataType are same for all functions only url, data and success functions will vary based on our requirement.

url: This is the path of our Webmethods

data: we will pass parameters using this parameter if no parameters then we need to use data: "{}"

success: Once our web method execution completed then success function will execute and return username matching’s

(Note: JSON is a case sensitive please be careful before write JSON format of data)

Now open code behind file and add following namespaces


using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Web.Services;
After that write the following code

C#.NET Code

protected void Page_Load(object sender, EventArgs e)
{

}
[WebMethod]

public static List<string> GetAutoCompleteData(string username)
{
List<string> result = new List<string>();
using (SqlConnection con = new SqlConnection("Data Source=SureshDasari;Integrated Security=true;Initial Catalog=MySampleDB"))
{
using (SqlCommand cmd = new SqlCommand("select DISTINCT UserName from UserInformation where UserName LIKE '%'+@SearchText+'%'", con))
{
con.Open();
cmd.Parameters.AddWithValue("@SearchText", username);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
result.Add(dr["UserName"].ToString());
}
return result;
}
}
}
VB.NET Code:


Imports System.Collections.Generic
Imports System.Data.SqlClient
Imports System.Web.Services

Partial Class Default
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load

End Sub

<WebMethod()> _
Public Shared Function GetAutoCompleteData(ByVal username As String) As List(Of String)
Dim result As New List(Of String)()
Using con As New SqlConnection("Data Source=SureshDasari;Integrated Security=true;Initial Catalog=MySampleDB")
Using cmd As New SqlCommand("select DISTINCT UserName from UserInformation where UserName LIKE '%'+@SearchText+'%'", con)
con.Open()
cmd.Parameters.AddWithValue("@SearchText", username)
Dim dr As SqlDataReader = cmd.ExecuteReader()
While dr.Read()
result.Add(dr("UserName").ToString())
End While
Return result
End Using
End Using
End Function
End Class
Now run your application and check the output that would be like this   


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

126 comments :

Anonymous said...

great sir

Anonymous said...

You, rock Thanks

Anonymous said...

binding with ID not done in this sample !!!

Anonymous said...

dont you think it will execute slowly because you have used SqlDataReader..??

Suresh Dasari said...

performance wise SqlDataReader is very fast so need to worry about execution.....

Anonymous said...

sir how can i implement this code in usercontrol its not working

Anonymous said...

Iam unable to get output means the textbox is not showing any suggestions

Anonymous said...

Please please respond

Suresh Dasari said...

if you are not getting any suggestions means you are not passing parameters in correct way or your table contain any data with that specific text. Please check it once...

Anonymous said...

very good suresh.

Anonymous said...

Thanks Very much Suresh,Excellent Stuff

Unknown said...

how can i get result's id (userID)?

Anonymous said...

This is Great.
But how do I populate my location to another textbox when you autocomplete the username

Anonymous said...

Please please respond

madanforall said...

i wrote stored procedure but it not work...any help?

gaurav said...

thank you...........

Anonymous said...

how do I populate my location to another textbox when you autocomplete the username

Anonymous said...

Didn´t work with Master Page...

sureshkumar said...

Thank you suresh its working..........

sureshkumar said...

Thank you suresh its working..........

ANIL KUMAR REDDY said...

hi suresh this is anilkumar.K ,
very good artical this, but i face one problem
auto fill textbox based on two parameters

in whre condtion i need to pass 2 parameters

like name,practiceid. iam unable to pass send parameter, can you help me.

anilreddy.mca03@gmail.com

Anmol said...

I want to get the location along with the Username when i type in the search box and have - in between the result. Also the search should start after i type in the 2 or 3 characters. How can i modify your code to work with this. Can you help me on this please.

Usha said...

hi
i copy your code as like your steps...
but my page shows error like..

TypeError: $(".autosuggest").autocomplete is not a function

Ranjan said...

It is not working mean only alert error is coming nothing else what is the problem can u tell me

Suresh Dasari said...

@Usha...
i hope you forgot to mention class="autosuggest" for your textbox. Please check it once...

Suresh Dasari said...

If anyone want to implement autocomplete textbox in master page you need to create handler or webservice file for that check this post
http://www.aspdotnet-suresh.com/2012/08/using-jquery-autocomplete-with-aspnet.html

Anonymous said...

how can i get Id in one text box and Name on another text box as output. I dont have much idea of jquery.

Anonymous said...

Hi

This is great thank you. However when I try and implement it from a child page within a master page it doesn;t work. have you any ideas why?

Suresh Dasari said...

please check whether you given your page name in your method or not..

Anonymous said...

Thanks Suresh - it's working now from master page - it was a problem with the case of class=autosuggest. Only problem now is that once a choice is selected it triggers a dynamic regular expression that checks if the username is a valid email address. The dynamic message displays even though the username passes the regular expression. The page then still allows me to submit the form despite the regexp message. Have you come across this before? Thanks, Simon

Anonymous said...

Note to add: after selecting a choice, the focus is at the end of the text within the textbox and the regular expression message is displayed. If I then hit the enter key the dynamic regexp message disappears.is there a way to stimulate an 'enter' key press once the choice has been selected?

Suresh Dasari said...

@Comment 12,27...
Please check below post to get get selected value from autocomplete
http://www.aspdotnet-suresh.com/2012/08/how-to-get-selected-value-from-jquery.html

Manoj said...

HOW TO PASS THE PAGE NAME IF THE PAGE IS INSIDE A FOLDER..LIKE

MAINFOLDER->CHILDFOLDER->DEFAULT.ASPX

PLEASE PLEASE GIVE ME THE SOLUTION. IT IS URGENT

Suresh Dasari said...

@Manoj..
You need to pass like MAINFOLDER/CHILDFOLDER/Default.aspx/GetAutoCompleteData

Amit Gupta said...

Suresh Dasari , Thankx Yaar ;) i just wanted this code to complete my project ;)

Himanshu Patel said...

It gives error when i access it using IIS Please Help.
it wont work please help

Suresh Dasari said...

@Himanshu Patel...
i hope that is the problem with your deployment in IIS. Please check your application...

Himanshu Patel said...

I have published my website but it is still not working.

When i run my application from from Visual studio then it works fine but when i access it from IIS or localhost it gives error.


Can you tell me the reason for the same.

please help me for the same.

sandeep sharma said...

bro please help me, i have three textbox like first for country, second for state and third for city, so i want whenever a user choose country , all the states related to it will be there in second textbox and same in three, but its not working plese help me

Anonymous said...

Great Article..

Anonymous said...

Good Post....But i want multiple values in my text box ...how can I get multiple values?

Suresh Dasari said...

check this article
http://www.aspdotnet-suresh.com/2012/07/jquery-autocomplete-textbox-with.html

Anonymous said...

thank you sir...........

Anonymous said...

Hey I have follwed the post ... but somehow the webmeathod is not getting executed.... Please help .. A little stiuck

Thanks
Shantanu

christian arellano said...

sir im receiving an error i only received a alert error. can't move on.. please please.. respond
thank you sir
christian.r.arellano@gmail.com

christian arellano said...

and sir I'm receiving an error that "The class or CssClass value is not defined" please please respond. thank you sir.
christian.r.arellano@gmail.com

Saleem said...

Hi its not working in master page,please help me its very urgent

Suresh Dasari said...

@Saleem...
Check Comment 26 for your solution...

Saleem said...

Hi its not working in master page,please help me its very urgent,Please mail to saleem458@gmail.com

Saleem said...

Yah i had created web service even it is not working suresh,and more over i had tried with jquery with code behind,this works fine without master page,but not working with master page

Saleem said...

Hi suresh its urgent please help me out

Anonymous said...

how could it can be done for windows application.

Ramya said...

Good Article. Thank you so much.

Anonymous said...

really good , Which helped us a lot , thank u ao much

rahul Upadhyay said...

suresh sir this code is not working with Text Box. pls help me.

rahul Upadhyay said...
This comment has been removed by the author.
Ramya said...

How to pass dynamic variable instead of txtSearch

Anonymous said...

Hi,
I have a problem when I pass a value in document.getElementById('txtSearch').value that contains a '.
For example txtSearch = Sant'Andrea.
The function SearchText() return error.
What I have to do?
Thank You very much.
Bye
Stefano

Anonymous said...

great job suresh.....keep it up

Waqar Ahmad said...

Very Very Nice Artical and at the end i am going with solution

sandeep kumar said...

how can i get result's id (userID)?

mathan said...

I tried this above example with mysql database connection. But it displaying only error message., Please tell me how to clear this problem?

jose angel palacios caballero said...
This comment has been removed by the author.
jose angel palacios caballero said...

Amazing

Jaya Kumar said...

Awesom great work

rohit said...

i got error message box everytime when i write 1st letter in textbox

rohit said...

i got error message box everytime when i write 1st letter in textbox
am i install any tool for it,,plz tell me

Anonymous said...

Please update your script to latest jquery... This stuff does not work..

Anonymous said...

@Mathan: for mysql I used...

public static List GetAutoCompleteData(string username)
{
username = "%" + username + "%";
List result = new List();
using (MySqlConnection con = new MySqlConnection("Server=localhost;Port=3306;Database=db;Uid=uid;Pwd=pwd;"))
{
using (MySqlCommand cmd = new MySqlCommand("select DISTINCT Column from Table where Column LIKE @param1", con))
{
con.Open();
cmd.Parameters.AddWithValue("@param1", username);
MySqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
result.Add(dr["Column"].ToString());
}
return result;
}
}
}

Avinash Kalburge said...

thanks

priyanka said...

how can i add scroll when it autosearch in below area...

priyanka said...

ho can i design autrosuggest area

Anonymous said...

sir i am making social networking site,after autocomplete search how to go for that account....pls reply as soon as possible.....

Gowthame Jahan said...

Hello Sir,
Your examples are very helpful, currently now i'm doing project for my reference it is very useeful really suberb ! ! ! !

Anonymous said...

hello sir,
i want to search employee names in my database based on any characters that appears in that name from textbox in asp.net.
Ex-suppose i want to search Suresh ..i can search it with typing any characters in textbox that appears in the name "Suresh".please help me

Arwinder Singh said...

sir plz tell me...how to implement autosuggest?

Anonymous said...

Is there a way to have 2 auto complete boxes? EX. The first one would be last name. Then then second would be a persons first name with the corresponding last name. So the selected last name would be a requirement for the first name.
for example the statement would be
Select firstname from People where lastname='@LastName' and firstname LIKE '%'+@firstname'%''.

Thoughts?
I would greatly appreciate any help! Thanks

Anonymous said...

hi suresh, after selecting one value from down populated values i want to fie a textchanged event but it is not firing can you please help me....

Genel said...

Thanks for example. It's working fine !

jaydee777 said...

Hi Suresh,
I tried your demo and it works fine. Thank you!.
do you have a demo where I could accomplish this using the multiple value option as demoed here http://jqueryui.com/autocomplete/#multiple
Thank you

Khinsandar Win said...

I'd like to run on local,so I download css and js files.But it didn't work. please help me sir.

sudha said...

it works in localhost fine but wen i publish it give error alert what to do please guide.

Anonymous said...

Hi Sir,

I am Umesh Kumar i had used your code. its working fine in local but when publish our project this not working. please tell the solution.

venu shivannagari said...

hello sir.
please tell me that auto complete for multiple text boxes in a single file....

SATHISH said...

HI Suresh My txtbox is just loading for long time but it not autocompleting............Help me

Anonymous said...

Great artical its Working Properly


Thank You...

Anonymous said...

How to perform jQuery textbox auto-completion using WCF?

rnstheprince said...

Hi.. can it will work if i return a datatable instead of list??? Plz reply me on rns6393@gmail.com
Thanks

rajkamal said...

how to apply same logic for one more textbox

Leena said...

Hello Suresh

Can you please help on how to get the values in other dropdown based on the selection of the first autocomplete textbox.

Thanks

Anonymous said...

Thank you so much

Dude said...

Works like a charm Thx :)

gatikash bajaj said...

HI, i am a fresher...

i want to know how to read Static Json data...can you give any example that would be very helpful..

Anonymous said...

ur code is not working

Suresh Dasari said...

my code working fine that's why i am able to show demo. your code is not working please check it

Sravan Kumar said...

Thank you Mr.Suresh this one helped me alot. Can i get the names when i place cursor in the textbox

Anonymous said...

Auto suggest is not working.Actually in my scenario I have taken Auto suggest text in the default.aspx but I have used master page for other content.but its not working(My text box is in the default page not in master page).But when I remove master page from this than its working fine.So can tell for this Its compulsory to take handler.If without handler u have a solutions than plz let me know.please reply me very soon Its very urgent.

Anonymous said...

Please reply me soon I am waiting for your quick response.

Anonymous said...

Sir...I'm stuck with a problem..plz help me to solve this.....The problem is , when I select an item from autocomplete list by using mouse click ,I'm not getting the item to the textbox......But sometimes it works properly.....Thank u

Sarath said...

your net connection may be slow.
thats y u r not getting output @ frqnt tyms.
try to call the files locally that are called remotely in the shown sample above,
cheers ;)

Baskaran L said...

Hi Suresh,
What do you do for the same using ascx file web method. We use web controls to integrate with CMS tool as a web part so I need to do the same in webcontrol using my business model to get ID,value data. When I tried replacing aspx file with ascx file, it does not seem to be calling the web method. Have you tried the same when you tried with aspx?

Anonymous said...

Hi Suresh,
The value is returning the result but not in the textbox, its loading, will u plz help me......
i am using vs 2005

Anonymous said...

Hi it's not working in master page...but an ordinary page is works...pls tell me the suggestions

Sri Kumarans School said...

How to execute a function on selecting any of the suggestions

Abhilash Kumar said...

Sir your code working fine but not work on postback.

Anonymous said...

Hi Suresh,
Works perfectly with input field but not with textbox. Would you please help? I tried replacing the .autosuggest with #txtSearch but did not work at all. Thanks in advance.

Suresh Muttagi said...

Hi suresh, I have used freindly URl in my project. But i need a autocomplete textbox in one of my page. I have included ur code but, the code written to fetch data is not being called. Its very necessary to do it . Please help me out....

Anonymous said...

Hi Suresh.. its great article with useful details...

while implementing this on my sharepoint web page, the "Object doesn't support property or method 'autocomplete'" error is generated..

FYI- I have added required JS libraries

Shinty said...

It is not working mean only alert error is coming nothing else what is the problem can u tell me

Anonymous said...

its working fine for normal aspx pages but its not working for master pages .....sir i am triyed to develop this code for child page but its not working properly,

NILMANI BHANJA said...
This comment has been removed by the author.
neeraj kanojiya said...

Hi suresh.

I hv implemented the above functionality in my aspx page. After first page load it works fine but problem is when any postback occurs in same page, so this auto suggestion deesn't work. Pleaase help...

Random Thoughts said...

I used this code in the example and works great on my web page. I have another panel and a datagrid in it where I wan to use autocomplete as well. Is it possible to use it for more than 1 input fields? My datagrid has itemtemplate field and I changed that from textbox to input field but it did not work. I had added another function similar to "GetAutoCompleteData" to get email but errored out.

Manish Kumar said...
This comment has been removed by the author.
Manish Kumar said...
This comment has been removed by the author.
Manish Kumar said...

Thank You So Much... Its very useful article....

Vishu Minhas said...

Thanks a lot Too easy and very useful.

Anonymous said...

Sir please can u Suggest how to implement JQuery UI autocomplete textbox inside GridView with database in asp.net? ???????/
Waiting for your response.....

Anonymous said...

i am facing a problem.my asmx page is inside the project folder.when i am accessing that asmx page from outside the folder...there is showing a error 401 .unauthorized....please help me...

Anonymous said...

Hi Suresh your article was nice but if I want to fetch the data from LDAP server using query like populating email id's in textbox. I have implemented but performance is very low. I have also set the minimum length. please suggest me what will be the correct way.

Anonymous said...

i Get NetworkError: 500 Internal Server Error - http://****/demo.aspx/GetAutoCompleteData
changed the url as well url: "demo.aspx/GetAutoCompleteData",

ALPESH VIRADIYA said...

how to send two value request in GetAutoCompleteData()?
please give me help

Anonymous said...

hi..
i want to show the letter in every word highlighted mode how is it possible..

Anonymous said...

Great Article

MADHU said...

Hi Suresh,

i want to clear the textbox if user doesnt select from auto data...?

Anonymous said...

How To Pass multiple parameters??? I want result based on two dropdown list selected

Give your Valuable Comments

Other Related Posts

© 2010-2012 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.