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">

<title>AutoComplete Box with jQuery</title>

$(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>

AutoSuggestion TextBox in jQuery<br />

</p>

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

</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

For more information, download the attached sample application.