Asp.net: Autocomplete textbox from Sql server database using Jquery | ASPMANTRA | Asp.Net,MVC,AngularJs,Jquery,Javascript,Sql Server AND WCF snippets and tutorial

Asp.net: Autocomplete textbox from Sql server database using Jquery

In this article I am going to explain implement the autocomplete functionality in asp.net application using jquery.

Description:

I have a table Tb_Movie which store the information of movie like name, budget and genre. I want to search the records from table according to name.

Implementation:
Add a webform to project and add the required control from toolbox to webform.

HTML Markup of webform:
<fieldset style="width:450px">
            <legend></legend>
            <table>
                <tr>
                    <td>Movie Name:</td>
                    <td><asp:TextBox ID="txtsearch" runat="server"></asp:TextBox></td>
                </tr>
            </table>
        </fieldset>   

Import the namespaces
C#:
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.Services;

VB:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Web.Services

Create a webmethod to get the movie name
C# code:


[WebMethod]
    public static List<string>GetMovieName(string MovieName)
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ToString());
        SqlCommand cmd = new SqlCommand("Select name from tb_movie where name like @name +'%'", con);
        cmd.Parameters.AddWithValue("@name", MovieName);
        con.Open();
        SqlDataReader dr = cmd.ExecuteReader();
        List<string> movienames = new List<string>();
        while (dr.Read())
        {
            movienames.Add(dr["name"].ToString());
        }
        con.Close();
        return movienames;
    }

VB.net code:
<WebMethod()>
    Public Shared Function GetMovieName(MovieName As String) As List(Of String)
        Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("connection").ToString())
        Dim cmd As New SqlCommand("Select name from tb_movie where name like @name +'%'", con)
        cmd.Parameters.AddWithValue("@name", MovieName)
        con.Open()
        Dim dr As SqlDataReader = cmd.ExecuteReader()
        Dim movienames As New List(Of String)()
        While dr.Read()
            movienames.Add(dr("name").ToString())
        End While
        con.Close()
        Return movienames
    End Function

Add the Jquery to webform
<link rel="stylesheet" href="//code.jquery.com/ui/1.11.4/themes/smoothness/jquery-ui.css">
  <script src="//code.jquery.com/jquery-1.10.2.js"></script>
  <script src="//code.jquery.com/ui/1.11.4/jquery-ui.js"></script>
  <link rel="stylesheet" href="/resources/demos/style.css">
  <script>
      $(document).ready (function () {
          $("#txtsearch").autocomplete({
              autoFocus: true,
              source: function (request, response) {
                  $.ajax({
                      url: "default2.aspx/GetMovieName",
                        data: "{'MovieName': '" + request.term + "'}",
                        dataType: "json",
                        type: "POST",
                        contentType: "application/json; charset=utf-8",
                        success: function (data) {
                            response($.map(data.d, function (item) {
                                return {
                                    label: item
                                }
                            }))
                        },
                        messages: {
                            noResults: "", results: ""
                        },
                        error: function (response) {
                            alert(response.responseText);
                        },
                        failure: function (response) {
                            alert(response.responseText);
                        }
                    });
                },
               //minLength to specify after how many characters input call for suggestions to be made.
               minLength: 1,
           });
      });
  </script>

After adding jquery to webform below given is the complete HTML markup of webfrom
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
      <link rel="stylesheet" href="//code.jquery.com/ui/1.11.4/themes/smoothness/jquery-ui.css">
  <script src="//code.jquery.com/jquery-1.10.2.js"></script>
  <script src="//code.jquery.com/ui/1.11.4/jquery-ui.js"></script>
  <link rel="stylesheet" href="/resources/demos/style.css">
  <script>
      $(document).ready (function () {
          $("#txtsearch").autocomplete({
              autoFocus: true,
              source: function (request, response) {
                  $.ajax({
                      url: "default2.aspx/GetMovieName",
                        data: "{'MovieName': '" + request.term + "'}",
                        dataType: "json",
                        type: "POST",
                        contentType: "application/json; charset=utf-8",
                        success: function (data) {
                            response($.map(data.d, function (item) {
                                return {
                                    label: item
                                }
                            }))
                        },
                        messages: {
                            noResults: "", results: ""
                        },
                        error: function (response) {
                            alert(response.responseText);
                        },
                        failure: function (response) {
                            alert(response.responseText);
                        }
                    });
                },
               //minLength to specify after how many characters input call for suggestions to be made.
               minLength: 1,
           });
      });
  </script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <fieldset style="width:450px">
            <legend></legend>
            <table>
                <tr>
                    <td>Movie Name:</td>
                    <td><asp:TextBox ID="txtsearch" runat="server"></asp:TextBox></td>
                </tr>
            </table>
        </fieldset>           

    </div>
    </form>
</body>
</html>

Build and run the application. Test it by the enter the name of movie exist in the records.
 Demo:
Asp.net: Autocomplete textbox from Sql server database using Jquery
Add caption

  In this article we have learn to how to implement the autocomplete textbox from database in asp.net application using Jquery(C#, VB.net)I hope you enjoyed this article.

Post a Comment

[blogger]

MKRdezign

Contact Form

Name

Email *

Message *

Powered by Blogger.
Javascript DisablePlease Enable Javascript To See All Widget