Auto Complete Jquery+asp.net+sql

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

Example AJAX Autocomplete Extender using jQuery in ASP.Net

 
 
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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s