Solved

Populate Drop down based on query

Posted on 2011-02-10
9
341 Views
Last Modified: 2012-06-27
Where can i find a C# example of dynamically populating drop down list based on query.  
company     project number
ATS                           301670        
ATS                           302034        
ATS                           302086        

ANDREWS INTL           800865B        
ANDREWS INTL            800865C        
ANDREWS INTL            800865D        
   


So If I choose   company "ats" only "301670, 302034,302086" will show dynamically in a drop down list

And if I choose compny  "ANDREWS INTL" only  " 800865D,800865C , 800865D"  will show dynamically in a drop down list

thanks                    
0
Comment
Question by:jaypappas
9 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 34865231
0
 
LVL 30

Expert Comment

by:anarki_jimbel
ID: 34865470
I put together a simple example how you may dynamically show results.

I'm not using a database - I just create a datatable manually. You get it from a database. That's the only difference. The rest is simple. One button sets one result for a combobox, another button  - another table.

Ask questions if not clear.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace MyTest
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            DataTable dt = new DataTable();
            DataColumn col1 = new DataColumn("company");
            DataColumn col2 = new DataColumn("project number");
            dt.Columns.Add(col1);
            dt.Columns.Add(col2);
            DataRow dr1 = dt.NewRow();
            DataRow dr2 = dt.NewRow();
            DataRow dr3 = dt.NewRow();
            dr1[col1] = "ATS";
            dr1[col2] = "301670";
            dr2[col1] = "ATS";
            dr2[col2] = "302034";
            dr3[col1] = "ATS";
            dr3[col2] = "302086";
            dt.Rows.Add(dr1);
            dt.Rows.Add(dr2);
            dt.Rows.Add(dr3);

            this.comboBox1.DataSource = dt;
            this.comboBox1.DisplayMember = "project number";
        }

        private void button2_Click(object sender, EventArgs e)
        {
            DataTable dt = new DataTable();
            DataColumn col1 = new DataColumn("company");
            DataColumn col2 = new DataColumn("project number");
            dt.Columns.Add(col1);
            dt.Columns.Add(col2);
            DataRow dr1 = dt.NewRow();
            DataRow dr2 = dt.NewRow();
            DataRow dr3 = dt.NewRow();
            dr1[col1] = "ANDREWS INTL ";
            dr1[col2] = "800865B";
            dr2[col1] = "ANDREWS INTL ";
            dr2[col2] = " 800865C";
            dr3[col1] = "ANDREWS INTL ";
            dr3[col2] = " 800865D";
            dt.Rows.Add(dr1);
            dt.Rows.Add(dr2);
            dt.Rows.Add(dr3);

            this.comboBox1.DataSource = dt;
            this.comboBox1.DisplayMember = "project number";
        }
    }
}

Open in new window

0
 
LVL 2

Expert Comment

by:KentMarsh
ID: 34873433
To dynamically populate a dropdown list based on a query do the following.

1. You will need a way to determine the Company Name (or companyID) that is going to drive the query. The company value could come from the end user, a session value, or whatever. Once you have that value then you can do step 2.
2. Create a method to load the DropDownListBox. Lets name things as follows:
The dropdown list box - CompanyProjectsDropDown

The load list box data Method - LoadCompanyProjects()

In the LoadCompanyProjects() method you will select data based on the company name.

Here is a sample table
CREATE TABLE [dbo].[CompanyProjects](
	[Company] [varchar](50) NULL,
	[ProjectNumber] [varchar](50) NULL
) ON [PRIMARY]

Open in new window


Sample Data
INSERT INTO CompanyProjects	(Company, ProjectNumber) VALUES	('ATS', '301670');
INSERT INTO CompanyProjects	(Company, ProjectNumber) VALUES	('ATS', '302034');         
INSERT INTO CompanyProjects	(Company, ProjectNumber) VALUES	('ATS', '302086');         
INSERT INTO CompanyProjects	(Company, ProjectNumber) VALUES	('ANDREWS INTL', '800865B');
INSERT INTO CompanyProjects	(Company, ProjectNumber) VALUES	('ANDREWS INTL', '800865C');        
INSERT INTO CompanyProjects	(Company, ProjectNumber) VALUES	('ANDREWS INTL', '800865D');     

Open in new window


This example uses ASP.NET, ADO.NET and C#. It also shows an alternative approach using the SQLDataSource object.

WEB PAGE SCRIPT

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="SampleDropDown.aspx.cs" Inherits="SamplesWebApplication.SampleDropDown" %>

<!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>
        <h2>Programatic Approach</h2>
        <br />
        Company Names:
    <asp:dropdownlist runat="server" ID="CompanyNames" AutoPostBack="True" 
            onselectedindexchanged="CompanyNames_SelectedIndexChanged">
        <asp:ListItem Selected="True">ATS</asp:ListItem>
        <asp:ListItem>ANDREWS INTL</asp:ListItem>
        </asp:dropdownlist>
        <br />
        <br />
        <br />
        Company Projects:
        <asp:DropDownList ID="CompanyProjectsDropDown" runat="server">
        </asp:DropDownList>
    </div>
    <div title="RAD Approach">
        <h2>RAD Approach</h2>
        <br />
        Company Names:
        <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" 
            DataSourceID="SqlDataSource1" DataTextField="Company" DataValueField="Company">
        </asp:DropDownList>
        <br />
        <br />
        Comany Projects: 
        <asp:DropDownList ID="DropDownList2" runat="server" 
            DataSourceID="SqlDataSource2" DataTextField="ProjectNumber" 
            DataValueField="ProjectNumber">
        </asp:DropDownList>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:MyConnectionString %>" 
            SelectCommand="SELECT DISTINCT Company FROM CompanyProjects ORDER BY Company">
        </asp:SqlDataSource>
        <asp:SqlDataSource ID="SqlDataSource2" runat="server" 
            ConnectionString="<%$ ConnectionStrings:MyConnectionString %>" 
            SelectCommand="SELECT ProjectNumber FROM CompanyProjects WHERE (Company = @CompanyName) ORDER BY ProjectNumber">
            <SelectParameters>
                <asp:ControlParameter ControlID="DropDownList1" DefaultValue="ATS" 
                    Name="CompanyName" PropertyName="SelectedValue" />
            </SelectParameters>
        </asp:SqlDataSource>
    </div>
    </form>
</body>
</html>

Open in new window


Code Behind

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;

namespace SamplesWebApplication
{
    public partial class SampleDropDown : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                if (CompanyNames.Items.Count > 0)
                {
                    CompanyNames.SelectedIndex = 0;
                    string companyName = CompanyNames.SelectedValue;
                    LoadCompanyProjects(companyName);
                }
            }
        }

        protected void CompanyNames_SelectedIndexChanged(object sender, EventArgs e)
        {
            string companyName = CompanyNames.SelectedValue;
            LoadCompanyProjects(companyName);
        }

        protected void LoadCompanyProjects(string companyName)
        {
            List<CompanyProject> items = CompanyBusinessObject.SelectProjects(companyName);
            this.CompanyProjectsDropDown.Items.Clear();
            foreach (CompanyProject item in items)
            {
                ListItem listItem = new ListItem();
                listItem.Text = item.ProjectNumber;
                listItem.Value = item.ProjectNumber;
                this.CompanyProjectsDropDown.Items.Add(listItem);
            }

        }
    }

    public class CompanyProject
    {
        public string CompanyName { get; set; }
        public string ProjectNumber { get; set; }
    }

    public class CompanyBusinessObject
    {
        static public List<CompanyProject> SelectProjects(string companyName)
        {
            List<CompanyProject> items = new List<CompanyProject>();

            string connStr = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
            SqlConnection conn = new SqlConnection(connStr);
            SqlDataReader reader = null;
            try
            {
                conn.Open();
                string sql = "SELECT ProjectNumber FROM CompanyProjects WHERE (Company = @CompanyName) ORDER BY ProjectNumber";
                SqlCommand cmd = new SqlCommand(sql, conn);

                SqlParameter param = new SqlParameter();
                param.ParameterName = "@CompanyName";
                param.Value = companyName;
                cmd.Parameters.Add(param);

                reader = cmd.ExecuteReader();

                // write each record
                while (reader.Read())
                {
                    CompanyProject item = new CompanyProject();
                    item.CompanyName = companyName;
                    item.ProjectNumber = reader[0].ToString();

                    items.Add(item);
                }
            }
            catch (SqlException sqlEx)
            {
                // Handle exceptions
            }
            finally
            {
                conn.Close();
            }

            return items;
        }
    }
}

Open in new window



Comment

Notice that the RAD approach doesn't require any C# code. This is great for quick and dirty and mock-ups. But I don't recommend this for demanding Corporate applications.
The programmatic approach can be extended further so that you have a group of classes for business objects and entities. Going further, you may want to consider using the Entity Framework.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Expert Comment

by:KentMarsh
ID: 34873512

Further Comment

I neglected to include the web.config that is referenced in the above example. The sample above assumes that the configuration file "web.config" contains the connection string needed to connect to the dbms. The appSettings area contains a connectionStrings area that has our connectionstring. Note that I'm using Integrated Security. This is a good way because you don't want to have to manage passwords in a configuration file.
    <appSettings />
    <connectionStrings>
        <add name="MyConnectionString" connectionString="Data Source=MyServer;Initial Catalog=SampleDB;Integrated Security=True"
            providerName="System.Data.SqlClient" />
    </connectionStrings>

Open in new window

0
 
LVL 2

Expert Comment

by:KentMarsh
ID: 34874511

Web Form Showing the example above.

Sample.png
0
 

Author Comment

by:jaypappas
ID: 34881313
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Register assembly="System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" namespace="System.Web.UI.WebControls" tagprefix="asp" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<script runat="server">
  
    
    
    protected void Page_Load(object sender, EventArgs e)
  {
      using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDbConn"].ToString()))
      {
          SqlCommand cmd = new SqlCommand("SELECT company, CUSTNAME, CUSTNMBR, PAPROJNUMBER, PADocnumber20, patotbillings, BACHNUMB, cycle2, cycle1 FROM OPENINVOICELOOK", cn);
          cn.Open();
          SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
          rdr.Read();
          Response.Write(rdr[0].ToString()); //read a value

          MyDbConn1.Visible = false;
          if (drop1.Items.FindByValue("all") != null)
          {
              //Message1.Text = "found";
              drop1.AppendDataBoundItems.Equals("false");
          }


          if (DropDownList2.Items.FindByValue("all") != null)
          {
              //Message1.Text = "found";
              DropDownList2.AppendDataBoundItems.Equals("false");
              drop3.AppendDataBoundItems.Equals("true");
          }
          if (drop3.Items.FindByValue("all") != null)
          {
              //Message1.Text = "found";
              drop3.AppendDataBoundItems.Equals("false");
          } 
          
          

      } 
  }

    


    

    protected void Unnamed1_Click1(object sender, EventArgs e)
    {
        
        if (drop1.SelectedValue == "all") 
        {
            MyDbConn2.Visible = true;
        }
            else {
               
            MyDbConn2.Visible = false;
            MyDbConn1.Visible = true;
        }

        
    }

    protected void drop3_SelectedIndexChanged(object sender, EventArgs e)
    {
      


    }



    protected void DropDownList2_SelectedIndexChanged(object sender, EventArgs e)
    {
        drop3.AppendDataBoundItems.Equals("false");  
    }
</script>
<html xmlSQL Authentication</title>
    <style type="text/css">
body
{
background-color:white;
}
h1
{
background-color:#00ff00;
}
p
{
background-color:rgb(255,0,255);
}
</style>
      <asp:label id="Message" runat ="server"/><br />

      <asp:label id="Message1" runat ="server"/><br />

      <asp:label id="Message2" runat ="server"/><br />

      <asp:label id="Message3" runat ="server"/>
    <br />

     <form id="form1" runat="server" method="post">
     
     <asp:Image ID="Image1" runat="server" ImageUrl="image1.gif" BackColor="#66CCFF" 
          Height="145px" Width="287px" />

    <br />
    <br />

      <asp:Button Text="Submit" runat="server" onclick="Unnamed1_Click1" 
         id="button1" CausesValidation="False" />

     



     <br />

     <asp:SqlDataSource ID="SqlDataSource3" runat="server" 
         ConnectionString="<%$ ConnectionStrings:ConnectionString3 %>" 
         ProviderName="<%$ ConnectionStrings:ConnectionString3.ProviderName %>" 
         
         SelectCommand="SELECT distinct [Company] FROM [OPENINVOICELOOK]">
       

     </asp:SqlDataSource>



     <br />
     <asp:DropDownList id="drop1" runat="server" DataSourceID="SqlDataSource3" 
         DataTextField="company" DataValueField="company" 
         AppendDataBoundItems="true" AutoPostBack="true"  
        >
         <asp:ListItem>all</asp:ListItem>
  
</asp:DropDownList>

  



     <br />
     <br />



This works perfectly except I need to add an "all" value for drop3


     <asp:DropDownList ID="DropDownList2" runat="server" AutoPostBack="True" 
         DataSourceID="SqlDataSource2" DataTextField="bachnumb" 
         DataValueField="bachnumb"  AppendDataBoundItems="true" onselectedindexchanged="DropDownList2_SelectedIndexChanged"
         
         >
             <asp:ListItem>all</asp:ListItem>
     </asp:DropDownList>



     <asp:SqlDataSource ID="SqlDataSource2" runat="server" 
         ConnectionString="<%$ ConnectionStrings:ConnectionString3 %>" 
         ProviderName="<%$ ConnectionStrings:ConnectionString3.ProviderName %>" 
         
         SelectCommand="SELECT distinct [bachnumb] FROM [OPENINVOICELOOK] WHERE ([Company] = ?)" 
         >
         <SelectParameters>
             <asp:ControlParameter ControlID="drop1" Name="Company" 
                 PropertyName="SelectedValue" Type="String" />
         </SelectParameters>
     </asp:SqlDataSource>
    
     [b]<asp:DropDownList ID="drop3" runat="server" AutoPostBack="false" 
         DataSourceID="SqlDataSource1" DataTextField="PAPROJNUMBER" 
         DataValueField="PAPROJNUMBER"  AppendDataBoundItems="false">
          <asp:ListItem>all</asp:ListItem>[/b]         
    
     </asp:DropDownList>


     <br />
     <br />
     <br />
     <br />
     <br />
     <br />
     <br />

     <asp:GridView ID="MyDbConn1" runat="server" AutoGenerateColumns="False" 
        DataSourceID="MyDbConn" EnableModelValidation="True" Visible="False">
        <Columns>
            <asp:BoundField DataField="Company" HeaderText="Company" 
                SortExpression="Company" />
            <asp:BoundField DataField="CUSTNAME" HeaderText="CUSTNAME" 
                SortExpression="CUSTNAME" />
            <asp:BoundField DataField="CUSTNMBR" HeaderText="CUSTNMBR" 
                SortExpression="CUSTNMBR" />
            <asp:BoundField DataField="PAPROJNUMBER" HeaderText="PAPROJNUMBER" 
                SortExpression="PAPROJNUMBER" />
            <asp:BoundField DataField="PADocnumber20" HeaderText="PADocnumber20" 
                SortExpression="PADocnumber20" />
            <asp:BoundField DataField="patotbillings" HeaderText="patotbillings" 
                SortExpression="patotbillings" />
            <asp:BoundField DataField="BACHNUMB" HeaderText="BACHNUMB" 
                SortExpression="BACHNUMB" />
            <asp:BoundField DataField="cycle1" HeaderText="cycle1" 
                SortExpression="cycle1" />
            <asp:BoundField DataField="cycle2" HeaderText="cycle2" 
                SortExpression="cycle2" />
              

        </Columns>
    </asp:GridView>
     <asp:SqlDataSource ID="MyDbConn" runat="server" 
        ConnectionString="<%$ ConnectionStrings:MyDbConn %>" 
        
         
         
         
         
          SelectCommand="SELECT * FROM [OPENINVOICELOOK] WHERE  (([Company] = @Company) AND ([bachnumb] = @bachnumb)AND ([PAPROJNUMBER] = @PAPROJNUMBER))" 
          ConflictDetection="CompareAllValues">
         <SelectParameters>
           
             <asp:ControlParameter ControlID="drop1" Name="Company" 
                 PropertyName="SelectedValue" Type="String" />
             <asp:ControlParameter ControlID="DropDownList2" Name="bachnumb" 
                 PropertyName="SelectedValue" Type="String" />
         
       <asp:ControlParameter ControlID="drop3" Name="PAPROJNUMBER" 
                 PropertyName="SelectedValue" Type="String" />
         </SelectParameters>

    </asp:SqlDataSource>
    

      <asp:SqlDataSource ID="drop3_proj" runat="server" 
          ConnectionString="Provider=SQLOLEDB;Data Source=GP-SERVER;Password=password;User ID=user;Initial Catalog=bizfact" 
          ProviderName="System.Data.OleDb" 
          SelectCommand="SELECT distinct [PAPROJNUMBER] FROM [OPENINVOICELOOK] WHERE (([Company] = ?) AND ([BACHNUMB] = ?))">
          <SelectParameters>
              <asp:ControlParameter ControlID="drop1" Name="Company" 
                  PropertyName="SelectedValue" Type="String" />
              <asp:ControlParameter ControlID="DropDownList2" Name="BACHNUMB" 
                  PropertyName="SelectedValue" Type="String" />

          </SelectParameters>
      </asp:SqlDataSource>
      <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
          
          ConnectionString="<%$ ConnectionStrings:ConnectionString5 %>" 
          ProviderName="<%$ ConnectionStrings:ConnectionString5.ProviderName %>" 
          SelectCommand="SELECT [PAPROJNUMBER] FROM [OPENINVOICELOOK] WHERE (([Company] = ?) AND ([BACHNUMB] = ?))">
          <SelectParameters>
              <asp:ControlParameter ControlID="drop1" Name="Company" 
                  PropertyName="SelectedValue" Type="String" />
              <asp:ControlParameter ControlID="DropDownList2" Name="BACHNUMB" 
                  PropertyName="SelectedValue" Type="String" />
          </SelectParameters>
      </asp:SqlDataSource>


</head>

     <asp:GridView ID="MyDbConn2" runat="server" AutoGenerateColumns="False" 
        DataSourceID="all_data" EnableModelValidation="True" Visible="False">
        <Columns>
            <asp:BoundField DataField="Company" HeaderText="Company" 
                SortExpression="Company" ReadOnly="True" />
            <asp:BoundField DataField="CUSTNAME" HeaderText="CUSTNAME" 
                SortExpression="CUSTNAME" ReadOnly="True" />
            <asp:BoundField DataField="CUSTNMBR" HeaderText="CUSTNMBR" 
                SortExpression="CUSTNMBR" ReadOnly="True" />
            <asp:BoundField DataField="PAPROJNUMBER" HeaderText="PAPROJNUMBER" 
                SortExpression="PAPROJNUMBER" ReadOnly="True" />
            <asp:BoundField DataField="PADocnumber20" HeaderText="PADocnumber20" 
                SortExpression="PADocnumber20" ReadOnly="True" />
            <asp:BoundField DataField="patotbillings" HeaderText="patotbillings" 
                SortExpression="patotbillings" ReadOnly="True" />
            <asp:BoundField DataField="BACHNUMB" HeaderText="BACHNUMB" 
                SortExpression="BACHNUMB" ReadOnly="True" />
            <asp:BoundField DataField="cycle1" HeaderText="cycle1" 
                SortExpression="cycle1" ReadOnly="True" />
            <asp:BoundField DataField="cycle2" HeaderText="cycle2" 
                SortExpression="cycle2" ReadOnly="True" />
              

        </Columns>
    </asp:GridView>


     <asp:SqlDataSource ID="all_data" runat="server" 
         ConnectionString="<%$ ConnectionStrings:all_data %>" 
         ProviderName="<%$ ConnectionStrings:all_data.ProviderName %>" 
         SelectCommand="SELECT * FROM [OPENINVOICELOOK]"></asp:SqlDataSource>


</form>



<body/>
</html>

Open in new window


this works except I need an "all" value on DropDownList called drop3 with appending the data.  I need it show up one time in my drop down.  If I set AppendDataBoundItems= "true" it makes my list longer after I click on
DropDownList2

 
     <asp:DropDownList ID="drop3" runat="server" AutoPostBack="false"
         DataSourceID="SqlDataSource1" DataTextField="PAPROJNUMBER"
         DataValueField="PAPROJNUMBER"  AppendDataBoundItems="false">
          <asp:ListItem>all</asp:ListItem>
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 34889653

I would not use DataSources . . . I would use the datareader and do it from the codebehind.

From the other example:


 protected void LoadCompanyProjects(string companyName)
        {
            List<CompanyProject> items = CompanyBusinessObject.SelectProjects(companyName);
            this.CompanyProjectsDropDown.Items.Clear();
            foreach (CompanyProject item in items)
            {
                ListItem listItem = new ListItem();
                listItem.Text = item.ProjectNumber;
                listItem.Value = item.ProjectNumber;
                this.CompanyProjectsDropDown.Items.Add(listItem);
            }
                ListItem listItem = new ListItem();
                listItem.Text = "All";
                listItem.Value = -1;
                this.CompanyProjectsDropDown.Items.InsertAt(listItem,0); //make it the first item.

                     
        }
0
 
LVL 2

Expert Comment

by:KentMarsh
ID: 34891509
Test
0
 
LVL 2

Accepted Solution

by:
KentMarsh earned 500 total points
ID: 34897204
I put the solution in the attached file.

I included 3 approaches.
Sample-solution-For-DropDown.doc
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Extention Methods in C# 3.0 by Ivo Stoykov C# 3.0 offers extension methods. They allow extending existing classes without changing the class's source code or relying on inheritance. These are static methods invoked as instance method. This…
This article describes a simple method to resize a control at runtime.  It includes ready-to-use source code and a complete sample demonstration application.  We'll also talk about C# Extension Methods. Introduction In one of my applications…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question