In this article I’ll be explaining how to use jQuery AutoComplete Plugin in ASP.Net. Using jQuery we can easily create AutoComplete Textbox without writing much code.
Database
For database I am using the Microsoft’s NorthWind Database. You can download the same using the link below
Download Northwind Database
Connection String
Below is the connection string to the database which I have placed in the web.config.
<connectionStrings>
<addname="constr"connectionString="Data Source = .\SQLExpress;
Initial Catalog = Northwind; Integrated Security = true"/>
</connectionStrings>
Download jQuery
You can download the latest version of jQuery and the AutoComplete Plugin using the links below.
Download jQuery
Download jQuery AutoComplete Plugin
AutoComplete Handler
We will need to build a handler that will process all the requests of AutoComplete and return the results back to the ASP.Net Web page
C#
<%@ WebHandler Language="C#" Class="Search_CS" %>
using System;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Text;
public class Search_CS : IHttpHandler {
public void ProcessRequest (HttpContext context) {
string prefixText = context.Request.QueryString["q"];
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = ConfigurationManager
.ConnectionStrings["constr"].ConnectionString;
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandText = "select ContactName from Customers where " +
"ContactName like @SearchText + '%'";
cmd.Parameters.AddWithValue("@SearchText", prefixText);
cmd.Connection = conn;
StringBuilder sb = new StringBuilder();
conn.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
sb.Append(sdr["ContactName"])
.Append(Environment.NewLine);
}
}
conn.Close();
context.Response.Write(sb.ToString());
}
}
}
public bool IsReusable {
get {
return false;
}
}
}
VB.Net
<%@ WebHandler Language="VB" Class="Search_VB" %>
Imports System
Imports System.Web
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Text
Public Class Search_VB : Implements IHttpHandler
Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
Dim prefixText As String = context.Request.QueryString("q")
Dim conn As SqlConnection = New SqlConnection
conn.ConnectionString = ConfigurationManager _
.ConnectionStrings("constr").ConnectionString
Dim cmd As SqlCommand = New SqlCommand
cmd.CommandText = ("select ContactName from Customers where " & _
"ContactName like @SearchText + '%'")
cmd.Parameters.AddWithValue("@SearchText", prefixText)
cmd.Connection = conn
Dim sb As StringBuilder = New StringBuilder
conn.Open()
Dim sdr As SqlDataReader = cmd.ExecuteReader
While sdr.Read
sb.Append(sdr("ContactName")) _
.Append(Environment.NewLine)
End While
conn.Close()
context.Response.Write(sb.ToString)
End Sub
Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
Get
Return False
End Get
End Property
End Class
The above handler accepts the request from jQuery in a Querystring parameter q. Based on the value of this parameter the handler searches the customer table and returns the customers whose names match the substring queried by the user.
Client Side Implementation
Below I have described the Client Side implementations of jQuery AutoComplete Plugin. Both the versions C# and VB.Net are exactly same except the URL of the handler.
C# Version
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<link href="css/jquery.autocomplete.css" rel="stylesheet" type="text/css" />
<script src="scripts/jquery-1.4.1.min.js" type="text/javascript"></script>
<script src="scripts/jquery.autocomplete.js" type="text/javascript"></script>
<script type="text/javascript">
$(document).ready(function() {
$("#<%=txtSearch.ClientID%>").autocomplete('Search_CS.ashx');
});
</script>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:TextBox ID="txtSearch" runat="server"></asp:TextBox>
</div>
</form>
</body>
</html>
VB.Net Version
<head id="Head1" runat="server">
<title></title>
<link href="css/jquery.autocomplete.css" rel="stylesheet" type="text/css" />
<script src="scripts/jquery-1.4.1.min.js" type="text/javascript"></script>
<script src="scripts/jquery.autocomplete.js" type="text/javascript"></script>
<script type="text/javascript">
$(document).ready(function() {
$("#<%=txtSearch.ClientID%>").autocomplete('Search_VB.ashx');
});
</script>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:TextBox ID="txtSearch" runat="server"></asp:TextBox>
</div>
</form>
</body>
Above you have noticed how easy it is to configure the jQuery AutoComplete Plugin. You simply need to inherit the required JS Files, place a textbox and write the below small snippet
<script type="text/javascript">
$(document).ready(function() {
$("#<TextBoxControlId>").autocomplete('<Url of handler>');
});
</script>
With this we come to end of this article. You can download the source code in VB.Net and C# using the link below.
JQueryAutoCompleteinASP.Net.zip