Deleting Records

[Meekso]

So what I want to accomplish is...

When a user wants to delete a record in current grid, I want to open another table and delete all the records that are associated with the row that is going to be deleted.

So I put the following code in the CanDeleteRecord

function CanDeleteRecord as v (DataSubmitted as P, Args as p, PageVariables as p, Result as P)
with PageVariables

'added code
dim tbl_records as p
dim sid as n
sid = DataSubmitted.STOREID

tbl_records = table.open("task_records")

tbl_records.fetch_first()
while .not. tbl_records.fetch_eof()
	if (tbl_records.tr_store_id = sid) then
		tbl_records.change_begin(.t.)
		tbl_records.delete()
		tbl_records.change_end(.t.)
	end if
	tbl_records.fetch_next()
end while

	
tbl_records.close()
'end added code

Result.Cancel = .f.
Result.ErrorHTML = ""
end with

end function

When I open the table to check if the records associated with the STOREID are deleted, they are still there.

I think it may have something to do with the way I'm reading in the STOREID. (STOREID is unique to each row in the current grid)

Any help will be greatly appreciated.

[Meekso]

Re: Deleting Records

I've have noticed that this deletes every other record that matches the STOREID.

So maybe it is something wrong with the way the code is written?

Am I putting the code in the right spot?

[aschone]

Re: Deleting Records

I can't test now (no time) but I believe that after a record delete the record pointer automatically jumps to the next record. Hence why you delete every other record because after you delete the record you issue a jump record statement (effectively jumping two records).

[Al Buchholz]

Re: Deleting Records

I've have noticed that this deletes every other record that matches the STOREID.

So maybe it is something wrong with the way the code is written?

Am I putting the code in the right spot?

A record delete automagically moves the record pointer to the next record.

So when you delete a record don't move the pointer also.

See (note the note)

http://wiki.alphasoftware.com/~alpha....DELETE+Method

[aschone]

Re: Deleting Records

You may want to try using http://wiki.alphasoftware.com/TBL.DELETE_RANGE+Method

[Meekso]

Re: Deleting Records

So when I omit the last fetch call, because delete automatically fetches the next record my code is as follows:

dim sid as n
dim tbl_records as p

sid = DataSubmitted.Storeid
tbl_records = table.open("task_records")

tbl_records.fetch_first()
while .not. tbl_records.fetch_eof()
	if tbl_records.tr_store_id = sid then
		tbl_records.change_begin()
		tbl_records.delete()
		tbl_records.change_end(.t.)
	end if
end while

	
tbl_records.close()

But now the code doesn't delete anything.
So for my case I'm thinking about using the delete_range method. My code would look something along the lines of...

dim sid as n
dim tbl_records as p

sid = DataSubmitted.Storeid
tbl_records = table.open("task_records")
query.filter = "Tr_store_id = sid "
query.order = "Recno()"
????? qry_ca_cust = tbl.query_create() ?????
tbl.delete_range()
tbl.close()

But my only question is what does the following line of code actually do and how do I change it for my scenario?

qry_ca_cust = tbl.query_create()

[Al Buchholz]

Re: Deleting Records

So what I want to accomplish is...

When a user wants to delete a record in current grid, I want to open another table and delete all the records that are associated with the row that is going to be deleted.

So I put the following code in the CanDeleteRecord

function CanDeleteRecord as v (DataSubmitted as P, Args as p, PageVariables as p, Result as P)
with PageVariables

'added code
dim tbl_records as p
dim sid as n
sid = DataSubmitted.STOREID

tbl_records = table.open("task_records")

tbl_records.fetch_first()
while .not. tbl_records.fetch_eof()
	if (tbl_records.tr_store_id = sid) then
		tbl_records.change_begin(.t.)
		tbl_records.delete()
		tbl_records.change_end(.t.)
	end if
	tbl_records.fetch_next()
end while

	
tbl_records.close()
'end added code

Result.Cancel = .f.
Result.ErrorHTML = ""
end with

end function

When I open the table to check if the records associated with the STOREID are deleted, they are still there.

I think it may have something to do with the way I'm reading in the STOREID. (STOREID is unique to each row in the current grid)

Any help will be greatly appreciated.

	if (tbl_records.tr_store_id = sid) then
		tbl_records.change_begin(.t.)
		tbl_records.delete()
		tbl_records.change_end(.t.)
     else
	tbl_records.fetch_next()
	end if

[Meekso]

Re: Deleting Records

how did I overlook that?! Thanks Al! Appreciate it! Also thanks Andrew for helping out on this too!

[aschone]

Re: Deleting Records

But my only question is what does the following line of code actually do and how do I change it for my scenario?

qry_ca_cust = tbl.query_create()

http://wiki.alphasoftware.com/TBL.QUERY_CREATE+Method

Try Al's suggestion first.

However here is a correction to your alternative code. Since "sid" is a variable you need the system to replace "sid" with its value hence why it needs to be outside the quotation marks. Based on the type of data that sid contains the replaced value may or may not need to be surrounded by quotes. So you can use the function s_quote() to handle that. In this case "sid" is a numeric and does not need to be surrounded by quotes hence why i left it out. When I am building filter strings I use the interactive window to test the string value verifying that I have my quotations correct. I also will open a browse and use the "Query Genie" to build the filter then I attempt to match its output with my filter building string.

dim sid as n
dim tbl_records as p

sid = DataSubmitted.Storeid
tbl_records = table.open("task_records")
query.filter = [COLOR="#FF0000"]"Tr_store_id = " + sid[/COLOR]
query.order = "Recno()"
qry_ca_cust = tbl.query_create()
tbl.delete_range()
tbl.close()

[Meekso]

Re: Deleting Records

Al's suggestion worked flawlessly. Again thank you to both Al and Andrew!

[aschone]

Re: Deleting Records

Glad you got it sorted.

[Al Buchholz]

Re: Deleting Records

Al's suggestion worked flawlessly. Again thank you to both Al and Andrew!

The delete_range may be faster, but your mileage may vary.

Always good to have options.

Using an index can help too.

It does depend how many records you have and how many are deleted.