Hi comunidadmexi...,
To overcome this issue i have created another method to fetch the distinct value for DropDownList.
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 NorthwindModel
{
public List<SelectListItem> Countries { get; set; }
public List<SelectListItem> Cities { get; set; }
public List<Customer> Customers { get; set; }
}
public class Customer
{
public string CustomerID { get; set; }
public string ContactName { get; set; }
public string City { get; set; }
public string Country { get; set; }
}
Namespaces
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
Controller
public class HomeController : Controller
{
// GET: Home
public ActionResult Index()
{
NorthwindModel model = PopulateModel(null, null);
return View(model);
}
[HttpPost]
public ActionResult Index(string country, string city)
{
NorthwindModel model = PopulateModel(country, city);
return View(model);
}
private static NorthwindModel PopulateModel(string country, string city)
{
NorthwindModel model = new NorthwindModel();
model.Countries = GetDropDownList("SELECT DISTINCT Country FROM Customers WHERE Country IS NOT NULL ORDER BY Country").ToList();
model.Cities = GetDropDownList("SELECT DISTINCT City FROM Customers WHERE City IS NOT NULL ORDER BY City").ToList();
model.Customers = GetData(country, city);
return model;
}
private static List<Customer> GetData(string country, string city)
{
List<Customer> customers = new List<Customer>();
string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(conString))
{
string query = "SELECT CustomerID,ContactName,City,Country FROM Customers";
SqlCommand cmd = new SqlCommand(query);
cmd.Connection = con;
con.Open();
SqlDataReader sdr = cmd.ExecuteReader();
while (sdr.Read())
{
customers.Add(new Customer
{
CustomerID = sdr["CustomerID"].ToString(),
ContactName = sdr["ContactName"].ToString(),
City = sdr["City"].ToString(),
Country = sdr["Country"].ToString(),
});
}
con.Close();
}
return customers;
}
private static List<SelectListItem> GetDropDownList(string query)
{
List<SelectListItem> items = new List<SelectListItem>();
string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(conString))
{
SqlCommand cmd = new SqlCommand(query);
cmd.Connection = con;
con.Open();
SqlDataReader sdr = cmd.ExecuteReader();
while (sdr.Read())
{
items.Add(new SelectListItem
{
Text = sdr[0].ToString(),
Value = sdr[0].ToString()
});
}
con.Close();
}
items.Insert(0, new SelectListItem { Text = "All", Value = "" });
return items;
}
}
View
@model WebGrid_EF_MVC.Models.NorthwindModel
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Index</title>
</head>
<body>
@using (Html.BeginForm("Index", "Home", FormMethod.Post, new { @Id = "WebGridForm" }))
{
<span>Country: </span>@Html.DropDownList("Country", Model.Countries, new { @id = "ddlCountries" })
<span>City: </span>@Html.DropDownList("City", Model.Cities, new { @id = "ddlCities" })
}
</body>
</html>
