Home » Developer & Programmer » Forms » error in insertion .. (forms 6i oracle 9.2.0.1.0 win xp)
icon5.gif  error in insertion .. [message #333255] Fri, 11 July 2008 00:57 Go to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

hello all,

im using the following code to make insertion into registration
table ,.but forms builder inserting the entered value 2 times,so it would cause me an ambiguity while looking through the table.
pls help me to overcome this.

DECLARE
   v_pass_word    VARCHAR2 (10);
   v_conf_paswd   VARCHAR2 (10);
   a              NUMBER;
BEGIN
   v_pass_word := :online_reg1.pass_word;
   v_conf_paswd := :online_reg1.confirm_pswd;

   IF :online_reg1.student_name IS NULL
   THEN
      MESSAGE ('enter the student name');
   ELSIF :online_reg1.user_name IS NULL
   THEN
      MESSAGE ('enter the User name');
   ELSIF :online_reg1.pass_word IS NULL
   THEN
      MESSAGE ('enter the password');
   ELSIF :online_reg1.confirm_pswd IS NULL
   THEN
      MESSAGE ('confirm password');
   ELSIF :online_reg1.dob IS NULL
   THEN
      MESSAGE ('enter the DOB');
   ELSIF :online_reg1.email_id IS NULL
   THEN
      MESSAGE ('enter the Email Id');
   ELSIF :online_reg1.phone IS NULL
   THEN
      MESSAGE ('enter the Phone No');
   END IF;

   IF v_pass_word != v_conf_paswd
   THEN
      a := SHOW_ALERT ('ALERT_INVALID');
   END IF;

   INSERT INTO online_reg1
               (student_name, user_name,
                pass_word, confirm_pswd,
                dob, email_id, phone
               )
        VALUES (:online_reg1.student_name, :online_reg1.user_name,
                :online_reg1.pass_word, :online_reg1.confirm_pswd,
                :online_reg1.dob, :online_reg1.email_id, :online_reg1.phone
               );

   COMMIT;
END;



thanks in advance..
Re: error in insertion .. [message #333260 is a reply to message #333255] Fri, 11 July 2008 01:07 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

STUDENT_NA USER_NAME PASS_WORD CONFIRM_PS DOB EMAIL_ID PHONE
soniya soniya soniya soniya 12-MAY-90 fsdsf f3er4f
terry terry terry terry 12-JUN-87 gfcgref 232
terry terry terry terry 12-JUN-87 gfcgref 232

smith smith smith smith 11-NOV-90 ggjgjgg 32323
smith smith smith smith 11-NOV-90 ggjgjgg 32323
seyed seyed seyed seyed 12-MAY-86 sidd 33e3
samyu samyu samyu samyu 15-OCT-84 ssss 31323
soniya soniya soniya soniya 12-MAY-90 fsdsf f3er4f

  • Attachment: untitled.JPG
    (Size: 54.08KB, Downloaded 126 times)
Re: error in insertion .. [message #333281 is a reply to message #333260] Fri, 11 July 2008 02:29 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It seems that the first record is inserted by committing values entered in a form (that's what you see on the screen), and the second is inserted using the INSERT INTO statement written in the trigger.

I'd suggest you to remove INSERT INTO; default Forms behaviour will save a record anyway. Or, create a control (instead of data) block and user INSERT INTO (as control block's items aren't database items).
Re: error in insertion .. [message #333291 is a reply to message #333255] Fri, 11 July 2008 02:51 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

thanks little foot.

i will remove insert into statement .
but instead of that wat i do ...

can u post the code pls ??
Re: error in insertion .. [message #333294 is a reply to message #333291] Fri, 11 July 2008 02:58 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Don't do anything. By committing changes you've made,
LF
default Forms behaviour will save a record anyway
Re: error in insertion .. [message #333312 is a reply to message #333255] Fri, 11 July 2008 03:55 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

i done it..thanks alot. .
Re: error in insertion .. [message #333322 is a reply to message #333255] Fri, 11 July 2008 04:23 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

hey little foot..
another problem just raised...
i have to disable the forms alert window
asking me that "do you want to save the changes you have made"..


how do i achieve this .
pls tell me..
  • Attachment: untitled.JPG
    (Size: 36.91KB, Downloaded 114 times)
Re: error in insertion .. [message #333329 is a reply to message #333322] Fri, 11 July 2008 04:46 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Forms thinks that you have made changes to your data and issues a warning that these changes might be lost if you don't save them. So, why would you want to skip this warning (and later ask yourself what has happened and think that it must be bug in Forms)?

As you've attached a screenshot with username and password items, perhaps you could put them into a CONTROL block (i.e. the one that is not a database block). Doing so, no changes would be made and there'd be no need to save them.

Anyway, what action did trigger this warning? Pushing a button? If so, what's the code you used in the WHEN-BUTTON-PRESSED trigger?
Re: error in insertion .. [message #333330 is a reply to message #333255] Fri, 11 July 2008 05:02 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

ok .
if im creating the control block with 2 text boxes
user_name and pass_word.then how do i get the values of these 2 text box values ???

previously i created a login form as a database block.
so i used to compare the values :online_login.user_name
with user_name from table login.

when button pressed trigger
SELECT count(*)
				INTO v_count
				FROM online_reg1
				WHERE lower(user_name) = :online_reg1.user_name
				AND lower(pass_word) = :online_reg1.pass_word;


IF v_count=1 THEN
		call_form('online_enroll');
		ROLLBACK;
		ELSE
			a := show_alert('ALERT_INVALID');
		END IF;



thanks in advance
Re: error in insertion .. [message #333337 is a reply to message #333330] Fri, 11 July 2008 05:14 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
how do i get the values of these 2 text box values ?
I thought that every user enters his/her own username and password. Doesn't (s)he?

The rest of the code seems to be OK; ":online_login.user_name" is correct regardless "online_login" is a data or control block.

However, perhaps you'd rather change
WHERE lower(user_name) = :online_reg1.user_name
to
WHERE lower(user_name) = lower(:online_reg1.user_name)
(the same goes for password).
Re: error in insertion .. [message #333342 is a reply to message #333255] Fri, 11 July 2008 05:40 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

thanks alot again..

PFA

this is online_enroll form

i created course and exam paper as control block text items,,
i created 2 lovs for one for selecting course and other for selecting paper.
when im selecting course it automatically retrieved the following papers which are regarding to the matched course.

i written sql query in record group paper_details
SELECT exam_paper
  FROM online_course1
 WHERE course_id IN (SELECT course_id
                       FROM online_course1
                      WHERE course = :course)


when im running i got an error

Quote:
FRM-30047: Cannot resolve item reference ONLINE_COURSE1.EXAM_PAPER.
LOV LOV_PAPER
Form: ONLINE_ENROLL
FRM-30085: Unable to adjust form for output.



thanks in advance
Re: error in insertion .. [message #333346 is a reply to message #333342] Fri, 11 July 2008 05:57 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This is what Oracle says:
Quote:
FRM-30047: Cannot resolve item reference %s.

Cause:  The item referenced does not exist.

Action:  Change the reference or create the item.


But ... regarding to the attached FMB, there is a block called "online_course1" as well as its item named "exam_paper".

This is what I say: recompile (All) the form (Ctrl + Shift + K) and try to run the form again.
Re: error in insertion .. [message #333353 is a reply to message #333255] Fri, 11 July 2008 06:24 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

that error gone ..

but i cudnt able to insert data into online_enroll1

I made course and exam_paper in control block,
exam_date and enrollment_date in datablock (online_enroll1)

table online_enroll1

CREATE TABLE ONLINE_ENROLL1
(
  EXAM_ID          NUMBER,
  USER_NAME        VARCHAR2(10 BYTE),
  COURSE_ID        NUMBER,
  EXAM_DATE        DATE,
  ENROLLMENT_DATE  DATE,
  STATUS           CHAR(1 BYTE)                 DEFAULT 'N'
)


ALTER TABLE ONLINE_ENROLL1 ADD (
  CONSTRAINT CHECK_USER_NAME1
 CHECK (LENGTH(user_name) BETWEEN 5 AND 12));


ALTER TABLE ONLINE_ENROLL1 ADD (
  CONSTRAINT ENROLL_COURSEID_FK 
 FOREIGN KEY (COURSE_ID) 
 REFERENCES ONLINE_COURSE (COURSE_ID));





submit button [WHEN-BUTTON-PRESSED]

DECLARE
   v_courseid   NUMBER;
   v_status     CHAR (1)      := 'N';
BEGIN
   IF :online_enroll1.exam_date IS NULL
   THEN
      MESSAGE ('enter the Exam date');
   ELSIF :online_enroll1.enrollment_date IS NULL
   THEN
      MESSAGE ('enter the enrollment date');
   ELSIF :course IS NULL
   THEN
      MESSAGE ('select the coursename');
   ELSIF :exam_paper IS NULL
   THEN
      MESSAGE ('select the exam paper');
   END IF;

   SELECT course_id
     INTO v_courseid
     FROM online_course1
    WHERE exam_paper = :exam_paper;

   INSERT INTO online_enroll1
               (exam_id, course_id, status
               )
        VALUES (examid_seq.NEXTVAL, v_courseid, v_status
               );

   COMMIT;
END;




thanks in advance..
Re: error in insertion .. [message #333364 is a reply to message #333353] Fri, 11 July 2008 07:01 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Please, post "CREATE TABLE online_course1" statement and your new FMB.
Re: error in insertion .. [message #333515 is a reply to message #333255] Sat, 12 July 2008 00:39 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

sorry for the late reply..


CREATE TABLE ONLINE_COURSE1
(
  COURSE_ID   NUMBER,
  COURSE      VARCHAR2(10 BYTE),
  EXAM_PAPER  VARCHAR2(10 BYTE),
constraint courseid_pk primary key(course_id)

)







thanks in advance/
icon9.gif  Re: error in insertion .. [message #333520 is a reply to message #333255] Sat, 12 July 2008 02:46 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

hello .. .
i cudnt able to insert data into online_enroll1,

i written the following code in my submit button.


DECLARE
   v_courseid   NUMBER;
   v_status     CHAR (1) := 'N';
BEGIN
   IF :onlin_enroll.exam_date IS NULL
   THEN
      MESSAGE ('enter the Exam date');
   ELSIF :onlin_enroll.enrollment_date IS NULL
   THEN
      MESSAGE ('enter the enrollment date');
   ELSIF :course IS NULL
   THEN
      MESSAGE ('select the coursename');
   ELSIF :exam_paper IS NULL
   THEN
      MESSAGE ('select the exam paper');
   END IF;

   SELECT course_id
     INTO v_courseid
     FROM online_course
    WHERE course = :block4.course AND exam_paper = :block4.exam_paper;

   INSERT INTO onlin_enroll
               (exam_id, user_name, course_id, exam_date,
                enrollment_date, status
               )
        VALUES (48, 'sonykk', v_courseid, :onlin_enroll.exam_date,
                :onlin_enroll.enrollment_date, v_status
               );

   COMMIT;
END;


the error i got is being found out in attachment..
pls help me to over come this..
thanks in advance
  • Attachment: untitled.JPG
    (Size: 54.53KB, Downloaded 123 times)
Re: error in insertion .. [message #333531 is a reply to message #333520] Sat, 12 July 2008 04:43 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Is the block in your form a database-block?
And did you create a new record/enter fields in an empty record?
If so, then why do you think you need to explicitly insert a record through a wbp-trigger? Why not let Forms do its job?
I expect that the above scenario is what's going on here, and that the exam_id is not filled through the regular Forms insert. The table would probably need a trigger + sequence, or your record needs adjustment.
Re: error in insertion .. [message #333537 is a reply to message #333255] Sat, 12 July 2008 05:03 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

thanks frank..

error gone despite i have to know the following :

i created a control block for all my items
user_name
exam_date
enrollment_date


-->user_name is between 5 to 12 characters ..
how do i achieve this ??...

and
-->how do i have to get the user_name from login form ie., once
the user logged into this application.
i think i may have to use global variables. .
instead of entering user_name can i get the value from login form??

thanks in advance
Re: error in insertion .. [message #338762 is a reply to message #333537] Tue, 05 August 2008 21:18 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Test the length using the 'length' command.

Look at the 'GET_APPLICATION_PROPERTY(USERNAME)' facility.

David
Previous Topic: Save Problem
Next Topic: Linux command in HOST
Goto Forum:
  


Current Time: Fri Dec 09 04:17:19 CST 2016

Total time taken to generate the page: 0.08992 seconds