Deleting curent record

Discussion in 'ASP General' started by xool, Sep 11, 2003.

  1. xool

    xool Guest

    Hi all, can anyone tell me how to delete just the current record

    if tempdate >= todaydate then
    Set newMail=Server.CreateObject("CDONTS.newMail")
    newMail.to = rs.fields("Email")
    newMail.From = ""
    newMail.Subject = "Wavestation Present Planner"
    newMail.Body = "Dear " & rs.fields("qName") & vbcrlf & vbcrlf & "This is
    your present planner reminder." & vbcrlf & vbcrlf & "It's " &
    rs.fields("Period") & " before " & rs.fields("qpName") & "'s birthday
    which falls on " & rs.fields("qDate") & "." & vbcrlf & vbcrlf & "Your
    message: " & rs.fields("Message") & vbcrlf & vbcrlf & "Regards" & vbcrlf
    & vbcrlf & "The Wavestation Team."
    newMail.Send

    conn.Execute "DELETE FROM Bmemo" <-------------------- ?????????
    end if
    rs.movenext


    many thanks




    ---
    Outgoing mail is certified Virus Free.
    Checked by AVG anti-virus system (http://www.grisoft.com).
    Version: 6.0.516 / Virus Database: 313 - Release Date: 02/09/2003
    xool, Sep 11, 2003
    #1
    1. Advertising

  2. Is there a primary key in your table? If so, you can say

    conn.execute("DELETE Bmemo WHERE [primaryKeyColumn] = " &
    rs("primaryKeyColumn"))

    Though I seriously question your methodology here... you're sending an
    e-mail out within every iteration of your recordset loop? There are more
    efficient ways to do this...





    "xool" <> wrote in message
    news:...
    > Hi all, can anyone tell me how to delete just the current record
    >
    > if tempdate >= todaydate then
    > Set newMail=Server.CreateObject("CDONTS.newMail")
    > newMail.to = rs.fields("Email")
    > newMail.From = ""
    > newMail.Subject = "Wavestation Present Planner"
    > newMail.Body = "Dear " & rs.fields("qName") & vbcrlf & vbcrlf & "This

    is
    > your present planner reminder." & vbcrlf & vbcrlf & "It's " &
    > rs.fields("Period") & " before " & rs.fields("qpName") & "'s birthday
    > which falls on " & rs.fields("qDate") & "." & vbcrlf & vbcrlf & "Your
    > message: " & rs.fields("Message") & vbcrlf & vbcrlf & "Regards" &

    vbcrlf
    > & vbcrlf & "The Wavestation Team."
    > newMail.Send
    >
    > conn.Execute "DELETE FROM Bmemo" <-------------------- ?????????
    > end if
    > rs.movenext
    >
    >
    > many thanks
    >
    >
    >
    >
    > ---
    > Outgoing mail is certified Virus Free.
    > Checked by AVG anti-virus system (http://www.grisoft.com).
    > Version: 6.0.516 / Virus Database: 313 - Release Date: 02/09/2003
    >
    >
    Aaron Bertrand - MVP, Sep 11, 2003
    #2
    1. Advertising

  3. xool

    Kris Eiben Guest

    Do you have an ID field, or a field or group of fields that define a
    unique row? Get that out of the recordset, close it, and then delete
    where that field = that value.

    "xool" <> wrote in message
    news:...
    > Hi all, can anyone tell me how to delete just the current record
    >
    > if tempdate >= todaydate then
    > Set newMail=Server.CreateObject("CDONTS.newMail")
    > newMail.to = rs.fields("Email")
    > newMail.From = ""
    > newMail.Subject = "Wavestation Present Planner"
    > newMail.Body = "Dear " & rs.fields("qName") & vbcrlf & vbcrlf &

    "This is
    > your present planner reminder." & vbcrlf & vbcrlf & "It's " &
    > rs.fields("Period") & " before " & rs.fields("qpName") & "'s

    birthday
    > which falls on " & rs.fields("qDate") & "." & vbcrlf & vbcrlf & "Your
    > message: " & rs.fields("Message") & vbcrlf & vbcrlf & "Regards" &

    vbcrlf
    > & vbcrlf & "The Wavestation Team."
    > newMail.Send
    >
    > conn.Execute "DELETE FROM Bmemo" <-------------------- ?????????
    > end if
    > rs.movenext
    >
    >
    > many thanks
    >
    >
    >
    >
    > ---
    > Outgoing mail is certified Virus Free.
    > Checked by AVG anti-virus system (http://www.grisoft.com).
    > Version: 6.0.516 / Virus Database: 313 - Release Date: 02/09/2003
    >
    >
    Kris Eiben, Sep 11, 2003
    #3
  4. xool

    xool Guest

    Hi, no I don't have an ID field, any way of just deleting the current row?

    "Kris Eiben" <> wrote in message
    news:O$Bqt$...
    > Do you have an ID field, or a field or group of fields that define a
    > unique row? Get that out of the recordset, close it, and then delete
    > where that field = that value.
    >
    > "xool" <> wrote in message
    > news:...
    > > Hi all, can anyone tell me how to delete just the current record
    > >
    > > if tempdate >= todaydate then
    > > Set newMail=Server.CreateObject("CDONTS.newMail")
    > > newMail.to = rs.fields("Email")
    > > newMail.From = ""
    > > newMail.Subject = "Wavestation Present Planner"
    > > newMail.Body = "Dear " & rs.fields("qName") & vbcrlf & vbcrlf &

    > "This is
    > > your present planner reminder." & vbcrlf & vbcrlf & "It's " &
    > > rs.fields("Period") & " before " & rs.fields("qpName") & "'s

    > birthday
    > > which falls on " & rs.fields("qDate") & "." & vbcrlf & vbcrlf & "Your
    > > message: " & rs.fields("Message") & vbcrlf & vbcrlf & "Regards" &

    > vbcrlf
    > > & vbcrlf & "The Wavestation Team."
    > > newMail.Send
    > >
    > > conn.Execute "DELETE FROM Bmemo" <-------------------- ?????????
    > > end if
    > > rs.movenext
    > >
    > >
    > > many thanks
    > >
    > >
    > >
    > >
    > > ---
    > > Outgoing mail is certified Virus Free.
    > > Checked by AVG anti-virus system (http://www.grisoft.com).
    > > Version: 6.0.516 / Virus Database: 313 - Release Date: 02/09/2003
    > >
    > >

    >
    >



    ---
    Outgoing mail is certified Virus Free.
    Checked by AVG anti-virus system (http://www.grisoft.com).
    Version: 6.0.516 / Virus Database: 313 - Release Date: 02/09/2003
    xool, Sep 11, 2003
    #4
  5. > Hi, no I don't have an ID field, any way of just deleting the current row?

    If you don't have a way to uniquely identify a single row, no. This is one
    of the reasons a primary key is an essential part of any sane table design.
    Aaron Bertrand - MVP, Sep 11, 2003
    #5
  6. xool

    Ray at Guest

    Eegs

    How about instead of trying to do that, execute one DELETE query after your
    loop is finished and use the same WHERE clause that you used to create the
    recordset? Of course, if a new record is inserted during this loop, that'll
    get deleted, but if you're using timestamps or anything, you could add the
    current date and time onto your WHERE clause in DELETE where the time would
    be set at the time your first recordset is created. Does this make sense?
    Example:

    (Assuming SQL)
    Set rs = objADO.Execute("SELECT GETDATE(),
    ,[qpName],[qDate],[Message],[Period] FROM [Bmemo] WHERE
    [Something]='a value'")
    sTime = rs.fields.item(0).value
    '''your looping code here
    rs.Close
    Set rs = Nothing

    objADO.Execute "DELETE FROM [Bmemo] WHERE [Something]='a value' AND
    [DateAddedToTheDatabase]<='" & sTime & "'"

    Ray at work



    "xool" <> wrote in message
    news:%...[color=blue]
    > Hi, no I don't have an ID field, any way of just deleting the current row?
    >
    > "Kris Eiben" <> wrote in message
    > news:O$Bqt$...[color=green]
    > > Do you have an ID field, or a field or group of fields that define a
    > > unique row? Get that out of the recordset, close it, and then delete
    > > where that field = that value.
    > >
    > > "xool" <> wrote in message
    > > news:...[color=darkred]
    > > > Hi all, can anyone tell me how to delete just the current record
    > > >
    > > > if tempdate >= todaydate then
    > > > Set newMail=Server.CreateObject("CDONTS.newMail")
    > > > newMail.to = rs.fields("Email")
    > > > newMail.From = ""
    > > > newMail.Subject = "Wavestation Present Planner"
    > > > newMail.Body = "Dear " & rs.fields("qName") & vbcrlf & vbcrlf &[/color]
    > > "This is[color=darkred]
    > > > your present planner reminder." & vbcrlf & vbcrlf & "It's " &
    > > > rs.fields("Period") & " before " & rs.fields("qpName") & "'s[/color]
    > > birthday[color=darkred]
    > > > which falls on " & rs.fields("qDate") & "." & vbcrlf & vbcrlf & "Your
    > > > message: " & rs.fields("Message") & vbcrlf & vbcrlf & "Regards" &[/color]
    > > vbcrlf[color=darkred]
    > > > & vbcrlf & "The Wavestation Team."
    > > > newMail.Send
    > > >
    > > > conn.Execute "DELETE FROM Bmemo" <-------------------- ?????????
    > > > end if
    > > > rs.movenext
    > > >
    > > >
    > > > many thanks
    > > >
    > > >[/color][/color][/color]
    Ray at, Sep 11, 2003
    #6
  7. xool

    Kris Eiben Guest

    No, afaik there is no way to delete a row as you loop through it,
    somehow knowing which row you're on just because it's "current." (I'm
    sure someone will correct me if I'm wrong -- Aaron, Ray, I'm looking in
    your general direction.) You have to be able to somehow identify the
    row from the data in the row.

    So, you don't have an ID field. Surely, though, you have some kind of
    check in place to keep users from entering duplicate data -- use the
    fields you check there. If you don't have such a check in place, it's
    time to put one in.

    "xool" <> wrote in message
    news:#...
    > Hi, no I don't have an ID field, any way of just deleting the current

    row?
    >
    > "Kris Eiben" <> wrote in message
    > news:O$Bqt$...
    > > Do you have an ID field, or a field or group of fields that define a
    > > unique row? Get that out of the recordset, close it, and then

    delete
    > > where that field = that value.
    > >
    > > "xool" <> wrote in message
    > > news:...
    > > > Hi all, can anyone tell me how to delete just the current record
    > > >
    > > > if tempdate >= todaydate then
    > > > Set newMail=Server.CreateObject("CDONTS.newMail")
    > > > newMail.to = rs.fields("Email")
    > > > newMail.From = ""
    > > > newMail.Subject = "Wavestation Present Planner"
    > > > newMail.Body = "Dear " & rs.fields("qName") & vbcrlf & vbcrlf

    &
    > > "This is
    > > > your present planner reminder." & vbcrlf & vbcrlf & "It's " &
    > > > rs.fields("Period") & " before " & rs.fields("qpName") & "'s

    > > birthday
    > > > which falls on " & rs.fields("qDate") & "." & vbcrlf & vbcrlf &

    "Your
    > > > message: " & rs.fields("Message") & vbcrlf & vbcrlf &

    "Regards" &
    > > vbcrlf
    > > > & vbcrlf & "The Wavestation Team."
    > > > newMail.Send
    > > >
    > > > conn.Execute "DELETE FROM Bmemo" <--------------------

    ?????????
    > > > end if
    > > > rs.movenext
    > > >
    > > >
    > > > many thanks
    > > >
    > > >
    > > >
    > > >
    > > > ---
    > > > Outgoing mail is certified Virus Free.
    > > > Checked by AVG anti-virus system (http://www.grisoft.com).
    > > > Version: 6.0.516 / Virus Database: 313 - Release Date: 02/09/2003
    > > >
    > > >

    > >
    > >

    >
    >
    > ---
    > Outgoing mail is certified Virus Free.
    > Checked by AVG anti-virus system (http://www.grisoft.com).
    > Version: 6.0.516 / Virus Database: 313 - Release Date: 02/09/2003
    >
    >
    Kris Eiben, Sep 11, 2003
    #7
  8. > No, afaik there is no way to delete a row as you loop through it,
    > somehow knowing which row you're on just because it's "current." (I'm
    > sure someone will correct me if I'm wrong -- Aaron, Ray, I'm looking in
    > your general direction.)


    Well, there might be a way to use rs.delete in this case, but I'm sure it is
    RDBMS-implementation-specific. For example, in SQL Server, you should get
    an "insufficient key information" error, because rs.delete translates to a
    DELETE statement, and without a key, there is no way to know which row(s)
    the delete statement applies to.

    > So, you don't have an ID field. Surely, though, you have some kind of
    > check in place to keep users from entering duplicate data -- use the
    > fields you check there. If you don't have such a check in place, it's
    > time to put one in.


    Agreed. Or, if duplicates are okay, at least have a surrogate key (like
    IDENTITY/AUTOINCREMENT) to feign sequence. Sometimes the only unique part
    about a row might be, for example, the body of an e-mail message, in an
    NTEXT column. You don't want to use that to identify a row. :)
    Aaron Bertrand - MVP, Sep 11, 2003
    #8
  9. xool

    xool Guest

    Ok, thanks all. I've decided to approach it a different way, just stuck with
    a data type mismatch now if any one can help (see new thread)

    many thanks


    "Aaron Bertrand - MVP" <> wrote in message
    news:...
    > > No, afaik there is no way to delete a row as you loop through it,
    > > somehow knowing which row you're on just because it's "current." (I'm
    > > sure someone will correct me if I'm wrong -- Aaron, Ray, I'm looking in
    > > your general direction.)

    >
    > Well, there might be a way to use rs.delete in this case, but I'm sure it

    is
    > RDBMS-implementation-specific. For example, in SQL Server, you should get
    > an "insufficient key information" error, because rs.delete translates to a
    > DELETE statement, and without a key, there is no way to know which row(s)
    > the delete statement applies to.
    >
    > > So, you don't have an ID field. Surely, though, you have some kind of
    > > check in place to keep users from entering duplicate data -- use the
    > > fields you check there. If you don't have such a check in place, it's
    > > time to put one in.

    >
    > Agreed. Or, if duplicates are okay, at least have a surrogate key (like
    > IDENTITY/AUTOINCREMENT) to feign sequence. Sometimes the only unique part
    > about a row might be, for example, the body of an e-mail message, in an
    > NTEXT column. You don't want to use that to identify a row. :)
    >
    >



    ---
    Outgoing mail is certified Virus Free.
    Checked by AVG anti-virus system (http://www.grisoft.com).
    Version: 6.0.516 / Virus Database: 313 - Release Date: 01/09/2003
    xool, Sep 11, 2003
    #9
    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. Harry Barker
    Replies:
    2
    Views:
    507
    Alf P. Steinbach
    Apr 19, 2006
  2. bthumber

    Deleting a record from a database

    bthumber, Jul 20, 2009, in forum: ASP .Net
    Replies:
    9
    Views:
    356
    Gregory A. Beamer
    Jul 23, 2009
  3. mldardy
    Replies:
    0
    Views:
    468
    mldardy
    Oct 4, 2010
  4. Kumar

    deleting DNS record using dnscmd.exe

    Kumar, Oct 14, 2004, in forum: ASP .Net Web Services
    Replies:
    0
    Views:
    160
    Kumar
    Oct 14, 2004
  5. crea
    Replies:
    2
    Views:
    399
    Nobody
    Dec 28, 2012
Loading...

Share This Page