Recordset problem w/ Access 2000

  • Thread starter Thread starter Michael
  • Start date Start date
M

Michael

Is anyone familiar with the following problem :
A simple query on a simple table returning records more
than once .........
There are no duplicate records in the database yet the
recordset reports more than one ...
Could this possibly be a corrupt data file ???
How can I tell ?

Below is my query .. I have checked the table in Access
and there are NO DUPS but the output from this query -
sometimes lists the same record more than once ....
hmmmm .. does the locktype affect the cursor ......
response . Is it possible that the cursor is reporting a
partial record and then re-reporting a complete
record ... ???


aSSQL = "SELECT * FROM rte_steps WHERE rte_id_num =" &
rid & " ORDER BY rte_seq ASC"

Set nRs = Server.CreateObject("ADODB.recordset")
Set nRs.ActiveConnection = oConn
nRs.Source = aSSQL
nRs.LockType = 3

nRs.Open


scratch="<TR><TD><strong>Site</strong></TD><TD><strong>Acti
on</strong></TD><TD><strong>Arrive/Start Time
</strong></TD><TD><strong>End
Time /Depart</strong></TD><TD><strong> Days of Week
</strong></TD><TD><strong> passenger code </strong></TD>"

response.write(scratch)

scratch = ""

do While Not nRs.EOF

routenumber = nRs.Fields(1).value
rtedesc = nRs.Fields(2).value

action = nRs.Fields("rte_activity")
action_start = nRs.Fields("rte_activ_start_time")
action_end= nRs.Fields("rte_activ_end_time")
psngr_code= nRs.Fields("passenger_code")
rte_DOW= nRs.Fields("rte_DOW")
rte_site= nRs.Fields("rte_site")
rtestep = nRs.Fields("rte_seq")

contline = 0
appendline = 0

if (psngr_code = "0") Then
psngr_code = " "
End If

if(action = "Arrive") Then
scratch = "<TR><B><TD>" & rte_site & "</TD><TD>" &
action &"</TD><TD>" & action_start & "</TD><TD> " &
action_end & "- Depart </TD></B>"
scratch = scratch & "<TD><i>" & rte_DOW
& "</i></TD><TD><B>" & psngr_code & "</B></TD>"
Else
scratch = scratch & "<TR><TD> </TD><TD>" & action
& "</TD><TD>" & action_start & "</TD><TD> " & action_end
& "</TD>"
End If

Response.write(scratch & "</TR>")

nRs.MoveNext

Loop

nRs.Close
 
Hi,


It is possible, with some cursors and database, that the cursor (or
recordset) sees the same record more than once, in the scenario where
another process (or the actual one) also play with the same records, mainly,
changing them (if you have "The Guru's Guide to Transact-SQL", by Ken
Henderson, as example, take a loot at the "Halloween Problem", the problem
occur while the process, alone, is the only one, but modify records on a
dynamic cursor ). This may occur if you, or another process, update the
non-clustered key, which, in turn, may move the record "forward" in the
table and thus, your cursor (recordset) may see the same record a second
time. In addition to the dynamic recordset, I suspect the read-only
forward-only may, in some circumstances, revisit a modified record for,
basically, the same reason. So, yes, it is possible.... BUT ...

If neither you, neither another process ( application using the same db)
updates (append or delete on) the table, then, I am unaware of a problem. If
this is really important, try using a snapshot, in any cases, since it is a
"photo" of the records at a given moment, it won't be influenced by other
processes.



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top