Skip to main content

JQuery UI autocomplete textbox with database in asp.net


Introduction:

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


Description:
  
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
_
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   
Labels:

Comments

Post a Comment

Popular posts from this blog

How to hide url parameters in asp.net

There are different ways to Hide the URL in asp.net , you can choose any one from bellow options . Previously I was Explained about the  Difference between Convert.tostring and .tostring() method Example  ,   Reasons to use Twitter Bootstrap , How to Register AJAX toolkit in web.config file in asp.net a) Using Post Method b) Using Of Session . c) URL Encoding & decoding process . d) Using Server.Transfer() instead of Response.Redirect() method (1)Use a form and POST the information. This might require additional code in source pages, but should not require logic changes in the target pages (merely change Request.QueryString to Request.Form). While POST is not impossible to muck with, it's certainly less appealing than playing with QueryString parameters. (2)Use session variables to carry information from page to page. This is likely a more substantial effort compared to (1), because you will need to take session variable checking into account...
Post a Comment
Read more

How to send mail asynchronously in asp.net with MailMessage

With Microsoft.NET Framework 2.0 everything is asynchronous and we can send mail also asynchronously. This features is very useful when you send lots of bulk mails like offers , Discounts , Greetings . You don’t have to wait for response from mail server and you can do other task . By using     SmtpClient . SendAsync Method (MailMessage, Object)    you need to do  System.Net.Mail has also added asynchronous support for sending email. To send asynchronously, you need need to Wire up a SendCompleted event Create the SendCompleted event Call SmtpClient.SendAsync smtpClient.send() will initiate the sending on the main/ui  thread and would block.  smtpClient.SendAsync() will pick a thread from the .NET Thread Pool and execute the method on that thread. So your main UI will not hang or block . Let's create a simple example to send mail. For sending mail asynchronously you need to create a event handler that will notify that mail success...
Post a Comment
Read more

12 Sentences that Change Your Attitude at Work

If you work with the right attitude and right way, you’ll have your productivity up another level, and eventually, success will be just a step away. These are the 12 encouraging sentences that could change your attitude at work. #1 Don’t work harder, work deeper. #2 When you are young, work to learn, not to earn. #3 If you work really hard and are kind, amazing things will happen. #4 Productivity is never an accident. It is always the result of commitment to excellence, intelligent planning, and focused effort. #5 Most people work just hard enough not to get fired and get paid just enough money not to quit. #6 Boost your productivity by taking action immediately after setting a target even without perfect plans. Adjust your course along the way. #7 To be successful, the first thing to do is to fall in love with your work. #8 Identify your peak hour of productivity. Schedule your most important task for this period. Work on unimportant tasks during non-peak hours. #9 Be a mo...
Post a Comment
Read more