ASP > Access Database Insert/Delete With A Checkbox

[robocop]

Hey there, really basic question....

i have a database with basically a bunch of image names, the paths, an ID, and a Yes/No box used to determine whether or not the image is "active" in my catalog.

what i need to do is make a page that displays all these images (active and non active) and has like checkboxes that correspond with the "Yes/No" checkboxes...so like, if I unchecked 15 and checked 3 more to be active and hit submit...it would update the records in the DB accordingly.

Does this even make any sense whatsoever???
Let me know if this can be done...Thanks!

Robocop

[raf]

It makes sense and it can be done.

Where do you need help with? Displaying the checkboxes? Running the updates ?

[robocop]

basically, i need to know how to 'translate' the html checkboxes into acccess so that it understands when i run the update that if a check box WAS checked and now is unchecked, it will update in the database accordingly (and visa versa)

[raf]

Well, you need to run 2 updatestatements :
- update table set variable=1 where PK In (999999,x,x,x,x)
- update table set variable=0 where PK In (999999,x2,x2,x2,x2)
--> table = tablename, variablename= variablename, 1 = the value for checked, 0 = the value for unchecked, PK is the variablename of your primary key value, 999999 = an unrealistic but valid num to make it all a bit easier, x = the PK value of the records that were 0 but are now 1, x2 = the PK value of the records that were 1 but are now 0

All that's realy interesting is hox to get these x's and x2's

When you build the formpage, you build a 'smart' unique name for each checkbox in a fixed format. cbVx, where V = the value for variable (1=checked,0=unchecked) and x the PK value of the record . Like cb12, cb09, cb134 etc
Code

do while rsRecordsetname.EOF = False
response.write("<input type=""checkbox"" value=""" & rsRecordsetname.Fields("variable") & """ name=""cb" & rsRecordsetname.Fields("variable") & rsRecordsetname.Fields("PK") & """")
if rsRecordsetname.Fields("variable") = 1 then
response;write(" checked=""checked""")
end if
response.write (" />")
...
rsRecordsetname.MoveNext
loop

So you build this names and check the boxes

Then, in the page where you post the form to :
you do something like


dim box, col1, col0
col1 = "999999" 'collection of PK values that need to be updated from 0 to 1
col0 = "999999" 'collection of PK values that need to be updated from 1 to 0
for each box in request.form
if left(box,2) = "cb" then
if request.form(box) = "1" then
if mid(request.form(box),3,1) = "0" then 'changed checkbox !
col1 = col1 & "," & mid(request.form(box),4) 'so the PK value is added to the collection
end if
end if
if request.form(box) = "0" then 'bit safer then an else-clause
if mid(request.form(box),3,1) = "1" then
col0 = col0 & "," & mid(request.form(box),4)
end if
end if
end if
next


And then it's just
sql="update table set variable=1 where PK In (" & col1 & ")"
etc

[robocop]

so basically i use the checkboxes to construct variables to use in my sql statements?

am i reading that right?

[raf]

Yes. You compare the value of the checkbox with the third caracter of it's name, and if they are different, then you know the checkbox was changed by the user.
And you then just add it to the PK-collection that is stored in a variable.
This variable is then later on inserted in the sql-statement.

[robocop]

Alas, i have solved it!! ....heres my solution....works like a freakin charm!


<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!----#include file="Connections/site.asp" -->
<!--#include file="../../../Connections/patternDB.asp" -->
<%
set rs = Server.CreateObject("ADODB.Recordset")
rs.ActiveConnection = MM_patternDB_STRING
rs.Source = "SELECT * FROM patterns"
rs.CursorType = 0
rs.CursorLocation = 2
rs.LockType = 3
rs.Open()
rs_numRows = 0
%>


<%
Dim Repeat1__numRows
Repeat1__numRows = -1
Dim Repeat1__index
Repeat1__index = 0
rs_numRows = rs_numRows + Repeat1__numRows
%>
<%
if request("selected") <> "" Then
rs.MoveFirst
while not rs.eof
patternID= rs("patternID")
rs("patternName")=Request("PatternName" & patternID)
rs("patternImage")=Request("PatternImage"& patternID)
rs("available")=DoCheck(Request("available"& patternID))
rs("coolnTie")=DoCheck(Request("coolnTie"& patternID))
rs("tissueBox")=DoCheck(Request("tissueBox"& patternID))
rs.update
rs.Movenext
wend
response.redirect "default.asp"
end if

function DoCheck(fld)
if fld <> "" then
DoCheck=1
else
DoCheck=0
end if
end function

%>
<html>
<head>
<title>Multiple Update</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body bgcolor="#FFFFFF">
<form name="form1" method="post" action="default.asp" style="margin:0px">
<font color="#666666" size="1" face="Verdana, Arial, Helvetica, sans-serif">
<input type="submit" name="Submit" value="Update">
Please Wait For Records To Load From Database...</font><br>
<br>
<table width="100%" border="1" cellpadding="4" cellspacing="0" bordercolor="#666666">
<tr bgcolor="#CCCCCC">
<td width="77" ><div align="center"><strong><font size="2" face="Verdana, Arial, Helvetica, sans-serif">ID</font></strong></div></td>
<td width="96"><p align="center"><strong><font color="#333333" size="2" face="Verdana, Arial, Helvetica, sans-serif">Display</font><font color="#333333" size="2" face="Verdana, Arial, Helvetica, sans-serif">
Name<br>
</font></strong><strong><font color="#333333" size="2" face="Verdana, Arial, Helvetica, sans-serif">&amp;<br>
Image Path</font></strong></p>
</td>
<td width="58"><strong><font color="#333333" size="2" face="Verdana, Arial, Helvetica, sans-serif">Active?</font></strong></td>
<td width="91"><div align="center"><strong><font color="#333333" size="2" face="Verdana, Arial, Helvetica, sans-serif">
Cool'n Tie<br>
Designation </font></strong></div></td>
<td width="90"><strong><font color="#333333" size="2" face="Verdana, Arial, Helvetica, sans-serif">Tissue
Box<br>
Designation </font></strong></td>
<td width="187"><strong><font color="#333333" size="2" face="Verdana, Arial, Helvetica, sans-serif">Image</font></strong></td>
</tr>
<%
While ((Repeat1__numRows <> 0) AND (NOT rs.EOF))
%>
<tr onMouseOver="this.bgColor='#EAEAEA'" onMouseOut="this.bgColor='#ffffff'">
<td><div align="center"><font size="1" face="Verdana, Arial, Helvetica, sans-serif"><%=(rs.Fields.Item("patternID").Value)%></font></div></td>
<td><input name="PatternName<%=(rs.Fields.Item("patternID").Value)%>" type="text" id="PatternName<%=(rs.Fields.Item("patternID").Value)%>" value="<%=(rs.Fields.Item("patternName").Value)%>" style="font-size:9px">
<br> <input name="PatternImage<%=(rs.Fields.Item("patternID").Value)%>" type="text" id="PatternImage<%=(rs.Fields.Item("patternID").Value)%>" value="<%=(rs.Fields.Item("patternImage").Value)%>" style="font-size:9px">
</td>
<td><div align="center"><font size="1" face="Verdana, Arial, Helvetica, sans-serif">Active<br>
</font>
<input <%If (CStr((rs.Fields.Item("available").Value)) = CStr("True")) Then Response.Write("checked") : Response.Write("")%> name="available<%=(rs.Fields.Item("patternID").Value)%>" type="checkbox" id="available<%=(rs.Fields.Item("patternID").Value)%>" value="checkbox">
</div></td>
<td><div align="center"><font size="1" face="Verdana, Arial, Helvetica, sans-serif">Cooln
Tie<br>
</font>
<input <%If (CStr((rs.Fields.Item("coolnTie").Value)) = CStr("True")) Then Response.Write("checked") : Response.Write("")%> name="coolnTie<%=(rs.Fields.Item("patternID").Value)%>" type="checkbox" id="coolnTie<%=(rs.Fields.Item("patternID").Value)%>" value="checkbox">
</div></td>
<td><div align="center"><font size="1" face="Verdana, Arial, Helvetica, sans-serif">Tissue
Box</font> <br>
<input <%If (CStr((rs.Fields.Item("tissueBox").Value)) = CStr("True")) Then Response.Write("checked") : Response.Write("")%> name="tissueBox<%=(rs.Fields.Item("patternID").Value)%>" type="checkbox" id="tissueBox<%=(rs.Fields.Item("patternID").Value)%>" value="checkbox">
</div></td>
<td><a href="<%Response.Write("http://www.cindysboutique.com/store/images/patterns/")&(rs.Fields.Item("patternImage").Value)%>" target="_blank"><img src="<%= Response.Write("http://www.cindysboutique.com/store/images/patterns/")&(rs.Fields.Item("patternImage").Value)%>" alt="Click To Enlarge" width="50" height="50" border="0"></a></td>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1

rs.MoveNext()
Wend
%>
</table>
<input name="selected" type="hidden" id="selected" value="1">
<input type="submit" name="Submit" value="Update">
</form>
</body>
</html>
<%
rs.Close()
Set rs = Nothing
%>



Works awesome!


Hope this can help someone out....
robocop