In this article I will explain how to implement and populate jQuery AutoComplete TextBox from database using AJAX PageMethods in ASP.Net using C# and VB.Net.
Database
Here I am making use of Microsoft’s Northwind Database. The download and install instructions are provided in the following article.
HTML Markup
The following HTML Markup of Page consists of an ASP.Net TextBox, a HiddenField and a Button.
The jQuery AutoComplete plugin has been applied to the TextBox. A jQuery AJAX call is made to the GetCustomers PageMethod and the list of customers returned from the PageMethod acts as source of data to the jQuery AutoComplete.
The data received from the server is processed in the jQuery AJAX call success event handler. A loop is executed for each received item in the list of items and then an object with text part in the label property and value part in the val property is returned.
A Select event handler has been defined for the jQuery AutoComplete and when an item is selected from the AutoComplete List, the value of the item is stored in the HiddenField.
<script src="http://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.10.0.min.js" type="text/javascript"></script>
<script src="http://ajax.aspnetcdn.com/ajax/jquery.ui/1.9.2/jquery-ui.min.js" type="text/javascript"></script>
<link href="http://ajax.aspnetcdn.com/ajax/jquery.ui/1.9.2/themes/blitzer/jquery-ui.css"
rel="Stylesheet" type="text/css" />
<script type="text/javascript">
$(function () {
$("[id$=txtSearch]").autocomplete({
source: function (request, response) {
$.ajax({
url: '<%=ResolveUrl("~/Default.aspx/GetCustomers") %>',
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) {
$("[id$=hfCustomerId]").val(i.item.val);
},
minLength: 1
});
});
</script>
Enter search term:
<asp:TextBox ID="txtSearch" runat="server" />
<asp:HiddenField ID="hfCustomerId" runat="server" />
<asp:Button ID="Button1" Text="Submit" runat="server" OnClick="Submit" />
Namespaces
You will need to import the following namespaces.
C#
using System.Web.Services;
using System.Configuration;
using System.Data.SqlClient;
VB.Net
Imports System.Web.Services
Imports System.Configuration
Imports System.Data.SqlClient
The ASP.Net PageMethod
The following AJAX PageMethod accepts a parameter prefix and its value is used to find matching records from the Customers Table of the Northwind database.
The select query gets the Name and the ID of the customer that matches the prefix text.
The fetched records are processed and a Key Value Pair is created by appending the Id to the Name field in the following format {0}-{1} where is the Name {0} and {1} is the ID of the Customer.
C#
[WebMethod]
public static string[] GetCustomers(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 ContactName, CustomerId from Customers where ContactName 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"]));
}
}
conn.Close();
}
}
return customers.ToArray();
}
VB.Net
<WebMethod()>
Public Shared Function GetCustomers(prefix As String) As String()
Dim customers As New List(Of String)()
Using conn As New SqlConnection()
conn.ConnectionString = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using cmd As New SqlCommand()
cmd.CommandText = "select ContactName, CustomerId from Customers where ContactName 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")))
End While
End Using
conn.Close()
End Using
End Using
Return customers.ToArray()
End Function
Fetching the selected item on Server Side
The Key (Customer Name) and Value (Customer ID) can be fetched on server side inside the click event handler of the Button from the Request.Form collection as shown below.
C#
protected void Submit(object sender, EventArgs e)
{
string customerName = Request.Form[txtSearch.UniqueID];
string customerId = Request.Form[hfCustomerId.UniqueID];
ClientScript.RegisterStartupScript(this.GetType(), "alert", "alert('Name: " + customerName + "\\nID: " + customerId + "');", true);
}
VB.Net
Protected Sub Submit(sender As Object, e As EventArgs)
Dim customerName As String = Request.Form(txtSearch.UniqueID)
Dim customerId As String = Request.Form(hfCustomerId.UniqueID)
ClientScript.RegisterStartupScript(Me.GetType(), "alert", "alert('Name: " & customerName & "\nID: " & customerId & "');", True)
End Sub
Screenshot
Demo
Downloads
