Asp.net: Autocomplete textbox from Sql server database using Jquery - Free Asp.Net,MVC,AngularJs,Jquery,Javascript,Sql Server,WCF,Entity framework 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.

What do you think about this article?

If you found this article useful, please share and follow on Facebook, Twitter, Google Plus and other social media websites. To get free updates subscribe to newsletter. Please put your thoughts and feedback in comments section.

Share this

Share on FacebookTweet on TwitterPlus on Google+


EmoticonEmoticon