Hi dilipsharm,
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
MasterPage
<%@ Master Language="C#" AutoEventWireup="true" CodeFile="MasterPage.master.cs" Inherits="MasterPage" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<script type="text/javascript" src="https://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.10.0.min.js"></script>
<script type="text/javascript" src="https://ajax.aspnetcdn.com/ajax/jquery.ui/1.9.2/jquery-ui.min.js"></script>
<link rel="Stylesheet" type="text/css" href="https://ajax.aspnetcdn.com/ajax/jquery.ui/1.9.2/themes/blitzer/jquery-ui.css" />
<script type="text/javascript">
$(function () {
$("[id*=txtSearch]").autocomplete({
source: function (request, response) {
$.ajax({
url: '<%=ResolveUrl("~/Services/CustomerService.asmx/GetCustomers") %>',
data: "{ 'prefix': '" + request.term + "'}",
dataType: "json",
type: "POST",
contentType: "application/json; charset=utf-8",
success: function (data) {
if (data.d.length > 0) {
response($.map(data.d, function (item) {
return {
label: item.split('-')[0].trim(),
val: item.split('-')[1]
};
}))
} else {
response([{ label: 'No results found.', val: 0}]);
}
},
error: function (response) {
alert(response.responseText);
},
failure: function (response) {
alert(response.responseText);
}
});
},
select: function (e, i) {
if (i.item.val == 0) {
return false;
} else {
$("[id*=hfCustomerId]").val(i.item.val);
}
}
});
});
</script>
<asp:ContentPlaceHolder ID="head" runat="server">
</asp:ContentPlaceHolder>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:TextBox ID="txtSearch" runat="server" />
<asp:HiddenField ID="hfCustomerId" runat="server" />
<hr />
<asp:ContentPlaceHolder ID="ContentPlaceHolder1" runat="server">
</asp:ContentPlaceHolder>
</div>
</form>
</body>
</html>
WebService
C#
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Web.Services;
/// <summary>
/// Summary description for CustomerService
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
[System.Web.Script.Services.ScriptService]
public class CustomerService : System.Web.Services.WebService
{
[WebMethod]
public string[] GetCustomers(string prefix)
{
List<string> customers = new List<string>();
using (SqlConnection con = new SqlConnection())
{
con.ConnectionString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandText = "SELECT DISTINCT CustomerID,ContactName FROM Customers WHERE ContactName LIKE @SearchText + '%'";
cmd.Parameters.AddWithValue("@SearchText", prefix);
cmd.Connection = con;
con.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
customers.Add(string.Format("{0}-{1}", sdr["ContactName"], sdr["CustomerID"]));
}
}
con.Close();
}
}
return customers.ToArray();
}
}
VB.Net
Imports System.Collections.Generic
Imports System.Configuration
Imports System.Data.SqlClient
Imports System.Web.Services
' To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
<System.Web.Script.Services.ScriptService()> _
<WebService(Namespace:="http://tempuri.org/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Public Class CustomerService
Inherits System.Web.Services.WebService
<WebMethod()> _
Public Function GetCustomers(ByVal prefix As String) As String()
Dim customers As List(Of String) = New List(Of String)()
Using con As SqlConnection = New SqlConnection()
con.ConnectionString = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using cmd As SqlCommand = New SqlCommand()
cmd.CommandText = "SELECT DISTINCT CustomerID,ContactName FROM Customers WHERE ContactName LIKE @SearchText + '%'"
cmd.Parameters.AddWithValue("@SearchText", prefix)
cmd.Connection = con
con.Open()
Using sdr As SqlDataReader = cmd.ExecuteReader()
While sdr.Read()
customers.Add(String.Format("{0}-{1}", sdr("ContactName"), sdr("CustomerID")))
End While
End Using
con.Close()
End Using
End Using
Return customers.ToArray()
End Function
End Class
