Buy Questpond's video subscriptions on huge discount.
DotNetFunda.com
Online: 4031
Home > Forums > ASP.NET >

how to relate two dropdown list [Resolved]

Posted by Ankitsrist under ASP.NET on 11/16/2012 | Points: 10 | Views : 13801 | Status : [Member] | Replies : 11
Write New Post | Search Forums | AnsweredResolved Posts | Un Answered Posts | Forums Home 
i am trying to make application in which i used two dropdown list which gets its value from database and one shows states and another shows its corresponded cities ......means when i select MP, city dropdownlist shud only show cities of MP state....and code for this is as follows
    using System.Data.SqlClient;
    public partial class _Default : System.Web.UI.Page
    {
    dbclass myobj =new dbclass();
    SqlConnection sqlcon=new SqlConnection();
    protected void Page_Load(object sender, EventArgs e)
    {
    myobj.myconnection();
    SqlCommand cmd = new SqlCommand("select * from states", myobj.sqlcon);
    SqlDataReader reader = cmd.ExecuteReader();
    while (reader.Read())
    {
    ListItem li = new ListItem();
    li.Value = reader["stateid"] as string;
    li.Text = reader["statename"] as string;
    DropDownList1.Items.Add(li);
    }
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
    }
    protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
    {
    myobj.myconnection();
    SqlCommand cmd2 = new SqlCommand("select * from cities where stateid='" + Convert.ToInt32(DropDownList1.SelectedValue)+"'", myobj.sqlcon);
    SqlDataReader reader2=cmd2.ExecuteReader();
    while(reader2.Read())
    {
    ListItem li2=new ListItem();
    li2.Value=reader2["stateid"]as string;
    li2.Text=reader2["cityname"]as string;
    DropDownList2.Items.Add(li2);
    }
    }


but i am getting this error "Input string was not in a correct format."
plz help me....:(



Reply | Reply with Attachment
Alert Moderator 

Responses

Posted by: Sandhyab on: 11/17/2012 [Member] Starter | Points: 50

Up
0
Down

Resolved
Hi,

See this following example.
Take three drop downs. Country,State,Region.
Here, I need to populate state based on country drop down,and I need to populate region based on state dropdown
Take three tables in database. Country,State and region
Use foriegn key relation.Example, Take CountryId,CountryName in Country table.Use SateId,StateName and CountryId in Sate table and RegionId,RegionName and StateId in RegionTable.Use relationships.

Now Let us design ASPX Page. 

 <html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

<title>Dropdowns Sample</title>

</head>

<body>

<form id="form1" runat="server">

<div>

<table align="center">

<tr>

<td>

Select Country:

</td>

<td>

<asp:DropDownList ID="ddlCountry" runat="server" AutoPostBack="true"

onselectedindexchanged="ddlCountry_SelectedIndexChanged"></asp:DropDownList>

</td>

</tr>

<tr>

<td>

Select State:

</td>

<td>

<asp:DropDownList ID="ddlState" runat="server" AutoPostBack="true"

onselectedindexchanged="ddlState_SelectedIndexChanged"></asp:DropDownList>

</td>

</tr>

<tr>

<td>

Select Region:

</td>

<td>

<asp:DropDownList ID="ddlRegion" runat="server"></asp:DropDownList>

</td>

</tr>

</table>

</div>

</form>

</body>

</html>


In Code Behind: 
 private String strConnection = "Data Source=SR-PC;Initial Catalog=DemoDB;Integrated Security=True";

protected void Page_Load(object sender, EventArgs e)

{

if(!IsPostBack)

{

BindCountryDropdown();

}



}

/// <summary>

/// Bind Countrydropdown

/// </summary>

protected void BindCountryDropdown()

{

//conenction path for database

SqlConnection con = new SqlConnection(strConnection);

con.Open();

SqlCommand cmd = new SqlCommand("select * from CountryTable", con);

SqlDataAdapter da = new SqlDataAdapter(cmd);

DataSet ds = new DataSet();

da.Fill(ds);

con.Close();

ddlCountry.DataSource = ds;

ddlCountry.DataTextField = "CountryName";

ddlCountry.DataValueField = "CountryID";

ddlCountry.DataBind();

ddlCountry.Items.Insert(0, new ListItem("--Select--", "0"));

ddlState.Items.Insert(0, new ListItem("--Select--", "0"));

ddlRegion.Items.Insert(0, new ListItem("--Select--", "0"));



}

/// <summary>

/// Bind State Dropdown Based on CountryID

/// </summary>

/// <param name="sender"></param>

/// <param name="e"></param>

protected void ddlCountry_SelectedIndexChanged(object sender, EventArgs e)

{

int CountryID = Convert.ToInt32(ddlCountry.SelectedValue);

SqlConnection con = new SqlConnection(strConnection);

con.Open();

SqlCommand cmd = new SqlCommand("select * from StateTable where CountryID="+CountryID, con);

SqlDataAdapter da = new SqlDataAdapter(cmd);

DataSet ds = new DataSet();

da.Fill(ds);

con.Close();

ddlState.DataSource = ds;

ddlState.DataTextField = "StateName";

ddlState.DataValueField = "StateID";

ddlState.DataBind();

ddlState.Items.Insert(0, new ListItem("--Select--", "0"));

if(ddlState.SelectedValue=="0")

{

ddlRegion.Items.Clear();

ddlRegion.Items.Insert(0, new ListItem("--Select--", "0"));

}



}

/// <summary>

/// Bind Region DRopDownbased 

/// </summary>

/// <param name="sender"></param>

/// <param name="e"></param>

protected void ddlState_SelectedIndexChanged(object sender, EventArgs e)

{

int StateID = Convert.ToInt32(ddlState.SelectedValue);

SqlConnection con = new SqlConnection(strConnection);

con.Open();

SqlCommand cmd = new SqlCommand("select * from RegionTable where StateID=" + StateID, con);

SqlDataAdapter da = new SqlDataAdapter(cmd);

DataSet ds = new DataSet();

da.Fill(ds);

con.Close();

ddlRegion.DataSource = ds;

ddlRegion.DataTextField = "RegionName";

ddlRegion.DataValueField = "RegionID";

ddlRegion.DataBind();

ddlRegion.Items.Insert(0, new ListItem("--Select--", "0"));

}

I hope this will help you
Thanks & Regards


Ankitsrist, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Ankitsrist on: 11/17/2012 [Member] Starter | Points: 25

Up
0
Down

Resolved
this is my solution after some editing......and it is running correctly....:) 

using System;

using System.Data;

using System.Configuration;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.Data.SqlClient;



public partial class _Default : System.Web.UI.Page 

{

    dbclass myobj =new dbclass();

    SqlConnection sqlcon=new SqlConnection();

    protected void Page_Load(object sender, EventArgs e)

    {

        if (!IsPostBack)

        { 

          binddropdownlist1();

        

        }



     }

    protected void binddropdownlist1()

    {



        myobj.myconnection();

        SqlCommand cmd = new SqlCommand("Select * from states", myobj.sqlcon);



        SqlDataAdapter adp = new SqlDataAdapter(cmd);

        DataSet ds = new DataSet();

        adp.Fill(ds, "states");

        DropDownList1.DataSource = ds;

        DropDownList1.DataTextField = "statename";

        DropDownList1.DataValueField = "stateid";

        DropDownList1.DataBind();

        DropDownList1.Items.Insert(0, new ListItem("--Select--", "0"));

        DropDownList2.Items.Insert(0, new ListItem("--Select--", "0"));

    

    }

    protected void Button1_Click(object sender, EventArgs e)

    {

      



    }

    protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)

    {

        int stateid = Convert.ToInt32(DropDownList1.SelectedValue);

        myobj.myconnection();

        SqlCommand cmd = new SqlCommand("select * from cities where stateid="+stateid, myobj.sqlcon);

       

        SqlDataAdapter da = new SqlDataAdapter(cmd);

        DataSet ds = new DataSet();

        da.Fill(ds);

        DropDownList2.DataSource = ds;

        DropDownList2.DataTextField = "cityname";

        DropDownList2.DataValueField = "Stateid";

        DropDownList2.DataBind();



     }

  

}


Ankitsrist, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Ankitsrist on: 11/17/2012 [Member] Starter | Points: 25

Up
0
Down
thanks sandhyab....i also made two tables states and cities in which state has two columns stateid and statename and cities table has stateid and cityname....i think cities table has creating problem actually i missed to add cityid column....

Ankitsrist, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Sandhyab on: 11/17/2012 [Member] Starter | Points: 25

Up
0
Down
Hi,

And,Once see this example.This example shows populating lisbox using drop down and dropdown using list box. I used xml file.
Once try this example also.

An my xml File is dd.xml:

dd.xml: 
<?xml version="1.0" encoding="utf-8" ?>

<countries>

  <country state="AP" city="HYD" location="KPHB"></country>

  <country state="AP" city="SATTUPALLI" location="SIDARAM"></country>



  <country state="WB" city="KOLKATA" location="SALTLAKE"></country>

  <country state="WB" city="KARAKPUR" location="IIT"></country>



</countries>


ASPX page: 

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %>



<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">



<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title></title>

</head>

<body>

    <form id="form1" runat="server">

    <div>

     <div>

        <asp:DropDownList ID="DropDown1" runat="server" AutoPostBack="true" OnSelectedIndexChanged="Displaylistbox" DataTextField="state" DataValueField="state"></asp:DropDownList> 

        <asp:ListBox ID="listbox1" runat="server" AutoPostBack="true" OnSelectedIndexChanged="DisplaySecondDropDown" DataTextField="city" DataValueField="city"></asp:ListBox>   

        <asp:DropDownList ID="DropDown2" runat="server" AutoPostBack="true" DataTextField="location" DataValueField="location"></asp:DropDownList>

    </div>

    </div>

    </form>

</body>

</html>


.CS file: 
using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data;



public partial class Default2 : System.Web.UI.Page

{

    protected void Page_Load(object sender, EventArgs e)

    {

        if (!IsPostBack)

        {

            PopulateData();

        }

    }

    protected void PopulateData()

    {

        DataTable table = GetData();

        DataTable state = table.DefaultView.ToTable(true, "state");

        DropDown1.DataSource = state;

        DropDown1.DataBind();

    }



    private DataTable GetData()

    {

        DataSet dset = new DataSet();

        dset.ReadXml(Server.MapPath("~/dd.xml"));

        DataTable table = dset.Tables[0];

        return table;

    }

    protected void Displaylistbox(object sender, EventArgs e)

    {

        DataTable table = GetData();

        string state = DropDown1.SelectedValue;

        table.DefaultView.RowFilter = "state='" + state + "'";

        listbox1.DataSource = table;

        listbox1.DataBind();



    }

    protected void DisplaySecondDropDown(object sender, EventArgs e)

    {

        DataTable table = GetData();

        string state = listbox1.SelectedValue;

        table.DefaultView.RowFilter = "city='" + state + "'";

        DropDown2.DataSource = table;

        DropDown2.DataBind();

    }

        

}


Try this. And,See the output.

Thanks & regards

Ankitsrist, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Ankitsrist on: 11/17/2012 [Member] Starter | Points: 25

Up
0
Down
yaa sandhyab bcoz of ur advice m very near to my solution....actually dropdownlist1 showing both statename MP nd UP......and when i select MP it shows city JBP in dropdownlist2 it is working properly...but when i select another state UP it again display MP in dropdownlist1 and JBP in dropdownlist2......why m gettng this problem?????

Ankitsrist, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Sandhyab on: 11/17/2012 [Member] Starter | Points: 25

Up
0
Down
If You used xml file,
Modify like this 
<?xml version="1.0" encoding="utf-8" ?>

<countries>

  <country state="Please Select" city="Please Select" location="Please Select"></country> 

  <country state="AP" city="HYD" location="KPHB"></country>

  <country state="AP" city="SATTUPALLI" location="SIDARAM"></country>

  <country state="AP" city="Nizamabad" location="Bodhan"></country>



  <country state="WB" city="KOLKATA" location="SALTLAKE"></country>

  <country state="WB" city="KARAKPUR" location="IIT"></country>



</countries>

Try Now.

Thanks & regards


Ankitsrist, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Ankitsrist on: 11/17/2012 [Member] Starter | Points: 25

Up
0
Down
thanks for ur replies....actually m just beginner and i dont know how to use xmls files...so thts y m ignoring it....

Ankitsrist, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Ankitsrist on: 11/17/2012 [Member] Starter | Points: 25

Up
0
Down
thanku u so much.....finaly i did it.....actualy there was a problem in pageload function....i made another method with name dropdownliststate and write all my pageload code in that dropdownliststate method....and called this method in pageload....with (!IsPostBack)....and i just got it...:)

Ankitsrist, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Sandhyab on: 11/17/2012 [Member] Starter | Points: 25

Up
0
Down
You are welcome.
And, Once try with xml also.

Thanks & regards



Ankitsrist, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Sandhyab on: 11/17/2012 [Member] Starter | Points: 25

Up
0
Down
If the above codes helped you,Please mark as answer. It will help others.

Thanks & Regards




Ankitsrist, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Ankitsrist on: 11/17/2012 [Member] Starter | Points: 25

Up
0
Down
ok sandhyab......

thanks & regards

Ankitsrist, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response

Latest Posts