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:
  
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
 
<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&#8221; rel=”stylesheet” type=”text/css”/>
<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>
<div>
<label for=”tbAuto”>Enter UserName: </label>
<input type=”text” id=”txtSearch” />
</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  
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: