Set ASP.net TextBox to auto complete with binding Sql server Data source using Json and web methods in c#

In this article i would like to explain, how to set asp.net text box to auto complete option with binding SQL server data source.

In first step add Jquery & Css references to page head,

http://../../Scripts/JQuery/jquery-2.1.4.js
http://../../Scripts/JQuery/jquery-ui.min.js
<link href="../../Styles/Reference/jquery-ui.css" rel="stylesheet" type="text/css" />

Access the web method in code behind throw JQuery script

function pageLoad() {
$(function () {

//Set Auto complete to ItemCodeFindTextBox texbox
$("[id$=ItemCodeFindTextBox]").autocomplete({
source: function (request, response) {
$.ajax({
url: '<%=ResolveUrl("~/Pages/Home.aspx/SetAutoComplete") %>', // set url for access the web method
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) {
},
minLength: 1
});
});
}

Create web method in Code-Behind for access from Client Side.

Get Data suggestions from Sql Table (ItemMaster)

[WebMethod]
public static string[] SetAutoComplete(string prefix)
{
Connection connection = new Connection();
List<string> customers = new List<string>();
using (SqlConnection conn = connection.GetConnection())
{

using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandText = “select * from ItemMaster where ItemCode like @ItemCode + ‘%'”; //Sql query to fill the textbox auto complete suggestions
cmd.Parameters.AddWithValue(“@ItemCode”, prefix);
cmd.Connection = conn;

using (SqlDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
customers.Add(string.Format(“{0}-{1}”, sdr[“ItemCode”], sdr[“Id”]));
}
}
conn.Close();
}
}
return customers.ToArray();
}

finally ItemCodeFindTextBox would be like this

1

Hope you learned something,

Thanks,

Udara

Advertisements

Author: eshan1604

Hi Guys, I'm Udara Eshan Ariyarathne from Sri Lanka. Currently i'm working as a Software Engineer (Microsoft Technologies). I'm sharing some IT related articles such as Development ,Configuration and Designing things what i have learn from day to day works. Hope it useful all of us. "Enjoying IT" Thanks & Regards, Udara Eshan Ariyarthne

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s