if else loop problems

[sbishops]

I have a web page utilizing asp, vbscript, msaccess 2000.
Here is my code:

First version (this works fine):
<%
for each Field in rs.fields
for k = 0 to UBound(arrSand)
if Field.name = arrSand(k) then
SQL = &quot;UPDATE sand SET &quot; & Field.name & &quot;
= 'True' WHERE(sand.personid = &quot; & varNewID & &quot &quot;
Response.Write(SQL)
connSand.execute(SQL)
end if
next
next
%>

But then I add an &quot;else&quot; in this second version, which I would prefer to use and it gets stuck on 2nd UPDATE syntax and I get error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.

This is the echo of the 2nd UPDATE statement that it hangs on:
UPDATE sand SET all = 'False' WHERE(sand.personid = 308)

<%
for each Field in rs.fields
for k = 0 to UBound(arrSand)
if Field.name <> &quot;personid&quot; then
if Field.name = arrSand(k) then
SQL = &quot;UPDATE sand SET &quot; & Field.name & &quot;
= 'True' WHERE(sand.personid = &quot; & varNewID & &quot &quot;
Response.Write(SQL)
connSand.execute(SQL)
else
SQL = &quot;UPDATE sand SET &quot; & Field.name & &quot;
= 'False' WHERE(sand.personid = &quot; & varNewID & &quot &quot;
Response.Write(SQL)
connSand.execute(SQL)
end if
end if
next
next
%>

What's the problem? Can UPDATE not be done in an if-else loop like this? By the way, both loops are utilized during the UPDATE (ie: there will always be values that go into the &quot;if&quot; and there will always be values that go into the &quot;else&quot;. Any help is appreciated. P.S. If i posted to wrong forum, sorry, i don't know if this is asp, msaccess or sql issue.

Thanks in advance for ANY enlightenment

 

[sharedworld]

i wouldn't do 2 if then one in the another, because the server dosen't know to which it has to go... i would better do something like this:

for each Field in rs.fields
for k = 0 to UBound(arrSand)
if Field.name = arrSand(k) and Field.name <> &quot;personid&quot; then
SQL = &quot;UPDATE sand SET &quot; & Field.name & &quot;
= 'True' WHERE(sand.personid = &quot; & varNewID & &quot &quot;
Response.Write(SQL)
connSand.execute(SQL)
else
SQL = &quot;UPDATE sand SET &quot; & Field.name & &quot;
= 'False' WHERE(sand.personid = &quot; & varNewID & &quot &quot;
Response.Write(SQL)
connSand.execute(SQL)
end if
next
next

 

[sbishops]

thanks, i'll try it i'll let you know how it works

 

[sbishops]

when it gets into the else, it's trying to update the personid field and i don't want it to even look at personid in either scenario. is there an &quot;else if&quot; that can be used here to prohibit personid from being looked at in the else?

thanks so much
also, could it be my connection. i've read a little about MS Jet and am wondering if my standard ADODB DSNconnection is up to par?

 

[sharedworld]

elseif Field.name <> &quot;personid&quot; then

this is instead the else line, and why you think that your connection is not up to date?

 

[sbishops]

This is my current connection set up with a DSN:

Dim conn, strSQL
set conn = server.createobject(&quot;ADODB.Connection&quot
conn.open &quot;sand1&quot;

I had just been reading about how Jet is better using a DSN-less connection.

anyway-back to my problem (thank you by the way-it worked partly) i got part of it figured out, but its never getting into the 2nd loop. i have &quot;false&quot; defaults set up in my db, so maybe i don't need to manually set these in this code. it would just be nice to &quot;hard&quot; wire those values into the db via this script once user has submitted. also, the default values of &quot;False&quot; in access db convert to 0's. so 2 problems for the price of one. any ideas, and thank you.

here's my revised code:
<%
for each Field in rs.fields
for k = 0 to UBound(arrSand)
if Field.name = arrSand(k) and Field.name <> &quot;personid&quot; then
SQL = &quot;UPDATE sand SET &quot; & Field.name & &quot;
= 'True' WHERE(sand.personid = &quot; & varNewID & &quot &quot;
Response.Write(SQL)
connSand.execute(SQL)
elseif &quot; & Field.name & &quot; = &quot;False&quot; and Field.name
<> &quot;personid&quot; then
SQL = &quot;UPDATE sand SET &quot; & Field.name & &quot; = 'False' WHERE
(sand.personid = &quot; & varNewID & &quot &quot;
Response.Write(SQL)
connSand.execute(SQL)
end if
next
next
%>

thanks so much for any thoughts

 

[sharedworld]

try like this:

elseif Field.name = False and Field.name <> &quot;personid&quot; then