Autosuggestion Textbox Using Database In jQuery

In this article I explain how to create an Autosuggestion TextBox with a SQL Database in jQuery.

Introduction

Autosuggestion shows a small window like a popup panel to display words matching a prefix typed into the TextBox. For example an EmpName text box, that will fill in the entry EmpName when the first letter is typed, then displays a suggestion list like a popup panel. First I created a database EmpDB then I created a table in this database. 

Query Code

CREATE TABLE [dbo].[emp](

      [EmpName] [varchar](50) NULL

) ON [PRIMARY]

Complete Program

AutoComplete_with_DataBase.html

 

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

 <html xmlns="http://www.w3.org/1999/xhtml">

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

<title>AutoComplete Box with jQuery</title>

<link href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.1/themes/base/jquery-ui.css" rel="stylesheet" type="text/css"/>

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js"></script>

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.1/jquery-ui.min.js"></script>

<script type="text/javascript">

    $(document).ready(function ()

    {

        SearchText();

    });

    function SearchText()

    {

        $(".autosuggest").autocomplete({

            source: function (request, response) {

                $.ajax({

                    type: "POST",

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

                    url: "Default.aspx/AutoCompleteData",

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

                    dataType: "json",

                    success: function (data) {

                        response(data.d);

                    },

                    error: function (result) {

                        alert("Error");

                    }

                });

            }

        });

    }

</script>

</head>

<body>

    <p style="color: #0000FF; font-weight: bold; font-size: x-large">

        AutoSuggestion TextBox in jQuery<br />

    </p>

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

<div class="demo">

<div class="ui-widget">

<label for="tbAuto">Enter EmpName: </label>

<input type="EmpName" id="txtSearch" class="autosuggest" />

</div>

</form>

</body>

</html>

 

Default.aspx

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Collections.Generic;

using System.Data.SqlClient;

using System.Web.Services;

public partial class _Default : System.Web.UI.Page

{

    protected void Page_Load(object sender, EventArgs e)

    {

    }

    [WebMethod]

 

    public static List<string> AutoCompleteData(string EmpName)

    {

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

        using (SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=EmpDB;Persist Security Info=True;User ID=sa;Password=*******"))

        {

            using (SqlCommand cmd = new SqlCommand("select DISTINCT EmpName from emp where EmpName LIKE '%'+@SearchText+'%'", con))

            {

                con.Open();

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

                SqlDataReader dr = cmd.ExecuteReader();

                while (dr.Read())

                {

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

                }

                return result;

            }

        }

    }

} 

 

Output


Animation1.gif

For more information, download the attached sample application. 

Up Next
    Ebook Download
    View all
    Learn
    View all