Professional Web Applications Themes

question about DELETE statement - ASP Database

Hi, I have to delete some clients who are older then 60 years and who are 'type 1' and not 'type 2'. But the type of a client is determined by its codename (if the name contains somewhere a "_", then it's type 1 otherwise it's type ) and the codenames can have any length. I did like this, but can i do better than this, because the DELETE statement is executed many times. set objdc = Server.CreateObject("ADODB.Connection") objdc.Open("provider=Microsoft.Jet.OLEDB.4.0; Data Source =c:\mydb.mdb") sql="select client from mytable where age>60" rs.open sql, objdc, 3, 3 rec=rs.recordcount if rec>0 then for i = ...

  1. #1

    Default question about DELETE statement

    Hi,

    I have to delete some clients who are older then 60 years and who are 'type
    1' and not 'type 2'. But the type of a client is determined by its codename
    (if the name contains somewhere a "_", then it's type 1 otherwise it's
    type ) and the codenames can have any length.
    I did like this, but can i do better than this, because the DELETE statement
    is executed many times.

    set objdc = Server.CreateObject("ADODB.Connection")
    objdc.Open("provider=Microsoft.Jet.OLEDB.4.0; Data Source =c:\\mydb.mdb")
    sql="select client from mytable where age>60"
    rs.open sql, objdc, 3, 3
    rec=rs.recordcount
    if rec>0 then

    for i = 1 to rec
    flg=0
    typ=rs.fields("client").value
    'here i determine which type (with mid, len etc ...)
    ....
    ....
    if lCase(mid(typ,...,1))="_"
    then flg=1 'flg=1 means "type 1" and flg=2
    means "type 2"
    else
    flg=2
    end if
    'now i know the type of the present client
    strsql="delete from client where age>60 and flg=1 ;"
    objdc.execute strsql, , adcmdtext and adcmdexecutenorecords

    rs.movenext
    next
    end if


    Thanks
    Walter


    Walter Guest

  2. #2

    Default Re: question about DELETE statement


    Walter wrote: 

    Why do you think your database knows anything about your flg variable??


    You're too quick to resort to a recordset.

    Your first objective should be to create a SELECT statement that selects the
    records that need to be deleted (yes, this can be done). Once you've ensured
    that the statement correctly selects the appropriate records, convert it
    into a DELETE statement. So open your database in Access, fire up the Query
    Builder (click into the Queries tab and press the button to create a new
    query in Design View), and let's get going.

    Start with your original select statement which you can simply paste into
    the SQL View of the Query Builder:

    select client from mytable where age>60

    Now let's add the criterion to decide whether or not the client field
    contains an "_" (does the underscore have to appear in a particular location
    in the string? If so, the problem is slightly more complicated. I will
    assume that the presence of an underscore anywhere in the field signifies
    that it is Type 1. Post back if this assumption is incorrect and provide
    more details):

    AND Instr([client],"_") > 0

    Test this (by running it). Does it select only the appropriate records? If
    not, post bck and provide more details. If so, convert it to a DELETE
    statement by replacing "select" with "delete". You could now use this
    statement in your asp page to delete the records, but my preference at this
    point would be to save the query, calling it "qDelOldType1" and execute the
    saved query in ASP:

    objdc.qDelOldType1

    Bob Barrows


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

  3. #3

    Default Re: question about DELETE statement

    Thanks for replying.
    Unfortunaltely, it's not so simple. In fact, all client names are of
    variable length and contains two "_" anywhere. If the position just after
    the second "_" contains a "L", then it's type 1 else it's type 2.
    I wrote this code in order to p the name, but i don't think i can put it
    as criterion in a sql statement:

    flg=0
    lol=rs.fields("client").value
    tiret1=0
    tiret2=0
    for i=1 to len(lol)
    a=mid(lol,i,1)
    if tiret1<>0 then
    if a="_" then
    tiret2=i
    end if
    else
    if a="_" then
    tiret1=i
    end if
    end if
    next

    if lCase(mid(lol,tiret2+1,1))="l" then flg=1 'means it's type 1
    ....



    "Bob Barrows [MVP]" <SPAMcom> wrote in message
    news:phx.gbl... 
    >
    > Why do you think your database knows anything about your flg variable??
    >
    >
    > You're too quick to resort to a recordset.
    >
    > Your first objective should be to create a SELECT statement that selects[/ref]
    the 
    ensured 
    Query 
    location 
    this 
    the 


    Walter Guest

  4. #4

    Default Re: question about DELETE statement

    Walter wrote: 

    It would have helped if you had provided a couple of examples of each type.
    A picture is worth a thousand words ...

    Could an L ever appear after the first underscore? If not, the task is that
    much simpler:
    WHERE Instr([client],"_l") > 0
     

    Yes, you can. You're still locked in the procedural code mindset (I fall
    into the same mindset at times, so don't take this as a dig).

    WHERE ... AND
    Mid([client], InStr(InStr([client],'_')+1, [client],'_')+1, 1)='l'
    (this would also work in your vbscript code, although InStrRev makes it
    simpler - unfortunately InStrRev cannot be used in queries)



    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.


    Bob Guest

  5. #5

    Default Re: question about DELETE statement

    yes, after the first "_", anything including "L" can be there.
    e.g. aze_leonard_plm (this is type 2)
    e.g. aze__plm (this is also type 2)
    e.g. aze_leonard_leo (this is type 1)
    e.g. aze__leo (this is type 1)

    ....

    "Bob Barrows [MVP]" <SPAMcom> wrote in message
    news:phx.gbl... 
    >
    > It would have helped if you had provided a couple of examples of each[/ref]
    type. 
    that 
    >
    > Yes, you can. You're still locked in the procedural code mindset (I fall
    > into the same mindset at times, so don't take this as a dig).
    >
    > WHERE ... AND
    > Mid([client], InStr(InStr([client],'_')+1, [client],'_')+1, 1)='l'
    > (this would also work in your vbscript code, although InStrRev makes it
    > simpler - unfortunately InStrRev cannot be used in queries)
    >
    >
    >
    > Bob Barrows
    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
    >
    >[/ref]


    Walter Guest

  6. #6

    Default Re: question about DELETE statement

    Try:

    WHERE Instr( (Instr([client],"_")+1), [client],"_l", 1) > 0

    --
    Mark Schupp
    Head of Development
    Integrity eLearning
    www.ielearning.com


    "Walter" <it> wrote in message
    news:phx.gbl... 
    > >
    > > It would have helped if you had provided a couple of examples of each[/ref]
    > type. 
    > that 
    > >
    > > Yes, you can. You're still locked in the procedural code mindset (I fall
    > > into the same mindset at times, so don't take this as a dig).
    > >
    > > WHERE ... AND
    > > Mid([client], InStr(InStr([client],'_')+1, [client],'_')+1, 1)='l'
    > > (this would also work in your vbscript code, although InStrRev makes it
    > > simpler - unfortunately InStrRev cannot be used in queries)
    > >
    > >
    > >
    > > Bob Barrows
    > > --
    > > Microsoft MVP -- ASP/ASP.NET
    > > Please reply to the newsgroup. The email account listed in my From
    > > header is my spam trap, so I don't check it very often. You will get a
    > > quicker response by posting to the newsgroup.
    > >
    > >[/ref]
    >
    >[/ref]


    Mark Guest

  7. #7

    Default Re: question about DELETE statement

    Yes, that works too.

    Mark Schupp wrote: [/ref][/ref]

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.


    Bob Guest

  8. #8

    Default Re: question about DELETE statement

    Even better would be to add a "type" column to the database and avoid this
    nonsense entirely.

    --
    Mark Schupp
    Head of Development
    Integrity eLearning
    www.ielearning.com


    "Bob Barrows [MVP]" <SPAMcom> wrote in message
    news:phx.gbl... [/ref]
    >
    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
    >
    >[/ref]


    Mark Guest

  9. #9

    Default Re: question about DELETE statement

    that's what i intended to do. Thanks for your advices ...


    "Mark Schupp" <com> wrote in message
    news:phx.gbl... 
    > >
    > > --
    > > Microsoft MVP -- ASP/ASP.NET
    > > Please reply to the newsgroup. The email account listed in my From
    > > header is my spam trap, so I don't check it very often. You will get a
    > > quicker response by posting to the newsgroup.
    > >
    > >[/ref]
    >
    >[/ref]


    Walter Guest

Similar Threads

  1. SQL error in DELETE statement
    By stillwaiting in forum Coldfusion Database Access
    Replies: 2
    Last Post: November 19th, 04:03 AM
  2. If statement question
    By Smriti Dev in forum PHP Development
    Replies: 3
    Last Post: December 8th, 11:12 PM
  3. Replies: 20
    Last Post: October 6th, 06:45 PM
  4. Very slow execution of a delete statement
    By Burkhard in forum IBM DB2
    Replies: 7
    Last Post: September 26th, 06:35 AM
  5. Replies: 3
    Last Post: September 5th, 04:16 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139