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