Expert: Ashley Brazier - 2/25/2009

Question
I have a very strange error that I'm getting. I assume it is from the delete function because all my other database related code pages work perfectly. Here is the background. I am using Access 2003 as the database and ASP for coding. The code is deisgned to create a schedule for a given day. The code first looks to see if there is data for that date (i.e. updating the schedule rather than creating a new schedule). If it finds data for that date, it first deletes all those records before saving the new information. The delete code is the following:


  sql="Select * FROM [Daily_Assignments] WHERE [Date] = '" & x & "'"
  Set rs = Server.CreateObject("ADODB.Recordset")
   rs.Open sql, cn, adOpenDynamic,adLockPessimistic  
   Do While not rs.eof
      rs.delete
      rs.movenext
   loop
   rs.close
   set rs = nothing
   cn.close
   set cn = nothing

Now here's the problem...When I go to look at the schedule, somehow the index gets screwed up and the page prints the schedule out of order. What makes this really strange is that if I look at the database directly, the records are in the correct place and if I repair the database, the page prints correctly! So it's not the code on the printing page and it's not the saving data function since the data is saved correctly. So I have to assume That the process of deleting the old records somehow screws  up the internal index system. This is driving me crazy! Any ideas what might cause this (BTW it doesn't happen everytime so I can't redily debug it).

Ron Levy

Answer
Hi,

You say your index are getting screwed up? Are the primary keys? Any chance of sending a sample of the data or database itself? (Could I download it from anywhere)

Off the top of my head I can't seem to think of any reasons why this might be happening.

Cheers