Database :
CREATE TABLE [dbo].[STUDENT](
[StudentID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL,
[RollNumber] [decimal](10, 2) NULL,
[Class] [nvarchar](50) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[STUDENT] ON
INSERT [dbo].[STUDENT] ([StudentID], [Name], [RollNumber], [Class]) VALUES (1, N'Andy', CAST(100.00 AS Decimal(10, 2)), N'MCA')
INSERT [dbo].[STUDENT] ([StudentID], [Name], [RollNumber], [Class]) VALUES (7, N'Anar', CAST(100.00 AS Decimal(10, 2)), N'MCA')
INSERT [dbo].[STUDENT] ([StudentID], [Name], [RollNumber], [Class]) VALUES (3, N'Bill', CAST(200.00 AS Decimal(10, 2)), N'BCA')
INSERT [dbo].[STUDENT] ([StudentID], [Name], [RollNumber], [Class]) VALUES (8, N'Amit', CAST(100.00 AS Decimal(10, 2)), N'MCA')
INSERT [dbo].[STUDENT] ([StudentID], [Name], [RollNumber], [Class]) VALUES (9, N'Anjali', CAST(200.00 AS Decimal(10, 2)), N'BCA')
INSERT [dbo].[STUDENT] ([StudentID], [Name], [RollNumber], [Class]) VALUES (6, N'Chris', CAST(300.00 AS Decimal(10, 2)), N'MBBS')
INSERT [dbo].[STUDENT] ([StudentID], [Name], [RollNumber], [Class]) VALUES (10, N'Avinash', CAST(200.00 AS Decimal(10, 2)), N'BCA')
INSERT [dbo].[STUDENT] ([StudentID], [Name], [RollNumber], [Class]) VALUES (11, N'Rohan', CAST(200.00 AS Decimal(10, 2)), N'BCA')
SET IDENTITY_INSERT [dbo].[STUDENT] OFF
Connection string in Web.config :
<connectionStrings>
<add name="Web" connectionString="Data Source=*;Initial Catalog=dbname;uid=sa;pwd=sql" providerName="System.Data.SqlClient"/>
</connectionStrings>
Asp.net :
<head runat="server">
<title>Auto Complete Textbox Using JQuery</title>
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.4/jquery.min.js" type="text/javascript"></script>
<script src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8/jquery-ui.min.js"
type="text/javascript"></script>
<link href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8/themes/base/jquery-ui.css"
rel="Stylesheet" type="text/css" />
<script type="text/javascript">
$(document).ready(function () {
$("#<%=txtStudent.ClientID %>").autocomplete({
source: function (request, response) {
$.ajax({
url: '<%=ResolveUrl("~/WebService.asmx/GetStudents") %>',
data: "{ 'prefix': '" + request.term + "'}",
dataType: "json",
type: "POST",
contentType: "application/json; charset=utf-8",
success: function (data) {
response($.map(data.d, function (item) {
return {
label: item.split('-')[0],
val: item.split('-')[1]
}
}))
},
error: function (response) {
alert(response.responseText);
},
failure: function (response) {
alert(response.responseText);
}
});
},
select: function (e, i) {
$("#<%=hfId.ClientID %>").val(i.item.val);
},
minLength: 1
});
});
</script>
</head>
<body>
<form id="form1" runat="server">
<asp:TextBox ID="txtStudent" runat="server"></asp:TextBox>
<asp:HiddenField ID="hfId" runat="server" />
</form>
</body>
</html>
WebSercice.cs :
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.Script.Services;
/// <summary>
/// Summary description for WebService
/// </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 WebService : System.Web.Services.WebService
{
public WebService()
{
//Uncomment the following line if using designed components
//InitializeComponent();
}
[WebMethod]
[ScriptMethod(ResponseFormat = ResponseFormat.Json)]
public string[] GetStudents(string prefix)
{
List<string> students = new List<string>();
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = ConfigurationManager.ConnectionStrings["Web"].ConnectionString;
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandText = "select Name, StudentID from STUDENT where Name like @prefix + '%'";
cmd.Parameters.AddWithValue("@prefix", prefix);
cmd.Connection = conn;
conn.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
students.Add(string.Format("{0}-{1}", sdr["Name"], sdr["StudentId"]));
}
}
conn.Close();
}
return students.ToArray();
}
}
}