jQuery AutoComplete textbox using Asp.Net Web Service C# and Vb.Net

AutoComplete in programming, is a feature that predicts list of words, matching a group of letters or a single letter, from a data source. I am sharing an example that shows how to attach the jQuery AutoComplete feature to a textbox control in an Asp.Net application by extracting data from a remote data base using a Web Service.

The data source (for the AutoComplete feature) can be either a database table or a file. When a user type a letter or a group of letters in an input box or a textbox, the program immediately connects with a data source, looks up for the words in the source and finally shows a list of words matching the typed letters.

Autocomplete Textbox Using jQuery

I am sharing two examples here in this post. The first example is very basic and uses a local data source or an array. It shows how your AutoComplete will actually work.

The Second example uses an SQL Server database table for the data and it calls a Web Service to communicate with the database.

01) Using an Array for AutoComplete

Usually, we define an array at the client side of the web page, inside the <script> tag. This array will have a collection of predefined string values, which will serve as a data source for AutoComplete in the input box. All we need to do is bind the data source (array) with the input box.

<script>
    var Countries = ["ARGENTINA", "AUSTRALIA", "BRAZIL", "BELARUS", "BHUTAN", "CHILE"...
</script>

Before using jQuery inside our demo website, we will add Google’s CDN and themes CSS. It must be embedded between the <head> tags.

The Markup and the Script

<body>
    <link href="https://ajax.googleapis.com/ajax/libs/jqueryui/1.8.1/themes/base/jquery-ui.css" rel="stylesheet" type="text/css"/>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js"></script>
    <script src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.8.1/jquery-ui.min.js"></script>

    <h3>Type some values in the textbox.</h3>
    <div>
        <input type="text" value="" id="tbCountries" />
    </div>
</body>

<script>
    $(document).ready(function() {
        BindControls();
    });
    function BindControls() {
        const Countries = ["ARGENTINA", "AUSTRALIA", "BRAZIL", "BELARUS", "BHUTAN", "CHILE"];

        // BIND ARRAY OF STRINGS WITH AUTOCOMPLETE FUNCTION.
        $("#tbCountries").autocomplete({ source: Countries });
    }
</script>

Try it

02) Using Asp.Net Web Service and SQL Server for AutoComplete

In the second example, I'll extract data from an SQL Server database table called "Country", by calling a Web Service in Asp.Net.

I am using jQuery Ajax that will make a call to the Web Service.

Create a new Asp.Net project. To add a Web Service in your website, right click the website in the Solution explorer and select Add New Item…. From the list of Templates select Web Service and click the Add button. The default name of file will be WebService.asmx, which will be located at the root of your website. This process will also create a Class file with the same name but different extensions. (For C# and Vb.Net)

Note: It might throw a runtime error: Internal Server Error (500). You can avoid this error by simply adding the below code in the web.config file, inside ><system.web> tag.

<webServices>
    <protocols>
        <add name="HttpGet"/>
        <add name="HttpPost"/>
    </protocols>
</webServices>>

We will add a function to extract data from the database

[WebMethod]
public List<string> ShowCountry (string sLookUP) { }

The Web Service (C#)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data.SqlClient;

[System.Web.Script.Services.ScriptService]
public class WebService : System.Web.Services.>WebService {
    [WebMethod]
    public List<string> ShowCountry(string sLookUP)
    {
        List<string> lstCountries = new List<string>();

        string sConnString = "Data Source=DNA;Persist Security Info=False;" +
            "Initial Catalog=DNA_CLASSIFIED;User Id=sa;Password=;Connect Timeout=30;";

        SqlConnection myConn = new SqlConnection(sConnString);
        SqlCommand objComm = new SqlCommand("SELECT CountryName FROM Country " + 
            "WHERE CountryName LIKE '%'+@LookUP+'%' ORDER BY CountryName", myConn);
        myConn.Open();

        objComm.Parameters.AddWithValue("@LookUP", sLookUP);
        SqlDataReader reader = objComm.ExecuteReader();

        while (reader.Read()) {
            lstCountries.Add(reader["CountryName"].ToString());
        }
        myConn.Close();  return lstCountries;
    }
}

Vb.Net

Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Data.SqlClient

<System.Web.Script.Services.ScriptService() _
Public Class WebService
    Inherits System.Web.Services.WebService

    <WebMethod()> _
    Public Function ShowCountry(ByVal sLookUP As String) As List(Of String)
        Dim lstCountries As New List(Of String)()

        Dim sConnString As String = "Data Source=DNA;Persist Security Info=False;" & _
            "Initial Catalog=DNA_CLASSIFIED;User Id=sa;Password=;Connect Timeout=30;"

        Dim myConn As New SqlConnection(sConnString)
        Dim objComm As New SqlCommand("SELECT CountryName FROM Country " & _
            "WHERE CountryName LIKE '%'+@LookUP+'%' ORDER BY CountryName", myConn)
        myConn.Open()

        objComm.Parameters.AddWithValue("@LookUP", sLookUP)
        Dim reader As SqlDataReader = objComm.ExecuteReader()

        While reader.Read()
            lstCountries.Add(reader("CountryName").ToString())
        End While

        myConn.Close() : Return lstCountries
    End Function
End Class

The jQuery Script

<body>
    <form id="form1" runat="server">
    <div>
        <input type="text" value="" id="tbListOfCountries" />
    </div>
    </form>
</body>

<script>
    $(document).ready(function() {
        BindControls();
    });

    function BindControls() {
        $("#tbListOfCountries").autocomplete({
            source: function(request, response) {
                $.ajax({
                    url: "WebService.asmx/ShowCountry",
                    data: "{ 'sLookUP': '" + request.term + "' }",
                    dataType: "json",
                    type: "POST",
                    contentType: "application/json; charset=utf-8",
                    dataFilter: function(data) { return data; },
                    success: function(data) {
                        response($.map(data.d, function(item) {
                            return { value: item }
                        }))
                    },
                    error: function(XMLHttpRequest, textStatus, errorThrown) {
                        alert(textStatus);
                    }
                });
            },
            minLength: 1    // MINIMUM 1 CHARACTER TO START WITH.
        });
    }
</script>

The .ajax() method has few parameters which you should know.

url: This has the path of WebService.asmx file and the function which is called to lookup of search for words based on the entered keyword(s). The path can vary depending on the location of the calling files.

(../WebService.asmx)

data: sLookUP in our demo is the name of the string parameter which declared inside the ShowCountry function. request.term has the value, say the first character “a”. The value for the parameter can also be passed using HTML’s DOM object.

Eg: document.getElementById("tbListOfCountries").value;

error: You can show a more customised error, in case there is an error.

error: function(XMLHttpRequest, textStatus, errorThrown)
{ alert('Got an Error'); }

← PreviousNext →