Introduction:
Here I will explain how to fill a jQuery autocomplete textbox from database
in asp.net using c#, vb.net with example or jQuery autocomplete textbox example in asp.net with database usiing c#, vb.net and show / display no results found message in autocomplete textbox when
no matching records found in asp.net using c#, vb.net.
Description:
In previous articles I explained jQuery show or hide loading image in asp.net, jQuery autocomplete textbox with multiple words in asp.net, jQuery autocomplete with images in asp.net, jQuery slideup, slidedown, slidetoggle effects example and many articles relating to autocomplete, css, AngularJS, jQuery, JavaScript and asp.net. Now I will explain how to fill jQuery autocomplete textbox from database in asp.net with example.
In previous articles I explained jQuery show or hide loading image in asp.net, jQuery autocomplete textbox with multiple words in asp.net, jQuery autocomplete with images in asp.net, jQuery slideup, slidedown, slidetoggle effects example and many articles relating to autocomplete, css, AngularJS, jQuery, JavaScript and asp.net. Now I will explain how to fill jQuery autocomplete textbox from database in asp.net with example.
Before
implement this example first design one table userdetails in your database
like as shown below
Column Name
|
Data Type
|
Allow Nulls
|
UserId
|
Int(IDENTITY=TRUE)
|
No
|
UserName
|
varchar(50)
|
Yes
|
Education
|
varchar(50)
|
Yes
|
Location
|
varchar(50)
|
Yes
|
Once
we design table in our database insert some dummy data in your table to use it
in our application that would be like as shown below
 |
Now
open your aspx page and write the code like as shown below
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>jQuery Show Records
Found Message in AutoComplete</title>
<link href="http://code.jquery.com/ui/1.11.4/themes/ui-lightness/jquery-ui.css"
rel="stylesheet"
type="text/css"/>
<script type="text/javascript"
src="http://code.jquery.com/jquery-1.10.2.js"></script>
<script type="text/javascript"
src="http://code.jquery.com/ui/1.11.4/jquery-ui.js"></script>
<script type="text/javascript">
$(function () {
SearchText();
});
function SearchText() {
$(".autosuggest").autocomplete({
source: function (request,
response) {
$.ajax({
type: "POST",
contentType: "application/json;
charset=utf-8",
url: "jQueryAutoCompleteTextbox.aspx/GetAutoCompleteData",
data: "{'username':'" +
$('#txtSearch').val() + "'}",
dataType: "json",
success: function (data) {
if (data.d.length >0) {
response($.map(data.d, function
(item) {
return {
label: item.split('/')[0],
val: item.split('/')[1]
}
}));
}
else {
response([{ label: 'No Records Found',
val: -1}]);
}
},
error: function (result) {
alert("Error");
}
});
},
select: function (event, ui) {
if (ui.item.val == -1) {
return false;
}
$('#lblUserId').text(ui.item.val);
}
});
}
</script>
</head>
<body>
<form id="form1"
runat="server">
<div class="demo">
<asp:HiddenField ID="hdnId"
runat="server"
/>
<div class="ui-widget">
<label for="tbAuto">Enter
UserName: </label>
<input type="text"
id="txtSearch"
class="autosuggest"
/>
</div>
<div> </div>
<div>
Selected UserId:<b><label id="lblUserId" /></b>
</div>
</form>
</body>
</html>
|
Now add following namespaces in code behind
C#
Code
using System;
using
System.Collections.Generic;
using System.Web.Services;
using System.Data.SqlClient;
|
After completion of adding namespaces you need to write the
code like as shown below
[WebMethod]
public static List<string> GetAutoCompleteData(string username)
{
List<string> result = new
List<string>();
using (SqlConnection con = new
SqlConnection("Data
Source=Suresh;Integrated Security=true;Initial Catalog=MySampleDB"))
{
using (SqlCommand cmd = new
SqlCommand("select
UserId,UserName from userdetails where UserName LIKE
'%'+@SearchText+'%'", con))
{
con.Open();
cmd.Parameters.AddWithValue("@SearchText",
username);
SqlDataReader dr =
cmd.ExecuteReader();
while (dr.Read())
{
result.Add(string.Format("{0}/{1}", dr["UserName"],
dr["UserId"]));
}
return result;
}
}
}
|
VB.NET
Code
Imports
System.Collections.Generic
Imports System.Web.Services
Imports System.Data.SqlClient
Partial Class VBCode
Inherits System.Web.UI.Page
Protected Sub Page_Load(sender As
Object, e As EventArgs)
End Sub
<WebMethod()> _
Public Shared Function
GetAutoCompleteData(ByVal username As String) As List(Of String)
Dim result As New List(Of String)()
Using con As New SqlConnection("Data
Source=Suresh;Integrated Security=true;Initial Catalog=MySampleDB")
Using cmd As New SqlCommand("select
UserId,UserName from userdetails where UserName LIKE
'%'+@SearchText+'%'", con)
con.Open()
cmd.Parameters.AddWithValue("@SearchText",
username)
Dim dr As SqlDataReader
= cmd.ExecuteReader()
While dr.Read()
result.Add(String.Format("{0}/{1}", dr("UserName"),
dr("UserId")))
End While
Return result
End Using
End Using
End Function
End Class
|
Demo
 |
Download
Sample Code Attached
|
If you enjoyed this post, please support the blog below. It's FREE! Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email. |
|||
Subscribe by RSS
Subscribe by Email
|
|||
14 comments :
Hello I have just copied the above code but during writing the first letter of any name it is displaying error! any solution!
Solved and works perfectly
IT IS SHOWING LENGTH IS UNDEFINED
i enter first letter displaying error
any solution solving this problem...?
if we need to set height and width for the autocomplete text mean whats the option?
i have two textbox first textbox is ok but another textbox is not working
i enter first letter displaying error
any solution solving this problem...?
If we have multiple selection criteria then how to do it???
Thank you..this code working
Hello I have just copied the above code but during writing the first letter of any name it is displaying error! any solution!
Tks! Code working.
how can i set the hiddenfield? is not working with this: $('#hdnId').text(ui.item.val);
Could the people that got this code to work. Please post your solution.
So if i have i values outside webmethod, how can i access for me to use as parameters in my webmethod query?
Note: Only a member of this blog may post a comment.