Support us .Net Basics C# SQL ASP.NET Aarvi MVC Slides C# Programs Subscribe Download

Autocomplete textbox using jquery in asp.net

Suggested Videos
Part 69 - Difference between window height and document height
Part 70 - Load data on page scroll using jquery
Part 71 - Calling live json web service using jquery ajax



In this video we will discuss, how to implement autocomplete textbox using jquery and asp.net



The suggestions should come from the following database table tblStudents.

Autocomplete textbox using jquery in asp.net

jquery autocomplete in asp.net.png

Step 1 : SQL Script to create and populate table tblStudents

Create Table tblStudents
(
 ID int identity primary key,
 Name nvarchar(50),
 Gender nvarchar(20),
 TotalMarks int
)
GO

Insert into tblStudents values('Mark Hastings','Male',900)
Insert into tblStudents values('Maria Nicholas','Female',760)
Insert into tblStudents values('John Stenson','Male',980)
Insert into tblStudents values('Rob Gerald','Male',990)
Insert into tblStudents values('Ron Simpson','Male',440)
Insert into tblStudents values('Rad Wicht','Male',320)
Insert into tblStudents values('Julian John','Male',983)
Insert into tblStudents values('James Bynes','Male',720)
Insert into tblStudents values('Mary Ward','Female',870)
Insert into tblStudents values('Michael Niron','Male',680)

Step 2 : Create a stored procedure to retrieve employee names

Create proc spGetStudentNames
@term nvarchar(50)
as
Begin
    Select Name from tblStudents where Name like @term + '%'
End

Step 3 : Create new asp.net web application project. Name it Demo. 

Step 4 : Include a connection string in the web.config file to your database.
<add name="DBCS"
      connectionString="server=.;database=SampleDB;integrated security=SSPI"/>

Step 5 : Add a Generic Handler to the project. Name it StudentHandler.ashx. Copy and paste the following code.

using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.Script.Serialization;

namespace Demo
{
    public class StudentHandler : IHttpHandler
    {
        public void ProcessRequest(HttpContext context)
        {
            string term = context.Request["term"] ?? "";
            List<string> listStudentNames = new List<string>();

            string cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            using(SqlConnection con = new SqlConnection(cs))
            {
                SqlCommand cmd = new SqlCommand("spGetStudentNames", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter()
                {
                    ParameterName = "@term",
                    Value = term
                });
                con.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                while(rdr.Read())
                {
                    listStudentNames.Add(rdr["Name"].ToString());
                }
            }

            JavaScriptSerializer js = new JavaScriptSerializer();
            context.Response.Write(js.Serialize(listStudentNames));
        }

        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }
}

Step 6 : Download jQuery UI from jqueryui.com. Copy and paste the following files in your project.
a) jquery-ui.js
b) jquery-ui.css
c) images

Step 7 : Add a WebForm to the ASP.NET project. Copy and paste the following HTML and jQuery code. 

<%@ Page Language="C#" AutoEventWireup="true"
         CodeBehind="WebForm1.aspx.cs"
         Inherits="Demo.WebForm1" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script src="jquery-1.11.2.js"></script>
    <script src="jquery-ui.js"></script>
    <link href="jquery-ui.css" rel="stylesheet" />
    <script type="text/javascript">
        $(document).ready(function () {
            $('#txtName').autocomplete({
                source: 'StudentHandler.ashx'
            });
        });
    </script>
</head>
<body style="font-family: Arial">
    <form id="form1" runat="server">
        Student Name :
        <asp:TextBox ID="txtName" runat="server">
        </asp:TextBox>
    </form>
</body>
</html>

jQuery tutorial for beginners

4 comments:

  1. Hi Venkat, I have to say I love your videos, very easy to follow and very well explained.

    the problem I have is I am trying to return a different value to the 'term' used above. How can I switch the 'term' used here for a different column in the database table?

    SqlCommand cmd = new SqlCommand("spGetStudentNames", con);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new SqlParameter()
    {
    ParameterName = "@term",
    Value = term
    });

    ReplyDelete
    Replies
    1. You will have to change Code and

      cmd.Parameters.Add(new SqlParameter()
      {
      ParameterName = "yourcodedParameterwhichName",
      Value = term
      });

      Same as Change Store Procedure

      @yourcodedParameterwhichName nvarchar(50)
      as
      Begin
      Select Name from tblStudents where ColumnNameYouLIKEToChange like @term + '%'
      End

      Delete
    2. thanks .. this helps a lot. I do have drop down list before the autocomplete textbox which has only two options. how i will pass the selected value of Dropdown to handler ?

      Delete
  2. it is not working in master pages inherit file

    ReplyDelete

It would be great if you can help share these free resources