Help on dropdown list [Resolved]

Posted by Vijayar under ASP.NET on 5/4/2011 | Points: 10 | Views : 1518 | Status : [Member] | Replies : 6
Hi , i want a bind data to a dropdownlist with data from 2 different tables i.e countries and states please help me
Ex:
India

Andhrapradesh
Usa
mexico

vijaya


Responses

Posted by: Ndebata on: 5/4/2011 [Member] Starter | Points: 50

Up
0
Down

Resolved
Lets say two tables Country and State are as follows
Country

ID Name
1 INDIA
2 USA

State

ID Name CountryID
1 Orissa 1
2 Karnataka 1
3 NewYork 2
4 Mexico 2

This query will produce the required result

Select ID,Name,CountryID from [State]
UNION
Select 0 as ID,Name,ID as CountryID from Country
order by CountryID,ID

Thanks,
Debata

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

Posted by: Ndebata on: 5/4/2011 [Member] Starter | Points: 25

Up
0
Down
Hi

You have to merge both Country and State. Either you can write a query which will return this type of data. Or in memery you can build a collection by merging both Country and State Collection to one you require.

//Populate some country
DataTable cntry = new DataTable();
cntry.Columns.Add("CntryID");
cntry.Columns.Add("Name");
cntry.Rows.Add(new object[] { 1, "India" });
cntry.Rows.Add(new object[] { 2, "USA" });

//Populate some states
DataTable state = new DataTable();
state.Columns.Add("SateID");
state.Columns.Add("Name");
state.Columns.Add("CntryID");

state.Rows.Add(new object[] { 1, "AP",1 });
state.Rows.Add(new object[] { 2, "UP",1 });
state.Rows.Add(new object[] { 3, "UP", 1 });
state.Rows.Add(new object[] { 4, "NY",2 });
state.Rows.Add(new object[] { 5, "MEXICO", 2 });

List<object> MyCustomState = new List<object>();
//for each country add states if any states present
foreach (DataRow item in cntry.Rows)
{
MyCustomState.Add(item["Name"]);
foreach (DataRow sitem in state.Rows.Cast<DataRow>().Where(s => s["CntryID"].Equals(item["CntryID"])))
{
MyCustomState.Add(sitem["Name"]);
}
}

Thanks,
Debata

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

Posted by: Vijayar on: 5/4/2011 [Member] Starter | Points: 25

Up
0
Down
Help me by using query

vijaya

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

Posted by: Nishithraj on: 5/4/2011 [Member] Bronze | Points: 25

Up
0
Down
You can try something like this

sqry = "SELECT a.states+' '+b.country as statecountry FROM state a left outer join country b on a.countryid=b.countryid" 

sqcom = New SqlCommand(sqry, sqcon)
da.SelectCommand = sqcom ds.Clear()
da.Fill(ds)
ddl_adv.DataSource = ds
ddl_adv.DataTextField = "emp_fullname"
ddl_adv.DataValueField = "emp_no"
ddl_adv.DataBind()
sqcon.Close()


Mark this as answer, if it is.....

With regards
Nishithraj Narayanan

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

Posted by: Vijayar on: 5/5/2011 [Member] Starter | Points: 25

Up
0
Down
Thanks alot,i worked fine.But When i click on save button ststeid should be saves i state table,country id in country table .please tell me how to do this

vijaya

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

Posted by: Vijayar on: 5/5/2011 [Member] Starter | Points: 25

Up
0
Down
hanks alot,i worked fine.But When i click on save button statee id should be saved in state table,country id in country table .please tell me how to do this


vijaya

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

Login to post response