Results 1 to 7 of 7
  1. #1
    Newbie
    Join Date
    October 15th, 2008
    Location
    San Diego
    Posts
    46
    Records not getting deleted
    I have a SQL query in my ASP code to delete all records from Ms access databse where date=today.

    what happens is I do not see the records on my webpage, but they do no get deleted from the database. And next day, I see those records back on the page. Here is my code:



    Code:
    <%
    Dim Conn
    Dim rs
    Dim sql
    
    Set Conn = Server.CreateObject("ADODB.Connection")
    
    Conn.Provider = "Microsoft.Jet.OLEDB.4.0"
    Conn.ConnectionString = "Data Source=" & Server.MapPath ("db.mdb")
    Conn.Open
    
    Set rs = Server.CreateObject("ADODB.Recordset") 
    sql = "SELECT record.* FROM record WHERE date=Date();" 
    Rs.Open sql, Conn, 1,3
    Do while not rs.EOF
    rs.delete
    rs.MoveNext
    Loop
    rs.close %>
    How can I get the records to be deleted permanently from the database?

  2. #2
    ABW Ambassador
    Join Date
    November 25th, 2005
    Posts
    639
    I don't know why it doesn't work - it sounds like it shows on the site because you are testing for something that is date dependent (it is not actually deleted today it is just not displaying).

    Try using a more direct delete command like:

    Dim rs
    Dim sql
    Dim strDate = date()

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

    Conn.Provider = "Microsoft.Jet.OLEDB.4.0"
    Conn.ConnectionString = "Data Source=" & Server.MapPath ("db.mdb")
    Conn.Open

    sql = "DELETE * FROM record WHERE date = " & strDate
    Set recordset = Conn.Execute(sql)


    Make sure that date() returns the date in the exact same date format as the db date field.

    Dave





    Quote Originally Posted by akinak
    I have a SQL query in my ASP code to delete all records from Ms access databse where date=today.

    what happens is I do not see the records on my webpage, but they do no get deleted from the database. And next day, I see those records back on the page. Here is my code:



    Code:
    <%
    Dim Conn
    Dim rs
    Dim sql
    
    Set Conn = Server.CreateObject("ADODB.Connection")
    
    Conn.Provider = "Microsoft.Jet.OLEDB.4.0"
    Conn.ConnectionString = "Data Source=" & Server.MapPath ("db.mdb")
    Conn.Open
    
    Set rs = Server.CreateObject("ADODB.Recordset") 
    sql = "SELECT record.* FROM record WHERE date=Date();" 
    Rs.Open sql, Conn, 1,3
    Do while not rs.EOF
    rs.delete
    rs.MoveNext
    Loop
    rs.close %>
    How can I get the records to be deleted permanently from the database?

  3. #3
    Newbie
    Join Date
    October 15th, 2008
    Location
    San Diego
    Posts
    46
    Dave,

    Thanks for replying. You are right..it does not get deleted, but is not displaying.

    anyways..I tried what you said, but even that doesn't works.

    any other help would be really appreciated. thanks

  4. #4
    ABW Ambassador
    Join Date
    November 25th, 2005
    Posts
    639
    I bet it is date format related. I don't use MS Access much but I know that in MySQL there is a common problem when you declare a field as "Date". It automatically defaults the field contents to the MySQL date format. So you are probably comparing "2009/3/25" with something like "03-25-2009". So nothing will ever match (or get deleted). Try setting your DB date field to a general text field (or something like that).

    This link may help a bit... http://www.mvps.org/access/datetime/date0005.htm

    Best of luck!
    Dave

  5. #5
    Newbie
    Join Date
    October 15th, 2008
    Location
    San Diego
    Posts
    46
    Dave,

    Thanks for the link. Date format was the problem here. It now runs perfectly.

    One more question - I am currently deleting records for date=current date. Can you help me with a SQL query where I can delete records for date=current date-1?

  6. #6
    ABW Ambassador
    Join Date
    November 25th, 2005
    Posts
    639
    I was afraid that was coming next (grin).

    It is the same query but you need some code to figure out what the current date is (if it is the 1st of the month then yesterday was the last day of the last month). I have not found an elegant solution like strDatedate=((today)-1) too bad though...

    I use this for the back end in my CMS that I built so I have never pushed it too far - sorry!

    Dave

  7. #7
    Newbie
    Join Date
    October 15th, 2008
    Location
    San Diego
    Posts
    46
    he he

    how did you figure..it was coming next?

    anyways...thanks for the reply..but I am not much of a coder..just trying my best to do whatever I can.

  8. Newsletter Signup

+ Reply to Thread

Similar Threads

  1. deleted
    By donsteitz in forum Midnight Cafe'
    Replies: 0
    Last Post: April 20th, 2002, 06:15 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •