OraFAQ Forum: Forms » Updating a Record through Form

Home » Developer & Programmer » Forms » Updating a Record through Form (10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Updating a Record through Form [message #527172]

Sun, 16 October 2011 03:23

shanie
Messages: 118
Registered: January 2011

Senior Member

Dear All,

I tried to update a record through oracle form.
Record is updating but instead of one, two records are updated with same data.

First I enter Student ID then It fetches the data from database table. Then I modify the data and click on Update command button.

It gives message i record applied and saved.
But in database two records with identical data is coming.

Update saturn.GZBGSSP set                     
		GZBGSSP_LEVEL = :GZBGSSP_LEVEL,
		GZBGSSP_DEG_PLAN_IND = :GZBGSSP_DEG_PLAN_IND,
		GZBGSSP_DEG_PLAN_DATE = :GZBGSSP_DEG_PLAN_DATE,
		GZBGSSP_COM_EXAM_IND = :GZBGSSP_COM_EXAM_IND, 
		GZBGSSP_COM_EXAM_DATE = :GZBGSSP_COM_EXAM_DATE,
		GZBGSSP_FT_RESD_IND = :GZBGSSP_FT_RESD_IND,
		GZBGSSP_FT_RESD_DATE = :GZBGSSP_FT_RESD_DATE
		Where GZBGSSP_STU_ID = :DUAL.KEY_ID and GZBGSSP_LEVEL = :dual.GZBGSSP_LEVEL; 
Commit;

Please Help!

Thanks & Regards,
Shanie

Report message to a moderator

Re: Updating a Record through Form [message #527176 is a reply to message #527172]

Sun, 16 October 2011 05:36

ranamirfan
Messages: 535
Registered: January 2006
Location: Pakistan / Saudi Arabia

Senior Member

ranamirfan@gmail.com

Try to Use Forms default update behaviour.

Regards,
Irfan

Report message to a moderator

Re: Updating a Record through Form [message #527178 is a reply to message #527176]

Sun, 16 October 2011 05:50

cookiemonster
Messages: 13931
Registered: September 2008
Location: Rainy Manchester

Senior Member

I assume you're using selects to populate the block rather than execute query?
If so then the second record will come from forms inserting the data in the block. If you put data in a database block forms will assume it's a new record and insert it.
As Irfan says - use forms default behaviour - execute query to get the data and save to update.

Report message to a moderator

Re: Updating a Record through Form [message #527211 is a reply to message #527178]

Mon, 17 October 2011 01:01

Baranor
Messages: 83
Registered: September 2011
Location: Netherlands

Member

... I think he means he fetches his record, modifies it and then saves, and then he gets two. But I am not sure. Do you do it like that? i.e Execute query - modifiy- commit, and then you have two? If so, you've added extra programming somewhere, either in the forms or as a trigger in the DB, because records normally do not clone themselves.

Report message to a moderator

Re: Updating a Record through Form [message #527222 is a reply to message #527211]

Mon, 17 October 2011 01:42

ranamirfan
Messages: 535
Registered: January 2006
Location: Pakistan / Saudi Arabia

Senior Member

ranamirfan@gmail.com

Quote:

If so, you've added extra programming somewhere, either in the forms or as a trigger in the DB

Why didn't you use forms default behaviour.No need to write extra code.
Try it.
Quote:

Execute query to get the data against Student ID and Save to update.

Regards,
Irfan

Report message to a moderator

Re: Updating a Record through Form [message #527233 is a reply to message #527222]

Mon, 17 October 2011 03:01

Baranor
Messages: 83
Registered: September 2011
Location: Netherlands

Member

So Irfan, why did you quote me? Seeing that I added a comment and this is not my thread, and the quote you took is actually part of a question I made to the OP?

Report message to a moderator

Re: Updating a Record through Form [message #527598 is a reply to message #527176]

Wed, 19 October 2011 03:47

shanie
Messages: 118
Registered: January 2011

Senior Member

Dear All

Actually I need to update a record for example:
I enter Student ID and Level.Then I retrieve data based on SQL Query in Next Block. It Shows Details of Student.
I want to change in the field with new value then and there and click on save.But It is saving two records.
I remove Update command also.
In key commit Trigger I put only commit_form
By going through Steps Query->Enter Query ->Execute Query->Save
It is time consuming Process.

Please Help me

Thanks & Regards,
Shanie

Report message to a moderator

Re: Updating a Record through Form [message #527599 is a reply to message #527598]

Wed, 19 October 2011 03:51

cookiemonster
Messages: 13931
Registered: September 2008
Location: Rainy Manchester

Senior Member

shanie wrote on Wed, 19 October 2011 09:47

By going through Steps Query->Enter Query ->Execute Query->Save
It is time consuming Process.

However it is the correct process.
The reason for your problem is what I said above. Use execute query to get the data and the problem will go away.

Report message to a moderator

Re: Updating a Record through Form [message #527601 is a reply to message #527598]

Wed, 19 October 2011 03:53

Littlefoot
Messages: 21817
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

It is not "time consuming", it is the way it works.

If you are traveling from New York to London, you: go out of the apartment - get into a car - drive to the airport - buy a ticket - get into the airplane - fly - get out of the plane - go through the customs services - get out of the airport - drive to London. Is it time consuming? Yes. Can you avoid it? Most probably not.

Report message to a moderator

Re: Updating a Record through Form [message #528160 is a reply to message #527172]

Sat, 22 October 2011 06:09

shanie
Messages: 118
Registered: January 2011

Senior Member

Dear All,

Sorry for using phrase 'time consuming'.

I use set_block_property to populate the block.It is coming fine.
But If Level is selected different for same Student.The block is not populating.It is giving error " Query caused no records to be retrieved".

The Scenario is I enter Student ID and Level in One block.
After selecting Level through LOV I go to next block which populates data based on the above condition.(Works fine whose Level is single).
But If Student has Multiple Levels then Data is not populating in the Block.

I write code in When_new_block_instance Trigger

 DECLARE
 tGZBGSSP_STU_ID SATURN.GZBGSSP.GZBGSSP_STU_ID%TYPE; 
 BEGIN
Select  GZBGSSP_STU_ID
INTO 			tGZBGSSP_STU_ID
FROM 			SATURN.GZBGSSP 
WHERE 		GZBGSSP_STU_ID = :DUAL.KEY_ID; 
	  
IF SQL%FOUND THEN
SET_BLOCK_PROPERTY('DUMMY', DEFAULT_WHERE, 'GZBGSSP_STU_ID = ''' || :DUAL.KEY_ID||''''||' and ' || 'GZBGSSP_LEVEL = '''|| :DUAL.GZBGSSP_LEVEL||''' ');
go_block('DUMMY');
EXECUTE_QUERY;
GO_ITEM('GZBGSSP_DEG_PLAN_IND');
END IF;
EXCEPTION 
WHEN NO_DATA_FOUND THEN
go_block('DUMMY');	  						

:DUMMY.KEY_ID := :DUAL.KEY_ID;	  						:DUMMY.GZBGSSP_PIDM := :DUAL.GZBGSSP_PIDM;   							:DUMMY.GZBGSSP_LEVEL := :DUAL.GZBGSSP_LEVEL;
:DUMMY.GZBGSSP_DEG_PLAN_IND := 'Not Submitted';
:DUMMY.GZBGSSP_DEG_PLAN_DATE := '';
:DUMMY.GZBGSSP_COM_EXAM_IND := 'Not Applicable';
:DUMMY.GZBGSSP_COM_EXAM_DATE := '';
:DUMMY.GZBGSSP_FT_RESD_IND := 'Not Applicable';
:DUMMY.GZBGSSP_FT_RESD_DATE := '';

go_item('DUMMY.GZBGSSP_DEG_PLAN_IND');
WHEN OTHERS THEN								MESSAGE(SQLERRM, SQLCODE);
END;

Please Help!

Thanks & Regards,
Shanie

Report message to a moderator

Re: Updating a Record through Form [message #528178 is a reply to message #528160]

Sat, 22 October 2011 12:41

cookiemonster
Messages: 13931
Registered: September 2008
Location: Rainy Manchester

Senior Member

Show us an example of what default where is set to if there's multiple levels.

Report message to a moderator

Re: Updating a Record through Form [message #528179 is a reply to message #528178]

Sat, 22 October 2011 13:34

Littlefoot
Messages: 21817
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Quote:

1.
If Student has Multiple Levels then Data is not populating in the Block.

Quote:

2.

SELECT GZBGSSP_STU_ID
  INTO tGZBGSSP_STU_ID
  FROM SATURN.GZBGSSP 
  WHERE GZBGSSP_STU_ID = :DUAL.KEY_ID;

Quote:

3.

WHEN OTHERS THEN MESSAGE(SQLERRM, SQLCODE);

So: when SELECT finds "multiple levels" for a student, TOO-MANY-ROWS is raised. You capture it with WHEN OTHERS which says "don't do anything, just display a message". Besides, the way you used the MESSAGE built-in seems to be invalid (so I wouldn't be surprised if it raises another error), as it requires a message string and user response (and SQLCODE is not really user response). Though, can't test it right now.

The question is: what do you want to do when a student has multiple levels? Pick one? Pick none? Something different?

Report message to a moderator

Re: Updating a Record through Form [message #528194 is a reply to message #528179]

Sat, 22 October 2011 23:34

shanie
Messages: 118
Registered: January 2011

Senior Member

Actually When a Student has multiple levels then If I select one level from these then It should populate the block based on this Level along with Student ID other wise It should display the data which I written in When no_data_found exception.
The Problem is When a Student has multiple Levels It is allowing me to go next block after selecting one level.I then can fill and save the block. But for second time when I select same student with different level then It is not allowing me to go to next block giving message "Query caused no records to be retrieved.

I dont understand about this situation.
Please help.

Thanks & Regards,
Shanie

Report message to a moderator

Re: Updating a Record through Form [message #528205 is a reply to message #528194]

Sun, 23 October 2011 02:00

shanie
Messages: 118
Registered: January 2011

Senior Member

I solved the Problem.
In Query I missed condition based on Level.

SELECT GZBGSSP_STU_ID
  INTO tGZBGSSP_STU_ID
  FROM SATURN.GZBGSSP 
  WHERE GZBGSSP_STU_ID = :DUAL.KEY_ID  AND GZBGSSP_LEVEL = :DUAL.GZBGSSP;

Now Its working fine.

There is one little problem

In detail block.I select date through Lov (Calender).After Selecting Date When I click on OK Button. A dialog box is coming with message " Do you want to save changes you have made".
Dont know why It is coming in the middle

Thanks & Regards,
Shanie

Report message to a moderator

Re: Updating a Record through Form [message #528218 is a reply to message #528205]

Sun, 23 October 2011 03:38

cookiemonster
Messages: 13931
Registered: September 2008
Location: Rainy Manchester

Senior Member

It gives that message whenever you make changes to a database datablock and then do something that'll remove the record you've just changed
(clear_record, clear_block, execute_query etc).
What does the OK button do?

Report message to a moderator

Re: Updating a Record through Form [message #528220 is a reply to message #528218]

Sun, 23 October 2011 03:50

shanie
Messages: 118
Registered: January 2011

Senior Member

After Selecting the Date through LOV When I click "ok"
The Dialog Box is coming with Message "Do You want to Save Changes You made" With THREE Buttons "Yes", "No" ,"Cancel" are coming.
When I click on "Yes", then It is saving the record from that point along with the selected Date.
When I click "No" The Date is not coming in from LOV to that field.
When I click "Cancel", the Date is coming in from LOV to that field.

Thanks & Regards,
Shanie

Report message to a moderator

Re: Updating a Record through Form [message #528245 is a reply to message #528220]

Sun, 23 October 2011 14:33

cookiemonster
Messages: 13931
Registered: September 2008
Location: Rainy Manchester

Senior Member

You mean the OK button on the LOV?
What triggers do you have on the date item?

Report message to a moderator

Re: Updating a Record through Form [message #528268 is a reply to message #528245]

Mon, 24 October 2011 00:13

shanie
Messages: 118
Registered: January 2011

Senior Member

Yes ok Button on LOV.
No Triggers on Date Item.

Date is a Text Item. Besides this I place command button which calls LOV when clicks and put the selected date in Date Item after user selects.

Report message to a moderator

Re: Updating a Record through Form [message #528303 is a reply to message #528268]

Mon, 24 October 2011 03:39

cookiemonster
Messages: 13931
Registered: September 2008
Location: Rainy Manchester

Senior Member

Is the button in the same datablock as the date item?
Do you do anything in the form before pressing the button to call the LOV?
Run the form in debug mode and see what triggers fire when you call the LOV.

Report message to a moderator

Re: Updating a Record through Form [message #528319 is a reply to message #528303]

Mon, 24 October 2011 04:59

shanie
Messages: 118
Registered: January 2011

Senior Member

Yes Button is in same Data block as Date Item.
I just select one value for one different field and then press LOV to select Date.
I dont have this option of Running in Debug Mode.
As this compiled in Unix Environment.

Thanks & Regards,
Shanie

Report message to a moderator

Re: Updating a Record through Form [message #528324 is a reply to message #528319]

Mon, 24 October 2011 05:17

cookiemonster
Messages: 13931
Registered: September 2008
Location: Rainy Manchester

Senior Member

Then you'll just have to stick messages in every trigger in order to see what is firing.

Report message to a moderator

Re: Updating a Record through Form [message #528413 is a reply to message #528324]

Mon, 24 October 2011 14:39

Littlefoot
Messages: 21817
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

shanie

I dont have this option of Running in Debug Mode.

How come? You use Forms 10g, which offers the Debug option.

Report message to a moderator

Previous Topic:	auto email sent to user with attachment of PDF report generated by oracle developer (2 threads merge
Next Topic:	Set_item_property

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Aug 11 07:53:12 CDT 2024