Autocomplete Textbox Using jQuery AJAX With Database In ASP.NET

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.

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