Home » Developer & Programmer » Forms » Erorr while updating or deleting a record... (Forms 10g)
Erorr while updating or deleting a record... [message #422794] Sat, 19 September 2009 06:04 Go to next message
sprity
Messages: 18
Registered: July 2009
Junior Member
Hi to all...

i have created a table group_master with 2 fields Group_id and Group_name
the user will specify a group_name in a text box and press save. Before commiting a seq number will be generated
inside the form procedure and it will be inserted.

The insert is working fine.

But when i select a record i.e a group_name and modify and commit it, it is not commiting.

when i checked the display error it showed Ora-01400 cannot insert null values into the table.

Iam not inserting a value here iam updating the group name only.

even when i delete a record it shows same error....

can any one help to solve this....

thanx in advance........
Re: Erorr while updating or deleting a record... [message #422841 is a reply to message #422794] Sun, 20 September 2009 08:57 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
But when i select a record i.e a group_name

How do you do that? Enter + Execute query?

How do you create a sequence? Did you write your own committing procedure, or do you use built-in COMMIT (save)? Any additional PRE / POST triggers? Database triggers, perhaps? What do they do?

Also, check this Martin Eysackers' question which contains a small test case (create table + insert into sample data), as well as sample form. It makes it possible to actually SEE what is going on.

In your case, there are too many open questions and it is difficult to guess what might be wrong.
Re: Erorr while updating or deleting a record... [message #422866 is a reply to message #422794] Sun, 20 September 2009 23:39 Go to previous messageGo to next message
sprity
Messages: 18
Registered: July 2009
Junior Member
Hi to all.... Smile


i have used this procedure to create sequence.

PROCEDURE GEN_SEQ_NO IS
l_seqno number;
BEGIN
SELECT MAX(GROUP_ID) INTO L_SEQNO FROM group_master;

IF L_SEQNO IS NULL THEN
message('No Value'); pause;
:group_master.GROUP_ID :=1;
ELSE
L_SEQNO := L_SEQNO+1;
:group_master.GROUP_ID := L_SEQNO;
END IF;

end;

i am using LOV to featch the records and then i select a record in a LOV which will be displayed in my form...

There is no pre/post triggers....

i use commit_form to save changes.....

Re: Erorr while updating or deleting a record... [message #422894 is a reply to message #422866] Mon, 21 September 2009 02:14 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, this "MAX + 1" principle might work in a single-user environment. In any other case, it will (sooner or later) lead into the DUP-VAL-ON-INDEX error. Use a real sequence instead (an Oracle object, created with the CREATE SEQUENCE command). You can then use it in "Initial value" item property (for more information, read Forms Help).

As of "selecting" records, you do use Forms' built-in querying system (Enter query - enter search criteria (LoV is OK) - Execute query), don't you? If not, well, you should.

Besides, where do you use this GEN_SEQ_NO procedure?
Re: Erorr while updating or deleting a record... [message #422913 is a reply to message #422794] Mon, 21 September 2009 02:57 Go to previous messageGo to next message
sprity
Messages: 18
Registered: July 2009
Junior Member
Hi...

thanx for the response...
before Commiting the form iam calling GEN_SEQ_NO procedure.

this is for admin use only.. so i used MAX+1 concept...

if i use execute query then it prompts a message box saying Do u want to save the changes u made with yes,no,cancel buttons.

thats why i avoided execute query...

one of the oracle programmer said there is no need to use execute query whiling retriving records thru LOV....
Re: Erorr while updating or deleting a record... [message #422924 is a reply to message #422913] Mon, 21 September 2009 04:16 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
one of the oracle programmer said there is no need to use execute query whiling retriving records thru LOV
This might be right, but also might be wrong - depends on what you plan to do with that record. In your case, I believe that this approach is wrong because you want to update an existing record. In that case, you first have to fetch it from the database (using execute query). If you fetch it via LoV, this record is treated as a new record.

I might be wrong, but I have a feeling that you should rewrite this form (using a wizard might be a good option) and use built-in Forms' functionalities up to their maximum. In other words, do not code anything by yourself. Once you make such a "basic" form work (which should be immediately - inserting, querying, updating and deleting will work just fine), try to improve it (by adding a sequence generator, for example).
Re: Erorr while updating or deleting a record... [message #422942 is a reply to message #422794] Mon, 21 September 2009 05:51 Go to previous messageGo to next message
sprity
Messages: 18
Registered: July 2009
Junior Member
hi...
thanx again....

i have recreated the form...

i used the default menu bar to enter,execute,update,remove the datas... its working fine...

then i tried with my own queries.
i have used sequence this time.

Insert and update or working fine. But deletion is not working.

i have used delete_record to delete the particular selected record and commit the form.

the same concept worked for me earlier.
But now its not working . The record which i select for deletion
is still present in the database .


the same concept is used in another form which works fine........



Re: Erorr while updating or deleting a record... [message #422995 is a reply to message #422942] Mon, 21 September 2009 12:16 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
the same concept worked for me earlier. But now its not working.
So it seems that code you've written has spoiled a working form.

Reload the last good version and start over from that point. Test frequently! Don't move forward until everything works fine up to this point.
Re: Erorr while updating or deleting a record... [message #423095 is a reply to message #422942] Tue, 22 September 2009 01:45 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Why have you written your own code to delete the record? The delete facility in the smartbar works.

David
Re: Erorr while updating or deleting a record... [message #423181 is a reply to message #422794] Tue, 22 September 2009 07:44 Go to previous messageGo to next message
sprity
Messages: 18
Registered: July 2009
Junior Member
my client has said not to use default and smart bar...
so all my form will have a horizontal tool bar with add,modify,delete,clear and save option....

whenever i fetach a data/record thru LOv and display it in the form and then if i made any update it says oracle unable to insert record...

One of field is primary key. After throwing a record into the form thru LOV it treats the selected record as a new record...

this is want...

i have three fields.
Sub_module_id primarykey
sub_modulename
File_location.

i have used data block wizard and created a form with sub_modulename and file location. i will generate a sequence number for sub_module_id before commitng the form in my Save button.
My insert/add is working fine...

Now i want t o modify an inserted record. So after pressing my modify button a LOV will be generated,then i will select a record.

That particular record will be displayed in my form. Now i want to update/modify the datas...

then i will commit the changes thru my save button. i have used commit_form in it.

But it display error unable to insert becoz of the primary key.It treats the data as anew record.

i want to avoid it n save my changes...

Any one can help me in this...

or

guide me to use the same concept for me....

thanx in advance....

with cheers
sprity
Re: Erorr while updating or deleting a record... [message #423187 is a reply to message #422794] Tue, 22 September 2009 08:33 Go to previous message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
As has been pointed out already - if you want to update a record you need to query it first, using execute_query. You can't just use an LOV to populate a record.
Previous Topic: frm:40508:oracle error
Next Topic: How to Re-write DDE code for Forms 10g
Goto Forum:
  


Current Time: Mon Dec 05 18:56:11 CST 2016

Total time taken to generate the page: 0.12690 seconds