How can I Autodelete one record from table when record is 3 months after insertion-date?

Rodrigo, did you receive the second email I sent to you (so not the one where I asked you to post it here in the forum, but the second one)?

Waldo Smeets - www.UDzone.com Webmaster
------------------------------------------
www.UDzone.com : A Dreamweaver, Ultradev and Fireworks recourse site for developers
by developers.
------------------------------------------

Waldo,

Do you have a solution ?
I would like to receive it alswell :-)

an already thanking you,

Jezter

Not yet, but we consider working on it.
Somehow Rodrigo's email keeps bouncing back to me, so I can;t get more info from him any more <img src=icon_smile_sad.gif border=0 align=middle>

Waldo Smeets - www.UDzone.com Webmaster
------------------------------------------
www.UDzone.com : A Dreamweaver, Ultradev and Fireworks recourse site for developers
by developers.
------------------------------------------

If you are using SQL Server its easy to do:

1. Make a stored procedure that deletes the old records.
Normally this will be just one DELETE query, with DATEDIFF to specify which
records
2. Schedule the execution of the SP as a JOB that runs monthly.

You are done

Greetings,
George Petrov
www.UDzone.com

George,

I'm no hotspot at ASP,
but what do you think of this ?

It's a "purge-page", that will
delete all records when loaded
into the browser.


&lt;%

set cPurge = Server.CreateObject("ADODB.Command"
cPurge.ActiveConnection = "dsn=coe;"
cPurge.CommandText = "DELETE FROM event WHERE start1 &lt; Date()-30"
cPurge.CommandType = 1
cPurge.CommandTimeout = 0
cPurge.Prepared = true
cPurge.Execute()

%&gt;


greetz

Jezter/Bjorn

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
&lt;%

set cPurge = Server.CreateObject("ADODB.Command"
cPurge.ActiveConnection = "dsn=coe;"
cPurge.CommandText = "DELETE FROM event WHERE start1 &lt; Date()-30"
cPurge.CommandType = 1
cPurge.CommandTimeout = 0
cPurge.Prepared = true
cPurge.Execute()

%&gt;
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

The Date() is not an SQL command,
You should use the DATEDIFF command in your SQL statement used on the Recordset query. For example:

DELETE FROM ... WHERE DATEDIFF(m,date_field,GETDATE()) &lt;= 3

Will delete the items with date_field value within the last 3 months.

DELETE FROM ... WHERE DATEDIFF(m,date_field,GETDATE()) &gt; 3 AND DATEDIFF(m,date_field,GETDATE()) &lt;= 6

Will delete the records between 3 and 6 months old.

You can read more on DATEDIFF on msdn.microsoft.com/library/psdk/sql/ts_da-db_5vxi.htm



Greetings,
George Petrov
www.UDzone.com

George,

I can understand the code
but will it work with a simple
Access file ?

Jezter/Bjorn

It should - I thought Access also supports the DATEDIFF command.
Otherwise you will have to do it with the more stupid:

WHERE date_field BETWEEN #01-31-00# AND #03-31-00#

because you will have to generate those days yourself.
And this will work only on Access - you can't upgrade later on to SQL Server - then you should use the DATEDIFF

Greetings,
George Petrov
www.UDzone.com

George,

Thanks for your replies.
I'll try them both this evening.

greetz

Jezter/Bjorn

George,

I tried some of you suggestions ..
Actually this is the code I used


&lt;%@LANGUAGE="VBSCRIPT"%&gt;
&lt;!--#include file="Connections/deletedatum.asp" --&gt;
&lt;%

set ddatum = Server.CreateObject("ADODB.Command"
ddatum.ActiveConnection = MM_deletedatum_STRING
ddatum.CommandText = "DELETE FROM Autozoekertjes WHERE DATEDIFF(m,fDatum,GETDATE()) &lt;= 3"
ddatum.CommandType = 1
ddatum.CommandTimeout = 0
ddatum.Prepared = true
ddatum.Execute()

%&gt;

The result was this error :

Microsoft OLE DB Provider for ODBC Drivers fout '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Undefined function 'GETDATE' in expression.

/test.asp, regel 11

Can you help me define this function ?
I would appreciate it a lot

greetz

Jezter/Bjorn

In Access you should use DATE() instead of GETDATE()
GETDATE() is for SQL Server only.

Greetings,
George Petrov
www.UDzone.com