Home » Developer & Programmer » Forms » DATE issues in Forms
DATE issues in Forms [message #282954] Sun, 25 November 2007 09:31 Go to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
when i insert date in text item for example..01-JUN-07 and press enter ,it changes the date to 01-JUN-0007.
I have to take two date arguments and use them in when button pressed trigger.

[MERGED and RENAMED by LF: I have merged your topics that talk about the same problem: DATE issues in Forms Builder]

[Updated on: Sun, 25 November 2007 11:30] by Moderator

Report message to a moderator

Re: date format [message #282959 is a reply to message #282954] Sun, 25 November 2007 09:49 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Open item's Property Palette and set its Format Mask to reflect your desire.
Re: date format [message #282960 is a reply to message #282954] Sun, 25 November 2007 09:57 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Thanks Littlefoot.
FRM-40508 [message #282962 is a reply to message #282954] Sun, 25 November 2007 11:13 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
I have two text item that take dates.
Code used in WHEN BUTTON PRESSED trigger:
declare 
tcode varchar2(4);
   vno   varchar2(9);
   recno varchar2(3);
 srcmjcd varchar2(4);
   pnplan char(1);
   vcharged char(1);
   ddocode varchar2(4);
   grantno number(4);
   grossamt number(16,2):=0;
   grossamt1 number(16,2):=0;
    fs char(1);
  rttype char(3);
    dday number(2):=0;
 mjcd varchar(4);
 sno number:=1;
 vdate date;
 vdate1 date;

cursor c is select trea_code,rec_no,substr(v_no,2,4) hd,v_no,v_date,p_nplan,v_charged,
ddo_code,grant_no,gross_amt
from treasury.bill_ent
where
v_date between :bill_ent.v_date and :bill_ent1.v_date
and grant_no not in('REC','PAC','PEN')
order by v_no;
begin
for var in c
loop
dday:=to_number(to_char(vdate,'DD'));
      if dday>11 then
       fs:='S';
      else
       fs:='F';
      end if;
if var.rec_no='103' then
         rttype:='CON';
      end if;
      if var.rec_no='102' then
         rttype:='TA';
      end if;
      if var.rec_no='105' then
         rttype:='MIS';
      end if;
      if var.rec_no='101' then
         rttype:='EST';
      end if;
      if var.rec_no='104' then
         rttype:='DEP';
      end if;
      if var.rec_no='106' then
         rttype:='PEN';
      end if;
insert into treasury.INTR_VCH_TMP_HDR 
values('01',sno,substr(var.trea_code,1,2),substr(var.v_no,2,4),fs,var.v_no,var.v_date,
var.v_charged,var.gross_amt,0,var.ddo_code,var.p_nplan,'','','',rttype,to_number(var.grant_no),'','N'
,'','',var.gross_amt,'Remark','','Y','Y','N','Y','');
sno:=sno+1;
end loop;
commit;
end;

When i click the button it gives this error:
ORA-01400: cannot insert NULL into ("TREASURY"."BILL_ENT"."TREA_CODE")

When i run this code in sqlplus,it worked fine and inserted the data in the desired table.
There are no null values inserted in any not null column

But the same code in forms gives :
FRM-40508:Oracle error:Unable to insert record
Re: FRM-40508 [message #282963 is a reply to message #282962] Sun, 25 November 2007 11:27 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Please, format the code. Formatting doesn't mean "using [code] tags" only; this is unreadable.

Use debugger (if your version of Developer Suite supports it) or use MESSAGE to see what's really in the variable:
for var in c
loop
  MESSAGE('var.trea_code = ' || var.trea_code);
If it is null, well, Oracle was right and you were wrong. Cause is probably in
where v_date between :bill_ent.v_date and :bill_ent1.v_date
  and grant_no not in ('REC', 'PAC', 'PEN')

What are ':bill_ent.v_date' and ':bill_ent1.v_date'? What is their datatype? It looks like a DATE, but - is it, really? If it is a CHARACTER which looks like a date, you'll have to use the TO_DATE function; something like
WHERE v_date between to_date(:bill_ent.v_date, 'dd.mm.yyyy') and ...
I've used 'dd.mm.yyyy' format just as an example; you'd use yours, of course.
Re: DATE issues in Forms [message #282965 is a reply to message #282954] Sun, 25 November 2007 11:41 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Bill_ent and bill_ent1 are blocks and v_date is an item.
I used data block wizard to create two data blocks and layout editor to create two text item.
shift-f1 shows:
sql statement in error:
INSERT INTO BILL_ENT(V_DATE) VALUES (:1)  

error:
ORA-01400: cannot insert NULL into ("TREASURY"."BILL_ENT"."TREA_CODE")

[Updated on: Sun, 25 November 2007 12:03]

Report message to a moderator

Re: DATE issues in Forms [message #282969 is a reply to message #282965] Sun, 25 November 2007 12:32 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Your last post brought nothing new. We already know all that. Why didn't you answer my questions? Why didn't you try what I suggested?

Until you do that, I'm afraid I can't help you any further.
Re: DATE issues in Forms [message #282971 is a reply to message #282954] Sun, 25 November 2007 13:07 Go to previous message
varu123
Messages: 754
Registered: October 2007
Senior Member
I added the code:
message('trea_code=' || var.trea_code);
it prompted with the trea_code values
I also tried
message('v_date=' || :bill_ent.v_date);
it showed the date i enter at runtime

IN this error code:
sql statement in error:
INSERT INTO BILL_ENT(V_DATE) VALUES (:1)
Why the value is :1 for v_date?

[Updated on: Sun, 25 November 2007 13:16]

Report message to a moderator

Previous Topic: proplem in displaying user id and date in the form
Next Topic: web forms 6i on IIS: client browser closed automatically
Goto Forum:
  


Current Time: Wed Dec 07 20:49:42 CST 2016

Total time taken to generate the page: 0.18988 seconds