I am having some null rows in my Cities table and i have not shown the Null values In the DropDownList
HTML
<asp:DropDownList ID="ddlCities" runat="server">
</asp:DropDownList>
Namespace
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
this.PopulateDropDownList();
}
}
private void PopulateDropDownList()
{
string constr = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
using (SqlConnection conn = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("SELECT CityName FROM Cities", conn))
{
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataTable dt = new DataTable();
da.Fill(dt);
IEnumerable<DataRow> query = from city in dt.AsEnumerable()
where !string.IsNullOrEmpty(city.Field<string>("CityName"))
select city;
// Create a table from the query.
DataTable boundTable = query.CopyToDataTable<DataRow>();
this.ddlCities.DataSource = boundTable;
this.ddlCities.DataValueField = "CityName";
this.ddlCities.DataTextField = "CityName";
this.ddlCities.DataBind();
}
}
}
}
SQL Here second row CityName is NULL

Screenshot