Click here to Skip to main content
14,692,359 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a code that i created for Cascading Dropdown List but ever i load the drop down lists don't have any data exception the first one the Country dropdown have data but the STATE AND City no so can any one help me ?? this is the code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
public partial class _Default : System.Web.UI.Page
{
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

    private void Bind_ddlCountry()
{
 	con.Open();
    SqlCommand com = new SqlCommand("select countryId,Country from Country", con);
        SqlDataReader dr= com.ExecuteReader();
        ddlCountry.DataSource = dr; 
        ddlCountry.Items.Clear();
        ddlCountry.Items.Add(".....Please Select Item.....");
        ddlCountry.DataTextField = "Country";
        ddlCountry.DataValueField = "CountryId";
        ddlCountry.DataBind();
        con.Close();
}

    
       public void Bind_ddlState()
    {
        con.Open();
        SqlCommand com = new SqlCommand("select StateId,State from CountryState where CountryId ='"+ ddlCountry.SelectedValue +"'", con);
        SqlDataReader dr = com.ExecuteReader();
        ddlCountry.DataSource = dr; 
        ddlCountry.Items.Clear();
        ddlCountry.Items.Add(".....Please Select State.....");
        ddlCountry.DataTextField = "State";
        ddlCountry.DataValueField = "StateId";
        ddlCountry.DataBind();
        con.Close();
            }
       public void Bind_ddlCity()
    {
        con.Open();
        SqlCommand com = new SqlCommand("select CityId,City from StateCity where StateId ='" + ddlState.SelectedValue + "'", con);
        SqlDataReader dr = com.ExecuteReader();
        ddlCountry.DataSource = dr; 
        ddlCountry.Items.Clear();
        ddlCountry.Items.Add(".....Please Select City.....");
        ddlCountry.DataTextField = "City";
        ddlCountry.DataValueField = "CityId";
        ddlCountry.DataBind();
        con.Close();
            }

    protected void Page_Load(object sender, EventArgs e)
    {

        if(!IsPostBack)
        {
            Bind_ddlCountry();
        }
    }

    protected void ddlCountry_SelectedIndexChanged(object sender, EventArgs e)
    {
        Bind_ddlState();
    }
    protected void ddlState_SelectedIndexChanged(object sender, EventArgs e)
    {
        Bind_ddlCity();
    }
}
Posted
Comments
Richard Deeming 20-May-15 7:40am
   
Your code is vulnerable to SQL Injection[^].

NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

1 solution

You need to fix the SQL Injection[^] vulnerability in your code.

You should also wrap disposable objects in a using block, to ensure that their resources are always cleaned up.

The SqlConnection should be moved to a local variable, rather than keeping it alive for the lifetime of the page.

And your Bind_ddlState and Bind_ddlCity methods are updating the wrong control.

public partial class _Default : System.Web.UI.Page
{
    private static SqlConnection CreateConnection()
    {
        return new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
    }

    private void Bind_ddlCountry()
    {
        ddlCountry.Items.Clear();
        ddlCountry.Items.Add(".....Please Select Item.....");
        ddlCountry.DataValueField = "CountryId";
        ddlCountry.DataTextField = "Country";

        using (var connection = CreateConnection())
        using (var command = new SqlCommand("SELECT CountryId, Country FROM Country", connection))
        {
            connection.Open();

            using (var reader = command.ExecuteReader(CommandBehavior.CloseConnection))
            {
                ddlCountry.DataSource = reader;
                ddlCountry.DataBind();
            }
        }
    }

    private void Bind_ddlState()
    {
        ddlState.Items.Clear();
        ddlState.Items.Add(".....Please Select Item.....");
        ddlState.DataValueField = "StateId";
        ddlState.DataTextField = "State";

        using (var connection = CreateConnection())
        using (var command = new SqlCommand("SELECT StateId, State FROM CountryState WHERE CountryId = @CountryId", connection))
        {
            command.Parameters.AddWithValue("@CountryId", ddlCountry.SelectedValue);
            connection.Open();

            using (var reader = command.ExecuteReader(CommandBehavior.CloseConnection))
            {
                ddlState.DataSource = reader;
                ddlState.DataBind();
            }
        }
    }

    private void Bind_ddlCity()
    {
        ddlCity.Items.Clear();
        ddlCity.Items.Add(".....Please Select Item.....");
        ddlCity.DataValueField = "CityId";
        ddlCity.DataTextField = "City";

        using (var connection = CreateConnection())
        using (var command = new SqlCommand("SELECT CityId, City FROM StateCity WHERE StateId = @StateId", connection))
        {
            command.Parameters.AddWithValue("@StateId", ddlState.SelectedValue);
            connection.Open();

            using (var reader = command.ExecuteReader(CommandBehavior.CloseConnection))
            {
                ddlCity.DataSource = reader;
                ddlCity.DataBind();
            }
        }
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            Bind_ddlCountry();
        }
    }

    protected void ddlCountry_SelectedIndexChanged(object sender, EventArgs e)
    {
        Bind_ddlState();
    }
    protected void ddlState_SelectedIndexChanged(object sender, EventArgs e)
    {
        Bind_ddlCity();
    }
}
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900