Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

jQuery Autocomplete Textbox in Asp.net with Database Example using C#, VB.NET

Jun 8, 2015
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.

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>&nbsp;</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

Fill a jQuery Autocomplete Textbox Example from Database in Asp.net

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 RSS subscribe by email Subscribe by Email

14 comments :

Naod said...

Hello I have just copied the above code but during writing the first letter of any name it is displaying error! any solution!

Naod said...

Solved and works perfectly

Unknown said...

IT IS SHOWING LENGTH IS UNDEFINED

Unknown said...

i enter first letter displaying error

any solution solving this problem...?

Bala said...

if we need to set height and width for the autocomplete text mean whats the option?

Unknown said...

i have two textbox first textbox is ok but another textbox is not working

satish dodia said...

i enter first letter displaying error

any solution solving this problem...?

Unknown said...

If we have multiple selection criteria then how to do it???

Unknown said...

Thank you..this code working

Unknown said...

Hello I have just copied the above code but during writing the first letter of any name it is displaying error! any solution!

Unknown said...

Tks! Code working.

Anonymous said...

how can i set the hiddenfield? is not working with this: $('#hdnId').text(ui.item.val);

Unknown said...

Could the people that got this code to work. Please post your solution.

Anonymous said...

So if i have i values outside webmethod, how can i access for me to use as parameters in my webmethod query?

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.