Fetching through table skips every other record

[reynolditpi]

I have the following (very basic) code attached to a button on a form (attached in zip):

dim tbl as p
tbl = table.open("demo",FILE_RW_SHARED)
tbl.fetch_first()
while .not. tbl.fetch_eof()
	
		tbl.change_begin(.t.)
		msgbox("",tbl.NAME)
		tbl.delete()
		tbl.change_end(.t.)
		
	tbl.fetch_next()
	
end while	
tbl.close()
athlete_Browse.refresh()
athlete_Browse.fetch_first()

The goal of this code is to delete all records in the table. However, it only deletes every other record for some reason. I have added the msgbox to see which record is getting deleted for testing purposes. The strange thing...if i remove tbl.delete() from the code, it stops skipping records.

This is a very trimmed down version of the code I am running in my really app. However, the rest of the code is not important to the problem. I thought I would mention this for those who would wonder why I would use this method to delete the records.

In the actual app, i have to read a value from each record before its deleted and use that value to find and delete records in other tables.

However, this doesnt completely work because of the record skipping.

Any thoughts on this?
Thanks
Tony

[Tbaker]

Re: Fetching through table skips every other record

Tony

With <tbl>.delete you don't need to fetch_next.

that is why it is deleting every other record.

Remove fetch_next and your script will delete all records

Tom Baker

[reynolditpi]

Re: Fetching through table skips every other record

You know Tom...Now that you say that, it makes perfect sense! The record is deleted so it has already moved to the next record. Duhh!

I have been banging my head against the wall for 2 days.

Lesson learned!
Thanks for your help.
Tony

[Tbaker]

Re: Fetching through table skips every other record

Tony

I want to caution you when using tbl.delete as you have in your example -

You are telling alpha to basically delete all records in the table - a better way to delete all records in the table is tbl.zap.

The reason I am cautioning is that I have never used tbl.delete to delete records the way that you show. If you do not want to delete all records then you could use tbl.delete_range after a query.

or use the <tbl>=table.current() to get the current record then use tbl.delete.

I am sure that you understand what I am saying and that your example was just that an example. Just a caution.

Tom Baker

[reynolditpi]

Re: Fetching through table skips every other record

Tom,
As I had mentioned, what I had posted was only a small part of the script. It was also a demo table.

In "real life" the user selects records by checking (setting to .t.) a logical field in the record.

The script then deletes just those records. However, there are multiple related records in other tables for each record deleted in the main table. There are also some jpg files stored in a folder for each record.

My intent was to delete the main records, all related records in the other tables, and the associated image files.

So, here is the complete script:

'Date Created: 13-Dec-2007 09:10:26 AM
'Last Updated: 02-Aug-2008 10:49:55 PM
'Created By  : Tony Reynolds
'Updated By  : Tony

'topparent.commit()
'athlete_browse.activate()
'athlete_browse.Commit()  'jic its in change mode
'Displays a message box (style:'OK, Cancel Buttons', image: 'Stop symbol', id: 'delete_client').]]

DIM SHARED  deleteclient_result as N
DIM SHARED  deleteclient_OK_Button as L 
DIM SHARED  deleteclient_CANCEL_Button as L 

deleteclient_OK_Button = .F.
deleteclient_CANCEL_Button = .F.

title_var = "Confirm Removal of Athletes"
message_text_var = <<%text%
Are you sure you want to delete these athletes?

This will completely remove them from the WCP 
database.  

If you only want to remove the athletes from 
this team, delete their team>position from 
the athletes "Team And Position" window.
%text%

deleteclient_result=ui_msg_box(title_var,message_text_var,UI_OK_CANCEL+ UI_SECOND_BUTTON_DEFAULT+ UI_STOP_SYMBOL)
'Test to see which button on the message box was pressed....
SELECT
	CASE deleteclient_result = UI_OK_SELECTED
		deleteclient_OK_Button= .t.
		
		
'************************************************************
'open the cl_team form. Get id and delete record.
'************************************************************		
dim clid as c
dim tbl as p
tbl = table.open("cl_team",FILE_RW_SHARED)

tbl.fetch_first()
tbl.batch_begin()
while .not. tbl.fetch_eof()
	
if tbl.Picked= .t. then
		clid = tbl.cl_id
		msgbox("",tbl.Cl_name)
		'***delete client for cl_team table
		tbl.change_begin(.t.)
		msgbox("",tbl.Cl_name)
		tbl.delete()
		tbl.change_end(.t.)
		
		
		'**************************
		'***open the clients table
		'**************************
		dim atbl as p
		atbl=table.open("clients",FILE_RW_SHARED)
		
		'***filter to client id
		atbl.index_primary_put("client_id")
		atbl.fetch_find(var->clid)

		
		'***delete the clients picture if it exists
		dim img as c
		img=atbl.Client_picture
		dim name as c
		dim bname as c
		dim fullpath as c
		name = word(img,-1,"\\",1)
		bname=word(name,1,chr(34))
		fullpath=a5.get_path()+chr(92)+"images"+chr(92)+"ClientPictures"+chr(92)+bname
		if file.exists(fullpath) then
			file.remove(fullpath)
		end if
		
		'***delete the client
		atbl.change_begin()
		atbl.delete()
		atbl.change_end(.t.)
		atbl.close()
								
		'**************************
		'***open the cl_exer table
		'**************************
		dim btbl as p
		btbl=table.open("cl_exer",FILE_RW_SHARED)
		btbl.delete_range("client_id="+s_quote(clid))
		btbl.close()
			
		'**************************
		'***open the cl_workout table
		'**************************
		dim ctbl as p
		ctbl=table.open("cl_wrkout",FILE_RW_SHARED)
		ctbl.delete_range("client_id="+s_quote(clid))
		ctbl.close()	
		
		'**************************
		'***open the cl_notes table
		'**************************
		dim dtbl as p
		dtbl=table.open("cl_notes",FILE_RW_SHARED)
		dtbl.delete_range("client_id="+s_quote(clid))
		dtbl.close()
			
end if	

	
end while	
tbl.batch_end()
tbl.close()
'************************************************************
'refresh the form
'************************************************************
athlete_Browse.refresh()
athlete_Browse.fetch_first()
			
	CASE deleteclient_result = UI_CANCEL_SELECTED
		deleteclient_CANCEL_Button= .t.
END SELECT

I am not for sure if I did this all correctly or not. It was the best I could come up with:)

[Tom Cone Jr]

Re: Fetching through table skips every other record

Tony, using the help file gets easier with practice. Your question is answered there. Check the index entry for <tbl>.delete().

-- tom

[reynolditpi]

Re: Fetching through table skips every other record

Tom,
Sure enough. Its right there! I did look at that page several times but must not have actually read it:)