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 9 1613
"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.
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.
"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.
"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.
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"
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!
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)
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'
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" This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Bob Murdoch |
last post by:
I'm receiving a sporadic error: "The remote procedure call failed and did
not execute", when calling the following page:
<%@ language="JavaScript" %>
<%
var vPath =...
|
by: Ike |
last post by:
I have an Activex Control created in VB6, which, displays no problem on any
machine I have, but, on some machines of others, it does not. All machines
are running either XP or Windows 2000, and all...
|
by: Victor Alcazar |
last post by:
Our ASP.NET (VB) is generating sporadic "Unable to validate data" and
"Invalid length for a Base-64 char array" errors.
The production environment is two Windows 2000 Advanced Servers in a web...
|
by: sameer |
last post by:
Hi All,
i have an asp.net application which has got sql server 2000 behind the
scenes.
Very simple application, all it does is extract the data from the databse
and display it in the grid. But...
|
by: Petemo94 |
last post by:
Folks,
I am getting a "sporadic" exception that contains the following stack
trace. I say sporadic b/c my DB connection usually works fine, but
occasionally it fails.
*** I also noted that...
|
by: christopher.davidson |
last post by:
Hello,
I am working with XML files and utilizing Array functions to take the
XML data and combined it with some html code to display a particular
page.
The process currently works like so:
...
|
by: botch |
last post by:
Hi! I'm trying to get some help implementing a classic ASP version of
the Google API search (http://www.google.com/apis/). I've got a version
that works, but it's very sporadic. Sometimes it works,...
|
by: Ike |
last post by:
Can anyone tell me, offhand, why the following might work on some servers
and not others? I am certain it has somthing to do with a setting in
php.ini, just not sure what that setting is. One some...
|
by: Michel Couche |
last post by:
Hello,
I have an ASP.Net application that uses the Wizard control to build a newsletter.
There are three steps in the wizard.
The customer's specific design data are loaded from a database in...
|
by: Daniel Rindt |
last post by:
Hello List,
i hope to find an answer here, my problem is that my Webserver sporadic
offers php scripts for download.
I search with google and find some issues belonging to php version 4.x
and...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
| |