VB-WEB: How to maintain a simple member signup db for a club

[keuler]

Experts: Please advise this newbie (me) as to coding techniques that might answer the following quandry:

The overall object is to allow people in a club (in my case, the YMCA Adventure Guides (AG for short)) to sign up for various club activities. The interface I would like to present is one where the user would select an activity (ie, through a drop-down list), such as "Hiking in the woods", then optionally select a subgroup (circle) of the AG members whose attendance status the user wants to adjust. At this point, a grid would appear that shows:

- One row for each member of the AG (or selected circle).
- One column each for
- No response (default)
- NOT going
- IS going

Each cell in the displayed columns would be a mutually exclusive radio button. The user (such as a circle leader) would go down the list of names and enable the radio button for one of the three options next to each name. (Default would be "No resonse".) When all appropriate buttons are selected, the user would click "Update Headcount", and each user's attendance status would be written to the DB.

Since membership and activities can vary all the time, MY THINKING SO FAR (and I might not be thinking correctly) has been to maintain at least three tables to support the above operation:

ACTIVITY
activity_id
activity_name

MEMBER
member_id
circle_id
member_name

HEADCOUNT
headcount_id
activity_id
(FK) member_id
no_answer (bit)
not_going (bit)
is_going (bit)

The main problem that I'm trying to solve is how to display the interface described when:

- To display the default no_answer radio button, a HEADCOUNT record for the member (for the selected activity) MIGHT NOT EVEN EXIST YET. (Otherwise I'd have to add dummy (member-acitvity) records every time I add a member to the AG or a new activity.)
- When the not_going or is_going radio button is changed between the various options, I must be able to add a HEADCOUNT record for those that don't exist yet for some of the members (ie, those in the original "no response" state), or UPDATE a record that already exists for members who, say, have decided not to go to the activity but now want to go.

On other forums, people have given me high-level suggestions such as using nested datalists or stored procedures. Its also been suggested that I use an outer JOIN to assist with the display of names when no corresponding HEADCOUNT record exists for a member for an activity. But this advise is too high level. If you can provide me with more detailed techniques, I be most grateful!

Thanks.

-Kurt

[Shashi Sadasivan]

Joining of the tables to get the required data all in one go is what I would be loking for.

Select activity.*, member.*, headcount.*
from headcount inner join activity on headcount.activity_id = activity.activity_id
Left outer join member on headcount.member_id = activity.member_id
where headcount.member_id = @Id_of_logged_in Member

this will join headcount with activity, and show all the activites irrespective if the member is a part of it or not.

If the user wants to subscribe to one of those activites, check if the user exists in head count, and insert or update accordingly

[keuler]

Thanks Shashi. The select command will be useful. But what I'm most interested in is how to, as you write "check if the user exists in head count, and insert or update accordingly".

Hmmm. Just thinking about this for a moment: Would you recommend I write a stored procedure run, soon as the user selects the activity and circle, that would create HEADCOUNT records for those users for whom records haven't yet been made, and THEN implement an UDPATE command? (No need for SELECT, I think, since the user is really going into this gridview to make updates.)

Thanks!

-Kurt

Joining of the tables to get the required data all in one go is what I would be loking for.

Select activity.*, member.*, headcount.*
from headcount inner join activity on headcount.activity_id = activity.activity_id
Left outer join member on headcount.member_id = activity.member_id
where headcount.member_id = @Id_of_logged_in Member

this will join headcount with activity, and show all the activites irrespective if the member is a part of it or not.

If the user wants to subscribe to one of those activites, check if the user exists in head count, and insert or update accordingly

[Shashi Sadasivan]

To update the selections, when a user submits the form,
foreach activity from the dataset, retrieve the record for that activity from headcount for that user.

if there are no rows, and the user selected to join it, then you need to insert a new record, else do nothing

if there was a record returned, then you need to edit the record and update it.

[keuler]

THanks again Shashi. I'm starting to understand.

So, would this be the general flow:

1) The user selects an activity.
2) The user (optionally) selects a subgroup of users (circle).
3) The user clicks a button called something like Retrieve Headcount Data.
4) That click triggers an stored procedure, and that stored procedure:
a) Checks for the existence of a user-activity record in HEADCOUNT for each user, and creates one if one doesn't exist. (Not sure how to do this yet, but that's later.)
b) Populates the gridview with an UPDATE command that is similar to the SELECT command you proposed earlier. (I don't need a SELECT command in any of this, right, if I assume the user is proceeding with the intent to modify data?)

Thanks!!!

-Kurt

To update the selections, when a user submits the form,
foreach activity from the dataset, retrieve the record for that activity from headcount for that user.

if there are no rows, and the user selected to join it, then you need to insert a new record, else do nothing

if there was a record returned, then you need to edit the record and update it.