Hi sallmomar,
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
Namespaces
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
Controller
public class HomeController : Controller
{
// GET: /Home/
public ActionResult Index()
{
return View();
}
public ActionResult GetEmployees(string country)
{
List<Employee> employees = new List<Employee>();
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
string query = "SELECT FirstName + ' ' + LastName Name,EmployeeID FROM Employees WHERE Country = @Country";
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Connection = con;
cmd.Parameters.AddWithValue("@Country", country);
con.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
employees.Add(new Employee
{
Name = sdr["Name"].ToString(),
Id = Convert.ToInt32(sdr["EmployeeID"])
});
}
}
con.Close();
}
}
return Json(employees, JsonRequestBehavior.AllowGet);
}
public class Employee
{
public int Id { get; set; }
public string Name { get; set; }
}
}
View
<script type="text/javascript" src="https://code.jquery.com/jquery-3.1.1.min.js"></script>
<script type="text/javascript">
$(function () {
$('[id*=ddlCountries]').change(function () {
var selectedCountry = $(this).find('option:selected').val();
if (selectedCountry != null && selectedCountry != '') {
$.getJSON('Home/GetEmployees', { country: selectedCountry }, function (response) {
if (response.length > 0) {
$("[id*=tblEmployees] tbody").empty();
var row;
$.each(response, function (index, item) {
row += "<tr><td>" + item.Id + "</td><td>" + item.Name + "</td></tr>"
})
$("[id*=tblEmployees] tbody").append(row);
}
});
}
});
});
</script>
<select id="ddlCountries">
<option value="">Select</option>
<option value="USA">USA</option>
<option value="UK">UK</option>
</select>
<br />
<br />
<table id="tblEmployees">
<thead>
<tr>
<th>Id</th>
<th>Name</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
Screenshot
