Autocomplete is the technique of showing only the relevant information to the users based on the input by the users.
You use Google rite?
Even Google search uses autocomplete feature. See the below screenshot.
Now, our objective is to autocomplete the textbox based on the user’s input. We would use the data entered by the user and query the database and present her with relevant options. We are going to use jQuery UI autocomplete widget for achieving the same. You can download it from here.
Sidenote: I am using Person.Contact table of AdventureWorks database which comes with SQL Express. The complete code is given at the bottom of the article.
At high level, we need do the following things
- Get the users input
- Send it to ASP.Net code-behind method
- From code-behind method, query the database based on users input and get the result set.
- Show the result set to the user
1. Get the user’s input:
We need to get the users input only when it’s changed. i.e., when user types. There is no need to send the same data again and again to the backend.
We just need to pass the id of textbox. Everything else is being taken care of autocomplete widget itself. Whenever you type, it will check the source to give us only the relevant data.
$("#txtNames").autocomplete()
2. Send it to ASP.Net code-behind method
We can use AJAX to send the data from the client to the server side method and get the response back. We would see what each of the parameters represent in AJAX below.
I have written a detailed article on using AJAX in ASP.Net here. You can read it for more information. The important point to note here is that the data that we get from AJAX should be passed to the response of autocomplete function. This would make use of the data to build the list.
type:
type parameter represents the data submission method. Here, we are using POST. If you use POST, the data would be sent through the request body message – invisible to normal users. If we use GET, the data would be appended as query string and would be visible in the URL.
contentType:
This parameter represents the request format. We are using json here with utf-8 charset.
url:
The method would provide us the data and this data would be shown to the users in textbox.
data:
This parameter is the input data for the server side AJAX method. Here we are passing the value entered by the user.
dataType:
datatype is the response format.We are using json.
success :
success parameter accepts a function which tells you what needs to be done when there is a successful response from server side method. Here we are just passing the data to the response of autocomplete widget so that it can build a list out of that data.
error:
Just like success function, this also accepts a function which would be called if there is any error.
$.ajax({
type: "POST",
contentType: "application/json; charset=utf-8",
url: "AutoComplete.aspx/GetFirstNames",
data: "{'namePrefix':'" + $("#txtNames").val() + "'}",
dataType: "json",
success: function (data) {
response(data.d)
},
error: function (response) {
alert("Error"+res.responseText);
}
});
3. From code-behind method, query the database based on users input and get the result set
This is just a normal database access code but there are couple of things to be noted here.
- The method should be a static WebMethod so that AJAX can call this server method without any issues. Though there are different types of calling server method from Ajax, this is the most commonly used option.
- You should send the data in a format which could be understandable by javascript. This means you cannot send the DataTable directly. As, DataTable is a C# Class which javascript could not understand. So, in the below method, I am converting data in DataTable to Array. You can send any json object – this does not need to be an array. We are using array as its simple and serves our purpose.
[WebMethod]
public static string[] GetFirstNames(string namePrefix)
{
List<string> lstNames = new List<string>();
DataTable dtNames = new DataTable();
string sqlQuery = "select distinct FirstName from Person.Contact where FirstName like '"+namePrefix+"%'";
string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["AdventureWorksContext"].ConnectionString;
try
{
SqlConnection conn = new SqlConnection(connectionString);
SqlDataAdapter da = new SqlDataAdapter(sqlQuery, conn);
da.Fill(dtNames);
foreach(DataRow row in dtNames.Rows)
{
string name = Convert.ToString(row["FirstName"]);
lstNames.Add(name);
}
}
catch (Exception ex)
{
throw ex;
}
return lstNames.ToArray<string>();
}
4. Show the result set to the user
We just need to pass the data that we have got from database to the source parameter. Everything else is being taken care of jQuery UI autocomplete widget.
Complete TextBox AutoComplete Example – Getting data from database
There are couple of things to be noted regarding the references.
1. We can include jquery UI CSS file reference(jquery-ui.css) so that the autocomplete in textbox is shown nicely.
2. jquery-ui.min.js provides us the autocomplete widget. This must be included in order for autocomplete to work.
<script type="text/javascript" src="jquery-1.11.2.min.js"></script>
<script type="text/javascript" src="jquery-ui.min.js"></script>
<link href="jquery-ui.css" rel="stylesheet" type="text/css"/>
<script type="text/javascript">
$(document).ready(function () {
$("#txtNames").autocomplete({
source: function (request, response) {
$.ajax({
type: "POST",
contentType: "application/json; charset=utf-8",
url: "AutoComplete.aspx/GetFirstNames",
data: "{'namePrefix':'" + $("#txtNames").val() + "'}",
dataType: "json",
success: function (data) {
response(data.d)
},
error: function (response) {
alert("Error"+res.responseText);
}
});
}
});
});
</script>
ASPX Markup:
<div> <label for="txtNames">Names:</label> <asp:TextBox ID="txtNames" runat="server"></asp:TextBox> </div>
Code Behind:
[WebMethod]
public static string[] GetFirstNames(string namePrefix)
{
List<string> lstNames = new List<string>();
DataTable dtNames = new DataTable();
string sqlQuery = "select distinct FirstName from Person.Contact where FirstName like '"+namePrefix+"%'";
string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["AdventureWorksContext"].ConnectionString;
try
{
SqlConnection conn = new SqlConnection(connectionString);
SqlDataAdapter da = new SqlDataAdapter(sqlQuery, conn);
da.Fill(dtNames);
foreach(DataRow row in dtNames.Rows)
{
string name = Convert.ToString(row["FirstName"]);
lstNames.Add(name);
}
}
catch (Exception ex)
{
throw ex;
}
return lstNames.ToArray<string>();
}
Configurable options:
There are several configurable options available in autocomplete widget for us to customize. We can see most frequently used options now.
delay:
This parameter – which accepts a number – represents the number in milliseconds to wait for before looking for data in source parameter. The default value is 300 milliseconds, if omitted.
minLength:
This parameter – which accepts a number – represents the number of characters to be entered before trying to get the data in source parameter. The default value is 1 character.
In the below example, we have used delay value as 1 sec(1000 milliseconds) and minLength as 3 characters so that until the user types 3 characters in textbox autocomplete will not do anything.
<script type="text/javascript">
$(document).ready(function () {
$("#txtNames").autocomplete({
source: function (request, response) {
$.ajax({
type: "POST",
contentType: "application/json; charset=utf-8",
url: "AutoComplete.aspx/GetFirstNames",
data: "{'namePrefix':'" + $("#txtNames").val() + "'}",
dataType: "json",
delay: 1000,
minLength: 3,
success: function (data) {
response(data.d)
},
error: function (response) {
alert("Error"+res.responseText);
}
});
}
});
});
</script>
Thanks for reading the article. If you like this article, you can subscribe in the below form.
Leave a Reply