Home » Developer & Programmer » Forms » updating using forms
updating using forms [message #258037] Thu, 09 August 2007 22:16 Go to next message
alister
Messages: 101
Registered: August 2007
Location: India
Senior Member
I created a form which should insert into a table if the record is not found and updated it if it is already there. But it is always inserting a new record into the table. So I made one field unique. Now it says record already inserted or Unable to insert, and does not update the fields that I have updated. Can anyone help me.

Alister
Re: updating using forms [message #258039 is a reply to message #258037] Thu, 09 August 2007 23:00 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Please read the sticky associated with this forum. Tell us the version of Forms and Database that you are using, and post the code, inside 'code' tags, that you are using.

I expect that you are populating data fields in a 'when-new-record-instance' trigger instead of in the 'when-create-record' trigger, and that you may be populating database fields in the 'post-query' trigger even though they are already populated. Please review your code for these two possibilities and get back to us.

David
Re: updating using forms [message #258958 is a reply to message #258039] Mon, 13 August 2007 22:20 Go to previous messageGo to next message
alister
Messages: 101
Registered: August 2007
Location: India
Senior Member
Dear David

I am using Forms 5.0.6 and Oracel 8.1.7.

I am enclosing the code below

key_next_item of :adjfschdr.empcode
BEGIN
DECLARE
L_NO NUMBER;
BEGIN
SELECT COUNT(EMPCODE) INTO L_NO FROM ADJFSCHDR WHERE EMPCODE=:adjfschdr.empcode;
IF L_NO=0 THEN 
 SELECT BILLUNIT,EMPNAME,RLYJOINDATE,PAYRATE,SCALECODE,DESIGNATION 
 into :adjfschdr.billunit,:adjfschdr.empname,:adjfschdr.dateofappointment,:adjfschdr.basicrate,
      :adjfschdr.scalecode,:adjfschdr.designation
 FROM AFPBLEMP
  WHERE EMPNO=:adjfschdr.empcode
  AND PAYBILLID IN (SELECT MAX(PAYBILLID) FROM AFPBLEMP
  WHERE EMPNO=:adjfschdr.empcode AND PAYBILLID LIKE '200%');

BEGIN
   select deptcode,birthdate,ticketno,empgroup
   into :adjfschdr.department,:adjfschdr.dateofbirth,:adjfschdr.staffno,
   :adjfschdr.empgroup
   from prmaemp 
   WHERE EMPno=:adjfschdr.empcode;
       EXCEPTION
	  WHEN NO_DATA_FOUND THEN
	    NULL;
END;
   SELECT SHORTDESC INTO 
   :adjfschdr.department
   from afgencod
   where codetype='DT'
   AND CODE=:adjfschdr.department;
ELSE
SELECT billunit, empcode, staffno, empname, department,
       dateofappointment, basicrate, scalecode, designation,
       dateofbirth, dateofsecondment, yearsofservice, officeorder,
       corporatebody, dateofrepatriation,empgroup
  INTO :adjfschdr.billunit, :adjfschdr.empcode, :adjfschdr.staffno, :adjfschdr.empname, 
       :adjfschdr.department,:adjfschdr.dateofappointment, :adjfschdr.basicrate,
       :adjfschdr.scalecode, :adjfschdr.designation,
       :adjfschdr.dateofbirth, :adjfschdr.dateofsecondment, :adjfschdr.yearsofservice,
       :adjfschdr.officeorder,:adjfschdr.corporatebody, :adjfschdr.dateofrepatriation,
       :adjfschdr.empgroup
  FROM adjfschdr
  WHERE EMPCODE=:adjfschdr.empcode;
END IF;
     :adjfschdr.yearsofservice:=round(MONTHS_BETWEEN( sysdate, :adjfschdr.dateofappointment) / 12,1);
END;
END;
--------
I the record is already availabe in the Adjfschdr table it will display it, otherwise it will fetch it from other tables. If it is a new record it gets updated into adjfschdr table. If it is not a new record, then only the corrected fields should get updated. But since empcode is unique it says FRM-40508 - ORACLE error Unable to insert record. On the save button I gave only commit.


Alister

[Updated on: Tue, 14 August 2007 02:16] by Moderator

Report message to a moderator

Re: updating using forms [message #259010 is a reply to message #258958] Tue, 14 August 2007 02:27 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
What are you doing!!!

The 'accepted' way of doing what you are trying to do is to have an 'empcode' defined to a 'control' block and then, to place the value into 'where' clause of your data block and do an 'execute-query' on it. You then use the 'post-query' trigger to populate fields for an existing record, and the 'when-create-record' trigger to populate fields for a record which does not exist.

David
Re: updating using forms [message #259016 is a reply to message #259010] Tue, 14 August 2007 02:45 Go to previous messageGo to next message
alister
Messages: 101
Registered: August 2007
Location: India
Senior Member
I am new to Oracle, so I may be doing things in a crude way. Thanks David, I shall try your suggestion.


Alister
Re: updating using forms [message #259018 is a reply to message #259016] Tue, 14 August 2007 02:57 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
If you are new to Oracle then I suggest that you download and run some of the tutorials and demonstration forms on the Oracle Forms web site.

David
Re: updating using forms [message #259538 is a reply to message #259018] Wed, 15 August 2007 22:30 Go to previous message
alister
Messages: 101
Registered: August 2007
Location: India
Senior Member
I have tried your suggestion of creating a control block on empcode and then did the execute query. I worked. Thank you very much. I shall download some of the demos and study them

Alister
Previous Topic: What This Forms6i Built-in Procedure Do
Next Topic: Footer & headers in excel using ole2
Goto Forum:
  


Current Time: Sat Dec 10 10:36:21 CST 2016

Total time taken to generate the page: 0.08427 seconds