PRB: Trouble Browsing Table That Has Indexed DateTime Field

ID: Q147587

The information in this article applies to:

SYMPTOMS

When you add the date and time to Datetime fields in a table and then set an index on that datetime field, the records of table cannot be accessed properly because of the index. After issuing a SET ORDER TO command to turn the index off, the table behaves correctly.


CAUSE

This seems to be a problem when the time of several fields is the same. When the datetime field has different times every so often, the index works correctly.


RESOLUTION

There can be a problem if the REPLACE command is issued to place dates and times in the datetime field of several records at the same time. When the REPLACE command is issued, the time of the datetime fields can all be the same time depending on the speed of the computer. If an index is issued on the datetime field, then the table cannot be browsed or sent to a certain record until the index is turned off. There are no errors when trying to move through the table; any action is just ignored. If you use the DATE() function to populate the Datetime field, nothing seems to go wrong even though the time of all Datetime fields will 12:00:00 AM, which is the default if no time is specified. This can also happen with the INSERT command when inserting several records at one time. Creating the index on the Datetime field before replacing or inserting the date and time will correct the problem.

Steps to Reproduce Behavior

Create a program and add the following code: 

CREATE TABLE datetime  (fld1 T)
FOR x = 1 to 200
  INSERT INTO datetime VALUES (DATETIME())
ENDFOR
INDEX on fld1 TAG fld1
BROWSE
Try paging down through the Browse window. After one or more pages scroll down, the browse stops; then paging up and down can only be done on that current page.

Move the line of code that indexes the table above the FOR loop. Now the browse will work correctly. If the index is created on the Datetime field before replacing or inserting the date and time, there seems to be no problem. If there is a pause between replacing or inserting to the Datetime field, the index also works correctly. For example, if a FOR loop is placed in the code below so that there is a small delay in time between replacing or inserting, the index works correctly.

Additional query words: 


Keywords          : kbVFp FxotherGeneral 
Version           : WINDOWS:3.0,3.0b
Platform          : WINDOWS 
Issue type        : kbprb

Last Reviewed: May 24, 1999