Sporadic errors

[MikeR]

I have a page that lists 362 items with a checkbox. When the user completes an item,
he checks it and clicks the submit button, subsequently writing the values to a
database. The problem I need to solve is a better way to delete un-checked items from
the database. The more items the user completes, the longer the request object.
TIA, Mike

One of my users get this intermittently.
HC=ABC123&CB=75&CB=415&CB=215&CB=430&CB=456&CB=400&CB=129&CB=497&CB=348&CB=46&CB=90&CB=160&
CB=391&CB=304&CB=386&CB=318&CB=203&CB=60&CB=112&CB=70&CB=446&CB=104&CB=272&CB=256&CB=149&CB=144&
CB=409&CB=230&CB=501&CB=281&CB=21&CB=29&CB=245&CB=179&CB=27&CB=227&CB=79&CB=162&CB=84&CB=63&
CB=223&CB=265&CB=122&CB=279&CB=106&CB=294&CB=239&CB=287&CB=72&CB=116&CB=137&CB=88&CB=80&CB=387&
CB=248&CB=225&CB=77&CB=339&CB=259&CB=118&CB=342&CB=291&CB=103&CB=110&CB=6&CB=285&CB=202&CB=266&
CB=100&CB=146&CB=212&CB=206&CB=224&CB=5&CB=503&CB=504&CB=209&CB=237&CB=221&CB=163&CB=91&CB=263&
CB=85&CB=61&CB=499&CB=284&CB=269&CB=236&CB=278&CB=390&CB=242&CB=308&CB=54&CB=15&CB=130&CB=288&
CB=249&CB=168&CB=1&CB=64&CB=33&CB=321&CB=50&CB=143&CB=145&CB=86&CB=275&CB=296&CB=148&CB=283&
CB=205&CB=170&CB=132&B1=Log+it

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Microsoft Access Driver] Query is too complex.

/sm/club/changeact.asp, line 52


The code is;

if request.form("CB").count > 0 then
USQL = "Select * from WORKED where CALL = '" & SanCall & "'"
ConnTemp.Execute(USQL)
USQL = ""
DSQL = "DELETE * FROM Worked WHERE CALL = '" & SanCall & "' AND ADIF <> '" &
request.form("CB")(1) & "'"
end if

for i = 1 to request.form("CB").count
SanNum = Replace(request.form("CB")(i), "'", "''")

if i > 1 then
DSQL = DSQL & " AND ADIF <> '" & SanNum & "'" 'request.form("CB")(i) & "'"
end if
USQL = "Select * from worked where ADIF = '" & request.form("CB")(i) & "' and CALL
= '" & sanCall & "'"
set RS = ConnTemp.Execute(USQL)
if RS.EOF and RS.BOF then
USQL = "Insert into [WORKED] (ADIF, Call) Values('" & SanNum & "', '" & sanCall
& "')"
Conntemp.execute(USQL)
end if
next
Conntemp.execute(DSQL) <==== LINE 52

 

[Old Pedant]

I have a page that lists 362 items with a checkbox. When the user completes an item,
he checks it and clicks the submit button, subsequently writing the values to a
database. The problem I need to solve is a better way to delete un-checked items from
the database. The more items the user completes, the longer the request object.

Learn to use IN( ) for such queries.

cbs = Request.Form("CB") ' will be "13, 15, 285, 296" etc.

' this regexp will zap all characters from the cbs string
' *except* digits and commas...so no SQL injection and
' result is a short as possible:
Set resafe = New RegExp
resafe.Pattern = "[^\d\,]"
resafe.Global = True

cbs = resafe.Replace( cbs, "" )

DSQL = "DELETE FROM Worked " _
& " WHERE CALL = '" & SanCall & "' " _
& " AND ADIF NOT IN (" & cbs & ")"

Response.Write "DEBUG DSQL: " & DSQL & "<hr>" ' just for debugging

**********

Now no for...next needed for DSQL !!!

And if you have a complete list of all possible checkbox values in some
other table, we can similarly fix your INSERT code, so you'd do it all in one
query and no loop used.

 

[MikeR]

I have a page that lists 362 items with a checkbox. When the user completes an item,
he checks it and clicks the submit button, subsequently writing the values to a
database. The problem I need to solve is a better way to delete un-checked items from
the database. The more items the user completes, the longer the request object.

Learn to use IN( ) for such queries.

cbs = Request.Form("CB") ' will be "13, 15, 285, 296" etc.

' this regexp will zap all characters from the cbs string
' *except* digits and commas...so no SQL injection and
' result is a short as possible:
Set resafe = New RegExp
resafe.Pattern = "[^\d\,]"
resafe.Global = True

cbs = resafe.Replace( cbs, "" )

DSQL = "DELETE FROM Worked " _
& " WHERE CALL = '" & SanCall & "' " _
& " AND ADIF NOT IN (" & cbs & ")"

Response.Write "DEBUG DSQL: " & DSQL & "<hr>" ' just for debugging

**********

Now no for...next needed for DSQL !!!

And if you have a complete list of all possible checkbox values in some
other table, we can similarly fix your INSERT code, so you'd do it all in one
query and no loop used.

Thank you sir! I stand in awe.I need to get up to speed on expressions. I tried some
quick reading, and quickly discovered I'm not as quick as I used to be. <vbg>
I did have to make a couple of minor changes, but works like a charm.

DSQL = "DELETE * FROM Worked " _
& " WHERE CALL = '" & SanCall & "' " _
& " AND ADIF NOT IN (" & cbs & ")"

and ADIF was a text field. I changed it to a number.

 

[Old Pedant]

DSQL = "DELETE * FROM Worked " _
& " WHERE CALL = '" & SanCall & "' " _
& " AND ADIF NOT IN (" & cbs & ")"

and ADIF was a text field. I changed it to a number.

Well, it's clearly better to make it a number, but it *could* have worked as
a text field with a minor change:

cbs = resafe.Replace( cbs, "" )
cbs = "'" & Replace( cbs, ",", "','" )
DSQL = "DELETE * FROM Worked " _
& " WHERE CALL = '" & SanCall & "' " _
& " AND ADIF NOT IN (" & cbs & ")"

You see that? But then the resafe regexp would need to be changed, also, to
allow textual data instead of all numeric data.

 

[Old Pedant]

DSQL = "DELETE * FROM Worked " _
& " WHERE CALL = '" & SanCall & "' " _
& " AND ADIF NOT IN (" & cbs & ")"

p.s.: You really shouldn't use the asterisk following the word "delete" in
a DELETE query.

Access allows it, but it's not standard SQL and all other DBs will choke on
it.

Access works just fine if you omit it, so it's good practice to learn to
live without it.

 

[Bob Barrows [MVP]]

p.s.: You really shouldn't use the asterisk following the word
"delete" in a DELETE query.

Access allows it, but it's not standard SQL and all other DBs will
choke on it.

Access works just fine if you omit it,

It used to be required (back in A97), especially if you were deleting from
one of the tables in a join.

 

[MikeR]

p.s.: You really shouldn't use the asterisk following the word "delete" in
a DELETE query.

Access allows it, but it's not standard SQL and all other DBs will choke on
it.

Access works just fine if you omit it, so it's good practice to learn to
live without it.

OK, I'll try it. I thought it was bitching at me about that, but maybe it was another
error. Again, thanks!

 

[Evertjan.]

Bob Barrows [MVP] wrote on 27 aug 2008 in
microsoft.public.inetserver.asp.general:

It used to be required (back in A97), especially if you were deleting
from one of the tables in a join.

Strange, Bob,

I thought an asterix would stand for "all fields"
while DELETE is about records?

Because if it is not about "all" what specific pointer could be placed
after the DELETE, as records are defined by WHERE, and have no name?

SQL = "DELETE blah, blah1 from tblWorked" ?

 

[MikeR]

Well, it's clearly better to make it a number, but it *could* have worked as
a text field with a minor change:

cbs = resafe.Replace( cbs, "" )
cbs = "'" & Replace( cbs, ",", "','" )
DSQL = "DELETE * FROM Worked " _
& " WHERE CALL = '" & SanCall & "' " _
& " AND ADIF NOT IN (" & cbs & ")"

You see that? But then the resafe regexp would need to be changed, also, to
allow textual data instead of all numeric data.

Kewl. Like resafe.Pattern = "[^\w\,]" ?

I need to find a 'RegExp for Dummies'

 

[Bob Barrows [MVP]]

Bob Barrows [MVP] wrote on 27 aug 2008 in
microsoft.public.inetserver.asp.general:


Strange, Bob,

I thought an asterix would stand for "all fields"
while DELETE is about records?

Yeh, it never made sense. I can't explain what they were thinking. The T-SQL
syntax is only slightly better:
DELEtE FROM ... FROM ...