Deleting curent record

[xool]

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 = "(e-mail address removed)"
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

 

[Aaron Bertrand - MVP]

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

 

[Kris Eiben]

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]

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

 

[Aaron Bertrand - MVP]

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.

 

[Ray at]

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

 

[Kris Eiben]

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.

 

[Aaron Bertrand - MVP]

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.

 

[xool]

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