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

ASP.NET e-Commerce website GridView with Product Listing

Introduction : E-Commerce web applications are everywhere these days, and many share a common set of functionality. In this article, I will show how to use the GridView and ListView controls to build a powerful product page with many of the features found on today's e-commerce sites. We'll build a bicycle store product grid using some free clip art bicycle images. The example files are user controls which can be easily added to a page. We're only using three images here to keep the size of the sample application small. In previously I was explained about  Sending Email from Asp.net With Dynamic Content  ,  How To Export gridview data to Excel/PDF , CSV Formates in asp.net C#  , How to print Specific Area in asp.net web page How To- Search records or data in gridview using jQuery  . A shopping cart application would require to display the products in a multi column grid, rather than a straight down list or a table. The each item in a product grid would have
Post a Comment
Read more

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 (e.g. the
Post a Comment
Read more

Nested GridView in ASP.NET Using c# with show/hide

In This example shows how to create Nested GridView In Asp.Net Using C# And VB.NET With Expand Collapse Functionality. Previous post I was Explained about the   ASP.NET e-Commerce website GridView with Product Listing  ,  How To Export gridview data to Excel/PDF , CSV Formates in asp.net C# , Sending Email from Asp.net With Dynamic Content  ,  SQL Server- Case Sensitive Search in SQL Server I have used JavaScript to Create Expandable Collapsible Effect by displaying Plus Minus image buttons. Customers and Orders Table of Northwind Database are used to populate nested GridViews. Drag and place SqlDataSource from toolbox on aspx page and configure and choose it as datasource from smart tags Go to HTML source of page and add 2 TemplateField in <Columns>, one as first column and one as last column of gridview. Place another grid in last templateField column. Markup of page after adding both templatefields will like as shown below. HTML SOURCE < a
Post a Comment
Read more