binding tables with relationes to 2 dropdownlist

Discussion in 'ASP .Net Web Controls' started by barney, Mar 4, 2005.

  1. barney

    barney Guest

    Hi,
    I'm trying to get a kind of dependency to 2 dropdownlists in asp.net:

    |Dropdown Vendor| -> 1:n -> |Dropdown Order|

    After selecting a Vendor the 2nd Dropdownbox should only show Orders
    linked to the selected Vendor. ( Both Dropdowns do autopostback )
    But it always shows all values ...
    Maybee someone has a tip for me ...

    I trY:
    if ( ! Page.IsPostBack ) {
    string dsn = "...";
    SqlConnection sql = new SqlConnection( dsn );
    SqlCommand cmd1 = new SqlCommand("select id_vendor, name from
    def_vendor", sqlC);
    SqlCommand cmd2 = new SqlCommand("select id_vendor, id_po, quantity
    from wrk_order", sqlC);
    sqlC.Open();
    SqlDataAdapter da1 = new SqlDataAdapter(cmd1);
    SqlDataAdapter da2 = new SqlDataAdapter(cmd2);
    ds = new DataSet("myds");

    da1.Fill( ds, "vendor" );
    da2.Fill (ds, "po" );

    ds.Relations.Add("map_vendor_po",
    ds.Tables["vendor"].Columns["id_vendor"],
    ds.Tables["po"].Columns["id_vendor"]);
    DropDownList1.DataSource = ds.Tables["vendor"];
    DropDownList1.DataTextField = "name";
    DropDownList1.DataValueField = "id_vendor";
    DropDownList1.DataBind();

    DropDownList2.DataSource = ds.Relations["map_vendor_po"].ChildTable;
    DropDownList2.DataTextField = "id_po";
    DropDownList2.DataValueField = "id_po";
    DropDownList2.DataBind();

    }

    Many thanks in advance
    Tobias
    barney, Mar 4, 2005
    #1
    1. Advertising

  2. barney

    DotNetJerome Guest

    Hi Barney,

    I don't find any dependency between your queries....

    Your second query suppose to be as follows.

    "select id_po,quantity from wrk_order where id_vendor=" +
    DropDownList1.SelectedItem.Value

    Now, when you execute your second query it will get the values from the
    wrk_order table for the selected vendor id avilable in the first dropdown
    list box.

    Cheers,

    Jerome. M

    "barney" wrote:

    > Hi,
    > I'm trying to get a kind of dependency to 2 dropdownlists in asp.net:
    >
    > |Dropdown Vendor| -> 1:n -> |Dropdown Order|
    >
    > After selecting a Vendor the 2nd Dropdownbox should only show Orders
    > linked to the selected Vendor. ( Both Dropdowns do autopostback )
    > But it always shows all values ...
    > Maybee someone has a tip for me ...
    >
    > I trY:
    > if ( ! Page.IsPostBack ) {
    > string dsn = "...";
    > SqlConnection sql = new SqlConnection( dsn );
    > SqlCommand cmd1 = new SqlCommand("select id_vendor, name from
    > def_vendor", sqlC);
    > SqlCommand cmd2 = new SqlCommand("select id_vendor, id_po, quantity
    > from wrk_order", sqlC);
    > sqlC.Open();
    > SqlDataAdapter da1 = new SqlDataAdapter(cmd1);
    > SqlDataAdapter da2 = new SqlDataAdapter(cmd2);
    > ds = new DataSet("myds");
    >
    > da1.Fill( ds, "vendor" );
    > da2.Fill (ds, "po" );
    >
    > ds.Relations.Add("map_vendor_po",
    > ds.Tables["vendor"].Columns["id_vendor"],
    > ds.Tables["po"].Columns["id_vendor"]);
    > DropDownList1.DataSource = ds.Tables["vendor"];
    > DropDownList1.DataTextField = "name";
    > DropDownList1.DataValueField = "id_vendor";
    > DropDownList1.DataBind();
    >
    > DropDownList2.DataSource = ds.Relations["map_vendor_po"].ChildTable;
    > DropDownList2.DataTextField = "id_po";
    > DropDownList2.DataValueField = "id_po";
    > DropDownList2.DataBind();
    >
    > }
    >
    > Many thanks in advance
    > Tobias
    >
    >
    DotNetJerome, Mar 4, 2005
    #2
    1. Advertising

  3. barney

    J L Guest

    Hi Barney and Jerome,
    I am a newbie to ADO.Net and VB.Net. The idea of binding two dropdown
    lists as you discuss is very interesting to me. My question, once the
    dataadapters and binding are set as shown, do you need to add any code
    to the event of the parent dropdown list or does a change in its
    selected value automatically cause the second list to update?

    TIA,
    John
    On Fri, 4 Mar 2005 04:31:02 -0800, DotNetJerome
    <reachjerome@_yahoo.com-remove-the-underscore-after@> wrote:

    >Hi Barney,
    >
    >I don't find any dependency between your queries....
    >
    >Your second query suppose to be as follows.
    >
    >"select id_po,quantity from wrk_order where id_vendor=" +
    >DropDownList1.SelectedItem.Value
    >
    >Now, when you execute your second query it will get the values from the
    >wrk_order table for the selected vendor id avilable in the first dropdown
    >list box.
    >
    >Cheers,
    >
    >Jerome. M
    >
    >"barney" wrote:
    >
    >> Hi,
    >> I'm trying to get a kind of dependency to 2 dropdownlists in asp.net:
    >>
    >> |Dropdown Vendor| -> 1:n -> |Dropdown Order|
    >>
    >> After selecting a Vendor the 2nd Dropdownbox should only show Orders
    >> linked to the selected Vendor. ( Both Dropdowns do autopostback )
    >> But it always shows all values ...
    >> Maybee someone has a tip for me ...
    >>
    >> I trY:
    >> if ( ! Page.IsPostBack ) {
    >> string dsn = "...";
    >> SqlConnection sql = new SqlConnection( dsn );
    >> SqlCommand cmd1 = new SqlCommand("select id_vendor, name from
    >> def_vendor", sqlC);
    >> SqlCommand cmd2 = new SqlCommand("select id_vendor, id_po, quantity
    >> from wrk_order", sqlC);
    >> sqlC.Open();
    >> SqlDataAdapter da1 = new SqlDataAdapter(cmd1);
    >> SqlDataAdapter da2 = new SqlDataAdapter(cmd2);
    >> ds = new DataSet("myds");
    >>
    >> da1.Fill( ds, "vendor" );
    >> da2.Fill (ds, "po" );
    >>
    >> ds.Relations.Add("map_vendor_po",
    >> ds.Tables["vendor"].Columns["id_vendor"],
    >> ds.Tables["po"].Columns["id_vendor"]);
    >> DropDownList1.DataSource = ds.Tables["vendor"];
    >> DropDownList1.DataTextField = "name";
    >> DropDownList1.DataValueField = "id_vendor";
    >> DropDownList1.DataBind();
    >>
    >> DropDownList2.DataSource = ds.Relations["map_vendor_po"].ChildTable;
    >> DropDownList2.DataTextField = "id_po";
    >> DropDownList2.DataValueField = "id_po";
    >> DropDownList2.DataBind();
    >>
    >> }
    >>
    >> Many thanks in advance
    >> Tobias
    >>
    >>
    J L, Mar 4, 2005
    #3
  4. barney

    barney Guest

    Hi Jerome & John

    Thanks for your quick answers.
    Jerome said:
    > >I don't find any dependency between your queries....


    I tried to do the dependencies through the Relation of the dataset. In
    Windows Programming with ADO & C# this works quite well.
    In ASPX I think I will do it like you showed me...

    Example Windows Programming:
    // ADD Dependency ( Relation ) to Comboboxes:
    // -> _ds is my Dataset with 2 Tables
    _ds.Relations.Add("trosymptom1_trosymptom2",
    _ds.Tables["trosymptom1"].Columns["key1"],
    _ds.Tables["trosymptom2"].Columns["key1"]);

    cboSymptom1Tech.DataSource = _ds;
    cboSymptom1Tech.DisplayMember = "trosymptom1.text";
    cboSymptom1Tech.ValueMember = "trosymptom1.key1";

    // If you pass the relation here your 2nd combobox will only show
    // the entries matching Combobox 1
    cboSymptom2Tech.DataSource = _ds;
    // Syntax: ParentTable -> Relation -> columnname of childetable
    cboSymptom2Tech.DisplayMember =
    "trosymptom1.trosymptom1_trosymptom2.text";
    cboSymptom2Tech.ValueMember =
    "trosymptom1.trosymptom1_trosymptom2.key2";

    I'm no experienced Webprogrammer and hoped that this would work also
    with DropDownBoxes, cause I like this feature very much in Windows
    Programming ...

    regards & thanks

    Tobias

    b-gumble@_nospam_gmx.net remove the _nospam_

    J L <> wrote in message news:<>...
    > Hi Barney and Jerome,
    > I am a newbie to ADO.Net and VB.Net. The idea of binding two dropdown
    > lists as you discuss is very interesting to me. My question, once the
    > dataadapters and binding are set as shown, do you need to add any code
    > to the event of the parent dropdown list or does a change in its
    > selected value automatically cause the second list to update?
    >
    > TIA,
    > John
    > On Fri, 4 Mar 2005 04:31:02 -0800, DotNetJerome
    > <reachjerome@_yahoo.com-remove-the-underscore-after@> wrote:
    >
    > >Hi Barney,
    > >
    > >I don't find any dependency between your queries....
    > >
    > >Your second query suppose to be as follows.
    > >
    > >"select id_po,quantity from wrk_order where id_vendor=" +
    > >DropDownList1.SelectedItem.Value
    > >
    > >Now, when you execute your second query it will get the values from the
    > >wrk_order table for the selected vendor id avilable in the first dropdown
    > >list box.
    > >
    > >Cheers,
    > >
    > >Jerome. M
    > >
    > >"barney" wrote:
    > >
    > >> Hi,
    > >> I'm trying to get a kind of dependency to 2 dropdownlists in asp.net:
    > >>
    > >> |Dropdown Vendor| -> 1:n -> |Dropdown Order|
    > >>
    > >> After selecting a Vendor the 2nd Dropdownbox should only show Orders
    > >> linked to the selected Vendor. ( Both Dropdowns do autopostback )
    > >> But it always shows all values ...
    > >> Maybee someone has a tip for me ...
    > >>
    > >> I trY:
    > >> if ( ! Page.IsPostBack ) {
    > >> string dsn = "...";
    > >> SqlConnection sql = new SqlConnection( dsn );
    > >> SqlCommand cmd1 = new SqlCommand("select id_vendor, name from
    > >> def_vendor", sqlC);
    > >> SqlCommand cmd2 = new SqlCommand("select id_vendor, id_po, quantity
    > >> from wrk_order", sqlC);
    > >> sqlC.Open();
    > >> SqlDataAdapter da1 = new SqlDataAdapter(cmd1);
    > >> SqlDataAdapter da2 = new SqlDataAdapter(cmd2);
    > >> ds = new DataSet("myds");
    > >>
    > >> da1.Fill( ds, "vendor" );
    > >> da2.Fill (ds, "po" );
    > >>
    > >> ds.Relations.Add("map_vendor_po",
    > >> ds.Tables["vendor"].Columns["id_vendor"],
    > >> ds.Tables["po"].Columns["id_vendor"]);
    > >> DropDownList1.DataSource = ds.Tables["vendor"];
    > >> DropDownList1.DataTextField = "name";
    > >> DropDownList1.DataValueField = "id_vendor";
    > >> DropDownList1.DataBind();
    > >>
    > >> DropDownList2.DataSource = ds.Relations["map_vendor_po"].ChildTable;
    > >> DropDownList2.DataTextField = "id_po";
    > >> DropDownList2.DataValueField = "id_po";
    > >> DropDownList2.DataBind();
    > >>
    > >> }
    > >>
    > >> Many thanks in advance
    > >> Tobias
    > >>
    > >>
    barney, Mar 5, 2005
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Jim French
    Replies:
    0
    Views:
    587
    Jim French
    Jul 18, 2003
  2. H5N1
    Replies:
    0
    Views:
    477
  3. Peter Bassett
    Replies:
    3
    Views:
    924
    Augustus
    Aug 15, 2003
  4. Otuatail

    Tables within tables

    Otuatail, Jul 31, 2004, in forum: HTML
    Replies:
    7
    Views:
    494
  5. Chris Brat
    Replies:
    5
    Views:
    694
    =?iso-8859-1?q?Luis_M._Gonz=E1lez?=
    Aug 22, 2006
Loading...

Share This Page