Introduction:
In this article I will explain how to implement Asp.net autocomplete textbox with database using JQuery.
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.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.
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 Email
|
|||
136 comments :
great sir
You, rock Thanks
binding with ID not done in this sample !!!
dont you think it will execute slowly because you have used SqlDataReader..??
performance wise SqlDataReader is very fast so need to worry about execution.....
sir how can i implement this code in usercontrol its not working
Iam unable to get output means the textbox is not showing any suggestions
Please please respond
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...
very good suresh.
Thanks Very much Suresh,Excellent Stuff
how can i get result's id (userID)?
This is Great.
But how do I populate my location to another textbox when you autocomplete the username
Please please respond
i wrote stored procedure but it not work...any help?
thank you...........
how do I populate my location to another textbox when you autocomplete the username
Didn´t work with Master Page...
Thank you suresh its working..........
Thank you suresh its working..........
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
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.
hi
i copy your code as like your steps...
but my page shows error like..
TypeError: $(".autosuggest").autocomplete is not a function
It is not working mean only alert error is coming nothing else what is the problem can u tell me
@Usha...
i hope you forgot to mention class="autosuggest" for your textbox. Please check it once...
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
how can i get Id in one text box and Name on another text box as output. I dont have much idea of jquery.
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?
please check whether you given your page name in your method or not..
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
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?
@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
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
@Manoj..
You need to pass like MAINFOLDER/CHILDFOLDER/Default.aspx/GetAutoCompleteData
Suresh Dasari , Thankx Yaar ;) i just wanted this code to complete my project ;)
It gives error when i access it using IIS Please Help.
it wont work please help
@Himanshu Patel...
i hope that is the problem with your deployment in IIS. Please check your application...
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.
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
Great Article..
Good Post....But i want multiple values in my text box ...how can I get multiple values?
check this article
http://www.aspdotnet-suresh.com/2012/07/jquery-autocomplete-textbox-with.html
thank you sir...........
Hey I have follwed the post ... but somehow the webmeathod is not getting executed.... Please help .. A little stiuck
Thanks
Shantanu
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
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
Hi its not working in master page,please help me its very urgent
@Saleem...
Check Comment 26 for your solution...
Hi its not working in master page,please help me its very urgent,Please mail to saleem458@gmail.com
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
Hi suresh its urgent please help me out
how could it can be done for windows application.
Good Article. Thank you so much.
really good , Which helped us a lot , thank u ao much
suresh sir this code is not working with Text Box. pls help me.
How to pass dynamic variable instead of txtSearch
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
great job suresh.....keep it up
Very Very Nice Artical and at the end i am going with solution
how can i get result's id (userID)?
I tried this above example with mysql database connection. But it displaying only error message., Please tell me how to clear this problem?
Awesom great work
i got error message box everytime when i write 1st letter in textbox
i got error message box everytime when i write 1st letter in textbox
am i install any tool for it,,plz tell me
Please update your script to latest jquery... This stuff does not work..
@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;
}
}
}
thanks
how can i add scroll when it autosearch in below area...
ho can i design autrosuggest area
sir i am making social networking site,after autocomplete search how to go for that account....pls reply as soon as possible.....
Hello Sir,
Your examples are very helpful, currently now i'm doing project for my reference it is very useeful really suberb ! ! ! !
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
sir plz tell me...how to implement autosuggest?
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
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....
Thanks for example. It's working fine !
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
I'd like to run on local,so I download css and js files.But it didn't work. please help me sir.
it works in localhost fine but wen i publish it give error alert what to do please guide.
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.
hello sir.
please tell me that auto complete for multiple text boxes in a single file....
HI Suresh My txtbox is just loading for long time but it not autocompleting............Help me
Great artical its Working Properly
Thank You...
How to perform jQuery textbox auto-completion using WCF?
Hi.. can it will work if i return a datatable instead of list??? Plz reply me on rns6393@gmail.com
Thanks
how to apply same logic for one more textbox
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
Thank you so much
Works like a charm Thx :)
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..
ur code is not working
my code working fine that's why i am able to show demo. your code is not working please check it
Thank you Mr.Suresh this one helped me alot. Can i get the names when i place cursor in the textbox
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.
Please reply me soon I am waiting for your quick response.
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
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 ;)
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?
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
Hi it's not working in master page...but an ordinary page is works...pls tell me the suggestions
How to execute a function on selecting any of the suggestions
Sir your code working fine but not work on postback.
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.
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....
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
It is not working mean only alert error is coming nothing else what is the problem can u tell me
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,
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.
Thank You So Much... Its very useful article....
Thanks a lot Too easy and very useful.
Sir please can u Suggest how to implement JQuery UI autocomplete textbox inside GridView with database in asp.net? ???????/
Waiting for your response.....
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...
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.
i Get NetworkError: 500 Internal Server Error - http://****/demo.aspx/GetAutoCompleteData
changed the url as well url: "demo.aspx/GetAutoCompleteData",
how to send two value request in GetAutoCompleteData()?
please give me help
hi..
i want to show the letter in every word highlighted mode how is it possible..
Great Article
Hi Suresh,
i want to clear the textbox if user doesnt select from auto data...?
How To Pass multiple parameters??? I want result based on two dropdown list selected
Pls Give me code for jquery autocomplete textbox using xml file
this code is not working in master page you given a comment 26 but it is still not working.give any suggestion
limited values how to show?
Thank u sir................Great
hey suresh thnx it works fine but am trying to set id to hidden filed but it taking blank plz help
Hi
Can i have this feature in MVC with WCF please.
My mail id lovesatti@gmail.com
hi
when i type in textbox i have an error everytime.
please help me
Hi,
I am using auto complete extender (developed as explained in the example) to get the cities based on countries which is working fine in the development environment as well in staging. But, same build throwing exception while working on production server. I am using MS Visual Web Developer 2010 Express
Where the production server has
1.https enabled
2.referring to the live URLs, like
https://ajax.googleapis.com/ajax/libs/jquery/1.8.1/jquery.min.js
https://ajax.googleapis.com/ajax/libs/jquery/2.0.0/jquery.min.js
3.CDN enabled and referring the https:/CDNPath/ajaxlib.js
How to resolve this issue
hi,
The code works in single page. But not works in content page of master page.
I put the .aspx page name in the url part. Am getting the value. But not binding to client side.
Everyone has a doubt implementing the code in content page of master page. So please give a reply.. That helps everyone..
Suresh Not even your source code is working on my side ! its like the function is getting called but the username is never passed to the c# file and so the query gets not parameters, resulting in no result retrieval.
Thanks man it helped me a lot
hi sir i am passing a text on button click to the search textbox but cant find suggestions on page load.When i click space bar i can see the suggestions..Please help me ..urgent......
Note: Only a member of this blog may post a comment.