Sporadic errors

Discussion in 'ASP General' started by MikeR, Aug 26, 2008.

  1. MikeR

    MikeR Guest

    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
     
    MikeR, Aug 26, 2008
    #1
    1. Advertising

  2. MikeR

    Old Pedant Guest

    "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, Aug 26, 2008
    #2
    1. Advertising

  3. MikeR

    MikeR Guest

    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, Aug 26, 2008
    #3
  4. MikeR

    Old Pedant Guest

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



    > >
     
    Old Pedant, Aug 27, 2008
    #4
  5. MikeR

    Old Pedant Guest

    "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, Aug 27, 2008
    #5
  6. 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"
     
    Bob Barrows [MVP], Aug 27, 2008
    #6
  7. MikeR

    MikeR Guest

    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!
     
    MikeR, Aug 27, 2008
    #7
  8. MikeR

    Evertjan. Guest

    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 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)
     
    Evertjan., Aug 27, 2008
    #8
  9. MikeR

    MikeR Guest

    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'
     
    MikeR, Aug 27, 2008
    #9
  10. 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"
     
    Bob Barrows [MVP], Aug 27, 2008
    #10
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?VHJhdmlz?=

    Sporadic ASP.NET State Server Errors

    =?Utf-8?B?VHJhdmlz?=, Jan 20, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    503
    Alvin Bruney
    Jan 24, 2004
  2. =?Utf-8?B?VmljdG9yIEFsY2F6YXI=?=

    Sporadic "Unable to validate data" and "Invalid length for a Base-

    =?Utf-8?B?VmljdG9yIEFsY2F6YXI=?=, Jan 14, 2005, in forum: ASP .Net
    Replies:
    0
    Views:
    414
    =?Utf-8?B?VmljdG9yIEFsY2F6YXI=?=
    Jan 14, 2005
  3. POL8985

    Sporadic Error: Cannot find table 0

    POL8985, Jan 29, 2005, in forum: ASP .Net
    Replies:
    3
    Views:
    9,080
    Karl Seguin
    Jan 30, 2005
  4. =?Utf-8?B?c2FtZWVy?=

    sporadic db connection error

    =?Utf-8?B?c2FtZWVy?=, Jul 1, 2005, in forum: ASP .Net
    Replies:
    3
    Views:
    562
    Bruce Barker
    Jul 1, 2005
  5. Petemo94
    Replies:
    0
    Views:
    835
    Petemo94
    Nov 14, 2005
Loading...

Share This Page