Click here to Skip to main content
15,896,118 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
i have 3 tables
1. state(state_id,state_name) state_id PK,Identity specification,
2.city (city_id,city_name.state_id) city_id PK,Identity specification,
state_id FK
3.university(uni_id,uni_name,state_id,city_id) uni_id PK,Identity specification,, state_id FK , city_id FK

and took one form (university.aspx) with three labels and respective dropdownlist for state, city , university.......
what i want is when i click on state then only list of cities belonging to that state should come and same for university ...i tried following code
university.aspx.cs
C#
BusinessLayer.State objstate = new BusinessLayer.State(System.Configuration.ConfigurationManager.ConnectionStrings["test"].ToString());
    BusinessLayer.cities objcity = new BusinessLayer.cities(System.Configuration.ConfigurationManager.ConnectionStrings["test"].ToString());
    BusinessLayer.university objuni = new BusinessLayer.university(System.Configuration.ConfigurationManager.ConnectionStrings["test"].ToString());
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            Fillstate();
        }

    }
    public void Fillstate()
    {
     
        DataTable dt = new DataTable();
        dt = objstate.getdatastate();
        ddlstate.DataSource = dt;
        ddlstate.DataValueField = "State_Id";
        ddlstate.DataTextField = "State_Name";
        ddlstate.DataBind();
        ddlstate.Items.Add(new ListItem("- Please Select -", "-1"));
        ddlstate.SelectedValue = "-1";
        int state_id = Convert.ToInt32(ddlstate.SelectedIndex)+1;
        TextBox1.Text = state_id.ToString();

    }

    public void Fillcity(int state_id)
    {
        DataTable dt = new DataTable();
        dt = objcity.getdatacity(state_id);
        ddlcity.DataSource = dt;
        ddlcity.DataValueField ="City_Id";
        ddlcity.DataTextField ="City_Name";
        ddlcity.DataBind();
        ddlcity.Items.Add(new ListItem("- Please Select -", "-1"));
        ddlcity.SelectedValue ="-1";
      }
    public void Filluniversity(int city_id)
    {
        DataTable dt = new DataTable();
        dt = objuni.getdatauniversity();
        ddluni.DataSource = dt;
        ddluni.DataValueField = "Uni_Id";
        ddluni.DataTextField = "Uni_Name";
        ddluni.DataBind();
        ddluni.Items.Add(new ListItem("- Please Select -", "-1"));
        ddluni.SelectedValue = "-1";

    }

     protected void ddlstate_SelectedIndexChanged(object sender, EventArgs e)
    {
        int state_id = Convert.ToInt32(ddlstate.SelectedIndex)+1;
        Fillcity(state_id);
    }
    protected void ddlcity_SelectedIndexChanged(object sender, EventArgs e)
    {
        int city_id = Convert.ToInt32(ddlcity.SelectedValue.ToString());
        Filluniversity(city_id);
    }


BusinessLayer cities.cs
C#
public class cities
   {

        DataLibrary dl;

        public cities(string dbconnectionstring)
       {

           dl=new DataLibrary(dbconnectionstring);
       }
        public DataTable getdatacity(int state_id)
        {
            DataTable dt = new DataTable();
            dt = dl.GetDataTable("select City_Id,City_Name from City  where State_Id=state_id");
            return dt;
        }          }


datalibrary.cs
C#
public class DataLibrary
    {
       public SqlConnection Sqlconn;
       public SqlCommand sqlcmd;
       public SqlDataAdapter SqlAdp;
       public DataSet SqlDst;
       public DataTable SqlDtTbl;

        private string StrSqlconnection;

        public DataLibrary(String ConnectionString)    //Constructor declaration
        {
            StrSqlconnection = ConnectionString;
        }

        public int DMLQuery(string SqlCommandText)
        {
            
            Sqlconn = new SqlConnection(StrSqlconnection);
            Sqlconn.Open();
            sqlcmd = new SqlCommand();
            sqlcmd.CommandText = SqlCommandText;
            sqlcmd.Connection = Sqlconn;
            int i = sqlcmd.ExecuteNonQuery();
            return i;
        }

        public DataSet GetDataSet(string SqlCommandText)
        {
             SqlAdp = new SqlDataAdapter(SqlCommandText, StrSqlconnection);
             SqlDst = new DataSet();
            SqlAdp.Fill(SqlDst);
            return SqlDst;
        }

        public DataTable GetDataTable(string SqlCommandText)
        {
           SqlAdp = new SqlDataAdapter(SqlCommandText, StrSqlconnection);
            SqlDtTbl = new DataTable();
            SqlAdp.Fill(SqlDtTbl);
            return SqlDtTbl;
        }
    } 

on page load i am getting list of states but on clicking states i am not getting cities..
Posted
Updated 2-Feb-13 0:11am
v2

 
Share this answer
 
Comments
shivani 2013 2-Feb-13 6:21am    
thanks for this control but can't i use like this...m mean i what io am missing in this code....
on page load i am getting list of states but on clicking states i am not getting cities.. .


in this case you pass the state value as a parameter then only you got the solution whatever you expect..

for ex :

SQL
select city_id,city_name from city_table where state_id=-- you pass the state id on here---
 
Share this answer
 
Comments
shivani 2013 2-Feb-13 6:28am    
i passed state id through method.pls see that
oliver grace 2-Feb-13 6:56am    
Hi what query u passed for this....
and try to call city method also in pageload
I agree with Kishor that your db needs a subtle change, but I totally disagree with him on the reasoning and method.
Your data is not normalized and that is one of your issues. I would suggest removing the state_id from the university table for starters. That column's presence only serves as a maintenance nightmare because anytime you update the city for a university, you'll have to update state as well along with an extra validation against the city-state relationship to ensure your data integrity. By removing the state_id field from the university table, you remove this entire issue. Your tables should look something like this:
SQL
[State](                                         
   [ID] [int] IDENTITY(1,1) NOT NULL,                     
   [Name] [nvarchar](50) NOT NULL )                       
                                                          
[City](
   [ID] [int] IDENTITY(1,1) NOT NULL,
   [Name] [nvarchar](50) NOT NULL ),
   [StateID] [int] FK to [State].[ID]

[University](
   [ID] [int] IDENTITY(1,1) NOT NULL,
   [Name] [nvarchar](50) NOT NULL ),
   [CityID] [int] FK to [City].[ID] )


Because you want to cascade update DropDownList controls on your WebForm, you may want to revisit how you are approaching this as well.
You could do it by just doing a complete postback like you are trying right now, but that is extremely inefficient as it causes the entire form to postback to the server and then the entire page has to be rerendered and sent back in the Response. What you are missing in this approach is that the page must be treated as a brand new page every time you receive a request. For example, if the user has selected the State and you want to populate Cities, you will have to populate both State and Cities and then also set the selected item of State as well before posting back. It may not sound like much, but the amount of data and hits to the database that you are going to have to make will be a huge performance nightmare.

A better approach is to implement an ajax or ajax/jquery generic handler to your form so that you can request just what you are looking for.
Here are some articles on how to approach it this way:
- ASP.NET Advanced Generic Handler ASHX[^]
- jQuery AJAX and HttpHandlers in ASP.NET[^]
- AJAX: The Easy Way In ASP.NET WebForms Using Genric Handlers[^]
 
Share this answer
 
Hello Shivani,
I think by modifying structure of your db you will be able to solve your problem.
I had similar sort of issue and the way I've resolved it was by removing state id integer value.
Because State name is unique as long as you are considering a single country.
Now with string StateName, you can add item in DropDown list like code shown below.
eg.
On StateName SelectedIndexChanged, code
C#
ddlCityNames.Items.Clear();
List<string> cities = dc.GetCities(ddlStateName.SelectedItem.Text.ToString());
foreach (string city in cities)
ddlCityNames.Items.Add(city);


Now with stateName and City, you search univerisities like,

On CityName SelectedIndexChanged, code

C#
            ddlUniNames.Items.Clear();
            List<string> universities =               dc.GetUniversities(ddlStateName.SelectedItem.Text.ToString(),
ddlCityName.SelectedItem.Text.ToString());
            foreach (string university in universities)
            ddlUniNames.Items.Add(university); 
 
Share this answer
 
v2

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