I have a test project built in ASP.NET Core 3.1/EF Core, created with a database-first approach.
In the Home/Privacy Razor view, I have 2 dropdowns which should be cascading, so the 2nd dropdown list should be populated based on the value from the first.
The example I've followed is here: https://www.youtube.com/watch?v=9NnLSrRYMxo which uses an ADO connection to the database.
Currently the first dropdown populates (Area) but the 2nd (Team) doesn't.
I've reviewed the code several times to check it against the example, including against the downloaded solution and debugged whilst running the project, it looks like the Ajax code doesn't seem to be running upon the change of value in the Area dropdown.
The list for the dropdown is therefore blank.
There are NO error messages appearing when the project is running.
Code is as follows:
Stored procedures
CREATE PROCEDURE [ddl].[getAllAreas]
AS
BEGIN
SELECT *
FROM [ddl].[Area]
END
CREATE PROCEDURE [ddl].[getTeamsByAreaId]
@AreaId
AS
BEGIN
SELECT *
FROM [ddl].[Team]
WHERE AreaId = @AreaId
END
Home controller:
namespace CentralMISData.Controllers
{
public class HomeController : Controller
{
DataAccessLayer.Ado ado = new DataAccessLayer.Ado();
private readonly ILogger<HomeController> _logger;
public HomeController(ILogger<HomeController> logger)
{
_logger = logger;
}
public IActionResult Index()
{
return View();
}
public IActionResult Privacy()
{
DataSet ds = ado.GetAllAreas();
List<SelectListItem> list = new List<SelectListItem>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
list.Add(new SelectListItem { Text = dr["AreaName"].ToString(),
Value = dr["AreaId"].ToString() });
}
ViewBag.Arealist = list;
return View();
}
public JsonResult GetTeamList(int areaid)
{
DataSet ds = ado.GetTeamByAreaId(areaid);
List<SelectListItem> list = new List<SelectListItem>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
list.Add(new SelectListItem
{
Text = dr["TeamName"].ToString(),
Value = dr["TeamId"].ToString()
});
}
return Json(list);
}
[ResponseCache(Duration = 0, Location = ResponseCacheLocation.None, NoStore = true)]
public IActionResult Error()
{
return View(new ErrorViewModel { RequestId = Activity.Current?.Id ?? HttpContext.TraceIdentifier });
}
}
}
DataAccessLayer ADO Class
using Microsoft.AspNetCore.Authentication;
using Microsoft.Data.SqlClient;
using System.Data;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
namespace CentralMISData.DataAccessLayer
{
public class Ado
{
readonly SqlConnection conn = new SqlConnection("Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=CentralMISData;Integrated Security=True;");
// Get Area List
public DataSet GetAllAreas()
{
SqlCommand cmd = new SqlCommand("ddl.getAllAreas", conn)
{
CommandType = CommandType.StoredProcedure
};
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
public DataSet GetTeamByAreaId(int areaId)
{
SqlCommand cmd = new SqlCommand("ddl.getTeamByAreaId", conn)
{
CommandType = CommandType.StoredProcedure
};
cmd.Parameters.AddWithValue("@AreaId", areaId);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
}
}
Home/Privacy.cshtml
@{
ViewData["Title"] = "Privacy Policy";
}
<script src="~\js\site.js"></script>
<h1>@ViewData["Title"]</h1>
<div class="container">
<div class="row">
<div class="col-sm-4"></div>
<div class="col-sm-4">
<form>
<div class="form-group">
<label class="control-label">Area</label>
<select id="ddlArea" class="form-control" asp-items="@(new SelectList(ViewBag.Arealist, "Value", "Text"))"></select>
</div>
<div class="form-group">
<label class="control-label">Team</label>
<select id="ddlTeam" class="form-control" asp-items="@(new SelectList(string.Empty, "Value", "Text"))"></select>
</div>
</form>
</div>
<div class="col-sm-4"></div>
</div>
</div>
site.js
$(function () {
$("ddlArea").change(function () {
$.getJSON("/Home/GetTeamList", { areaid: $("#ddlArea").val() }, function (d) {
var row = "";
$("#ddlTeam").empty();
$.each(d, function (i, v) {
row += "<option value=" + v.value + ">" + v.text + "</option>";
});
$("#ddlTeam").html(row);
})
})
});
I'm quite new to C# and Ajax etc, just trying to learn by trying different things.
I cannot myself see anything that is preventing the 2nd dropdown from being populated.
If anyone can suggest or point out where I've gone wrong then it would be very much appreciated.
Alternatively if anyone can suggest an alternative/improved/better way of creating cascading dropdowns then again it would be very much appreciated.