Delete range of entries from database!!

[priyammaheshwari]

Hi All

I am creating a database archiving tool,in which a user can delete all the data before a date he would specify.But the problem is i am not able to create a delete query which can delete a range of data.

I had created the following query

<% 
pdt=request.form("prevdate")   'contains the date starting from which data has to be deleted
cdt=request.form("currdate")    'contains the date till which data has to be deleted
' declaring variables
' not neccesary but a good habit
Dim DataConn
Dim CmdDeleteRecord
Dim MYSQL
 
Set DataConn = Server.CreateObject("ADODB.Connection")
 
 
Set CmdDeleteRecord = Server.CreateObject("ADODB.Recordset")
 
' The line below shows how to use a system DSN instead of a DNS-LESS connection
' DataConn.Open "DSN=System_DSN_Name"
DataConn.Open "DBQ=" & Server.Mappath("../fpdb/taxireq.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};"
 
MYSQL = "DELETE FROM Request_detail WHERE Repdate between '" & pdt &"' and '" & cdt &"'"
 
CmdDeleteRecord.Open MYSQL, DataConn
 
' closing objects and setting them to nothing
' not neccesary but a good habit
DataConn.Close
Set DataConn = Nothing
%>
 

please suggest any changes so that i can delete a range of data from the database

Thank You!!

[omerbutt]

Change Your Current Code with this one

' DataConn.Open "DSN=System_DSN_Name"
DataConn.Open "DBQ=" & Server.Mappath("../fpdb/taxireq.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};"
 
sql_chk="select * from  Request_detail where Repdate between '" & pdt &"' and '" & cdt &"';"
CmdDeleteRecord.open sql_chk,DataConn,3
total_records=CmdDeleteRecord.recordcount
for i=1 to total_records
MYSQL = "DELETE FROM Request_detail WHERE Repdate between '" & pdt &"' and '" & cdt &"'"
CmdDeleteRecord.Open MYSQL, DataConn
loop
DataConn.Close
Set DataConn = Nothing
 

and yes if you do post any questions next time do remember to tell what problem are you having pointing out the exact line in the code
Regards,
Omer Aslam