Subscribe For Free Updates!

We'll not spam mate! We promise.

Thursday, 24 October 2013

jQuery Autocomplete Getting Data From Database in ASP.Net

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 
To download Source Click Here

Socializer Widget By Blogger Yard
SOCIALIZE IT →
FOLLOW US →
SHARE IT →

0 comments:

Post a comment