PRB: Trouble Browsing Table That Has Indexed DateTime Field

The information in this article applies to:

• Microsoft Visual FoxPro for Windows, versions 3.0 and 3.0b

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.

WORKAROUND

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.

STATUS

Microsoft is researching this behavior and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

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

Move the line of code that indexes the table above the FOR loop. Now the browse will work correctly.