[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 349
  • Last Modified:

Populate Drop down based on query

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
jaypappas
Asked:
jaypappas
1 Solution
 
anarki_jimbelCommented:
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
 
KentMarshCommented:
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
Independent Software Vendors: 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!

 
KentMarshCommented:

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
 
KentMarshCommented:

Web Form Showing the example above.

Sample.png
0
 
jaypappasAuthor Commented:
<%@ 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
 
Kyle AbrahamsSenior .Net DeveloperCommented:

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
 
KentMarshCommented:
Test
0
 
KentMarshCommented:
I put the solution in the attached file.

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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now