populate two drop downlist and textbox

[peerraghu]

hi
i want to populate country's name in 1st dropdownlist and their states should be placed in the 2nd dropdownlist, if we select any state it should place the name of the capital in the textbox besides that and so on.....
If possible send me code

thank you in advance
raghu

[dip_developer]

hi
i want to populate country's name in 1st dropdownlist and their states should be placed in the 2nd dropdownlist, if we select any state it should place the name of the capital in the textbox besides that and so on.....
If possible send me code

thank you in advance
raghu

you should keep a good table structure for your work....I recommend four(4) tables to create...

1.tblCountry_Master
------------------------------
CountryId(PrimaryKey),CountryName

2.tblSate_Master
-----------------------
StateID(PK),StateName

3. tblStateCountryRelation
-------------------------------------
CountryId,StateId

4.tblStateCapital
-------------------------------
StateId(PK),Capital_Name

populate your country dropdown first with sql like...........

SELECT * FROM tblCountry_Master

make a dataset.....set the datasource of the dropdown to the Dataset.
if you are making a windows application then set ValueMember of the dropdownlist to "CountryId" and DisplayMember to "CountryName"[ you have not mentioned about your application.......In web application these are DataValueField and DataTextField...........]

Now in the SelectedIndexChanged event of your country dropdown choose the valuefield(Here it is CountryId)

 
 Dim country_id As String 
 country_id = myDropdown.SelectedValue 
 

Now after getting the country_id you can easily fetch the corresponding statename with a simple query like....

 
 SELECT StateID,StateName FROM tblSate_Master S INNER JOIN 
 tblStateCountryRelation SC ON S.StateID=SC.StateId WHERE 
 CountryId='" & country_id & "'

again create a dataset with these records and bind it to State dropdownlist

Repeat the above mechanism for state capital name::
--------------------------------------------------------------------------------
In the SelectedIndexChanged event of your state dropdown choose the valuefield(Here it is StateId)..........and choose the capital from tblStateCapital with sql statement

 SELECT Capital_Name FROM tblStateCapital WHERE 
 StateId='" & state_id & "'
 

Hope you understand.........I have not given you the full code ......try yourself....I think you can do it now........

[peerraghu]

you should keep a good table structure for your work....I recommend four(4) tables to create...

1.tblCountry_Master
------------------------------
CountryId(PrimaryKey),CountryName

2.tblSate_Master
-----------------------
StateID(PK),StateName

3. tblStateCountryRelation
-------------------------------------
CountryId,StateId

4.tblStateCapital
-------------------------------
StateId(PK),Capital_Name

populate your country dropdown first with sql like...........

SELECT * FROM tblCountry_Master

make a dataset.....set the datasource of the dropdown to the Dataset.
if you are making a windows application then set ValueMember of the dropdownlist to "CountryId" and DisplayMember to "CountryName"[ you have not mentioned about your application.......In web application these are DataValueField and DataTextField...........]

Now in the SelectedIndexChanged event of your country dropdown choose the valuefield(Here it is CountryId)

 
 Dim country_id As String 
 country_id = myDropdown.SelectedValue 
 

Now after getting the country_id you can easily fetch the corresponding statename with a simple query like....

 
 SELECT StateID,StateName FROM tblSate_Master S INNER JOIN 
 tblStateCountryRelation SC ON S.StateID=SC.StateId WHERE 
 CountryId='" & country_id & "'

again create a dataset with these records and bind it to State dropdownlist

Repeat the above mechanism for state capital name::
--------------------------------------------------------------------------------
In the SelectedIndexChanged event of your state dropdown choose the valuefield(Here it is StateId)..........and choose the capital from tblStateCapital with sql statement

 SELECT Capital_Name FROM tblStateCapital WHERE 
 StateId='" & state_id & "'
 

Hope you understand.........I have not given you the full code ......try yourself....I think you can do it now........

thank you for giving me such a good detail, i will try to sought my problem

[Usharva]

Hi,
I feel 2 tables are enough

1. Country Table (Country_ID(Primary Key),Country_Name)
2. State Table (State_ID(Primary Key), State_Name,Country_Id(Foreign Key), Capital_City)

Each State will have one Capital City so i feel one more table is not necessary for Capital City

In the SelectedIndexChanged event of the Country DropDown write the below query

Select State_Id,State_Name From State Where Country_Id=ddlCountry.selectedValue ( Country dropDownList Name)


In the SelectedIndexChanged event of the State DropDown write the below query

Select Capital_City From State Where State_ID=ddlState.selectedValue ( StatedropDownList Name)


I hope this will work

[peerraghu]

i am developing web application using c#,,
i got an idea how to populate but were shoould i write coding for state_master coding(select * from state_master......)

[dip_developer]

Hi,
I feel 2 tables are enough

1. Country Table (Country_ID(Primary Key),Country_Name)
2. State Table (State_ID(Primary Key), State_Name,Country_Id(Foreign Key), Capital_City)

Each State will have one Capital City so i feel one more table is not necessary for Capital City

In the SelectedIndexChanged event of the Country DropDown write the below query

Select State_Id,State_Name From State Where Country_Id=ddlCountry.selectedValue ( Country dropDownList Name)


In the SelectedIndexChanged event of the State DropDown write the below query

Select Capital_City From State Where State_ID=ddlState.selectedValue ( StatedropDownList Name)


I hope this will work

dear Usharva,

with due respect I have to tell you that your table structure is not smart enough to do the required work.......its true that the whole thing is going good with your provided code....but think of a situation where you have one country with one state[though it has a fair chance of ocurrence] only and I have to DELETE that record from your State Table...then what happened......the only country ID will be deleted.....and you have to update the Country Table.....

your table structure is not Normalized....it will have Redundant data...as well as it has not met the criteria of Functional dependancy

So Normalize your database.........

I have created 4 tables after taking into consideration all of the possibilities.........

[peerraghu]

hi
i populated dropdownlisst of country by writing this code

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


protected void Page_Load(object sender, EventArgs e)
{

if (Page.IsPostBack == false)
{
SqlConnection con=new SqlConnection("user id=sa;password=vubrain;database=raghu;data source=vubrain4");
SqlDataAdapter da = new SqlDataAdapter("select countryid,countryname from country", con);
DataSet ds = new DataSet();
da.Fill(ds, "country");
dd1.DataSource = ds.Tables["country"];
dd1.DataTextField = "countryname";
dd1.DataValueField = "countryid";
dd1.DataBind();

}
}
protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
string capitalid;
capitalid = dd1.SelectedValue;


}
}




after this how to populate next dropdownlist and textbox

[dip_developer]

I have told you in detail...please read it carefully...you will get the answer.......otherwise I have to write the same code.....

[peerraghu]

I have told you in detail...please read it carefully...you will get the answer.......otherwise I have to write the same code.....

shell i write coding to populate dropdownlist2 in DropDownList1_SelectedIndexChanged

i mean to say where should i write sql querry

[peerraghu]

I have told you in detail...please read it carefully...you will get the answer.......otherwise I have to write the same code.....

its very urgent i been fighting for this from 3 days

[dip_developer]

shell i write coding to populate dropdownlist2 in DropDownList1_SelectedIndexChanged

i mean to say where should i write sql querry

in the DropDownList1_SelectedIndexChanged event you have to write the sql query and fetch the states......

[peerraghu]

i wrote code in dropdownlist_selectedindexchanged....

protected void dd1_SelectedIndexChanged(object sender, EventArgs e)
{
string countryid;
countryid = dd1.SelectedValue;
SqlConnection con = new SqlConnection("user id=sa;password=vubrain;database=raghu;data source=vubrain4");
SqlDataAdapter da1 = new SqlDataAdapter("SELECT State_id,statename FROM state_master S INNER JOIN state_country SC ON S.state_id=SC.stateId8 WHERE countyr_id='" + countryid + "'");
DataSet ds1 = new DataSet();
da1.Fill(ds1, "state_master");
dd2.DataSource = ds1.Tables["state_master"];
dd2.DataTextField = "statename";
dd2.DataValueField = "State_id";
dd2.DataBind();


}
protected void dd2_SelectedIndexChanged(object sender, EventArgs e)
{
string stateid;
stateid = dd2.SelectedValue;

}

but it showing two error

1.Argument '1': cannot convert from 'string'to 'System.Data.SqlClient.SqlCommand'

2.The best overloaded method match for 'System.Data.SqlClient.SqlDataAdapter.SqlDataAdapt er

please sought this problem

[peerraghu]

hi dip i am trying the same you said but i am geting the same error in the dropdownlist1_selectedindexedchanged as i earlieri mentioned you
please solve my problem
thank you