Displaying a 1-many relationship properly

[Frank]

Hi!

I just run into a problem wich some of you might be able to solve
really quickly so i decided to give this group a shot.

In the database a have 2 tables (well, to make it easy here).
1) Question
2) Answer

Each question has multiple answers attached to it. this means that
your will get the following result set.


---------------------------
quesion1 (type 1) -- answer1q1
quesion1 (type 1) -- answer2q1
quesion1 (type 1) -- answer3q1

quesion2 (type 1) -- answer1q2
quesion2 (type 1) -- answer2q2

quesion3 (type 2) -- answer1q3
quesion3 (type 2) -- answer2q3
quesion3 (type 2) -- answer3q3
quesion3 (type 2) -- answer4q3
---------------------------

Now each question has also a 'type'.

The problem:

I would like to display the question,if type 1, in a single line with
behind it a select box with all the answers in it. So display the
question once and put the answers to that question in a selectbox.

I have no idea how many questions or how many answers each question
has...

anyone any idea's ??

 

[Guest]

As far as I know there's no magical way for the UI(ASP.NET) to recognize that you hava hierarchical dataset

Now your Answers table should have a foreign key column from the questions table that tells you which answers belong to which questions

So get two datatables 1 for your questions and 1 for you answers

From the questions table display the questions(choose your own way to display this i.e. HTMLTable or DataList) Then following each question create a dropdownlist dynamically and populate the answers into it based on which question you are on

HTH
Suresh


----- Frank wrote: ----

Hi

I just run into a problem wich some of you might be able to solv
really quickly so i decided to give this group a shot

In the database a have 2 tables (well, to make it easy here).
1) Questio
2) Answe

Each question has multiple answers attached to it. this means tha
your will get the following result set


--------------------------
quesion1 (type 1) -- answer1q
quesion1 (type 1) -- answer2q
quesion1 (type 1) -- answer3q

quesion2 (type 1) -- answer1q
quesion2 (type 1) -- answer2q

quesion3 (type 2) -- answer1q
quesion3 (type 2) -- answer2q
quesion3 (type 2) -- answer3q
quesion3 (type 2) -- answer4q
--------------------------

Now each question has also a 'type'

The problem

I would like to display the question,if type 1, in a single line wit
behind it a select box with all the answers in it. So display th
question once and put the answers to that question in a selectbox

I have no idea how many questions or how many answers each questio
has..

anyone any idea's ?

 

[Bram]

Hi,

just create two datasets and bind them to a listbox, datagrid or some
otherview.
In the first dataset, you load all your questions. On selection changes in
the question grid,
you just update the SELECT statement of the second dataset (the answers). I
guess you
linked the answers to the questions through some primary/foreign keys ?
That's the global scenario.

Greetings,

Bram

 

[Rene]

just create two datasets and bind them to a listbox, datagrid or some
otherview.
In the first dataset, you load all your questions. On selection changes in
the question grid,
you just update the SELECT statement of the second dataset (the answers). I
guess you
linked the answers to the questions through some primary/foreign keys ?
That's the global scenario.

That is also the reason why many database frontends are slow, programming
isn't just attaching a recordset to a grid/listcontrol.

You can use just one dataset and when loading the questions (primary key
data) store the rowindex (or bookmark) of each first row from the primary
key (some grids have a rowdata or rowtag property, a invisible column can
be used too).

When a user selects a row of the questions list, move to the stored row
(from the index/bookmark you stored) and update the second control (movenext
till the primary key changes or EOF reached).

This gives you a faster GUI reducing server balast and network I/O.

Rene