Updated on Kisan Patel
This tutorial will explain you how to work with jQuery UI Autocomplete with SQL Server Database in ASP.NET?
Lets start by creating a new project called “AjaxAutoCompleteDemo” in Visual Studio 2012.
Next, add Default.aspx page and add one TextBox control with Id “txt_autocomplete”.
In this demo, we have to use table called “tbl_class” from the Student Database as shown in below.
Now, add jQuery UI script as shown in below code.
<head runat="server"> <title>jQuery Auto Complete Demo</title> <link href="Content/css/smoothness/jquery-ui-1.10.4.autocomplete.min.css" rel="stylesheet" /> <script src="Content/js/jquery-1.10.2.js"></script> <script src="Content/js/jquery-ui-1.10.4.autocomplete.min.js"></script>
Now, add below static Web Method and Student Class into the Default.aspx.cs page.
[WebMethod]
public static List<Student> GetAutoCompleteData(string term)
{
List<Student> student = new List<Student>();
using (SqlConnection con = new SqlConnection("Data Source=DEVANG-PC\\SQLEXPRESS;Initial Catalog=Student;User ID=sa;Password=sa123"))
{
using (SqlCommand cmd = new SqlCommand("select * from tbl_class where Name LIKE '%'+@SearchText+'%'", con))
{
con.Open();
cmd.Parameters.AddWithValue("@SearchText", term);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
student.Add(new Student() { ID = Convert.ToInt32(dr["Id"]), Name = dr["Name"].ToString(), RollNo = dr["RollNo"].ToString() });
}
return student;
}
}
}
public class Student
{
public int ID { get; set; }
public string Name { get; set; }
public string RollNo { get; set; }
}
Now, Add below script to Default.aspx page.
<script type="text/javascript">
jQuery(document).ready(function ($) {
$("#txt_autocomplete").autocomplete({
source: function(request, response) {
$.ajax({
type: "POST",
contentType: "application/json; charset=utf-8",
url: "Default.aspx/GetAutoCompleteData",
data: "{'term':'" + $('#txt_autocomplete').val() + "'}",
dataType: "json",
success: function (data) {
//console.log(data);
response(data.d);
},
error: function(result) {
response(result);
}
});
},
minLength: 1,
select: function (event, ui) {
$(this).val(ui.item.Name);
return false;
}
}).data( "ui-autocomplete" )._renderItem = function( ul, item ) {
return $( "<li>" )
.append("<a>" + item.Name + " - " + item.RollNo + "</a>")
.appendTo( ul );
};
});
</script>
Done!
