jQuery Autocomplete Getting Data From Database in ASP.Net

This article describes how to add an Auto Complete feature to a text box and get the data from a database in ASP.Net using a jQuery UI .

Introduction

This article describes how to add an Auto Complete feature to a text box and get the data from a database in ASP.Net using a jQuery UI .

Description

To create this application you need the jQuery files listed below.

  • jquery-1.9.1.js
  • jquery-ui.js
  • jquery-ui.css

You can download them from the source code attached to this page.

Design

Go to the database and design your table or execute the following script:

CREATE TABLE [dbo].[DEPT]

(DEPTNO NUMERIC(2),

DNAME VARCHAR(14),

LOC VARCHAR(13) );

GO

 

INSERT INTO [dbo].[DEPT] VALUES (10, 'ACCOUNTING', 'NEW YORK');

INSERT INTO [dbo].[DEPT] VALUES (20, 'RESEARCH', 'DALLAS');

INSERT INTO [dbo].[DEPT] VALUES (30, 'SALES', 'CHICAGO');

INSERT INTO [dbo].[DEPT] VALUES (40, 'OPERATIONS', 'BOSTON'); 

In Visual Studio create a website and add a page.

Add a Text Box to the page for searching.

Now design your screen as in the following screen:

JQuery-Autocomplete-1.jpg

Or you can copy the following source code:

<body>

   <form id="form1" runat="server">

    Search Department :<asp:TextBox ID="txtSearch" runat="server" ></asp:TextBox>

    </form>

</body>


Next add the following JavaScript and CSS style code in the head tag of an aspx file
(this is used to get the data to show the auto complete).

<head id="Head1" runat="server">

    <title></title>

    <link href="Styles/jquery-ui.css" rel="stylesheet" type="text/css" />

    <script src="Scripts/jquery-1.9.1.js" type="text/javascript"></script>

    <script src="Scripts/jquery-ui.js" type="text/javascript"></script>

    <script type="text/javascript">

               $(document).ready(function () {

    $("#txtSearch").autocomplete({

        source: function (request, response) {

            $.ajax({

                type: "POST",

                contentType: "application/json; charset=utf-8",

                url: "WebService.asmx/GetData",

                data: "{'DName':'" + document.getElementById('txtSearch').value + "'}",

                dataType: "json",

                success: function (data) {

                    response(data.d);

                },

                error: function (result) {

                    alert("Error......");

                }

            });

        }

    });

});

   </script>

</head>

JQuery-Autocomplete-2.jpg


Now go to Solution Explorer and add a Web Service to the project (for example, "WebService.asmx").

Now write the following code in the .cs file of the Web Service:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.Services;

using System.Data.SqlClient;

/// <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 WebService : System.Web.Services.WebService

{

    [WebMethod]

    public List<string> GetData(string DName)

    {

        List<string> result = new List<string>();

        using (SqlConnection con = new SqlConnection("Data Source=SYSTEM-30;database=DB_Test_Trainees;user id=test;password=Test"))

        {

            using (SqlCommand cmd = new SqlCommand("select Dname from DEPTDHII where Dname like '%'+@SearchText+'%'", con))

            {

                con.Open();

                cmd.Parameters.AddWithValue("@SearchText", DName);

                SqlDataReader dr = cmd.ExecuteReader();

                while (dr.Read())

                {

                    result.Add(dr["DName"].ToString());

                }

                return result;

            }

        }

    }

}

Now check this line in the code above:

[System.Web.Script.Services.ScriptService]

By default this line is commented. To allow this Web Service to be called from a script using ASP.NET AJAX, uncomment the line.

Now build your application. Enter a search query in the TextBox.


JQuery-Autocomplete-3.jpg