DELETE using CHECKBOX [message #152433] |
Fri, 23 December 2005 02:30 |
aarontan78
Messages: 63 Registered: August 2005
|
Member |
|
|
I have a form that display 2 fields from a table.
I created a checkbox along the rows to allow user the check the checkbox and
click the delete button in order to delete the checked records.
May I know where should I put my delete statement? My delete statement
involves deleting from 3 tables.
I have no idea how to link the checkboxes to the corresponding record
rows...
may I have some ideas how should I do this?
Thanks.
|
|
|
Re: DELETE using CHECKBOX [message #152435 is a reply to message #152433] |
Fri, 23 December 2005 02:42 |
manjuvasu
Messages: 22 Registered: May 2005
|
Junior Member |
|
|
u can get the no.of records using get_block_property.
u would have assigned value to checkbox when checked and unchecked.
so, thru loop from first record to last record,give if condition inside loop as if :ch=1 then
u can give delete stmt.block should be non database block.
|
|
|
Re: DELETE using CHECKBOX [message #152437 is a reply to message #152435] |
Fri, 23 December 2005 02:50 |
aarontan78
Messages: 63 Registered: August 2005
|
Member |
|
|
I created this procedure for the delete statement
PROCEDURE delete_checked IS
BEGIN
go_block('BLOCK_A');
first_record;
LOOP
IF :BLOCK_A.CHECK_DEL = 'Y' THEN
DELETE FROM BLOCK_A
WHERE FIELD1 = :BLOCK_A.FIELD1;-- and
COMMIT;
END IF;
IF :SYSTEM.last_record='TRUE' THEN
EXIT;
ELSE
next_record;
END IF;
END LOOP;
END;
and in my DELETE BUTTON, I created WHEN-BUTTON-PRESS trigger:
delete_checked;
However, only one row is deleted...
Any idea? Thanks.
|
|
|
|
Re: DELETE using CHECKBOX [message #152735 is a reply to message #152437] |
Mon, 26 December 2005 20:33 |
|
djmartin
Messages: 10181 Registered: March 2005 Location: Surges Bay TAS Australia
|
Senior Member Account Moderator |
|
|
What happens if you put the 'commit' outside the loop? Also, depending on whether the 'delete_checked' procudere is in the database or the Form, use 'standard.commit', as 'commit' in a Form will not see any changes if they have not been done via a Forms command.
Also, as they have been deleted you need to do an 'Execute_Query' to refresh your display as the records in the block no longer exist in the database and they need to be removed from the display.
David
[Updated on: Mon, 26 December 2005 20:37] Report message to a moderator
|
|
|
Re: DELETE using CHECKBOX [message #153069 is a reply to message #152735] |
Wed, 28 December 2005 20:13 |
aarontan78
Messages: 63 Registered: August 2005
|
Member |
|
|
Thanks for all the reply,
I put this code in my delete button and it's working.
begin
go_block('block_1');
first_record;
while :system.record_status <> 'NEW' loop
if checkbox_checked('block_1.check_del') then
delete_record;
else
next_record;
end if;end loop;
end;
I have 2 more tables that link to table_1 (block_1)'s primary key.
How do I delete records from these 2 tables as well when I delete record from block_1 ?
Thanks in advance.
|
|
|
|
Re: DELETE using CHECKBOX [message #153489 is a reply to message #153487] |
Mon, 02 January 2006 22:03 |
aarontan78
Messages: 63 Registered: August 2005
|
Member |
|
|
Hi David,
Thanks for the reply.
I got it working.
I inserted this statement "WHERE item_2 IN (SELECT item_2 FROM table_2)" in the WHERE CLAUSE in the datablock and got it working as well.
However, when I changed the statement to WHERE... NOT IN, the records couldn't be queried out...but when I run the statement in TOAD, it gave me the correct records.
Any idea?
Thanks.
|
|
|
|
|
Re: DELETE using CHECKBOX [message #153874 is a reply to message #152433] |
Wed, 04 January 2006 10:06 |
vakkalan
Messages: 20 Registered: January 2006 Location: US
|
Junior Member |
|
|
Aarontan,
I am going to try to explain this quick because of the lack of time but here is what I propose and hope that may help you.
I am presuming that you have a multi record block in your forms with a checkbox beside each of these records and you have a delete button when pressed should delete all the checked records. If that is correct here is what i suggest --
1. Create a PL SQL table(coolection) in a forms package. Let us say you have the id's of the record hidden in the form.
2. In the package create 3 procs 1.add_id 2. delete_id 3. delete records, Procs 1 and 2 will have a number parameter passed to it.
3. Create a WHEN-CHECKBOX-CHANGED trigger and add code such as
if checkboc_checked then
add_id(primary_key)
else /* this is when we are removing the checked record*/
For i in 1..collection.count loop
delete_id(primary_key);
end loop;
end if; 4. To further help you this is what the code in add and delete looks like
/*Procedure to add the checked records to the collection*/
PROCEDURE ADD_ID(p_id NUMBER) IS
v_cur NUMBER;
BEGIN
v_cur := REC_PENDING.count ; /*rec_pending is a collection of number*/
REC_PENDING(v_cur+1) := p_id;
END ADD_ID;
/*Procedure to delete the unchecked records from the collection*/
PROCEDURE DELETE_ID(p_id NUMBER) IS
v_cur NUMBER;
BEGIN
FOR i in REC_PENDING.FIRST .. REC_PENDING.LAST LOOP
IF REC_PENDING(i) = p_id THEN
rec_pending.delete(i);
END IF;
END LOOP;
END DELETE_ID;
5. Once you have tested these collections with the tright population then you can loop thru and delete your records
This is much faster and cleaner than first record and last record and that would require the form to do unnecessary navigation.
Hope this helps
HARSHA VAKKALANKA
Upd-mod: Add code tags.
[Updated on: Wed, 04 January 2006 17:53] by Moderator Report message to a moderator
|
|
|