binding tables with relationes to 2 dropdownlist

[barney]

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
(e-mail address removed)

 

[DotNetJerome]

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

 

[J L]

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

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

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
(e-mail address removed)

 

[barney]

Hi Jerome & John

Thanks for your quick answers.
Jerome said:
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_

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

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

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
(e-mail address removed)