Deleting records.. Plz help me ....

[Roel018]

Hi all..

I'm simply trying to delete the top most records of my database tabel.. I first get the ID's of the top records, but then I dunno what to do next... :-(


Here's my complete (TESTING-) page, while
testing I include the "recordTotalFlash"
var in the URL when calling the asp file:

==============================================


<%

Response.CacheControl = "no-cache"
Response.AddHeader "Pragma", "no-cache"
Response.Expires = -1

Dim DBConn, strDB, strInsertSQL, chatname, messagetxt, messagetime

strDB = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("data/messages.mdb")

recordTotalFlash=Request("recordstotal")

if recordTotalFlash>6 then
response.write "&Above_6"
strDELETESQL = "SELECT TOP 3 * FROM Messages ORDER BY id DESC"


Set DBConn = Server.CreateObject("ADODB.Connection")

DBConn.Open strDB
DBConn.execute strDELETESQL

DBConn.delete
DBConn.close
Set DBConn=Nothing
end if
%>


Hope someone can help me....
thnx
Roel

 

[Roel018]

==================================================
Woops.. wrong piece of code.. this is what I use:
==================================================


<%

Response.CacheControl = "no-cache"
Response.AddHeader "Pragma", "no-cache"
Response.Expires = -1

Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open "Driver=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("data/messages.mdb")

Set cmdTemp = Server.CreateObject("ADODB.Command")
Set Recordview = Server.CreateObject("ADODB.Recordset")

cmdTemp.CommandText = "Select TOP 4 * From Messages ORDER BY id DESC"
cmdTemp.CommandType = 1
Set cmdTemp.ActiveConnection = DataConn

Recordview.Open cmdTemp, , 1, 3


id=RecordView("id")
Response.Write "&" & id


TotalRecords=RecordView.RecordCount
Response.Write "&" & TotalRecords



Recordview.Close
Set RecordView = Nothing
DataConn.Close

%>

 

[Tarwn]

First, to satisfy a personal pet peeve of mine, remove the line that sets the Recordview variable = to Server.CreateObject("ADODB.CRecordset"). This is unnecessary since a new recordset is created by the Execute command and passe dback, replacing the fresh one you created earlier.

Next, you'll probably want to create a list of the id's from the recordset by looping through the Recordset and dumping the id's in a comma-delimited string.

Next you will want to take that string and build a DELETE SQL statement that looks something like: "DELETE FROM Messages WHERE id IN(" & yourIdsString & ")"
This (once it is executed) will delete the records based on the id's in your string you built in the previous string.

That should be it, hope it helps,
-T

 

[bhuninghake]

DELETE FROM Messages WHERE Id IN (SELECT TOP 3 Id FROM Messages ORDER BY id DESC)

 

[Roel018]

Thnx all !!!

It worked for me this way:

if recordTotalFlash>50 then
response.write "&SizeAbove50"
strDELETESQL = "DELETE * from Messages WHERE id < ((SELECT MAX(id) FROM Messages) - 36)"


Set DBConn = Server.CreateObject("ADODB.Connection")

DBConn.Open strDB
DBConn.execute strDELETESQL
DBConn.update
DBConn.close
Set DBConn=Nothing
end if


Thnx again !
R