Hi, I have two tables in DB to populate cascading dropdownlist. On change of value in first dropdown, second dropdown has to be populated using second table. I am getting same data while tried to populate database data in dropdownlist. countryid is foreign key in state table. first row countryid in state table is 6 which corresponds to north america in country table. It displays the same for all 11 rows in country dropdownlist. Please give me right direction?
Below is the code which i tried.
public ActionResult Submit()
{
List<country> allcountry= new List<country>();
List<state> allstate = new List<state>();
using (portalconnectionstring pc = new portalconnectionstring())
{
allcountry = pc.countries.OrderBy(a => a.countryname.ToList();
}
ViewBag.CountryID = new SelectList(allcountry, "countryid", "countryname");
ViewBag.StateID = new SelectList(allstate, "stateid", "statename");
return View();
}
[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Submit(Feedback s)
{
List<country> allcountry= new List<country>();
List<state> allstate= new List<state>();
using (portalconnectionstring pc = new portalconnectionstring())
{
allcountry= pc.countries.OrderBy(a => a.countryname).ToList();
if (s != null && s.countryid > 0)
{
allstate= pc.states.Where(a => a.countryid.Equals(s.countryid)).OrderBy(a => a.statename).ToList();
}
}
ViewBag.CountryID = new SelectList(allcountry, "countryid", "countryname", s.countryid);
ViewBag.StateID = new SelectList(allstate, "stateid", "statename", s.stateid);
if (ModelState.IsValid)
{
using (portalconnectionstring pc = new portalconnectionstring())
{
using (NpgsqlCommand pgsqlcommand = new NpgsqlCommand("ddltest", conn))
{
pgsqlcommand.CommandType = CommandType.StoredProcedure;
pgsqlcommand.Parameters.Add(new NpgsqlParameter("countryname", NpgsqlDbType.Varchar));
pgsqlcommand.Parameters.Add(new NpgsqlParameter("statename", NpgsqlDbType.Varchar));
pgsqlcommand.Parameters[0].Value = model.countryname;
pgsqlcommand.Parameters[1].Value = model.statename;
pgsqlcommand.ExecuteNonQuery();
}
ViewBag.Message = "Successfully submitted";
}
}
else
{
ViewBag.Message = "Failed! Please try again";
}
return View(s);
}
[HttpGet]
public JsonResult GetStates(string countryid = "")
{
List<state> allstate= new List<state>();
int ID = 0;
if (int.TryParse(countryid, out ID))
{
using (portalconnectionstring dc = new portalconnectionstring())
{
allstate = dc.states.Where(a => a.countryid.Equals(ID)).OrderBy(a => a.statename).ToList();
}
}
if (Request.IsAjaxRequest())
{
return new JsonResult
{
Data = allstate,
JsonRequestBehavior = JsonRequestBehavior.AllowGet
};
}
else
{
return new JsonResult
{
Data = "Not valid request",
JsonRequestBehavior = JsonRequestBehavior.AllowGet
};
}
}
}
}
cshtml: script file
<script type="javascript">
$(document).ready(function () {
$("#countryid").change(function () {
var countryID = parseInt($("#countryid").val());
if (!isNaN(countryID)) {
var ddstate = $("#stateid");
ddstate.empty();
ddstate.append($("<option></option").val("").html("Select State"));
$.ajax({
url: "@Url.Action("GetState","cascadedddl")",
type: "GET",
data: { countryID: countryid },
dataType: "json",
success: function (data) {
$.each(data, function (i, val) {
ddstate.append(
$("<option></option>").val(val.stateid).html(val.statename)
);
});
},
error: function () {
alert("Error!");
}
});
}
});
});
</script>
}