Unchecked Checkboxes

[jeremywatco]

Here is what I've got... I have a table with a userid(autonumber) and a table with products. The table with products has a userid field associated with records that the user is allowed to view. Now I want to create a page to determine what products the user can view (like a control panel). Ideally I would want a list of checkboxes that control access. If a box associated with a user is checked they have access, if it is not, they do not have access. I can get it to give them access, but I cannot figure out how to take it away if a box is not checked. any ideas?

[whammy]

Just make sure that whenever you use the query to display the products, that only the ones they have access to are selected.

"SELECT * FROM Products P INNER JOIN Users U ON(P.UserID = U.UserID) WHERE P.UserID = " & UserID

or something like that to start with... not sure exactly what your table setup is like though... what kind of data do you have in the field determining access?

[jeremywatco]

That is pretty much what I am doing already. What I need is so that if I uncheck a checkbox the user no longer has access. I need some way to interact with checkboxes that are unchecked.

[whammy]

Ahh... then what you need to do is check for EACH checkboxes' value when the form is submitted. I would (in this case) name each checkbox differently, i.e. C_1, C_2, etc.

Then loop through them:

For i = 1 to [whatever your last checkbox is]
If Request.Form("C_" & i) = "" Then
'The checkbox isn't checked
Else
'They have access
End If
Next

Since only checked checkboxes pass a value. No use using an array of same-name checkboxes in this case, I don't think... although if you have numbers for the different categories, you could name all the checkboxes the same (with perhaps the autoincrement number as a value), and check for the presence of them in the posted array with InStr()... i.e.:

If InStr(checkboxarray,"32") Then '...

I think the first method (although not perhaps the most elegant or correct way) might be the easiest to figure out and debug, though... and I know it works since it's in use on about 70 sites I work with where a C++ programmer wrote that part of the code... :D

[jeremywatco]

OK...here's where I am stuck.

I am able to determine what groups need to be added to the database field by doing a

for each item in request("frmcheckboxs")

sqlstring = "Select * From Products Where Group='" & item & "' Prod_Name='test'"

SET RS = con.execute (sqlstring)

IF RS.EOF THEN

'THE USER NEEDS TO BE ADDED

ELSE IF RS("Group") = item THEN

' THE USER IS ALREADY A MEMBER

Next

Now I need some way to delete the user if he exists in the database but was not checked...Basically the product table look like this

PRODUCT NAME GROUP
Item 1 1
Item 1 3
Item 1 5
Item 1 2
Item 2 1
Item 2 3

Does this make any sense?

[glenngv]

I'd go with whammy's solution and expand on it.

Have another set of hidden fields that corresponds to the checkboxes.


For i = 1 to [whatever your last checkbox is]

sqlstring = "Select * From Products Where Group='" & Request.Form("Hidden_" & i) & "'"

SET RS = con.execute (sqlstring)

If RS.EOF Then

'THE USER NEEDS TO BE ADDED

ElseIf Request.Form("Check_" & i)="" Then

' THE USER IS ALREADY A MEMBER AND WAS NOT CHECKED
'Delete statement here

End if
Next


Your checkbox should be named Check_1, Check_2, ... with the corresponding hidden fields named Hidden_1, Hidden_2,...
Of course, you can change the names but you should have _Number at the end

[jeremywatco]

Perfect! Thanks you guys. Also, I am generating these checkboxes from a database table. Basically just doing a:

sql = "Select * from groups"
set rs = con.execute(sql)

do while not rs.eof
%>


<input type="Checkbox" value="<%=rs("group_id")%>

%>

loop


How can I number each checkbox this way?

[whammy]

Counter = 1

do while not rs.eof
%>


<input type="Checkbox" name="Check_<% = Counter %>" value="<%=rs("group_id")%>

%>
Counter = Counter + 1
loop

If you need to pass the counter, make that a hidden field as well... just subtract it by one...

P.S. I'm not totally getting what you're trying to do, I'm willing to bet there's a better way of doing this with another related table (or something) instead (?!?)... seems like it but hard to tell without looking at it first hand...

[jeremywatco]

Hmm having difficulties with this here is the code and output:

tester = Request.QueryString( "sub" )

IF tester = 1 THEN

Counter = Request.Form("counter")




For i = 1 to Counter


testvar = trim(Request.Form("CHECKBOX_" & i))


Sqlstring4 = "SELECT * FROM Subcat WHERE Group_ID='" & testvar & "' AND Subcat_Name='Test'"
Response.Write(sqlstring4)
SET RS2 = Con.Execute(SqlString4)

IF RS2.EOF THEN

Response.Write( "Group " & testvar & " needs to be added<BR>")

ELSEIF testvar="" THEN

Response.Write( "Group " & Request("CHECKBOX_" & i) & " needs to be deleted<BR>")

ELSE
Response.Write("<FONT COLOR=RED>ERROR</FONT><BR>")

END IF

Next

ELSE

sqlString = "SELECT * FROM GROUPS"

Set RS = Con.Execute( sqlString )
%>

<FORM METHOD=POST ACTION="test.asp?sub=1" NAME="FORM">

<%
Counter = 1
DO WHILE NOT RS.EOF
%>

<INPUT TYPE="CHECKBOX" NAME="CHECKBOX_<%=Counter%>" VALUE="<%=RS("ID")%>"><%=RS("GROUPNAME")%><BR>

<%
Counter = Counter + 1
RS.Movenext

LOOP

Counter = Counter - 1

Response.Write ( "<INPUT TYPE=HIDDEN NAME=COUNTER VALUE=" & counter & ">" )
Response.write ( "<INPUT TYPE=SUBMIT></FORM>" )

END IF
%>







OUTPUT:

SELECT * FROM Subcat WHERE Group_ID='' AND Subcat_Name='Test'Group needs to be added
SELECT * FROM Subcat WHERE Group_ID='' AND Subcat_Name='Test'Group needs to be added
SELECT * FROM Subcat WHERE Group_ID='' AND Subcat_Name='Test'Group needs to be added
SELECT * FROM Subcat WHERE Group_ID='' AND Subcat_Name='Test'Group needs to be added
SELECT * FROM Subcat WHERE Group_ID='' AND Subcat_Name='Test'Group needs to be added
SELECT * FROM Subcat WHERE Group_ID='' AND Subcat_Name='Test'Group needs to be added
SELECT * FROM Subcat WHERE Group_ID='20' AND Subcat_Name='Test'ERROR
SELECT * FROM Subcat WHERE Group_ID='21' AND Subcat_Name='Test'ERROR
SELECT * FROM Subcat WHERE Group_ID='' AND Subcat_Name='Test'Group needs to be added
SELECT * FROM Subcat WHERE Group_ID='' AND Subcat_Name='Test'Group needs to be added
SELECT * FROM Subcat WHERE Group_ID='' AND Subcat_Name='Test'Group needs to be added

Any ideas? Where it says "Group needs to be added" It should say nothing..this should be ignored...Where it says ERROR it should say "Group needs to be added" and I cannot get it to say group needs to be deleted.

[glenngv]

I told you to have another set of hidden fields corresponding to the checkboxes. This might work (If i understand your requirements correctly)

tester = Request.QueryString( "sub" )

IF tester = 1 THEN

Counter = Request.Form("counter")




For i = 1 to Counter


testvar = trim(Request.Form("CHECKBOX_" & i))
groupid = trim(Request.Form("HIDDEN_" & i))


Sqlstring4 = "SELECT * FROM Subcat WHERE Group_ID='" & groupid & "' AND Subcat_Name='Test'"
Response.Write(sqlstring4)
SET RS2 = Con.Execute(SqlString4)

IF RS2.EOF THEN

Response.Write( "Group " & groupid & " needs to be added<BR>")

ELSEIF testvar="" THEN

Response.Write( "Group " & Request("CHECKBOX_" & i) & " needs to be deleted<BR>")

ELSE
Response.Write("<FONT COLOR=RED>ERROR</FONT><BR>")

END IF

Next

ELSE

sqlString = "SELECT * FROM GROUPS"

Set RS = Con.Execute( sqlString )
%>

<FORM METHOD=POST ACTION="test.asp?sub=1" NAME="FORM">

<%
Counter = 1
DO WHILE NOT RS.EOF
%>

<INPUT TYPE="CHECKBOX" NAME="CHECKBOX_<%=Counter%>" VALUE="<%=RS("ID")%>"><%=RS("GROUPNAME")%><BR>
<INPUT TYPE="hidden" NAME="HIDDEN_<%=Counter%>" VALUE="<%=RS("ID")%>">


<%
Counter = Counter + 1
RS.Movenext

LOOP

Counter = Counter - 1

Response.Write ( "<INPUT TYPE=HIDDEN NAME=COUNTER VALUE=" & counter & ">" )
Response.write ( "<INPUT TYPE=SUBMIT></FORM>" )

END IF
%>

[jeremywatco]

Ok, made all the changes you suggested and added the hidden fields, and now the output is just this no matter what you do and do not select.

SELECT * FROM Subcat WHERE Group_ID='' AND Subcat_Name='Test'Group needs to be added
SELECT * FROM Subcat WHERE Group_ID='' AND Subcat_Name='Test'Group needs to be added
SELECT * FROM Subcat WHERE Group_ID='' AND Subcat_Name='Test'Group needs to be added
SELECT * FROM Subcat WHERE Group_ID='' AND Subcat_Name='Test'Group needs to be added
SELECT * FROM Subcat WHERE Group_ID='' AND Subcat_Name='Test'Group needs to be added
SELECT * FROM Subcat WHERE Group_ID='' AND Subcat_Name='Test'Group needs to be added
SELECT * FROM Subcat WHERE Group_ID='' AND Subcat_Name='Test'Group needs to be added
SELECT * FROM Subcat WHERE Group_ID='' AND Subcat_Name='Test'Group needs to be added
SELECT * FROM Subcat WHERE Group_ID='' AND Subcat_Name='Test'Group needs to be added
SELECT * FROM Subcat WHERE Group_ID='' AND Subcat_Name='Test'Group needs to be added
SELECT * FROM Subcat WHERE Group_ID='' AND Subcat_Name='Test'Group needs to be added



BTW...what does the Hidden field do that the regular checkbox does not

[glenngv]

The GroupID should not be blank, maybe you are requesting the wrong hidden field names. Can you post the new code?

ALL the hidden fields and ONLY checked checkboxes get submitted.

[jeremywatco]

Sure:

<%

'Open the Database
Set Con = Server.CreateObject ( "ADODB.Connection" )
Con.Open "test"

tester = Request.QueryString( "sub" )

IF tester = 1 THEN

Counter = Request.Form("counter")
groupid = trim(Request.Form("HIDDEN_" & i))




For i = 1 to Counter


testvar = trim(Request.Form("CHECKBOX_" & i))


Sqlstring4 = "SELECT * FROM Subcat WHERE Group_ID='" & groupid & "' AND Subcat_Name='Test'"
Response.Write(sqlstring4)
SET RS2 = Con.Execute(SqlString4)

IF RS2.EOF THEN

Response.Write( "Group " & groupid & " needs to be added<BR>")

ELSEIF testvar="" THEN

Response.Write( "Group " & Request("CHECKBOX_" & i) & " needs to be deleted<BR>")

ELSE
Response.Write("<FONT COLOR=RED>ERROR</FONT><BR>")

END IF

Next

ELSE

sqlString = "SELECT * FROM GROUPS"

Set RS = Con.Execute( sqlString )
%>

<FORM METHOD=POST ACTION="test.asp?sub=1" NAME="FORM">

<%
Counter = 1
DO WHILE NOT RS.EOF
%>

<INPUT TYPE="CHECKBOX" NAME="CHECKBOX_<%=Counter%>" VALUE="<%=RS("ID")%>"><%=RS("GROUPNAME")%><BR>
<INPUT TYPE="hidden" NAME="HIDDEN_<%=Counter%>" VALUE="<%=RS("ID")%>">

<%
Counter = Counter + 1
RS.Movenext

LOOP

Counter = Counter - 1

Response.Write ( "<INPUT TYPE=HIDDEN NAME=COUNTER VALUE=" & counter & ">" )
Response.write ( "<INPUT TYPE=SUBMIT></FORM>" )

END IF
%>

[glenngv]

you forgot to set the variable groupid inside the loop:

groupid = trim(Request.Form("HIDDEN_" & i))

[jeremywatco]

OK... I caught that already... It is somewhat working now... Here is the issue. It reports correctly on group_id's already in the fields IE not EOF. So it knows when I do and do not have them checked. However it there is a group listed that is not in the fields of the subcat table then it still reports as needing to be added. With no boxes checked here is the result:

SELECT * FROM Subcat WHERE Group_ID='1' AND Subcat_Name='Test'Group 1 needs to be deleted
SELECT * FROM Subcat WHERE Group_ID='15' AND Subcat_Name='Test'Group 15 needs to be deleted
SELECT * FROM Subcat WHERE Group_ID='16' AND Subcat_Name='Test'Group 16 needs to be deleted
SELECT * FROM Subcat WHERE Group_ID='17' AND Subcat_Name='Test'Group 17 needs to be deleted
SELECT * FROM Subcat WHERE Group_ID='18' AND Subcat_Name='Test'Group 18 needs to be deleted
SELECT * FROM Subcat WHERE Group_ID='19' AND Subcat_Name='Test'Group 19 needs to be deleted
SELECT * FROM Subcat WHERE Group_ID='20' AND Subcat_Name='Test'Group 20 needs to be deleted
SELECT * FROM Subcat WHERE Group_ID='21' AND Subcat_Name='Test'Group 21 needs to be deleted
SELECT * FROM Subcat WHERE Group_ID='22' AND Subcat_Name='Test'Group 22 needs to be deleted
SELECT * FROM Subcat WHERE Group_ID='23' AND Subcat_Name='Test'Group 23 needs to be added
SELECT * FROM Subcat WHERE Group_ID='24' AND Subcat_Name='Test'Group 24 needs to be added

[jeremywatco]

Ok i think this is it. I modified some of the code to achieve the desired result:

<%

'Open the Database
Set Con = Server.CreateObject ( "ADODB.Connection" )
Con.Open "test"

tester = Request.QueryString( "sub" )

IF tester = 1 THEN

Counter = Request.Form("counter")




For i = 1 to Counter


testvar = trim(Request.Form("CHECKBOX_" & i))
groupid = trim(Request.Form("HIDDEN_" & i))


Sqlstring4 = "SELECT * FROM Subcat WHERE Group_ID='" & groupid & "' AND Subcat_Name='Test'"
Response.Write(sqlstring4)
SET RS2 = Con.Execute(SqlString4)

IF RS2.EOF THEN
IF testvar="" THEN
Response.Write("DO NOTHING!<BR>")
ELSE


Response.Write( "Group " & groupid & " needs to be added<BR>")
END IF
ELSEIF testvar="" THEN

Response.Write( "Group " & groupid & " needs to be deleted<BR>")

ELSE
Response.Write("<FONT COLOR=RED>ERROR</FONT><BR>")

END IF

Next

ELSE

sqlString = "SELECT * FROM GROUPS"

Set RS = Con.Execute( sqlString )
%>

<FORM METHOD=POST ACTION="test5.asp?sub=1" NAME="FORM">

<%
Counter = 1
DO WHILE NOT RS.EOF
%>

<INPUT TYPE="CHECKBOX" NAME="CHECKBOX_<%=Counter%>" VALUE="<%=RS("ID")%>"><%=RS("GROUPNAME")%><BR>
<INPUT TYPE="hidden" NAME="HIDDEN_<%=Counter%>" VALUE="<%=RS("ID")%>">


<%
Counter = Counter + 1
RS.Movenext

LOOP

Counter = Counter - 1

Response.Write ( "<INPUT TYPE=HIDDEN NAME=COUNTER VALUE=" & counter & ">" )
Response.write ( "<INPUT TYPE=SUBMIT></FORM>" )

END IF
%>

[glenngv]

Glad you made it worked :)

[jeremywatco]

Me Too! But I couldn't have done it without your guy's help. I really do appreciate it! THANKS!