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.