Implement jQuery UI Autocomplete without using web service in Asp.Net C#,VB

Implement jQuery UI Autocomplete without using web service in Asp.Net C#,VB

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

Implement jQuery UI Autocomplete without using web service in Asp.Net C#,VB
In previous articles i explained How to implement Ajax autocompleteextender in asp.net without using web service and Example to implement jquery form validations inasp.net and Show jquery notification pop up message box and hide after 5 seconds and Create drop down menu for login and signup using jquery and css 

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. Read the article
  2. By defining static web method on page to fetch data from database based on search term passed
 In this article I am going to implement the second 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');

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


Asp.Net C# Code to implement jquery UI Autocomplete

In the code behind file(default.aspx.cs) write the code :
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Services;

[WebMethod]   
    public static List<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;
        }
    } 


Asp.Net VB Code to implement jquery UI Autocomplete

In the code behind file(default.aspx.vb) write the code :
Imports System.Collections.Generic
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Services

<WebMethod> _
    Public Shared Function FetchCityNames(SearchText As String) As List(Of 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
        End Using
    End Function

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) {
                    $.ajax({
                        url: '<%=ResolveUrl("~/jqueryAutocompletewithoutservice.aspx/FetchCityNames") %>',
                        data: "{'SearchText': '" + request.term + "'}",
                        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 to specify after how many characters input call for suggestions to be made.
                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.
Read More
Next
« Prev Post
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..

Subscribe to: Post Comments (Atom)