Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Sporadic errors

Reply
Thread Tools

Sporadic errors

 
 
MikeR
Guest
Posts: n/a
 
      08-26-2008
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&C B=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
 
Reply With Quote
 
 
 
 
Old Pedant
Guest
Posts: n/a
 
      08-26-2008


"MikeR" wrote:

> 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.


 
Reply With Quote
 
 
 
 
MikeR
Guest
Posts: n/a
 
      08-26-2008
Old Pedant wrote:
>
> "MikeR" wrote:
>
>> 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.


 
Reply With Quote
 
Old Pedant
Guest
Posts: n/a
 
      08-27-2008

"MikeR" wrote:

> 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.



> >


 
Reply With Quote
 
Old Pedant
Guest
Posts: n/a
 
      08-27-2008
"MikeR" wrote:

> 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.


 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      08-27-2008
Old Pedant wrote:
> "MikeR" wrote:
>
>> 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,


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

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


 
Reply With Quote
 
MikeR
Guest
Posts: n/a
 
      08-27-2008
Old Pedant wrote:
> "MikeR" wrote:
>
>> 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.
>
>

OK, I'll try it. I thought it was bitching at me about that, but maybe it was another
error. Again, thanks!
 
Reply With Quote
 
Evertjan.
Guest
Posts: n/a
 
      08-27-2008
Bob Barrows [MVP] wrote on 27 aug 2008 in
microsoft.public.inetserver.asp.general:

> Old Pedant wrote:
>> "MikeR" wrote:
>>
>>> 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,

>
> 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[s] could be placed
after the DELETE, as records are defined by WHERE, and have no name?

SQL = "DELETE blah, blah1 from tblWorked" ?

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)
 
Reply With Quote
 
MikeR
Guest
Posts: n/a
 
      08-27-2008
Old Pedant wrote:
> "MikeR" wrote:
>
>> 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.


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

I need to find a 'RegExp for Dummies'
 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      08-27-2008
Evertjan. wrote:
> Bob Barrows [MVP] wrote on 27 aug 2008 in
> microsoft.public.inetserver.asp.general:
>
>> Old Pedant wrote:
>>> "MikeR" wrote:
>>>
>>>> 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,

>>
>> 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?


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



--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sporadic line drops every 20 seconds or so using Cisco 585 LRE twduff@gmail.com Cisco 2 09-25-2005 08:45 PM
Sporadic Internet with AOL niallbruce@gmail.com Wireless Networking 1 05-04-2005 10:58 AM
Sporadic Issues Anthony Robinison Wireless Networking 1 08-30-2004 05:13 PM
Sporadic ASP.NET State Server Errors =?Utf-8?B?VHJhdmlz?= ASP .Net 1 01-24-2004 10:30 PM
Errors, errors, errors Mark Goldin ASP .Net 2 01-17-2004 08:05 PM



Advertisments