Implement jQuery AutoComplete TextBox using ADO.Net in ASP.Net Core MVC

trisetia302
 
on Jul 22, 2021 09:11 AM
Sample_140231.zip
984 Views

Hi Guys,

I'm trying to show data in textbox using Jquery Auto Complete, but data not showing when type in textbox. I'm not using EF for load data in textbox, I'm just use ADO.NET.

Where is the wrong?

Please help correct my code, Any help could be apriciate.

Create.cshtml

<div class="col-md-6">
    <div class="position-relative form-group">
        <label asp-for="Nama_Rak" class="control-label"></label>
        <input asp-for="Nama_Rak" id="Nama_Rak" class="form-control" />
        <span asp-validation-for="Nama_Rak" class="text-danger"></span>
    </div>
</div>
<link href="~/assets/plugins/jquery-ui/jquery-ui.css" rel="stylesheet" />
<script src="~/lib/jquery/dist/jquery.js"></script>
<script src="~/assets/plugins/jquery-ui/jquery-ui.js"></script>
<script type="text/javascript">
    $(document).ready(function () {
        SearchText();
    });
    function SearchText() {
        $("#Nama_Rak").autocomplete({
            source: function (request, response) {
                $.ajax({
                    type: "POST",
                    contentType: "application/json; charset=utf-8",
                    url: "/Buku/GetNamaRaK",
                    data: "{'Nama_Rak':'" + document.getElementById('Nama_Rak').value + "'}",
                    dataType: "json",
                    success: function (data) {
                        response(data.d);
                    },
                    error: function (result) {
                        alert("Data tidak ditemukan,,");
                    }
                });
            }
        });
    }
</script>

BukuController.cs

//Auto Complete textbox search
[HttpPost]
public static List<string> GetNamaRaK (string Nama_Rak)
{
    List<string> ResultRakName = new List<string>();
    using (SqlConnection con = new SqlConnection(@"Data Source = DESKTOP-REM8S41\SQLEXPRESS; Initial Catalog = db_perpustakaan; Integrated Security = True"))
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.CommandText = "Select Top 10 Nama_Rak from Rak where Nama_Rak LIKE ''+@Nama_Rak+'%'";
            cmd.Connection = con;
            con.Open();
            cmd.Parameters.AddWithValue("@Nama_Rak", Nama_Rak);
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                ResultRakName.Add(dr["Nama_Rak"].ToString());
            }
            con.Close();
            return ResultRakName;
        }
    }
}

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Jul 24, 2021 05:09 AM
on Jul 24, 2021 05:10 AM

Hi trisetia302,

Check this example. Now please take its reference and correct your code.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

Model

public class Customer
{
    public string Id { get; set; }
    public string Name { get; set; }
}

Namespaces

using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

Controller

public class HomeController : Controller
{
    public IActionResult Index()
    {
        return View();
    }

    [HttpPost]
    public IActionResult Index(string CustomerName, string CustomerId)
    {
        ViewBag.Message = "CustomerName: " + CustomerName + "\\nCustomerId: " + CustomerId;
        return View();
    }

    [HttpPost]
    public IActionResult AutoComplete(string Prefix)
    {
        List<Customer> customers = new List<Customer>();
        using (SqlConnection con = new SqlConnection(@"Data Source=.; Initial Catalog = Northwind; UID=sa;PWD=pass@123;"))
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "SELECT CustomerID,ContactName FROM Customers WHERE ContactName LIKE @Name + '%'";
                cmd.Connection = con;
                con.Open();
                cmd.Parameters.AddWithValue("@Name", Prefix);
                using (SqlDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        customers.Add(new Customer
                        {
                            Id = dr["CustomerID"].ToString(),
                            Name = dr["ContactName"].ToString()
                        });
                    }
                }
                con.Close();
            }
        }

        return Json(customers);
    }
}

View

@model jQuery_AutoComplete_Core_MVC.Models.Customer
@addTagHelper*, Microsoft.AspNetCore.Mvc.TagHelpers
@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
    <style type="text/css">
        body {
            font-family: Arial;
            font-size: 10pt;
        }

        .ui-autocomplete {
            overflow: auto;
            max-height: 100px;
        }
    </style>
    <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
    <script type="text/javascript" src="https://ajax.aspnetcdn.com/ajax/jquery.ui/1.9.2/jquery-ui.min.js"></script>
    <link rel="Stylesheet" type="text/css" href="https://ajax.aspnetcdn.com/ajax/jquery.ui/1.9.2/themes/blitzer/jquery-ui.css" />
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.2/css/bootstrap.min.css" />
    <script type="text/javascript">
        $(function () {
            $("#txtCustomer").autocomplete({
                source: function (request, response) {
                    $.ajax({
                        url: '/Home/AutoComplete/',
                        data: { "Prefix": request.term },
                        dataType: "json",
                        type: "POST",
                        contentType: "application/json; charset=utf-8",
                        success: function (data) {
                            response($.map(data, function (item) {
                                return {
                                    label: item.Name,
                                    val: item.Id
                                };
                            }))
                        },
                        error: function (response) {
                            alert(response.responseText);
                        }
                    });
                },
                select: function (e, i) {
                    $("#hfCustomerId").val(i.item.val);
                },
                minLength: 1
            });
        });
    </script>
</head>
<body class="container">
    <form asp-action="Index" asp-controller="Home" method="post">
        <label asp-for="Name" class="control-label"></label>
        <input asp-for="Name" type="text" name="CustomerName" id="txtCustomer" class="form-control" />
        <input asp-for="Id" type="hidden" name="CustomerId" id="hfCustomerId" />
        <input type="submit" id="btnSubmit" value="Submit" class="btn btn-primary" />
    </form>
    @if (ViewBag.Message != null)
    {
        <script type="text/javascript">
            window.onload = function () {
                alert("@ViewBag.Message");
            };
        </script>
    }
</body>
</html>

Screenshot