Search multiple columns from database using jQuery AutoComplete TextBox in ASP.Net

Hi gabylopez,

Check this example. Now please take its reference and correct your code.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

HTML

<asp:TextBox ID="txtName" runat="server" />
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<link rel="Stylesheet" type="text/css" href="https://code.jquery.com/ui/1.11.4/themes/start/jquery-ui.css" />
<script type="text/javascript" src="https://code.jquery.com/ui/1.11.4/jquery-ui.min.js"></script>
<style type="text/css">
    .ui-autocomplete {
        max-height: 320px;
        overflow-x: auto;
    }
</style>
<script type="text/javascript">
    $(function () {
        $("[id*=txtName]").autocomplete({
            minLength: 0,
            source: function (request, response) {
                $.ajax({
                    url: '<%=ResolveUrl("~/Default.aspx/GetDetails") %>',
                    data: "{ 'prefix': '" + request.term + "'}",
                    dataType: "json",
                    type: "POST",
                    contentType: "application/json; charset=utf-8",
                    success: function (data) {
                        response($.map(data.d, function (item) {
                            return {
                                name: item.split('-')[0],
                                id: item.split('-')[1],
                                country: item.split('-')[2],
                                val: item.split('-')[1],
                                label: item.split('-')[1]
                            }
                        }))
                    }
                });
            }
        }).data("ui-autocomplete")._renderItem = function (ul, item) {
            return $("<li>")
                .append('<a>'
                    + '<table><tr>'
                    + '<td style="width:200px;">' + item.name + '</td>'
                    + '<td style="width:100px;">' + item.country + '</td>'
                    + '<td style="width:40px;">' + item.id + '</td>'
                    + '</tr></table></a>')
                .appendTo(ul);
        };
    });
</script>

Namespaces

C#

using System.Configuration;
using System.Data.SqlClient;
using System.Web.Services;

VB.Net

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

Code

C#

[WebMethod]
public static string[] GetDetails(string prefix)
{
    List<string> customers = new List<string>();
    using (SqlConnection conn = new SqlConnection())
    {
        conn.ConnectionString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.CommandText = "SELECT CustomerId,ContactName,Country FROM Customers"
                                + " WHERE CustomerId LIKE @SearchText + '%'"
                                + " OR ContactName LIKE @SearchText + '%'"
                                + " OR Country LIKE @SearchText + '%'";
            cmd.Parameters.AddWithValue("@SearchText", prefix);
            cmd.Connection = conn;
            conn.Open();
            using (SqlDataReader sdr = cmd.ExecuteReader())
            {
                while (sdr.Read())
                {
                    customers.Add(string.Format("{0}-{1}", sdr["ContactName"], sdr["CustomerId"] + "-" + sdr["Country"]));
                }
            }
            conn.Close();
        }
    }
    return customers.ToArray();
}

VB.Net

<WebMethod>
Public Shared Function GetDetails(ByVal prefix As String) As String()
    Dim customers As List(Of String) = New List(Of String)()
    Using conn As SqlConnection = New SqlConnection()
        conn.ConnectionString = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Using cmd As SqlCommand = New SqlCommand()
            cmd.CommandText = "SELECT CustomerId,ContactName,Country FROM Customers" _
                                + " WHERE CustomerId Like @SearchText + '%'" _
                                + " OR ContactName LIKE @SearchText + '%'" _
                                + " OR Country LIKE @SearchText + '%'"
            cmd.Parameters.AddWithValue("@SearchText", prefix)
            cmd.Connection = conn
            conn.Open()
            Using sdr As SqlDataReader = cmd.ExecuteReader()
                While sdr.Read()
                    customers.Add(String.Format("{0}-{1}", sdr("ContactName"), sdr("CustomerId") & "-" & sdr("Country")))
                End While
            End Using
            conn.Close()
        End Using
    End Using

    Return customers.ToArray()
End Function

Screenshot