Home » Developer & Programmer » Forms » From not working properly (10g developer suite win xp)
From not working properly [message #344572] Sat, 30 August 2008 12:15 Go to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
Hi,

I have a form that does not seem to be working. Attached is the screen shot of the form.

Here is the WHEN_BUTTON_PRESSED trigger

declare
	id varchar(25);
	a number;
	n varchar2(25);
	output boolean;
begin	
	/*a few alerts to see that the user does not miss out the mandatory fields */
	
       /*consultant_insert is the function that inserts the records*/
output := consultant_insert(n,:address1, :address2,:day_phone, :day_phone_1,
           :day_phone_2,:evening_phone,:evening_phone_1,:evening_phone_2,
           :city, :li_states, :li_work_authorization, :employer,
           :skills,:qualification, :rate, :experience, :comments,id);	          
	 if output = TRUE then
	 	commit_form;
	 else 
	 	raise form_trigger_failure;
	 	
	 end if;
	 
end;


Here is the function code

FUNCTION consultant_insert 
(name varchar2, address1 varchar2, address2 varchar2,
day_phone number,day_phone_1 number, day_phone_2 number, 
evening_phone number,evening_phone_1 number,evening_phone_2 number, 
city varchar2,state char, work_authorization varchar2, 
employer varchar2, skills varchar2, qualification varchar2,
rate number, experience number, comments varchar2, c_id varchar2
)
RETURN boolean IS
  no1 number(10);
  no2 number(10);
BEGIN
  no1 := day_phone||day_phone_1||day_phone_2;
  no2 := evening_phone||evening_phone_1||evening_phone_2;

insert into consultant values (name, address1, address2, city,state, work_authorization, 
employer, skills, qualification, rate, experience, comments, c_id, ' ',no1, no2);

commit;
return TRUE;
exception
when others then
return FALSE;
raise form_trigger_failure;
END;


Here is the table structure

Quote:
SQL> desc consultant
Name Null? Type
----------------------------------------- -------- --------------------------

NAME VARCHAR2(50)
ADDRESS1 VARCHAR2(30)
ADDRESS2 VARCHAR2(30)
CITY VARCHAR2(20)
STATE CHAR(2)
WORK_AUTHORIZATION VARCHAR2(10)
EMPLOYER VARCHAR2(40)
SKILLS VARCHAR2(50)
QUALIFICATION VARCHAR2(10)
RATE NUMBER
EXPERIENCE NUMBER(7,2)
COMMENTS VARCHAR2(50)
C_ID NOT NULL VARCHAR2(10)
RESUME CLOB
EVENING_PHONE NUMBER
DAYTIME_PHONE NUMBER



I know there is something wrong somewhere but how to find that..nothing anywhere is giving any error.

Can you tell me how to find the error? how to test this?
[EDITED by DJM: fixed some of the crappy formatting]

[Updated on: Mon, 01 September 2008 21:07] by Moderator

Report message to a moderator

Re: From not working properly [message #344627 is a reply to message #344572] Sun, 31 August 2008 03:19 Go to previous messageGo to next message
mudabbir
Messages: 235
Registered: April 2006
Location: Kuwait
Senior Member

1. How do you know its not working? Is it saving in the database?
2. Why dont you create a database block for Consultants?
3. Why are you giving an INSERT command in a function?
4. What are you trying to achieve by this?
Re: From not working properly [message #344632 is a reply to message #344627] Sun, 31 August 2008 04:28 Go to previous messageGo to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member

1) It is not saving in the database

2) I started off by creating a database block but then found out that I the C_ID column (ID) had to be made from firstname and the phone number also that firstname and lastname had to be added to make the name column of the table CONSULTANT, even the phone number had to be made in such a way that the use enters in the 3-3-4 format due to this I took this approach. I know I could have kept the datablock and written code for only the ones I needed but I tried to do everything with a code. Is this a bad way to do it?

3) I could have given the insert in the WHEN_BUTTON_PRESSED trigger but I thought it might give me better performance using a function

4) When the button 'ENTER' is pressed then the records should be inserted into the database table

Is this the wrong approach?
Re: From not working properly [message #344639 is a reply to message #344632] Sun, 31 August 2008 05:41 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
Is this the wrong approach?
In my opinion, yes.

A database block is the simplest solution. Phone number format can be set in the item properties palette, "name" columns can be concatenated, ID column value can be set, as well as other "difficulties" you have met.

I'd suggest you to make to form work, and later make it fancy. By default, form will do its job if you simply create it using the Wizard. After that, you can add certain features, one by one, testing the form after each of them is implemented.
Re: From not working properly [message #344644 is a reply to message #344632] Sun, 31 August 2008 06:15 Go to previous messageGo to next message
mudabbir
Messages: 235
Registered: April 2006
Location: Kuwait
Senior Member

Yes it is... use Database blocks as much as you can. You can use nodatabase items in the database block and get the result you want.

1. For Name
Create a database block in your form and add 2 NON DATABASE textitems to it called FIRSTNAME and LASTNAME. Before saving the record just give

:CONSULTANT.NAME := :CONSULTANT.FIRSTNAME||' '||:CONSULTANT.LASTNAME;



2. Similar for phone numbers
create 3 NON DATABASE textitems EACH for EVENING_PHONE & DAYTIME_PHONE Before saving the record give

:CONSULTANT.EVENING_PHONE := :CONSULTANT.EVE_PH1||:CONSULTANT.EVE_PH2||:CONSULTANT.EVE_PH3


I hope from this you can understand.
Re: From not working properly [message #344647 is a reply to message #344644] Sun, 31 August 2008 06:38 Go to previous messageGo to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
Yes..got it I will try the suggestions given here and start again on the form...

Thanks..
Re: From not working properly [message #344680 is a reply to message #344647] Mon, 01 September 2008 00:08 Go to previous messageGo to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
I did as told..I first made the normal form and checked how it worked..then I added the customized features that I needed.

As seen in attached screen shot of the layout editor (layout_editor.gif) the columns which had to be worked upon are kept as not visible but still in the form. Now I when I use the insert option in the form should I be using ON-INSERT trigger to put my logic in the functioning?
Re: From not working properly [message #344686 is a reply to message #344680] Mon, 01 September 2008 00:54 Go to previous messageGo to next message
mudabbir
Messages: 235
Registered: April 2006
Location: Kuwait
Senior Member

No No No... You dont require any ON-INSERT trigger....

In my previous post I had given you 2 pieces of code you could use on the ENTER button which you had in your first pic. Use the similar logic and at the end just say COMMIT_FORM; This will save the record in the database.

Note : For the textitems which you dont want to display on your form. change the CANVAS property of the item to NULL.

[Updated on: Mon, 01 September 2008 00:56]

Report message to a moderator

Re: From not working properly [message #344709 is a reply to message #344686] Mon, 01 September 2008 01:37 Go to previous messageGo to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
Thanks..it is still not working ...

Here is my WHEN_BUTTON_PRESSED trigger

begin
	
:db_consultant.name := :db_consultant.firstname||' '||:db_consultant.lastname;

:db_consultant.daytime_phone :=  :db_consultant.day_phone||:db_consultant.day_phone_1||
                                 :db_consultant.day_phone_2;

:db_consultant.evening_phone := :db_consultant.evening_phone_1||
                                :db_consultant.evening_phone_2||:db_consultant.evening_phone_3;

:db_consultant.c_id:=:db_consultant.firstname||
                    :db_consultant.daytime_phone;

commit_form;
	
end;


Attached is the form (after running it). I have filled all the mandatory fields but it is still not inserting the record in the database.any idea why?
[EDITED by DJM: fixed more crappy formatting]
  • Attachment: form.GIF
    (Size: 64.02KB, Downloaded 87 times)

[Updated on: Mon, 01 September 2008 21:09] by Moderator

Report message to a moderator

Re: From not working properly [message #344713 is a reply to message #344709] Mon, 01 September 2008 01:45 Go to previous messageGo to next message
mudabbir
Messages: 235
Registered: April 2006
Location: Kuwait
Senior Member

1. Did you use the Datablock Wizard to create the Datablock?
2. After you press the ENTER button press CTRL+SHIFT+E to check if there are any errors.
Re: From not working properly [message #344940 is a reply to message #344713] Mon, 01 September 2008 23:14 Go to previous messageGo to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
Shocked phew....this was really tiresome..there were two errors in the form. a property was not set properly in the non-database column EVENING_PHONE_1 and I forgot to convert number to char while adding the name and phone number...

I had to search all the items and check most of the properties for them...Is there a better way to do this as the error just says 'not able to insert - Oracle error and it is not telling which item is giving that error. Also, in the database section there is a property 'QUERY LENGTH' what is the meaning of this?
Re: From not working properly [message #345125 is a reply to message #344940] Tue, 02 September 2008 06:43 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
When you run the forum which ends up with an error, go to Help menu and select "Display error" - it might help to narrow search for the cause.

"Query length" property is described in Forms Online Help System. Take some time and read it; after you do that, say if you don't understand something.
Previous Topic: Oracle forms connecting SQL Server
Next Topic: Form Error
Goto Forum:
  


Current Time: Thu Dec 08 16:16:31 CST 2016

Total time taken to generate the page: 0.10011 seconds