really stumped by this

[middletree]

I have a page which allows users to enter some info into what we will call
Table A.

Also on the same form is some checkboxes, which are populated by a mostly
static table we'll call Table B.

Because it's a many-to-many relationship (that is, a user can check more
than one checkbox), I have created a table we'll refer to as Table AB. It's
a union table for resolving the many-to-many between tables A and B. It
consists of only two fields: the ID (Primary Key) of Table A and Table B.

This works fine, but now I'm asked to create an edit page. A user needs to
be able to click a name, which will take them to a page which displays the
information which was entered in the original form. For most of the info,
which was stored in Table A, this is no problem. But I need to be able to
list all 25 checkboxes, and if there is a match for that person (using Table
A's PK), I need to have that checkbox to be checked, and if not, it needs to
be unchecked.

I've having the darndest time figuring out the syntax in ASP 3.0 and the
accompanying SQL to make this happen.

It seems like I could do this by doing a SELECT from table AB:
SELECT B_ID
FROM AB
WHERE A_ID = 2 (2 being an example)

If it turns out that there are rows in table AB like this:
A_ID B_ID

2 2
2 4
2 8


this would give me a result set of (for example)
2,4,8

So I just have to build my checkboxes to display the word "checked" if the
ID of that checkbox is somewhere in that resultset.

Does this seem like the best way to do this? (Make the correct checkboxes
appear as checked on page load)? If so, can someone please help me with the
syntax?

 

[Mike Brind]

I have a page which allows users to enter some info into what we will call
Table A.

Also on the same form is some checkboxes, which are populated by a mostly
static table we'll call Table B.

Because it's a many-to-many relationship (that is, a user can check more
than one checkbox), I have created a table we'll refer to as Table AB.
It's a union table for resolving the many-to-many between tables A and B.
It consists of only two fields: the ID (Primary Key) of Table A and Table
B.

This works fine, but now I'm asked to create an edit page. A user needs to
be able to click a name, which will take them to a page which displays the
information which was entered in the original form. For most of the info,
which was stored in Table A, this is no problem. But I need to be able to
list all 25 checkboxes, and if there is a match for that person (using
Table A's PK), I need to have that checkbox to be checked, and if not, it
needs to be unchecked.

I've having the darndest time figuring out the syntax in ASP 3.0 and the
accompanying SQL to make this happen.

It seems like I could do this by doing a SELECT from table AB:
SELECT B_ID
FROM AB
WHERE A_ID = 2 (2 being an example)

If it turns out that there are rows in table AB like this:
A_ID B_ID

2 2
2 4
2 8


this would give me a result set of (for example)
2,4,8

So I just have to build my checkboxes to display the word "checked" if the
ID of that checkbox is somewhere in that resultset.

Does this seem like the best way to do this? (Make the correct checkboxes
appear as checked on page load)? If so, can someone please help me with
the syntax?

Yes, you have eventually stumbled on the way to do this. Assuming that you
are building the checkboxes dynamically, you will has a resultset that
contains all the IDs for all 25 boxes. We'll call that rsAllBoxes. Your
resultset containing the checked ones (2,4,8) should ideally be put into an
array:

arrChecked = rsChecked.GetRows()

Now you can iterate the array as many times as you like, which you will want
to do each time you write a checkbox:

Do Until rsAllBoxes.EOF
Response.Write "<input type=""checkbox"" id=""" & rsAllBoxes("ID") & """"
For i = 0 To Ubound(arrChecked,2)
If Cint(arrChecked(0,i) = Cint(rsAllBoxes("ID") Then Response.Write "
checked=""checked"""
Next
Response.Write ">" & rsAllBoxes("TextValue") & "<br />"
rsAllBoxes.MoveNext
Loop