jQuery UI Autocomplete textbox with database in Asp.Net C#,VB with example ~ Asp.Net,C#.Net,VB.Net,MVC,jQuery,JavaScipt,AJAX,WCF,Sql Server example

Introduction: In this article I am going to explain how to populate jquery UI auto complete in asp.net text box to auto suggest city names fetched from database on starting typing in textbox using web service as shown in image below:

jquery UI Autocomplete textbox expamle in Asp.Net

This article also serves the following:

How to implement jQuery AutoComplete in Content Page with Master Page in ASP.Net
Jquery UI autocomplete not working inside update panel

In previous articles i explained Ajax autocompleteextender control example in asp.net using web service and Jqueryand json to bind gridview from sql server database and Jquery ajax to bind dropdownlist dynamically from sql server database and Jquery ajax json example in asp.net to insert data into sqlserver database without postback and Jquery to calculate running total of textbox values

Description: Have you ever noticed how the related suggestions highlights as you start typing in the Google search box? This is called AutoComplete. We can also implement this functionality in our asp.net web applications using jquery UI AutoComplete

The concept is simple. When you start typing in the TextBox, It fetches the matching strings from the database and display while typing e.g. when you type a single character 'm' in the TextBox then all the city names starting with 'm' will be fetched from the database and displayed as a suggestion in textbox.

There are two methods to implement jquery UI Autocomplete:

By defining web method in web service to fetch data from database based on search term passed.
By defining static web method on page to fetch data from database based on search term passed. Read the article

In this article I am going to implement the first method.

Implementation: Let’s create a demo page to demonstrate autocomplete feature using jquery.

First of all create a table using following script

CREATE TABLE tbCityMaster

(

CityId INT NOT NULL PRIMARY KEY IDENTITY(1,1),

CityName VARCHAR(100)

)

Now insert some dummy data into this tables using following script

INSERT INTO tbCityMaster (CityName)

VALUES

('Chandigarh'),

('Agra'),

('Bharatnagar'),

('Pathankot'),

('Amritsar'),

('Bhiwani'),

('Delhi'),

('Panchkula'),

('Gurgaon'),

('Baroda'),

('Palampur'),

('Bhuj')

('Bathinda'),

('Patiala'),

('Panipat');

Now create a stored procedure to fetch top 15 city names based on the text we enter in autocomplete textbox.

CREATE PROCEDURE spGetCityNames

(

@SearchText VARCHAR(50)

)

BEGIN

SET NOCOUNT ON;

SELECT TOP 15 CityName FROM tbCityMaster WHERE CityName LIKE @SearchText+'%'

END

Now create connection string in web.config as:

</connectionStrings>

Note: Replace the Data Source and the Initial Catalog as per your application.

Now add a web service in the project: for this go to Website menu -> Add new item -> select Web Service and name Cities.asmx

A web service will be added in your project and a new folder App_Code will also be created in your project containing Cities.cs file.

Below is the code for the web service which will handle the jQuery Autocomplete calls and will return the matching records from the tbCityMaster table.

Asp.Net C# Code

In Cities.cs file create a web method to fetch city names from sql server database as:

using System;

using System.Collections.Generic;

using System.Configuration;

using System.Data;

using System.Data.SqlClient;

using System.Linq;

using System.Web;

using System.Web.Script.Services;

using System.Web.Services;

/// <summary>

/// Summary description for WebService

/// </summary>

[WebService(Namespace = "http://tempuri.org/")]

[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]

// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.

[System.Web.Script.Services.ScriptService]

public class Cities : System.Web.Services.WebService

{

[WebMethod]

[ScriptMethod(ResponseFormat = ResponseFormat.Json)]

public string[] FetchCityNames(string SearchText)

{

List<string> CityList = new List<string>();

using (SqlConnection con = new SqlConnection())

{

con.ConnectionString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;

using (SqlCommand cmd = new SqlCommand("spGetCityNames", con))

{

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@SearchText", SearchText);

cmd.Connection = con;

con.Open();

using (SqlDataReader dr = cmd.ExecuteReader())

{

while (dr.Read())

{

CityList.Add(dr["CityName"].ToString());

}

con.Close();

}

return CityList.ToArray();

}

Asp.Net VB Code

Imports System.Web

Imports System.Web.Services

Imports System.Web.Services.Protocols

Imports System.Data.SqlClient

Imports System.Data

' To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.

<System.Web.Script.Services.ScriptService()> _

<WebService(Namespace:="http://tempuri.org/")> _

<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _

<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _

Public Class Cities

Inherits System.Web.Services.WebService

<WebMethod()> _

Public Function FetchCityNames(SearchText As String) As String()

Dim CityList As New List(Of String)()

Using con As New SqlConnection()

con.ConnectionString = ConfigurationManager.ConnectionStrings("conStr").ConnectionString

Using cmd As New SqlCommand("spGetCityNames", con)

cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.AddWithValue("@SearchText", SearchText)

cmd.Connection = con

con.Open()

Using dr As SqlDataReader = cmd.ExecuteReader()

While dr.Read()

CityList.Add(dr("CityName").ToString())

End While

End Using

con.Close()

End Using

Return CityList.ToArray()

End Using

End Function

End Class

HTML Source Code

$(function () {

CityAutoComplete();

});

function pageLoad() {

initPageRequestManager();

}

function initPageRequestManager() {

var prmInstance = Sys.WebForms.PageRequestManager.getInstance();

prmInstance.add_endRequest(function () {

//re-bind jquery events

CityAutoComplete();

});

}

function CityAutoComplete() {

$("#<%=txtSearchCity.ClientID %>").autocomplete({

autoFocus: true,

source: function (request, response) {

var inputdata = "{'SearchText': '" + request.term + "'}";

$.ajax({

url: '<%=ResolveUrl("~/Cities.asmx/FetchCityNames") %>',

data: inputdata,

dataType: "json",

type: "POST",

contentType: "application/json; charset=utf-8",

success: function (data) {

response($.map(data.d, function (item) {

return {

label: item

}

}))

error: function (response) {

alert(response.responseText);

failure: function (response) {

alert(response.responseText);

}

});

minLength: 1,

});

}

</script>

#dvSearch {

background: #e1e1e1; /* Fallback color for non-css3 browsers */

width: 400px;

/* Gradients */

background: -webkit-gradient( linear,left top, left bottom, color-stop(0, rgb(243,243,243)), color-stop(1, rgb(225,225,225)));

background: -moz-linear-gradient( center top, rgb(243,243,243) 0%, rgb(225,225,225) 100%);

/* Rounded Corners */

border-radius: 17px;

-webkit-border-radius: 17px;

-moz-border-radius: 17px;

/* Shadows */

box-shadow: 1px 1px 2px rgba(0,0,0,.3), 0 0 2px rgba(0,0,0,.3);

-webkit-box-shadow: 1px 1px 2px rgba(0,0,0,.3), 0 0 2px rgba(0,0,0,.3);

-moz-box-shadow: 1px 1px 2px rgba(0,0,0,.3), 0 0 2px rgba(0,0,0,.3);

}

/*** TEXT BOX ***/

.searchbox {

background: #fafafa; /* Fallback color for non-css3 browsers */

/* Gradients */

background: -webkit-gradient( linear, left bottom, left top, color-stop(0, rgb(250,250,250)), color-stop(1, rgb(230,230,230)));

background: -moz-linear-gradient( center top, rgb(250,250,250) 0%, rgb(230,230,230) 100%);

border: 0;

border-bottom: 1px solid #fff;

border-right: 1px solid rgba(255,255,255,.8);

font-size: 16px;

margin: 4px;

padding: 5px;

width: 380px;

/* Rounded Corners */

border-radius: 17px;

-webkit-border-radius: 17px;

-moz-border-radius: 17px;

/* Shadows */

box-shadow: -1px -1px 2px rgba(0,0,0,.3), 0 0 1px rgba(0,0,0,.2);

-webkit-box-shadow: -1px -1px 2px rgba(0,0,0,.3), 0 0 1px rgba(0,0,0,.2);

-moz-box-shadow: -1px -1px 2px rgba(0,0,0,.3), 0 0 1px rgba(0,0,0,.2);

}

/*** USER IS FOCUSED ON TEXT BOX ***/

.searchbox:focus {

outline: none;

background: #fff; /* Fallback color for non-css3 browsers */

/* Gradients */

background: -webkit-gradient( linear, left bottom, left top, color-stop(0, rgb(255,255,255)), color-stop(1, rgb(235,235,235)));

background: -moz-linear-gradient( center top, rgb(255,255,255) 0%, rgb(235,235,235) 100%);

}

</style>

</head>

<body>

<div>

<asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>

<asp:UpdatePanel ID="UpdatePanel1" runat="server">

<legend>jQuery UI Autocomplete example in asp.net</legend>

<asp:TextBox ID="txtSearchCity" CssClass="searchbox" placeholder="Search by city name" runat="server"></asp:TextBox>

</div>

</fieldset>

</ContentTemplate>

</asp:UpdatePanel>

</div>

</form>

</body>

</html>

Now over to you:

A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linkedin and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates.

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..

jQuery UI Autocomplete textbox with database in Asp.Net C#,VB with example

Related Post