Autocomplete Textbox Using jQuery AJAX With Database In ASP.NET

In this blog, we will discuss how to create autocomplete textbox in asp.net with the database using jQuery AJAX and web service.

Introduction

In this blog, we will discuss how to create autocomplete textbox in asp.net with the database using jQuery AJAX and web service.

Step-1

Create a database in SQL server of your choice as given below.

USE [JQueryDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Country](
    [CountryID] [int] IDENTITY(1,1) NOT NULL,
    [CountryName] [nvarchar](100) NULL,
 CONSTRAINT [PK_Country] PRIMARY KEY CLUSTERED
(
    [CountryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[spGetCountryName]
@term varchar(50)
as
begin
    select CountryName
    from Country
    where CountryName like @term +'%'
end
GO

Step-2

Add database connection in a webconfig file of your project change data source and database as you created.

<connectionStrings>
  <add name="DBCS" connectionString="data source=DESKTOP-K5N6EMF\SQLEXPRESS; database=JQueryDB;integrated security=SSPI" providerName="System.Data.SqlClient;"/>
</connectionStrings>

Step-3

Add CountryService.asmx right click on project add new item and choose .asmx file. Write below code.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace AutoCompleteTextBox_Demo
{
    /// <summary>
    /// Summary description for CountryService
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // 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 CountryService : System.Web.Services.WebService
    {
        [WebMethod]
        public List<string> GetCountryNames(string term)
        {
            List<string> listCountryName = new List<string>();
            string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            using(SqlConnection con=new SqlConnection(CS))
            {
                SqlCommand cmd = new SqlCommand("spGetCountryName", con);
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter parameter = new SqlParameter()
                {
                    ParameterName = "@term",
                    Value = term
                };
                cmd.Parameters.Add(parameter);
                con.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                while(rdr.Read())
                {
                    listCountryName.Add(rdr["CountryName"].ToString());
                }
                return listCountryName;
            }
        }
    }
}

Step-4

Add web form right click on project add new item and choose web form. Add script and styles cdn link in head section.

<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css">
<link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>

Step-5

Write below script to call CountryService.asmx

<script type="text/javascript">
        $(document).ready(function () {
            $("#txtCountry").autocomplete({
                source: function (request, responce) {
                    $.ajax({
                        url: "CountryService.asmx/GetCountryNames",
                        method: "post",
                        contentType: "application/json;charset=utf-8",
                        data: JSON.stringify({ term: request.term }),
                        dataType: 'json',
                        success: function (data) {
                            responce(data.d);
                        },
                        error: function (err) {
                            alert(err);
                        }
                    });
                }
            });
        });
    </script>

Step-6

Design HTML by grad and drop textbox control in web form.

 

<div class="container">
            <h2>Autocomplete Texbox using JQuery Ajax with database in ASP.NET</h2>
            <label>Country Name</label>
            <asp:TextBox ID="txtCountry" CssClass="form-control col-md-3" runat="server"></asp:TextBox>
        </div>

Complete html code of web form.

<!DOCTYPE html>

<html>
<head runat="server">
    <title>Autocomplete</title>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css">
    <link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
    <script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
    <script type="text/javascript">
        $(document).ready(function () {
            $("#txtCountry").autocomplete({
                source: function (request, responce) {
                    $.ajax({
                        url: "CountryService.asmx/GetCountryNames",
                        method: "post",
                        contentType: "application/json;charset=utf-8",
                        data: JSON.stringify({ term: request.term }),
                        dataType: 'json',
                        success: function (data) {
                            responce(data.d);
                        },
                        error: function (err) {
                            alert(err);
                        }
                    });
                }
            });
        });
    </script>
</head>
<body>
    <form id="form1" runat="server">
        <div class="container">
            <h2>Autocomplete Texbox using JQuery Ajax with database in ASP.NET</h2>
            <label>Country Name</label>
            <asp:TextBox ID="txtCountry" CssClass="form-control col-md-3" runat="server"></asp:TextBox>
        </div>
    </form>
</body>
</html>

Step-7 Run project ctr+F5

Final output

 

G

M

T

Detect languageAfrikaansAlbanianArabicArmenianAzerbaijaniBasqueBelarusianBengaliBosnianBulgarianCatalanCebuanoChichewaChinese (Simplified)Chinese (Traditional)CroatianCzechDanishDutchEnglishEsperantoEstonianFilipinoFinnishFrenchGalicianGeorgianGermanGreekGujaratiHaitian CreoleHausaHebrewHindiHmongHungarianIcelandicIgboIndonesianIrishItalianJapaneseJavaneseKannadaKazakhKhmerKoreanLaoLatinLatvianLithuanianMacedonianMalagasyMalayMalayalamMalteseMaoriMarathiMongolianMyanmar (Burmese)NepaliNorwegianPersianPolishPortuguesePunjabiRomanianRussianSerbianSesothoSinhalaSlovakSlovenianSomaliSpanishSundaneseSwahiliSwedishTajikTamilTeluguThaiTurkishUkrainianUrduUzbekVietnameseWelshYiddishYorubaZulu

AfrikaansAlbanianArabicArmenianAzerbaijaniBasqueBelarusianBengaliBosnianBulgarianCatalanCebuanoChichewaChinese (Simplified)Chinese (Traditional)CroatianCzechDanishDutchEnglishEsperantoEstonianFilipinoFinnishFrenchGalicianGeorgianGermanGreekGujaratiHaitian CreoleHausaHebrewHindiHmongHungarianIcelandicIgboIndonesianIrishItalianJapaneseJavaneseKannadaKazakhKhmerKoreanLaoLatinLatvianLithuanianMacedonianMalagasyMalayMalayalamMalteseMaoriMarathiMongolianMyanmar (Burmese)NepaliNorwegianPersianPolishPortuguesePunjabiRomanianRussianSerbianSesothoSinhalaSlovakSlovenianSomaliSpanishSundaneseSwahiliSwedishTajikTamilTeluguThaiTurkishUkrainianUrduUzbekVietnameseWelshYiddishYorubaZulu

Text-to-speech function is limited to 200 characters

Options : History : Feedback : Donate

Close