Hey all,

Just like to say first off: Thanks to everyone, i couldn't survive without you guys.

Okay here is my problem. I have a set of scripts for posting online ads. One particular script is supposed to find all of the records that are past 30 days old and delete them. I figured with would be easy, but no luck so far. Here is my script:

<%

Dim query


Set dbGlobalWeb = Server.CreateObject("ADODB.Connection")
dbGlobalWeb.ConnectionTimeout = 15
dbGlobalWeb.CommandTimeout = 30
dbGlobalWeb.Open "DBQ=" & Server.MapPath("emarket.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};DriverId=25;MaxBufferSize=8192;Threads=20 ;", "username", "password"


query = "DELETE * FROM listings WHERE TO_DAYS(NOW()) - TO_DAYS(DatePosted) > '30'"


dbGlobalWeb.Execute query
%>

<center>
Cleanup completed successfully!<br><br>
<a href="admin_cp.asp">Back to Control Panel </a>
</center>

<%
dbGlobalWeb.Close
Set con = Nothing
%>


But I keep getting errors about the TO_DAYS in the query. I used that from a reference on this page: http://www.phpbuilder.com/mail/php-db/2001051/0084.php. I know it was for php, but I thought it would translate to ASP. Any help is greatly appreciated.

Michael W. Vollmer

__________________
Michael W. Vollmer

Hi
First Your Write this query to global.asa
'''''''''''''''''''''''''''''''''''''''''''''
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Application_OnStart
        Application("Cnn_ConnectionString") = "DSN=emarket;DBQ=c:\database\emarket.mdb;DriverId= 281;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
        Application("Cnn_ConnectionTimeout") = 15
        Application("Cnn_CommandTimeout") = 30
        Application("Cnn_CursorLocation") = 3
        Application("Cnn_RuntimeUserName") = ""
        Application("Cnn_RuntimePassword") = ""
End Sub
</SCRIPT>
'''''''''''''''''''''''''''''''
and make a DSN

1.Open the ODBC icon in your Control Panel.
2.Choose the System DSN tab.
3.Click on Add in the System DSN tab.
4.Select the Microsoft Access Driver. Click Finish.
5.In the next screen, click Select to locate the database.
6.Give the database a Data Source Name (DSN).
7.Click OK.
'''''''''''''''''''''''''''''
then u can write a asp page
<%
set cnn=Server.CreateObject("ADODB.connection")
cnn.Open Application("Cnn_connectionstring")
query = "DELETE * FROM listings WHERE TO_DAYS(NOW()) - TO_DAYS(DatePosted) > '30'"
cnn.Execute(query)
%>




S.S

I cannot use a DSN connection in my pages. I have everything else working fine without one. Is a DSN the only way that this will work? I have to have a DSN-less connection due to the way the server I am using is setup.

Michael W. Vollmer

No, you don't need a DSN. You just need to know about the DateDiff function!

As an aside, you really should use the Access OLEDB driver from ASP (see http://www.able-consulting.com/ADO_Conn.htm for examples), not the ODBC driver which you are using.

hello mvollmer
yes you need to use DateDiff function
(TO_DAYS(NOW()) - TO_DAYS(DatePosted) > '30'")
try to use DateDiff function.

surendran