Home » Developer & Programmer » Forms » unable to save record (XP,6i)
| unable to save record [message #480223] |
Thu, 21 October 2010 15:39  |
fahady2010
Messages: 6
Registered: October 2010
Location: karachi
|
Junior Member |
|
|
please solve this task the data not save the record
the item CMPID not generate a number or id to press button NEW
secondly to click the button SAVE they not save the partcular transaction
please solve that problem in the or to send the form on to mail
fahad_aijaz81@hotmail.com
|
|
|
|
|
|
| Re: unable to save record [message #480373 is a reply to message #480223] |
Fri, 22 October 2010 15:11  |
fahady2010
Messages: 6
Registered: October 2010
Location: karachi
|
Junior Member |
|
|
this query has perform the button
trigger When_button_press
col_name varchar2(15);
select 'CEN'||TO_CHAR(MAX(NVL(col_name),0))+1) into :table_name.col_name from table_name;
not generate the number with character in text item
and not save the data
please solve that problem
|
|
|
|
|
|
| Re: unable to save record [message #480400 is a reply to message #480373] |
Sat, 23 October 2010 13:29 |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
It seems that - in the current OraFAQ Forum engine - CODE tags don't work correctly, so I'll use PRE tags instead. This will mess things up, but I can't do much about it.
[EDIT]After Frank Naude applied the patch, CODE tags are working just fine so - here they are.
fahady2010 wrote on Fri, 22 October 2010 22:11
select 'CEN'||TO_CHAR(MAX(NVL(col_name),0))+1) into :table_name.col_name from table_name;
not generate the number with character in text item and not save the data
Perhaps you should check use of functions you used in a query. First make it work, then make it fancy.
SQL> desc test;
Name Null? Type
----------------------------- -------- --------------------
COL_NAME NUMBER
SQL> select 'CEN'||TO_CHAR(MAX(NVL(col_name),0))+1) from test;
select 'CEN'||TO_CHAR(MAX(NVL(col_name),0))+1) from test
*
ERROR at line 1:
ORA-00909: invalid number of arguments
[EDIT]
OK then, this is how it goes. A table needs to be empty so that we could test it. You meant to use NVL function because it returns "something" when there's nothing there. But, when there's REALLY nothing in a table, NVL didn't find NULL - it found no records:SQL> select * from test;
no rows selected
SQL> select nvl(col_name, 0) from test;
no rows selected SQL> select max(col_name) from test;
MAX(COL_NAME)
-------------
Now, apply NVL to this value:SQL> select nvl(max(col_name), 0) from test;
NVL(MAX(COL_NAME),0)
--------------------
0
Cool. It is possible to add "1" to "0" we managed to select:SQL> select nvl(max(col_name), 0) + 1 from test;
NVL(MAX(COL_NAME),0)+1
----------------------
1
As you wanted to convert it to a character - no problem:SQL> select to_char(nvl(max(col_name), 0) + 1) from test;
TO_CHAR(NVL(MAX(COL_NAME),0)+1)
----------------------------------------
1
Finally, concatenate a string:SQL> select 'CEN' || to_char(nvl(max(col_name), 0) + 1) from test;
'CEN'||TO_CHAR(NVL(MAX(COL_NAME),0)+1)
-------------------------------------------
CEN1
However, it seems that a new problem is on a horizon: you are storing the result ("CEN1" at the moment) into a form item named ":table_name.col_name". But: this means that "col_name" is a column whose datatype is CHARACTER. Otherwise, you couldn't put it in there. So suppose that we did that:SQL> alter table test modify (col_name varchar2(10));
Table altered.
SQL> select 'CEN' || to_char(nvl(max(col_name), 0) + 1) from test;
'CEN'||TO_CHAR(NVL(MAX(COL_NAME),0)+1)
-------------------------------------------
CEN1
SQL> insert into test (col_name) values ('CEN1');
1 row created.
So far, so good.
A next record:SQL> select 'CEN' || to_char(nvl(max(col_name), 0) + 1) from test;
select 'CEN' || to_char(nvl(max(col_name), 0) + 1) from test
*
ERROR at line 1:
ORA-01722: invalid number
Maybe if you could explain what you are trying to do, we could suggest an alternative approach (such as a sequence, for example).
[Updated on: Sat, 23 October 2010 14:18] Report message to a moderator |
|
|
|
| Re: unable to save record [message #480974 is a reply to message #480400] |
Thu, 28 October 2010 15:30 |
fahady2010
Messages: 6
Registered: October 2010
Location: karachi
|
Junior Member |
|
|
this query wil not generate a number in form level if he generate
nnot save the record
why
I send you 2 tables create us in sql database
then your will perform on to 6i form
two butons create NEW and SAVE
NEW perform to generate number in CMPID
SAVE perform to commit a data
CREATE TABLE EMP
(
CMPID VARCHAR2(6) PRIMARY KEY,
ENAME VARCHAR2(45),
FNAME VARCHAR2(45),
DOB DATE,
MSTA VARCHAR2(6),
DESIG VARCHAR2(20),
NATION VARCHAR2(15),
NIC NUMBER(14) PRIMARY KEY,
EDU VARCHAR2(15),
PROVI VARCHAR2(13),
CITY VARCHAR2(13),
DOM VARCHAR2(10),
REL VARCHAR2(15),
MOBNO VARCHAR2(15),
PERADD VARCHAR2(200),
CURRADD VARCHAR2(200),
REL_AD VARCHAR2(200),
COUNTRY VARCHAR2(10),
SALARY NUMBER(6));
CREATE TABLE ATTN(
EMATNID VARCHAR2(3),
CMPID NUMBER(5),
PRESDATE DATE,
NIC NUMBER(5),
TIMIN timestamp with local time zone,
TIMOUT timestamp with local time zone,
CONSTRAINT FK_CMPID_NIC_ATTN FOREIGN KEY(CMPID,NIC)
REFERENCES EMP(CMPID,NIC));
|
|
|
|
|
|
| Re: unable to save record [message #481009 is a reply to message #480383] |
Fri, 29 October 2010 02:44 |
fahady2010
Messages: 6
Registered: October 2010
Location: karachi
|
Junior Member |
|
|
GO_BLOCK('EMP');
BEGIN
SELECT NVL(MAX(C_ID),0)+1 INTO :EMP.C_ID
FROM EMP;
END;
this query on the form level 6i the button NEW the trigger is when_button_press wil not generate a number in form level if he generate a number if he generate then not save record
why
I send you 2 tables create us in sql database
then your will perform on to 6i form
two butons create NEW and SAVE
NEW perform to generate number in CMPID
SAVE perform to commit a data
CREATE TABLE EMP
(
CMPID VARCHAR2(6) PRIMARY KEY,
ENAME VARCHAR2(45),
FNAME VARCHAR2(45),
DOB DATE,
MSTA VARCHAR2(6),
DESIG VARCHAR2(20),
NATION VARCHAR2(15),
NIC NUMBER(14) PRIMARY KEY,
EDU VARCHAR2(15),
PROVI VARCHAR2(13),
CITY VARCHAR2(13),
DOM VARCHAR2(10),
REL VARCHAR2(15),
MOBNO VARCHAR2(15),
PERADD VARCHAR2(200),
CURRADD VARCHAR2(200),
REL_AD VARCHAR2(200),
COUNTRY VARCHAR2(10),
SALARY NUMBER(6));
CREATE TABLE ATTN(
EMATNID VARCHAR2(3),
CMPID NUMBER(5),
PRESDATE DATE,
NIC NUMBER(5),
TIMIN timestamp with local time zone,
TIMOUT timestamp with local time zone,
CONSTRAINT FK_CMPID_NIC_ATTN FOREIGN KEY(CMPID,NIC)
REFERENCES EMP(CMPID,NIC))
|
|
|
|
|
|
| Re: unable to save record [message #481014 is a reply to message #481013] |
Fri, 29 October 2010 03:14 |
fahady2010
Messages: 6
Registered: October 2010
Location: karachi
|
Junior Member |
|
|
basically sir
i can used one datatype at the time
but not performing result
datatype number which can used with C_ID column name
they can not generate the number on the form
then i will try second option used varchar2(5) this problem has same
previsouly i can send us the schema u create them on sql
then u wil performing on the form with two button NEW and SAVE
the form u can send me then i will check him
thanks
|
|
|
|
|
|
|
|
| Re: unable to save record [message #481085 is a reply to message #481084] |
Sat, 30 October 2010 05:47 |
ranamirfan
Messages: 535
Registered: January 2006
Location: Pakistan / Saudi Arabia
|
Senior Member |
|
|
Dear,
Quote:
Besides, MAX + 1 will most probably fail (sooner or later) in multi-user environment.
As Mr.Littlefoot already Described.
Better to used this code in PRE-INSERT Trigger at Block level.
SELECT NVL(MAX(CMPID),0)+1 INTO
:EMP.CMPID FROM EMP;
Regards,
Irfan
|
|
|
|
Goto Forum:
Current Time: Sun Feb 09 10:30:15 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
