if i have to pupulate a dropdown from another dropdown which are usind data from 2 seprate tables do i have to use join those two tables before i can do that ? also i have a sql-server query...

Question

1.00/5 (1 vote)

See more:

SQL

USE [Ph6Practice]
GO

/****** Object:  Table [dbo].[State]    Script Date: 11/01/2012 23:34:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[State](
    [stateid] [bigint] NOT NULL,
    [stateName] [nvarchar](50) NULL,
    [countryid] [bigint] NULL,
 CONSTRAINT [PK_State] PRIMARY KEY CLUSTERED
(
    [stateid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[State]  WITH CHECK ADD  CONSTRAINT [FK_State_State] FOREIGN KEY([stateid])
REFERENCES [dbo].[State] ([stateid])
GO

ALTER TABLE [dbo].[State] CHECK CONSTRAINT [FK_State_State]
GO

There are two drop down box .
1) One for country
2) One for state

We have to populate country drop down box from country table
and bases on user selection on country drop down the state drop down should be populated.

Posted 1-Nov-12 16:07pm

a2ulthakur

Updated 17-Jan-13 17:55pm

RDBurmon

v2

Add a Solution

Comments

AshishChaudha 2-Nov-12 0:29am

What you are asking, as per my understanding the code snippet is not related to your problem..

a2ulthakur 2-Nov-12 0:32am

ya the code snippet is not related to my problem.. i want to know wat all is specified in this example cause i m making my query on this example only

Minghang 3-Nov-12 12:03pm

you have provided sql script for creating and altering a table...so what are we supposed to tell you with the information you provided?? you want to populate a dropdownlist based on an item selected in another dropdownlist right? what field should populate the second dropdown? if the second dropdown is linked to another table as you say, if it needs to be poplated by a field not present in the table that it is linked to then, yes you need to perform a join between the two related tables...

2 solutions

Solution 1

Hello OP ,

I have modified your question and as per this below is the solution.

Let say you have two table like this
Country {CountryId, CountryName }
State {stateid,stateName,countryid}

Create below procedure for Coutry drop down box

SQL

CREATE PROCEDURE FindAllCountry
BEGIN
  SELECT CountryId ,CountryName FROM Country
END

then execute this proc and populate country drop down box and display countryname and also tag countryid with each country name so that when user does selection then will get country id from tag.

Again create one procedure to populate state drop down box

SQL

CREATE PROCEDURE FindState(@CountryId BIGINT)
BEGIN
  SELECT stateid,stateName FROM State where countryid=@CountryId
END

now execute this proc and populate state drop down box .
Display state name and tag state id

Hope this helps . If yes then accept and vote this answer or revert back with your queries
--RDBurmon

Posted 17-Jan-13 18:03pm

RDBurmon

Updated 17-Jan-13 18:04pm

v2

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)

a2ulthakur · Accepted Answer · 2013-02-12T15:17:00

public void Page_Load(object sender, EventArgs e)
   {


       if (con.State == ConnectionState.Closed)
       {
           con.Open();
       }
       if (Page.IsPostBack == false)
       {
           string query = "select * from project";
           SqlCommand cmd = new SqlCommand(query, con);
           cmd.CommandType = CommandType.Text;
           SqlDataAdapter adp = new SqlDataAdapter(cmd);
           DataSet ds = new DataSet();
           adp.Fill(ds);
           DropDownList1.DataSource = ds;
           DropDownList1.DataValueField = ds.Tables[0].Columns[0].ToString();
           DropDownList1.DataTextField = ds.Tables[0].Columns[1].ToString();
           DropDownList1.DataBind();
           DropDownList1.Items.Insert(0, "--Select--");
           cmd.Dispose();
       }

       con.Close();

   }




   public void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
   {
       if (con.State == ConnectionState.Closed)
       {
           con.Open();
       }

       try
       {
           string query1 = "Select * from yearly where pid =" + DropDownList1.SelectedItem.Value.ToString();
           SqlCommand cmd = new SqlCommand(query1, con);
           DataSet ds = new DataSet();
           SqlDataAdapter adp = new SqlDataAdapter(cmd);
           adp.Fill(ds);
           DropDownList2.DataSource = ds;

           //DropDownList2.DataValueField = ds.Tables[0].Columns[0].ToString();
           DropDownList2.DataTextField = ds.Tables[0].Columns["fyyear"].ToString();

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

           cmd.Dispose();
       }
       catch (Exception ex)
       {
           ex.Message.ToString();
       }

       con.Close();
   }



   public void Button2_Click(object sender, EventArgs e)
   {
       try
       {


           SqlDataAdapter da = new SqlDataAdapter("select pcode,fyyear,date,salary,ta,contigency,nrc,institcharges,others from monthly where pcode=('" + DropDownList1.SelectedItem.ToString() + "' ) AND fyyear=('" + DropDownList2.SelectedItem.ToString() + "')", con);
           DataTable dt = new DataTable();
           da.Fill(dt);
           GridView1.DataSource = dt;
           GridView1.DataBind();

           DropDownList1.SelectedIndex = 0;
           DropDownList2.SelectedIndex = 0;
       }

       catch (Exception ex)
       {
           ex.Message.ToString();
       }


   }