Home » Developer & Programmer » Forms » DELETE using CHECKBOX
icon4.gif  DELETE using CHECKBOX [message #152433] Fri, 23 December 2005 02:30 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #152459 is a reply to message #152437] Fri, 23 December 2005 06:05 Go to previous messageGo to next message
manjuvasu
Messages: 22
Registered: May 2005
Junior Member
no idea on that any more.if u get the solution,send me.
Re: DELETE using CHECKBOX [message #152735 is a reply to message #152437] Mon, 26 December 2005 20:33 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #153487 is a reply to message #153069] Mon, 02 January 2006 21:52 Go to previous messageGo to next message
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
Senior Member
Account Moderator
Before your 'delete_record' use a 'delete' command with a where clause based on the values of the record that you are about to delete.

David
Re: DELETE using CHECKBOX [message #153489 is a reply to message #153487] Mon, 02 January 2006 22:03 Go to previous messageGo to next message
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 #153703 is a reply to message #153489] Tue, 03 January 2006 17:39 Go to previous messageGo to next message
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
Senior Member
Account Moderator
Check your 'quotes' to make sure that they are 'paired' correctly.

Put a 'display' line between your 'string' creation and 'string' usage statements.

Are you trying to delete entries that are currently being displayed? Please consider using 'not exists' instead of 'not in'.

David
Re: DELETE using CHECKBOX [message #153748 is a reply to message #153703] Tue, 03 January 2006 23:19 Go to previous messageGo to next message
aarontan78
Messages: 63
Registered: August 2005
Member
Thanks,

But when I replaced the STATEMENT with WHERE NOT EXISTS, no records were shown up, whereas there's records for the WHERE..NOT IN statement.
icon7.gif  Re: DELETE using CHECKBOX [message #153874 is a reply to message #152433] Wed, 04 January 2006 10:06 Go to previous message
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

Previous Topic: Oracle Report Help
Next Topic: re-populating poplist problem??
Goto Forum:
  


Current Time: Fri Sep 20 02:32:03 CDT 2024