Please disable your adblock and script blockers to view this page
 

jQuery UI Autocomplete textbox with database in Asp.Net C#,VB with 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

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:
  1. By defining web method in web service to fetch data from database based on search term passed. 
  2. 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

 GO
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.

GO
CREATE PROCEDURE spGetCityNames
(
                @SearchText VARCHAR(50)
)
AS
BEGIN
                SET NOCOUNT ON;
                SELECT TOP 15 CityName FROM tbCityMaster WHERE CityName LIKE @SearchText+'%'
END

  • Now create connection string in web.config as:
<connectionStrings>
    <add name="conStr" connectionString="Data Source=LALIT;Initial Catalog=MyDataBase;Integrated Security=True"/>
  </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

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title></title>
    <script src="http://code.jquery.com/jquery-1.11.3.js" type="text/javascript"></script>
    <script src="http://code.jquery.com/ui/1.11.3/jquery-ui.js" type="text/javascript"></script>
    <link rel="stylesheet" href="//code.jquery.com/ui/1.11.4/themes/smoothness/jquery-ui.css" />

    <script type="text/javascript">
        $(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>

    <style type="text/css">
        #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>
    <form id="form1" runat="server">
        <div>
            <asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>
            <asp:UpdatePanel ID="UpdatePanel1" runat="server">
                <ContentTemplate>
                    <fieldset style="width: 410px; height: 200px;">
                        <legend>jQuery UI Autocomplete example in asp.net</legend>

                        <div id="dvSearch">
                            <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.
Previous
Next Post »

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.. ConversionConversion EmoticonEmoticon