this code basically does a batch update to a db based on a checkbox value. it updates if it is checked, but not if it is not.
<%
arrAll = Split(Request.Form("hfAllBoxes", ","
arrVals = Split(Request.form("chkBox", ","
dim strSQL
dim bFirst
j = 0
For each fld in ArrVals
if Not IsEmpty(fld) then j= j+1
next
if ubound(arrVals) <> -1 then 'array is not empty
strSQL = ""
strSql = strSQL & "UPDATE tblProjects SET ForKyle = True WHERE "
for y = 0 to Ubound(arrVals)
if y = 0 then
strSql = strSQL & "Project_ID = " & arrVals(y)
Else
strSql = strSQL & " AND Project_ID = " & arrVals(y)
end if
next

set cmdAddToReport = Server.CreateObject("ADODB.Command"
cmdAddToReport.ActiveConnection = MM_strConn_STRING
cmdAddToReport.CommandText = strSQL
cmdAddToReport.CommandType = 1
cmdAddToReport.CommandTimeout = 0
cmdAddToReport.Prepared = true
cmdAddToReport.Execute()
cmdAddToReport.close
Set cmdAddToReport = Nothing
end if
if ubound(arrVals) <> -1 then
redim arrInActive(ubound(arrAll) - ubound(arrVals))
Else
redim arrInActive(ubound(arrAll))
End if

'set our indexer variable
IDX = 0

'loop through all the checkbox values
for i = 0 to ubound(arrAll)
'a boolean value to check if we match or not
bThere = false
'loop through the values which were submitted
if ubound(arrVals) <> -1 then
for z = 0 to ubound(arrVals)
'if it is found then set the boolean to true
'this is so we don't add it to the new array
if trim(arrVals(z)) = trim(arrAll(i)) then
bThere = true
end if
next
end if ' ends arrVals check
'if it wasn't in the submitted array (i.e. it wasn't checked)
if bThere = false then
'add the value to the new array
arrInactive(IDX) = arrAll(i)
'increment our indexer
IDX = IDX + 1
end if
next

strSql2 = ""
strSql2 = strSql2 & "UPDATE tblProjects SET ForKyle = False WHERE "
'loop through the array which holds the values that were NOT ticked
for x = 0 to ubound(arrInactive)
if arrInactive(x) <> "" then
if bFirst <> true then
strSql2 = strSql2 & "Project_ID = " & arrInactive(x)
bFirst = true
else
'only add the " ADD " if this is not the first variable
strSql2 = strSql2 & " AND Project_ID = " & arrInactive(x)
end if
end if
response.write(arrInactive(x))

next
response.Write("<br>"
response.Write(strSQL2)
Set cmdUpdateReport = Server.CreateObject("ADODB.Command"
cmdUpdateReport.ActiveConnection = MM_strConn_STRING
cmdUpdateReport.CommandText = strSQL2
cmdUpdateReport.CommandType = 1
cmdUpdateReport.CommandTimeout = 0
cmdUpdateReport.Prepared = true
cmdUpdateReport.Execute()



Michael Rudge

Hi

on first look, and believe me its only a quick look, but it seems that your update code is not included in the array loop. You need to inlcude this so when ever the loop finds a value to update it does it immediatly.

Hope this helps

Graham