table's record countr indicates 5 records but table displays 8 records

[teajean]

Hello all!

Here's my program:

Note: Beforehand, I created my structural index with:
INDEX ON ALLTRIM(clname)+ALLTRIM(cfname)+ALLTRIM(cmname)+ ALLTRIM(DTOC(dbdate)) TAG namebirth

In my formset's LOAD event, i wrote:
SELECT myTable
SET ORDER TO namebirth

Here's the program I wrote for my cmdSave.Click. It finds duplicate records first before saving records to my table:

* Assume that gcChoice = 'A' when my Add button was pressed or 'E' when my Edit button was pressed.

* Assume that gnRecNo = RECNO()

LOCAL lclname as Character, ; && Last Name
lcfname as Character, ; && First Name
lcmname as Character, ; && Middle Name
ldbdate as Date, ; && Birth Date
lcDupFlag as Character && Duplicate Flag

* Initialiaze values
lclname = ALLTRIM(.txtLName.Value)
lcfname = ALLTRIM(.txtFName.Value)
lcmname = ALLTRIM(.txtMName.Value)
ldbdate = .txtBDate.Value
lcDupFlag = '0'

* Search duplicate record
SEEK lclname+lcfname+lcmname+ALLTRIM(DTOC(ldbdate))
IF FOUND() THEN
IF (gcChoice == "E" .AND. RECNO() != gnRecNo) THEN
lcDupFlag = '1'
ELSE
lcDupFlag = '0'
IF gcChoice == 'A' THEN
lcDupFlag = '1'
ENDIF
ENDIF
ENDIF

IF lcDupFlag == '1' THEN
WAIT WINDOW 'Record already exists! Unable to add record.'
GO gnRecNo
ELSE
IF gcChoice == 'A' THEN
APPEND BLANK
ELSE
GO gnRecNo
ENDIF
REPLACE clname WITH lclname
REPLACE cfname WITH lcfname
REPLACE cmname WITH lcmname
REPLACE dbdate WITH ldbdate

WAIT WINDOW 'No duplicate record found! Record has been saved.'
ENDIF

RELEASE lclname, lcfname, ;
lcmname, ldbdate, ;
lcDupFlag

* End of program

My code works fine at the first few attempts I make, whether in Add or Edit mode. But my problem is that after sometime, everything goes wrong because when I attempt to add a record that I know is already present in the table, that record is appended to it. Thus, I have 2 duplicate records in my table already. If there is a problem with my code, how come it runs fine at first then goes wrong after some time?

As loss of power is somewhat frequent in our neighborhood, my PC always shuts down improperly as I've no UPS. I guess there is something wrong with my table so I recreated it again from scratch to ensure that everything is fresh and clean. Then I executed my program again. Unfortunately, I encountered the same problem I've stated earlier. Then one day, while I was running my program, I typed BROWSE in my command window to see all the contents of my table. There, I found that there are "virtual" records in them. I say "virtual" because when I attempt to scroll down or click to select such a record/s, the record pointer does not move accordingly as if that or those records are not really there. Also, the record counter indicates there are 5 records in the table but when you browse it, it displays 8 records - the other 3 are the virtual ones.

I figured I should try to reindex my table again with:
INDEX ON ALLTRIM(clname)+ALLTRIM(cfname)+ALLTRIM(cmname)+ ALLTRIM(DTOC(dbdate)) TAG namebirth
, then browsed my table. The virtual records disappeared. So, I added my reindexing code to my cmdSave.Click code. I deleted the duplicate entries and executed the program. It started to work fine from then on. But I'm afraid that I might have the same problem again in the future. Is it possible that the real problem with my program is my lack of reindexing my table after I've saved new records? But I've read that when you use structural index files, they are automatically maintained as you add, change, or delete table records? Have I got it wrong? If it's not about reindexing my table, any other ideas?

Please help! Thanks very much in advance! By the way, if you don't get what I mean about the virtual records, I would appreciate it if you could email me at teajean0000@yahoo.com and I'll be glad to send you my table so you could see it.. Have a nice day! ;0)

 

[Mike Gagnon]

teajean

Is your table buffered? If so, are you using a TableUpdate() to save your record?

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[teajean]

hi mgagnon!
no, unfortunately i'm not using any table buffering that i know of. i've read about it but i haven't tried using it yet.. but i'm going to read more about it again.

i still need help.. thanks again and good day.

 

[teajean]

mgagnon,
i forgot to include my vfp version. it's vfp 7. also, i would like to thank you for the thread you pointed me to. i'll do my best to be a better forum member next time.

thea

 

[Mike Gagnon]

teajean
Is it possible that the real problem with my program is my lack of reindexing my table after I've saved new records? But I've read that when you use structural index files, they are automatically maintained as you add, change, or delete table records? Have I got it wrong? If it's not about reindexing my table, any other ideas?

No, not a lack of re-indexing, since once your table is indexed the first time, and you add another record, the index file gets updated automatically. But your problem seems to be an index issue. If the values that you seek are right in SEEK lclname+lcfname+lcmname+ALLTRIM(DTOC(ldbdate)) and you know there is a record that matches this, then your problem might be a corrupted index. I would suggest these things:

[ol][li]I would suggest you consider using buffering on your data.[/li]
[li]If you have a power issue where your are, you might consider investing in a UPS Battery if your data is vital.[/li]
[li]If not the your only option would be to create a re-indexing routine and schedule so maintenance routine on your tables/[/li][/ol]



Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[craigsboyd]

I have seen SEEKs affected by the collating sequence as well. SEEKs will not always behave as expected with certain concatenated index keys... I have seen the issue when switching a table from MACHINE to GENERAL before.

Slighthaze = NULL
_{craig1442@mchsi.com}

"Whom computers would destroy, they must first drive mad." - Anon​

 

[teajean]

mgagnon,

I'll follow your pieces of advice most especially no. 1. thanks very much. I'm already working on my reindexing routine before i wrote, so that takes care of some of my problems. thanks very much again.

slighthaze,

Thanks very much for the idea.

Have a good day all!

 

[craigsboyd]

teajean,

For a reindexing routine you may want to look at my post in:

How Do I get an Array of Index Expressions for all CDX files
thread184-755689

...near the bottom of that thread I have placed code to save all index information for tables in a directory to a table (Indexes.dbf) and then later use that table to recreate the indexes on your tables.

Slighthaze = NULL
_{craig1442@mchsi.com}

"Whom computers would destroy, they must first drive mad." - Anon​

 

[teajean]

slighthaze,

Thanks very much for the code. I'm going to review it and use it in my programs.
Have a good day!

thea