Connectivity of Dropdown lists with two tables of SQL

Question

0.00/5 (No votes)

See more:

Ques: There are two dropdown lists in my page and both are to be binded with two separate Tables in sql. But there is a problem that, the selected value of dropdownlist1 will filter the contents of dropdoenlist2.

I.e For example, if one dropdownlist(state) is having names of states, and state id is auto generated while inserting records in table, on the basis of this record id, which is integer type, how will i fill dropdownlist2(cities) filtered by the stateID present in state table and city table.

Posted 10-May-12 2:49am

Himanshu Sharma (Gaurav Sharma)

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Sandeep Mewara · Accepted Answer · 2012-05-10T03:06:00

Solution 1

You are trying to have Cascading dropdown. Now, since you have just tagged it as C#, we assume you want this in Winforms and not in ASP.NET. (Even though you use dropddownlist word!)

Steps,
1. Bind your dropdown1 to datatable fetched from DB.
Ex:

SQL

SELECT StateID, StateName FROM MstStates

2. Have dropdown change event defined for dropdown1
3. In this selectedindexchange event, get the selected state ID and use this as a filter for getting data for dropdown 2
Ex:

SQL

SELECT CityID, CityName FROM MstCity WHERE RelatedStateID = 5

4. Bind this query data to your dropdown2

This will bind the two drodowns with two SQL tables and also have the filter dependent criteria you want.

Posted 10-May-12 3:06am

Sandeep Mewara

Comments

Himanshu Sharma (Gaurav Sharma) 11-May-12 1:30am

Hi Thanks for this helpful answer.
but the selected value in dropdownlist1 is in integer type in table.. but when i fetch it in dropdownlist1 it gives an error.
that "the input string is not in correct format."

This is my code... for aspx

Collapse | Copy Code

<tr>
<td width="33%" align="left">
HOTEL
<asp:DropDownList ID="HotelName" runat="server" AutoPostBack="True"
DataSourceID="HotelRoom" DataTextField="HotelName" DataValueField="HotelId">

<asp:SqlDataSource ID="HotelRoom" runat="server"
ConnectionString="<%$ ConnectionStrings:HMSConnectionString %>"
SelectCommand="SELECT [HotelId], [HotelName] FROM [tblHotel]">

</td>
<td width="33%" align="left">
ROOM TYPE
<asp:DropDownList ID="RoomType" runat="server"
DataSourceID="HotelRoomType" DataTextField="RoomTypeName"
DataValueField="RoomTypeId">
<asp:SqlDataSource ID="HotelRoomType" runat="server"
ConnectionString="<%$ ConnectionStrings:HMSROOMTYPES %>"
SelectCommand="SELECT [RoomTypeId],[RoomTypeName] FROM [tblRoomType] WHERE [HotelId]=1" >

</td>

The question is that, on the basis of selected hotel, how will i fill room types..

HotelID(AutoGenerated) HotelName Description
1 5Star Hotel KrolBagh
2 4STAR Hotel Kirti Nagar
3 3Star Hotel Rajori Garden
4 2Star Hotel tagore Garden

RoomTypeID(AutoGeneradted) HotelId RoomTypeName
1 2 Brownz
2 2 Gold
3 2 Luxary
4 2 Silver
5 1 Luxary
6 4 Gold
7 4 Brownz
8 4 Silver
9 3 Ordinary

Sandeep Mewara 11-May-12 2:01am

You need to populate the second dropdown based on first dropdown selected value in it's selected change index. You cannot directly populate both of them.

By default if you want to put some value then above looks fine, see if hotelID is expected as "1" instead of just 1.

AshishChaudha · Accepted Answer · 2012-05-10T04:26:00

Solution 2

C#

string queryState="select stateid,statename from statemaster";

//write code for filling dataset     

       ddlstate.DataSource = dsState;
       ddlstate.DataTextField = statename ;
       ddlstate.DataValueField = stateid;
       ddlstate.DataBind();
       ddlstate.Items.Insert(0, "Select..");
       ds.Clear();

//remember for

C#

autopostback=true

of dropdown of state

XML

string queryCity="select cityid,cityname from citymaster where stateid=" + ddlstate.selectedvalue;

//write code for filling dataset dsCity

       ddlCity.DataSource = ds;
       ddlCity.DataTextField = dataTextField;
       ddlCity.DataValueField = dataValueField;
       ddlCity.DataBind();
       ddlCity.Items.Insert(0, "Select..");
       dsCity.Clear();

hope you can do now...i had given somewhat better hints to you..

make it answer if you got it right...

Posted 10-May-12 4:26am

AshishChaudha

Comments

Himanshu Sharma (Gaurav Sharma) 11-May-12 1:11am

Hi Thanks for this helpful answer.
but the selected value in dropdownlist1 is in integer type in table.. but when i fetch it in dropdownlist1 it gives an error.
that "the input string is not in correct format."

This is my code... for aspx

<tr>
<td width="33%" align="left">
HOTEL
<asp:DropDownList ID="HotelName" runat="server" AutoPostBack="True"
DataSourceID="HotelRoom" DataTextField="HotelName" DataValueField="HotelId">

<asp:SqlDataSource ID="HotelRoom" runat="server"
ConnectionString="<%$ ConnectionStrings:HMSConnectionString %>"
SelectCommand="SELECT [HotelId], [HotelName] FROM [tblHotel]">

</td>
<td width="33%" align="left">
ROOM TYPE
<asp:DropDownList ID="RoomType" runat="server"
DataSourceID="HotelRoomType" DataTextField="RoomTypeName"
DataValueField="RoomTypeId">
<asp:SqlDataSource ID="HotelRoomType" runat="server"
ConnectionString="<%$ ConnectionStrings:HMSROOMTYPES %>"
SelectCommand="SELECT [RoomTypeId],[RoomTypeName] FROM [tblRoomType] WHERE [HotelId]=1" >

</td>

The question is that, on the basis of selected hotel, how will i fill room types..

HotelID(AutoGenerated) HotelName Description
1 5Star Hotel KrolBagh
2 4STAR Hotel Kirti Nagar
3 3Star Hotel Rajori Garden
4 2Star Hotel tagore Garden

RoomTypeID(AutoGeneradted) HotelId RoomTypeName
1 2 Brownz
2 2 Gold
3 2 Luxary
4 2 Silver
5 1 Luxary
6 4 Gold
7 4 Brownz
8 4 Silver
9 3 Ordinary

AshishChaudha 11-May-12 2:23am

himanshu, binding controls directly is not a good practice, so avoid using it.
while adding through datasource you have to pass the selectedvalue of the control to the datasource you are using in room type..

Himanshu Sharma (Gaurav Sharma) 11-May-12 4:59am

Thanks sir..... Your code helped me a lot... Thanku very much

AshishChaudha 11-May-12 5:01am

why dont you rate if you got the answer and it satisfies your needs...