Introduction
This article describes how to add an Auto Complete feature to a text box and get the data from a database in ASP.Net using a jQuery UI .
Description
To create this application you need the jQuery files listed below.
- jquery-1.9.1.js
- jquery-ui.js
- jquery-ui.css
You can download them from the source code attached to this page.
Design
Go to the database and design your table or execute the following script:
CREATE TABLE [dbo].[DEPT]
(DEPTNO NUMERIC(2),
DNAME VARCHAR(14),
LOC VARCHAR(13) );
GO
INSERT INTO [dbo].[DEPT] VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO [dbo].[DEPT] VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO [dbo].[DEPT] VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO [dbo].[DEPT] VALUES (40, 'OPERATIONS', 'BOSTON');
In Visual Studio create a website and add a page.
Add a Text Box to the page for searching.
Now design your screen as in the following screen:
![JQuery-Autocomplete-1.jpg]()
Or you can copy the following source code:
<body>
<form id="form1" runat="server">
Search Department :<asp:TextBox ID="txtSearch" runat="server" ></asp:TextBox>
</form>
</body>
Next add the following JavaScript and CSS style code in the head tag of an aspx file
(this is used to get the data to show the auto complete).
<head id="Head1" runat="server">
<title></title>
<link href="Styles/jquery-ui.css" rel="stylesheet" type="text/css" />
<script src="Scripts/jquery-1.9.1.js" type="text/javascript"></script>
<script src="Scripts/jquery-ui.js" type="text/javascript"></script>
<script type="text/javascript">
$(document).ready(function () {
$("#txtSearch").autocomplete({
source: function (request, response) {
$.ajax({
type: "POST",
contentType: "application/json; charset=utf-8",
url: "WebService.asmx/GetData",
data: "{'DName':'" + document.getElementById('txtSearch').value + "'}",
dataType: "json",
success: function (data) {
response(data.d);
},
error: function (result) {
alert("Error......");
}
});
}
});
});
</script>
</head>
![JQuery-Autocomplete-2.jpg]()
Now go to Solution Explorer and add a Web Service to the project (for example, "WebService.asmx").
Now write the following code in the .cs file of the Web Service:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data.SqlClient;
/// <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
{
[WebMethod]
public List<string> GetData(string DName)
{
List<string> result = new List<string>();
using (SqlConnection con = new SqlConnection("Data Source=SYSTEM-30;database=DB_Test_Trainees;user id=test;password=Test"))
{
using (SqlCommand cmd = new SqlCommand("select Dname from DEPTDHII where Dname like '%'+@SearchText+'%'", con))
{
con.Open();
cmd.Parameters.AddWithValue("@SearchText", DName);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
result.Add(dr["DName"].ToString());
}
return result;
}
}
}
}
Now check this line in the code above:
[System.Web.Script.Services.ScriptService]
By default this line is commented. To allow this Web Service to be called from a script using ASP.NET AJAX, uncomment the line.
Now build your application. Enter a search query in the TextBox.
![JQuery-Autocomplete-3.jpg]()