This is a the second article for the JQuery auto complete text box which demonstrates how to create an autocomplete text box which retrieve value and key or by other words value member and display member,
in our previous article we know how we can retrieve data from Data Base and fill it into the autocomplete like here .
but in most scenarios this is not satisfied for us unless we get the two dimensions or fields.
Example: we need to create a autocomplete text box which can search by employee name and after selecting the employee name we can get his ID.
First our method inside the aspx page to get the data
[WebMethod]
[ScriptMethod]
public static ArrayList GetAutoCompleteData(string EmployeeName)
{
List<Employee> result = new List<Employee>();
List<string> result2 = new List<string>();
ArrayList myArr = new ArrayList();
System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
using (SqlConnection con = new SqlConnection("Data Source=sv-intranet04;Initial Catalog=HR_DEV;Persist Security Info=True;User ID=cpr1;Password=cpr1"))
{
using (SqlCommand cmd = new SqlCommand("select DISTINCT FullName_E as EmployeeName,EmployeeID as ID from EmployeeProfile_view1 where FullName_E LIKE '%" + EmployeeName + "%'", con))
{
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Employee newEmp = new Employee();
newEmp.EmployeeName = dr["EmployeeName"].ToString();
newEmp.EmployeeID = dr["ID"].ToString();
newEmp.ID = dr["ID"].ToString();
myArr.Add(newEmp);
}
string str = serializer.Serialize(result);
return myArr;
}
}
}
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
Second our Emplyee Class
[DataContract()]
public class Employee
{
[DataMember]
public string ID;
[DataMember]
public string EmployeeID;
[DataMember]
public string EmployeeName;
}
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
Third our JQuery code
$(function () {
SearchText();
});
function SearchText() {
$("#autosuggest").autocomplete({
source: function (request, response) {
$.ajax({
type: "POST",
contentType: "application/json; charset=utf-8",
url: "MyTest.aspx/GetAutoCompleteData",
data: "{'EmployeeName':'" + document.getElementById('autosuggest').value + "'}",
dataType: "json",
success: function (data) {
// response(data.d);
response($.map(data.d, function (item) {
return {
label: item.EmployeeName,
val: item.ID
}
}))
},
error: function (XMLHttpRequest, callStatus, errorThrown) { alert(callStatus); }
});
},
select: function (event, ui) { $("#Result").text(ui.item.val); }
});
}
in our previous article we know how we can retrieve data from Data Base and fill it into the autocomplete like here .
but in most scenarios this is not satisfied for us unless we get the two dimensions or fields.
Example: we need to create a autocomplete text box which can search by employee name and after selecting the employee name we can get his ID.
First our method inside the aspx page to get the data
[WebMethod]
[ScriptMethod]
public static ArrayList GetAutoCompleteData(string EmployeeName)
{
List<Employee> result = new List<Employee>();
List<string> result2 = new List<string>();
ArrayList myArr = new ArrayList();
System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
using (SqlConnection con = new SqlConnection("Data Source=sv-intranet04;Initial Catalog=HR_DEV;Persist Security Info=True;User ID=cpr1;Password=cpr1"))
{
using (SqlCommand cmd = new SqlCommand("select DISTINCT FullName_E as EmployeeName,EmployeeID as ID from EmployeeProfile_view1 where FullName_E LIKE '%" + EmployeeName + "%'", con))
{
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Employee newEmp = new Employee();
newEmp.EmployeeName = dr["EmployeeName"].ToString();
newEmp.EmployeeID = dr["ID"].ToString();
newEmp.ID = dr["ID"].ToString();
myArr.Add(newEmp);
}
string str = serializer.Serialize(result);
return myArr;
}
}
}
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
Second our Emplyee Class
[DataContract()]
public class Employee
{
[DataMember]
public string ID;
[DataMember]
public string EmployeeID;
[DataMember]
public string EmployeeName;
}
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
Third our JQuery code
$(function () {
SearchText();
});
function SearchText() {
$("#autosuggest").autocomplete({
source: function (request, response) {
$.ajax({
type: "POST",
contentType: "application/json; charset=utf-8",
url: "MyTest.aspx/GetAutoCompleteData",
data: "{'EmployeeName':'" + document.getElementById('autosuggest').value + "'}",
dataType: "json",
success: function (data) {
// response(data.d);
response($.map(data.d, function (item) {
return {
label: item.EmployeeName,
val: item.ID
}
}))
},
error: function (XMLHttpRequest, callStatus, errorThrown) { alert(callStatus); }
});
},
select: function (event, ui) { $("#Result").text(ui.item.val); }
});
}
