Related Posts Plugin for WordPress, Blogger...

About

Follow Us

Monday, 8 June 2015

Introduction: 

In this article I will explain how to fill a jQuery autocomplete textbox from database 
in asp.net using c#, vb.net with example
 and show / display no results found message in autocomplete textbox when no matching records found in asp.net using c#, vb.net.


Implementation:

Create a table in database i.e. Emp_personal. As given below:

Column Name
Data Type
Allow Nulls
EmpPer_Id
Int(IDENTITY=TRUE)
No
EmpName
varchar(100)
Yes
Age
int
Yes
Address
varchar(500)
Yes

You can use this script to create table.
  CREATE TABLE [dbo].[Emp_Personal](
            [EmpPer_Id] [int] IDENTITY(1,1) NOT NULL,
            [EmpName] [varchar](100) NULL,
            [Age] [int] NULL,
            [Address] [varchar](500) NULL
)
Now insert data in table:


Design Section:

Now create your webpage as given 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: "autoComplete.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">
<fieldset style="width:350px"><legend><strong>Auto Complete TextBox using Jquery in Asp.net</strong></legend>
<div class="demo">
<asp:HiddenField ID="hdnId" runat="server" />
    <br />
<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>
    <br />
</div>
    </label>
</fieldset>
</form>
</body>
</html>

ASP.NET Code behind File Using C#:


C# Code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System;
using System.Collections.Generic;
using System.Web.Services;
using System.Data.SqlClient;

public partial class autoComplete : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    [WebMethod]
    public static List<string> GetAutoCompleteData(string username)
    {
        List<string> result = new List<string>();
        using (SqlConnection con = new SqlConnection("Data Source=localhost;Integrated Security=true;Initial Catalog=test"))
        {
            using (SqlCommand cmd = new SqlCommand("select EmpPer_Id,EmpName from Emp_Personal where EmpName LIKE '%'+@SearchText+'%'", con))
            {
                con.Open();
                cmd.Parameters.AddWithValue("@SearchText", username);
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    result.Add(string.Format("{0}/{1}", dr["EmpName"], dr["EmpPer_Id"]));
                }
                return result;
            }
        }
    }

VB.NET Code

Imports System.Collections.Generic
Imports System.Web.Services
Imports System.Data.SqlClient

Partial Public Class autoComplete
    Inherits System.Web.UI.Page
    Protected Sub Page_Load(sender As Object, e As EventArgs)

    End Sub

    <WebMethod()> _
    Public Shared Function GetAutoCompleteData(username As String) As List(Of String)
        Dim result As New List(Of String)()
        Using con As New SqlConnection("Data Source=localhost;Integrated Security=true;Initial Catalog=test")
            Using cmd As New SqlCommand("select EmpPer_Id,EmpName from Emp_Personal where EmpName 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("EmpName"), dr("EmpPer_Id")))
                End While
                Return result
            End Using
        End Using
    End Function
End Class

0 comments:

Post a Comment