JQuery UI AutoComplete Textbox From DataBase in ASP.NET with Example

JQuery UI AutoComplete Textbox From DataBase in ASP.NET with Example

In this article I am going to explain how to populate JQuery UI Autocomplete TextBox from Database in ASP.NET. AutoComplete Widget provides suggestions while you type into TextBox.
 
JQuery UI Plugins are free and can be downloaded here or you can directly add CDN reference in your application. In this Article I will create a web site Application and point to "NORTHWIND" Database for demo purpose, you can download NorthWind Database here or you can point to your DataBase.
 
Include reference of required JQuery and CSS files, using it your TextBox will perform autocomplete and render suggestions for user.
 
Adding Script Refernce and calling JQuery AutoComplete Plugin in .aspx Page
 
 I have created a new website and added below code in default.aspx page
 
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>JQuery AutoComplete TextBox Demo</title>
<link rel="Stylesheet" href="http://ajax.aspnetcdn.com/ajax/jquery.ui/1.8.10/themes/redmond/jquery-ui.css" />
</head>
<body>
<form id="form1" runat="server">
<div><h1>AutoComplete Textbox</h1>
City:
<asp:TextBox ID="txtCity" runat="server"></asp:TextBox>
</div>
</form>
<script src="http://ajax.aspnetcdn.com/ajax/jquery/jquery-1.8.0.js"></script>
<script src="http://ajax.aspnetcdn.com/ajax/jquery.ui/1.8.22/jquery-ui.js"></script>
<script type="text/javascript">
$(function () {
$("#txtCity").autocomplete({
source: function (request, response) {
var param = { cityName: $('#txtCity').val() };
$.ajax({
url: "Default.aspx/GetCities",
data: JSON.stringify(param),
dataType: "json",
type: "POST",
contentType: "application/json; charset=utf-8",
dataFilter: function (data) { return data; },
success: function (data) {
response($.map(data.d, function (item) {
return {
value: item
}
}))
},
error: function (XMLHttpRequest, textStatus, errorThrown) {
alert(textStatus);
}
});
},
minLength: 2//minLength as 2, it means when ever user enter 2 character in TextBox the AutoComplete method will fire and get its source data. 
});
});
</script>
</body>
</html>

WebMethod to Fetch City from NorthWind Database
 
I have added a WebMethod "GetCities" in default.aspx.cs to  call it in Source of JQuery Autocomplete
 
[WebMethod]
    public static List<string> GetCities(string cityName)
    {
        List<string> City = new List<string>();
        string query = string.Format("SELECT DISTINCT City FROM Customers WHERE City LIKE '%{0}%'", cityName);
        //Note: you can configure Connection string in web.config also.
        using (SqlConnection con = new SqlConnection(@"Data Source = .\SQLEXPRESS2008R2; initial Catalog = NORTHWND; Integrated Security = true"))
        {
            using (SqlCommand cmd = new SqlCommand(query, con))
            {
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    City.Add(reader.GetString(0));
                }
            }
        }
        return City;
    }
 
Now Press F5 or Run the application to see the result. Enter 2 letters into Textbox to get the suggested list of Cities.
 
Result :
 
 
 
Summary
I hope this Article will help you to learn how to use JQuery AutoComplete plugin in Asp.NET Textbox. Please share your valuable feedback on this.
 
comments powered by Disqus

Related Articles