OraFAQ Forum: Forms » Cann't commit form when form status change from New to Query and to Changed

Home » Developer & Programmer » Forms » Cann't commit form when form status change from New to Query and to Changed (10g)

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

Cann't commit form when form status change from New to Query and to Changed [message #551394]

Tue, 17 April 2012 21:27

tieudieuphong
Messages: 10
Registered: April 2012
Location: Viet Nam

Junior Member

I cann't commit form when new instance form with form status is "NEW".

And then i call:
Text_Item := WebUtil_File.File_Selection_Dialog('', '', 'Excel 2003|*.xls|Excel 2010|*.xlsx|All File|*.*', 'Select a file to Import', Open_File, True);
Form status change to "QUERY"

And then i click button "Import Data" from excel file to Data Block. Now form status change to "CHANGED". But i cann't call "Commit_Form" built-in to insert data to database oracle 10g

Please help me!!!

Attachment: aa99.fmb
(Size: 192.00KB, Downloaded 1240 times)

Report message to a moderator

Re: Cann't commit form when form status change from New to Query and to Changed [message #551424 is a reply to message #551394]

Wed, 18 April 2012 03:20

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

Senior Member

I'm unsure what you are trying to do, and unfortunately your fmb won't open correctly for me.
What do you mean you can't call commit_form?

Report message to a moderator

Re: Cann't commit form when form status change from New to Query and to Changed [message #551444 is a reply to message #551424]

Wed, 18 April 2012 05:33

tieudieuphong
Messages: 10
Registered: April 2012
Location: Viet Nam

Junior Member

Thank you for reply.

I want to import data from excel file to oracle database through oracle form. I use WebUtil to browse the path excel file.

In database: i have a table EMPLOYEES with four field: EmpID (varchar2), Emp_Name (nvarchar2), DOB (Date), Sex (char).

CREATE TABLE EMPLOYEES
(
EMPID VARCHAR2(15 CHAR) DEFAULT '' NOT NULL,
EMP_NAME NVARCHAR2(50) DEFAULT '',
DOB DATE DEFAULT '01-Jan-1900',
SEX CHAR(1 CHAR) DEFAULT ''
)

In excel file: there are four column: EmpID | Emp_Name | DOB | SEX
(Sorry, because i cann't attach excel file - Allowed File Extensions: txt sql log lst csv pls plb gif jpg png pdf fmb fmt fmx ora ctl dat bad dsc pks pkb pck rdf)

On form: when i click button "..." to file excel file. After bind the excel path to item text, form status change to "Query". So, i use "Clear_Form" to change form statu to "New" and bind data from excel to the items text on form. Then status form change to "Changed" and data into data block. And then i commit form to save data on data block to oracle database. But i cann't commit. Perhaps i missed a condition....?

Please help me!! Thanks.

Report message to a moderator

Re: Cann't commit form when form status change from New to Query and to Changed [message #551445 is a reply to message #551444]

Wed, 18 April 2012 05:40

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

Senior Member

Does using standard.commit work?

Why don't you use sqlloader instead? It's whole purpose is load data from files into the DB.

Report message to a moderator

Re: Cann't commit form when form status change from New to Query and to Changed [message #551460 is a reply to message #551445]

Wed, 18 April 2012 08:39

tieudieuphong
Messages: 10
Registered: April 2012
Location: Viet Nam

Junior Member

Thank you for reply.

Because it is required by my client and application. I will try to use standard.commit.

Thank you very much.

Report message to a moderator

Re: Cann't commit form when form status change from New to Query and to Changed [message #551464 is a reply to message #551445]

Wed, 18 April 2012 09:04

owais_baba
Messages: 289
Registered: March 2008
Location: MUSCAT

Senior Member

so many examples are available here read data from excel sheet by using wenutil search this forum
one of them


DECLARE 
   application    Client_OLE2.Obj_Type; 
   workbooks      Client_OLE2.Obj_Type; 
   workbook       Client_OLE2.Obj_Type; 
   worksheets     Client_OLE2.Obj_Type; 
   worksheet      Client_OLE2.Obj_Type;
   worksheet2     Client_OLE2.Obj_Type;   
   cell           Client_OLE2.OBJ_TYPE;
   args           Client_OLE2.OBJ_TYPE;
   cell_value     varchar2(100);
   num_wrkshts    NUMBER;
   wksht_name     VARCHAR2(250);
   eod            Boolean := false;
   j              integer := 1; 
   v_fName        VARCHAR2(250);
BEGIN 
file.
   v_fName := WebUtil_File.File_Open_Dialog(
                  directory_name => 'C:\'
                  ,File_Filter => null
                  ,Title => 'Select Client filename to Open.'
            );

   IF ( v_fName IS NOT NULL ) THEN 
      application :=
                    Client_OLE2.create_obj
                   ('Excel.Application'); 
                    Client_OLE2.set_property(
                    application,'Visible','false');
                    workbooks :=
                    Client_OLE2.Get_Obj_Property(
                    application, 'Workbooks');
       args :=
                    Client_OLE2.CREATE_ARGLIST;
                    Client_OLE2.add_arg(args,v_fName); 
      workbook :=
                    Client_OLE2.GET_OBJ_PROPERTY(
                    workbooks,'Open',args);
                    Client_OLE2.destroy_arglist(args);
      
      worksheets :=
                    Client_OLE2.GET_OBJ_PROPERTY(
                    workbook, 'Worksheets');
      num_wrkshts :=
                    Client_OLE2.GET_NUM_PROPERTY(
                    worksheets, 'Count');
      worksheet :=
                    Client_OLE2.GET_OBJ_PROPERTY( 
                        application,'activesheet');
      go_block('baba'); 
      first_record; 
      loop
         If :system.record_status <> 'NEW' then
            create_record;
         end if;

         exit when eod;
         for k in 1..3 loop  
            args:= Client_OLE2.create_arglist;
            Client_OLE2.add_arg(args, j);
            Client_OLE2.add_arg(args, k);
        cell:=
             Client_OLE2.get_obj_property(
                    worksheet, 'Cells', args);
             Client_OLE2.destroy_arglist(args);
        cell_value :=
             Client_OLE2.get_char_property(cell, 'Value');
      
            if upper(cell_value) = 'EOD' then
                  eod:=true;
                  Message('End of Data');
                  exit;
            end if;
            
            copy(cell_value,name_in('system.cursor_item'));
            next_item;
      
         end loop; 
                  
            j:=j+1;
      end loop;  
            
      IF (cell IS NOT NULL) THEN 
            Client_OLE2.release_obj(cell);
      END IF;
      IF (worksheet IS NOT NULL) THEN 
            Client_OLE2.release_obj(worksheet);
      END IF;
      IF (worksheets IS NOT NULL) THEN 
            Client_OLE2.release_obj(worksheets);
      END IF;
      IF (worksheet2 IS NOT NULL) THEN 
            Client_OLE2.release_obj(worksheet2);
      END IF;
      IF (workbook IS NOT NULL) THEN 
            Client_OLE2.release_obj(workbook);
      END IF;
      IF (workbooks IS NOT NULL) THEN 
            Client_OLE2.release_obj(workbooks);
      END IF;
      Client_OLE2.invoke(application,'Quit');
      Client_OLE2.release_obj(application);
   ELSE
      Message('No File selected.');
      message(' ');
      RAISE Form_Trigger_Failure;
   END IF;
END;

its working good for me
hope u got something from it

regards
baba

[Updated on: Wed, 18 April 2012 09:08]

Report message to a moderator

Re: Cann't commit form when form status change from New to Query and to Changed [message #551479 is a reply to message #551464]

Wed, 18 April 2012 11:06

tieudieuphong
Messages: 10
Registered: April 2012
Location: Viet Nam

Junior Member

thank you very much.

I will try your code in my project.

Report message to a moderator

Re: Cann't commit form when form status change from New to Query and to Changed [message #551525 is a reply to message #551479]

Wed, 18 April 2012 15:47

owais_baba
Messages: 289
Registered: March 2008
Location: MUSCAT

Senior Member

but remember friend before doing all these must read & configure first

here is a link

http://www.baigzeeshan.com/2010/01/open-file-dailog-box-example-using.html

Report message to a moderator

Re: Cann't commit form when form status change from New to Query and to Changed [message #551527 is a reply to message #551525]

Wed, 18 April 2012 16:04

owais_baba
Messages: 289
Registered: March 2008
Location: MUSCAT

Senior Member

and if u want to copy the value from excel to form block item then add this code before this "copy(cell_value,name_in('system.cursor_item'));"

    if k =1 then
        :A.NO:=cell_value;
    end if;

    if k =2 then
        :B.name:=cell_value;
    end if;

   if k =3 then
        :C.QTY:=cell_value;
   end if;

if still confusing tell me i can send u fmb file

Regards
baba

[Updated on: Wed, 18 April 2012 16:05]

Report message to a moderator

Re: Cann't commit form when form status change from New to Query and to Changed [message #551531 is a reply to message #551527]

Wed, 18 April 2012 20:13

tieudieuphong
Messages: 10
Registered: April 2012
Location: Viet Nam

Junior Member

Thank you for reply.

Thanks for all member of forum. The problem has been resolved.

Thank you very much!!!

I will need to help me in the future. Thanks!!!

Report message to a moderator

Previous Topic:	Webutil problem
Next Topic:	Selection Limit on block

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Feb 03 04:11:29 CST 2025