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.

  1. USE [JQueryDB]
  2. GO
  3. SET ANSI_NULLS ON
  4. GO
  5. SET QUOTED_IDENTIFIER ON
  6. GO
  7. CREATE TABLE [dbo].[Country](
  8. [CountryID] [int] IDENTITY(1,1) NOT NULL,
  9. [CountryName] [nvarchar](100) NULL,
  10. CONSTRAINT [PK_Country] PRIMARY KEY CLUSTERED
  11. (
  12. [CountryID] ASC
  13. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  14. ) ON [PRIMARY]
  16. GO
  17. SET ANSI_NULLS ON
  18. GO
  19. SET QUOTED_IDENTIFIER ON
  20. GO
  21. CREATE procedure [dbo].[spGetCountryName]
  22. @term varchar(50)
  23. as
  24. begin
  25. select CountryName
  26. from Country
  27. where CountryName like @term +'%'
  28. end
  29. GO

Step-2

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

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

Step-3

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

  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Web;
  5. using System.Web.Services;
  6. using System.Data;
  7. using System.Data.SqlClient;
  8. using System.Configuration;
  10. namespace AutoCompleteTextBox_Demo
  11. {
  12. /// <summary>
  13. /// Summary description for CountryService
  14. /// </summary>
  15. [WebService(Namespace = "http://tempuri.org/")]
  16. [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
  17. [System.ComponentModel.ToolboxItem(false)]
  18. // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
  19. [System.Web.Script.Services.ScriptService]
  20. public class CountryService : System.Web.Services.WebService
  21. {
  22. [WebMethod]
  23. public List<string> GetCountryNames(string term)
  24. {
  25. List<string> listCountryName = new List<string>();
  26. string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
  27. using(SqlConnection con=new SqlConnection(CS))
  28. {
  29. SqlCommand cmd = new SqlCommand("spGetCountryName", con);
  30. cmd.CommandType = CommandType.StoredProcedure;
  32. SqlParameter parameter = new SqlParameter()
  33. {
  34. ParameterName = "@term",
  35. Value = term
  36. };
  37. cmd.Parameters.Add(parameter);
  38. con.Open();
  39. SqlDataReader rdr = cmd.ExecuteReader();
  40. while(rdr.Read())
  41. {
  42. listCountryName.Add(rdr["CountryName"].ToString());
  43. }
  44. return listCountryName;
  45. }
  46. }
  47. }
  48. }

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.

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

Step-5

Write below script to call CountryService.asmx

  1. <script type="text/javascript">
  2. $(document).ready(function () {
  3. $("#txtCountry").autocomplete({
  4. source: function (request, responce) {
  5. $.ajax({
  6. url: "CountryService.asmx/GetCountryNames",
  7. method: "post",
  8. contentType: "application/json;charset=utf-8",
  9. data: JSON.stringify({ term: request.term }),
  10. dataType: 'json',
  11. success: function (data) {
  12. responce(data.d);
  13. },
  14. error: function (err) {
  15. alert(err);
  16. }
  17. });
  18. }
  19. });
  20. });
  21. </script>

Step-6

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

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

Complete html code of web form.

  1. <!DOCTYPE html>
  3. <html>
  4. <head runat="server">
  5. <title>Autocomplete</title>
  6. <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css">
  7. <link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
  8. <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
  9. <script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
  10. <script type="text/javascript">
  11. $(document).ready(function () {
  12. $("#txtCountry").autocomplete({
  13. source: function (request, responce) {
  14. $.ajax({
  15. url: "CountryService.asmx/GetCountryNames",
  16. method: "post",
  17. contentType: "application/json;charset=utf-8",
  18. data: JSON.stringify({ term: request.term }),
  19. dataType: 'json',
  20. success: function (data) {
  21. responce(data.d);
  22. },
  23. error: function (err) {
  24. alert(err);
  25. }
  26. });
  27. }
  28. });
  29. });
  30. </script>
  31. </head>
  32. <body>
  33. <form id="form1" runat="server">
  34. <div class="container">
  35. <h2>Autocomplete Texbox using JQuery Ajax with database in ASP.NET</h2>
  36. <label>Country Name</label>
  37. <asp:TextBox ID="txtCountry" CssClass="form-control col-md-3" runat="server"></asp:TextBox>
  38. </div>
  39. </form>
  40. </body>
  41. </html>

Step-7 Run project ctr+F5

Final output

output