Populate DropdownList from database based on TextBox value using jQuery Ajax in ASP.Net Core MVC

trisetia302
 
on Nov 28, 2021 09:56 PM
Sample_971153.zip
547 Views

Hi,

I tried to populate data from database using ajax but I'm stuck on the ajax function data not populate on dropdownlist.

I have table like this below :

CREATE TABLE [dbo].[Tbl_Kamar](
	[ID_Kamar] [int] IDENTITY(1,1) NOT NULL,
	[ID_Tipe_Kamar] [int] NULL,
	[Deskripsi] [varchar](255) NULL,
	[Status] [varchar](255) NULL,
 CONSTRAINT [PK_Tbl_Kamar] PRIMARY KEY CLUSTERED 
(
	[ID_Kamar] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

I want is show all the available ID_Kamar based ID_Tipe_Kamar when button is clicked and then show all the available ID_Kamar to dropdownlist. For example is when user type ID_Tipe_Kamar on textbox number 1 it will show available ID_Kamar number 1,2,3,6.

Any help could be apriciate.

The View

<div class="col-md-6">
    <div class="position-relative form-group">
        <label asp-for="ID_Tipe_Kamar" class="control-label"></label>
        <div class="input-group">
            <input asp-for="ID_Tipe_Kamar" id="ID_Tipe_Kamar" name="ID_Tipe_Kamar" class="form-control" required />
            <div class="input-group-append">
                <button type="button" id="BtnCariIDTipeKamar" class="btn btn-warning">Cari ID Tipe Kamar</button>
            </div>
        </div>
        <span asp-validation-for="ID_Tipe_Kamar" class="text-danger"></span>
    </div>
</div>
<div class="col-md-6">
    <div class="position-relative form-group">
        <label asp-for="No_Kamar_Tersedia" class="control-label"></label>
        <select id="DropdownList" class="form-control" name="DropdownList"> </select>
        <span asp-validation-for="No_Kamar_Tersedia" class="text-danger"></span>
    </div>
</div>
<div class="col-md-6">
    <div class="position-relative form-group">
        <label asp-for="Deskripsi_Kamar" class="control-label"></label>
        <textarea asp-for="Deskripsi_Kamar" id="Deskripsi_Kamar" name="Deskripsi_Kamar" cols="4" class="form-control" readonly="readonly" required></textarea>
        <span asp-validation-for="Deskripsi_Kamar" class="text-danger"></span>
    </div>
</div>

The Ajax

<script type="text/javascript" lang="javascript">
    $(document).ready(function () {
        $('#BtnCariIDTipeKamar').on("click", function () {
            $.ajax({
                url: "@Url.Action("CariDataTipeKamar", "PesananKamar")",
                type: "GET",
                contentType: "application/json;charset=UTF-8",
                dataType: "json",
                data: { ID_Tipe_Kamar: $('#ID_Tipe_Kamar').val() },
                success: function (data) {
                    if (data != "") {
                        $('#DropdownList').val(data.ID_Kamar);
                        $('#Deskripsi_Kamar').val(data.Deskripsi_Kamar);
                     }
                    else {
                        window.alert(' error : ' + respons.message);
                     }
                 }
             });
        });
        return false;
    });
</script>

The Controller.cs

[HttpGet]
public IActionResult CariDataTipeKamar(string ID_Tipe_Kamar)
{
    List<PesananKamarModel> TipeKamar = new List<PesananKamarModel>();
    using (SqlConnection con = new SqlConnection(this._configuration.GetConnectionString("Penginapan_Apps")))
    {
        using (SqlCommand cmd = new SqlCommand())
        {
            con.Open();
            cmd.Connection = con;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "Select * From Tbl_Kamar Where ID_Tipe_Kamar='" + ID_Tipe_Kamar.Trim() + "' And Status='N'";
            cmd.Parameters.AddWithValue("@ID_Tipe_Kamar", ID_Tipe_Kamar);
            using (SqlDataReader rdr = cmd.ExecuteReader())
            {
                if (rdr.Read())
                {
                    TipeKamar.Add(new PesananKamarModel
                    {
                        ID_Kamar = Convert.ToInt32(rdr["ID_Kamar"].ToString()),
                        Deskripsi_Kamar = rdr["Deskripsi"].ToString()
                    });
                }
                else
                {
                    ViewBag.Message = "ID Tipe Kamar tidak ditemukan !!!";
                }
            }
            con.Close();
            cmd.Connection.Close();
        }
    }
    return Json(TipeKamar);
}

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Nov 29, 2021 02:02 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 ProductModel
{
    public int ProductId { get; set; }
    public string ProductName { get; set; }
}

Namespaces

using Microsoft.Extensions.Configuration;
using System.Data;
using System.Data.SqlClient;

Controller

public class HomeController : Controller
{
    private IConfiguration Configuration;

    public HomeController(IConfiguration _configuration)
    {
        Configuration = _configuration;
    }

    public IActionResult Index()
    {
        return View();
    }

    public IActionResult SearchCategioryTypeData(string id)
    {
        List<ProductModel> products = new List<ProductModel>();
        using (SqlConnection con = new SqlConnection(this.Configuration.GetConnectionString("MyConn")))
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = con;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "SELECT * FROM Products WHERE CategoryID = @Id";
                cmd.Parameters.AddWithValue("@Id", id);
                con.Open();
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        products.Add(new ProductModel
                        {
                            ProductId = Convert.ToInt32(rdr["ProductID"]),
                            ProductName = rdr["ProductName"].ToString()
                        });
                    }
                }
                con.Close();
            }
        }

        return Json(products);
    }
}

View

@addTagHelper *, Microsoft.AspNetCore.Mvc.TagHelpers

@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.2/css/bootstrap.min.css" />
    <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
</head>
<body>
    <div class="container">
        <div class="row">
            <div class="col-md-6">
                <div class="position-relative form-group">
                    <label class="control-label"></label>
                    <div class="input-group">
                        <input id="txtCategoryId" class="form-control" />
                        <div class="input-group-append">
                            <button type="button" id="btnCategory" class="btn btn-warning">Find Category Type ID</button>
                        </div>
                    </div>
                </div>
            </div>
            <div class="col-md-6">
                <div class="position-relative form-group">
                    <label class="control-label"></label>
                    <select id="ddlCategories" class="form-control"></select>
                </div>
            </div>
        </div>
    </div>

    <script type="text/javascript">
        $(document).ready(function () {
            $('#btnCategory').on("click", function () {
                $.ajax({
                    url: "@Url.Action("SearchCategioryTypeData", "Home")",
                    type: "GET",
                    contentType: "application/json;charset=UTF-8",
                    dataType: "json",
                    data: { id: $('#txtCategoryId').val() },
                    success: function (data) {
                        if (data != "") {
                            var ddlCategories = $("#ddlCategories").empty();
                            ddlCategories.append($("<option value='0'>Select</option>"));
                            $(data).each(function (index,item) {
                                ddlCategories.append($("<option></option>").val(item.ProductId).html(item.ProductName));
                            });
                        }
                    }
                });
            });
        });
    </script>
</body>
</html>

Screenshot