Editing TSmartQuery Result with Calculated Field

jfudickar · Post by **jfudickar** » Wed 05 Jul 2006 17:13

Hi,

i have a TSmartQuery-Dataset using the following sql-statement:

SELECT TAET.*, TO_CHAR(TAET_DATUM, 'YYYY')||'-'||TO_CHAR(TAET_DATUM, 'IW') TAET_JAHR_KW, ROWID
FROM V_PROJ_TAETIGKEIT TAET
WHERE TAET_2_BEARB_ID = :TAET_2_BEARB_ID
ORDER BY TAET_DATUM

When i start to insert a new record i've got the follwing error-message:
"Field 'Jahr KW' cannot be modified."

After this message i can change the contents of all fields and then i can post the result.

What must be done to prevent this error-message.

And is it possible the field "JAHR_KW" is automaticly filled after the post of the new record.

Greetings and thanks
Jens

Plash · Post by **Plash** » Fri 07 Jul 2006 09:11

Please specify this situation more detailed. Provide us SQL statement for creating table V_PROJ_TAETIGKEIT.

jfudickar · Post by **jfudickar** » Fri 07 Jul 2006 11:23

Here are the statements:

--
-- OraTool Pro v 2.0.19.1 BETA DEBUG - (c) by Softwaredevelopment Jens Fudickar
-- Softwareentwicklung Jens Fudickar - 0000000001 (Unlimited)
--
-- Generator : Create Object
-- Objecttype : TABLE
-- Generated at : 07.07.2006
-- Generated by : PAS_PROJEKT
--

--##############################################################
-- TABLE PROJ_TAETIGKEIT
--##############################################################
--
-- Description
--
--
-- Columns
--
-- TAET_ID
-- Primary Key
-- TAET_2_BEARB_ID
-- Foreign Key auf die Tabelle PROJ_BEARBEITER
-- TAET_2_AUFG_ID
-- Foreign Key auf die Tabelle PROJ_AUFGABE
-- TAET_DATUM
-- Datum der Tätigkeit
-- TAET_DAUER_STUNDEN
-- Dauer der Tätigkeit an dem Datum in Stunden
-- TAET_DAUER_TAGE
-- Dauer der Tätigkeit an dem Datum in Personentagen
-- TAET_INTERN
-- Flag ob Interner oder Externer Mitarbeiter/Bearbeitung
-- TAET_KURZBESCHREIBUNG
-- Kurzbeschreibung
-- TAET_KOMMENTAR
-- Zusätzlicher Kommentar
-- TAET_PROJEKT
-- Zugehöriges Projekt
-- TAET_BEARBEITER
-- Zugehöriger Bearbeiter
-- TAET_AUFGABE
-- Zugehörige Aufgabe
-- TAET_SUMMARY
-- Zusammenfassende Beschreibung
--

PROMPT CREATE TABLE PAS_PROJEKT.PROJ_TAETIGKEIT
BEGIN DBMS_APPLICATION_INFO.SET_MODULE ('CREATE', 'TABLE');DBMS_APPLICATION_INFO.SET_CLIENT_INFO('PAS_PROJEKT.PROJ_TAETIGKEIT'); END;
/
CREATE TABLE PROJ_TAETIGKEIT (
TAET_ID NUMBER NOT NULL ,
TAET_2_BEARB_ID NUMBER NOT NULL ,
TAET_2_AUFG_ID NUMBER NOT NULL ,
TAET_DATUM DATE DEFAULT TRUNC(SYSDATE) NOT NULL ,
TAET_DAUER_STUNDEN NUMBER DEFAULT 0 NOT NULL ,
TAET_DAUER_TAGE NUMBER DEFAULT 0 NOT NULL ,
TAET_INTERN VARCHAR2 (1) DEFAULT 'Y' NOT NULL ,
TAET_KURZBESCHREIBUNG VARCHAR2 (4000) ,
TAET_KOMMENTAR CLOB ,
TAET_START_UHRZEIT VARCHAR2 (5) ,
TAET_START_ZEITPUNKT DATE ,
TAET_ENDE_UHRZEIT VARCHAR2 (5) ,
TAET_ENDE_ZEITPUNKT DATE ,
TAET_PROJEKT VARCHAR2 (500) ,
TAET_BEARBEITER VARCHAR2 (200) ,
TAET_AUFGABE VARCHAR2 (500) ,
TAET_SUMMARY VARCHAR2 (4000) ,
TAET_AUDIT_INSERT_USER VARCHAR2 (100) ,
TAET_AUDIT_INSERT_TIMESTAMP DATE ,
TAET_AUDIT_UPDATE_USER VARCHAR2 (100) ,
TAET_AUDIT_UPDATE_TIMESTAMP DATE
)
;

----------------------------------------------------------------
-- Constraints
----------------------------------------------------------------

PROMPT CREATE CHECK CONSTRAINT CONSTRAINT PAS_PROJEKT.PROJ_TAET_INTERN_CK
BEGIN DBMS_APPLICATION_INFO.SET_MODULE ('CREATE CHECK CONSTRAINT', 'CONSTRAINT');DBMS_APPLICATION_INFO.SET_CLIENT_INFO('PAS_PROJEKT.PROJ_TAET_INTERN_CK'); END;
/
ALTER TABLE PROJ_TAETIGKEIT
ADD CONSTRAINT PROJ_TAET_INTERN_CK CHECK (TAET_INTERN IN ('Y', 'N'))
;

PROMPT CREATE PRIMARY KEY CONSTRAINT CONSTRAINT PAS_PROJEKT.PROJ_TAET_PK
BEGIN DBMS_APPLICATION_INFO.SET_MODULE ('CREATE PRIMARY KEY CONSTRAINT', 'CONSTRAINT');DBMS_APPLICATION_INFO.SET_CLIENT_INFO('PAS_PROJEKT.PROJ_TAET_PK'); END;
/
ALTER TABLE PROJ_TAETIGKEIT
ADD CONSTRAINT PROJ_TAET_PK PRIMARY KEY
(TAET_ID)
NOT DEFERRABLE INITIALLY IMMEDIATE
USING INDEX
;

PROMPT CREATE UNIQUE CONSTRAINT CONSTRAINT PAS_PROJEKT.PROJ_TAET_UNQ
BEGIN DBMS_APPLICATION_INFO.SET_MODULE ('CREATE UNIQUE CONSTRAINT', 'CONSTRAINT');DBMS_APPLICATION_INFO.SET_CLIENT_INFO('PAS_PROJEKT.PROJ_TAET_UNQ'); END;
/
ALTER TABLE PROJ_TAETIGKEIT
ADD CONSTRAINT PROJ_TAET_UNQ UNIQUE
(TAET_2_BEARB_ID,
TAET_2_AUFG_ID,
TAET_DATUM,
TAET_START_ZEITPUNKT,
TAET_ENDE_ZEITPUNKT,
TAET_KURZBESCHREIBUNG)
NOT DEFERRABLE INITIALLY IMMEDIATE
USING INDEX
;

----------------------------------------------------------------
-- Sequencees
----------------------------------------------------------------

CREATE SEQUENCE SEQ_PROJ_TAETIGKEIT
MINVALUE 1
MAXVALUE 1E27
INCREMENT BY 1
NOCYCLE
CACHE 20
;

----------------------------------------------------------------
-- Trigger
----------------------------------------------------------------

PROMPT CREATE OR REPLACE TRIGGER PROJ_TAET_A_U_R_CHK_RO
BEGIN DBMS_APPLICATION_INFO.SET_MODULE ('CREATE OR REPLACE', 'TRIGGER');DBMS_APPLICATION_INFO.SET_CLIENT_INFO('PROJ_TAET_A_U_R_CHK_RO'); END;
/
CREATE OR REPLACE TRIGGER PROJ_TAET_A_U_R_CHK_RO
AFTER UPDATE ON PROJ_TAETIGKEIT
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
-- Auto-Generated by Excel Generator

-- Feld : TAET_2_AUFG_ID
IF (:OLD.TAET_2_AUFG_ID = :NEW.TAET_2_AUFG_ID) OR
((:OLD.TAET_2_AUFG_ID IS NULL) AND (:NEW.TAET_2_AUFG_ID IS NULL)) THEN
NULL;
ELSE
raise_application_error (-20000, 'Das Feld "PROJ_TAETIGKEIT.TAET_2_AUFG_ID" darf nicht geändert werden!');
END IF;

END PROJ_TAET_A_U_R_CHK_RO;
/

ALTER TRIGGER PROJ_TAET_A_U_R_CHK_RO DISABLE;

PROMPT CREATE OR REPLACE TRIGGER PROJ_TAET_B_IU_R_AUDIT
BEGIN DBMS_APPLICATION_INFO.SET_MODULE ('CREATE OR REPLACE', 'TRIGGER');DBMS_APPLICATION_INFO.SET_CLIENT_INFO('PROJ_TAET_B_IU_R_AUDIT'); END;
/
CREATE OR REPLACE TRIGGER PROJ_TAET_B_IU_R_AUDIT
BEFORE INSERT OR UPDATE ON PROJ_TAETIGKEIT
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
-- Auto-Generated by Excel Generator
IF inserting THEN
:NEW.TAET_AUDIT_INSERT_USER := USER||' @ '||PKG_SYST_TOOLS.GET_CURRENT_OSUSER;
:NEW.TAET_AUDIT_INSERT_TIMESTAMP := SYSDATE;
ELSIF updating THEN
:NEW.TAET_AUDIT_INSERT_USER := :OLD.TAET_AUDIT_INSERT_USER;
:NEW.TAET_AUDIT_INSERT_TIMESTAMP := NVL(:OLD.TAET_AUDIT_INSERT_TIMESTAMP, TO_DATE('01.01.1900', 'DD.MM.YYYY'));
IF NOT (pkg_syst_tools.is_equal (:NEW.TAET_ID , :OLD.TAET_ID ) AND
pkg_syst_tools.is_equal (:NEW.TAET_2_BEARB_ID , :OLD.TAET_2_BEARB_ID ) AND
pkg_syst_tools.is_equal (:NEW.TAET_2_AUFG_ID , :OLD.TAET_2_AUFG_ID ) AND
pkg_syst_tools.is_equal (:NEW.TAET_KURZBESCHREIBUNG , :OLD.TAET_KURZBESCHREIBUNG ) AND
pkg_syst_tools.is_equal (:NEW.TAET_KOMMENTAR , :OLD.TAET_KOMMENTAR ) AND
pkg_syst_tools.is_equal (:NEW.TAET_DATUM , :OLD.TAET_DATUM ) AND
pkg_syst_tools.is_equal (:NEW.TAET_START_UHRZEIT , :OLD.TAET_START_UHRZEIT ) AND
pkg_syst_tools.is_equal (:NEW.TAET_START_ZEITPUNKT , :OLD.TAET_START_ZEITPUNKT ) AND
pkg_syst_tools.is_equal (:NEW.TAET_ENDE_UHRZEIT , :OLD.TAET_ENDE_UHRZEIT ) AND
pkg_syst_tools.is_equal (:NEW.TAET_ENDE_ZEITPUNKT , :OLD.TAET_ENDE_ZEITPUNKT ) AND
pkg_syst_tools.is_equal (:NEW.TAET_DAUER_STUNDEN , :OLD.TAET_DAUER_STUNDEN ) AND
pkg_syst_tools.is_equal (:NEW.TAET_DAUER_TAGE , :OLD.TAET_DAUER_TAGE ) AND
pkg_syst_tools.is_equal (:NEW.TAET_PROJEKT , :OLD.TAET_PROJEKT ) AND
pkg_syst_tools.is_equal (:NEW.TAET_BEARBEITER , :OLD.TAET_BEARBEITER ) AND
pkg_syst_tools.is_equal (:NEW.TAET_AUFGABE , :OLD.TAET_AUFGABE ) AND
pkg_syst_tools.is_equal (:NEW.TAET_SUMMARY , :OLD.TAET_SUMMARY ) AND
pkg_syst_tools.is_equal (:NEW.TAET_INTERN , :OLD.TAET_INTERN ) ) THEN
:NEW.TAET_AUDIT_UPDATE_USER := USER||' @ '||PKG_SYST_TOOLS.GET_CURRENT_OSUSER;
:NEW.TAET_AUDIT_UPDATE_TIMESTAMP := SYSDATE;
ELSE
:NEW.TAET_AUDIT_UPDATE_USER := :OLD.TAET_AUDIT_UPDATE_USER;
:NEW.TAET_AUDIT_UPDATE_TIMESTAMP := :OLD.TAET_AUDIT_UPDATE_TIMESTAMP;
END IF;
END IF;
END PROJ_TAET_A_IU_R_AUDIT;
/

PROMPT CREATE OR REPLACE TRIGGER PROJ_TAET_B_IU_R_CHECK_DATA
BEGIN DBMS_APPLICATION_INFO.SET_MODULE ('CREATE OR REPLACE', 'TRIGGER');DBMS_APPLICATION_INFO.SET_CLIENT_INFO('PROJ_TAET_B_IU_R_CHECK_DATA'); END;
/
CREATE OR REPLACE TRIGGER PROJ_TAET_B_IU_R_CHECK_DATA
BEFORE INSERT OR UPDATE ON PROJ_TAETIGKEIT
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
-- Auto-Generated by Excel Generator
IF (:NEW.TAET_BEARBEITER IS NOT NULL) AND (:NEW.TAET_2_BEARB_ID IS NULL) THEN
:NEW.TAET_2_BEARB_ID := pkg_proj_tools.get_bearbeiter_id (:NEW.TAET_BEARBEITER);
END IF;
IF ((:NEW.TAET_AUFGABE IS NOT NULL) AND (:NEW.TAET_2_AUFG_ID IS NULL)) THEN
:NEW.TAET_2_AUFG_ID := pkg_proj_tools.get_aufgabe_id (:NEW.TAET_AUFGABE);
END IF;
:NEW.TAET_DATUM := TRUNC(NVL(:NEW.TAET_DATUM, SYSDATE));
pkg_proj_tools.check_taet_datum_uhrzeit (
i_datum => :NEW.TAET_DATUM,
io_uhrzeit => :NEW.TAET_START_UHRZEIT,
io_zeitpunkt => :NEW.TAET_START_ZEITPUNKT);
pkg_proj_tools.check_taet_datum_uhrzeit (
i_datum => :NEW.TAET_DATUM,
io_uhrzeit => :NEW.TAET_ENDE_UHRZEIT,
io_zeitpunkt => :NEW.TAET_ENDE_ZEITPUNKT);
IF (:NEW.TAET_START_ZEITPUNKT IS NOT NULL) AND (:NEW.TAET_ENDE_ZEITPUNKT IS NOT NULL) THEN
:NEW.TAET_DAUER_STUNDEN := ABS(ROUND((:NEW.TAET_ENDE_ZEITPUNKT-:NEW.TAET_START_ZEITPUNKT)*24,2));
ELSIF (:NEW.TAET_DAUER_STUNDEN IS NULL) OR (UPDATING (':NEW.TAET_DAUER_TAGE') AND NOT UPDATING (':NEW.TAET_DAUER_STUNDEN')) THEN
:NEW.TAET_DAUER_STUNDEN := GREATEST(ROUND(:NEW.TAET_DAUER_TAGE * 8,1),0);
ELSE
:NEW.TAET_DAUER_STUNDEN := GREATEST (:NEW.TAET_DAUER_STUNDEN, 0);END IF;
:NEW.TAET_DAUER_TAGE := ROUND(:NEW.TAET_DAUER_STUNDEN / 8, 2);
:NEW.TAET_BEARBEITER := pkg_proj_tools.get_bearbeiter_bezeichnung_lng (:NEW.TAET_2_BEARB_ID);
:NEW.TAET_AUFGABE := pkg_proj_tools.get_aufgabe_summary (:NEW.TAET_2_AUFG_ID);
END PROJ_TAET_B_IU_R_CHECK_DATA;
/

PROMPT CREATE OR REPLACE TRIGGER PROJ_TAET_B_I_R_KEYGEN
BEGIN DBMS_APPLICATION_INFO.SET_MODULE ('CREATE OR REPLACE', 'TRIGGER');DBMS_APPLICATION_INFO.SET_CLIENT_INFO('PROJ_TAET_B_I_R_KEYGEN'); END;
/
CREATE OR REPLACE TRIGGER PROJ_TAET_B_I_R_KEYGEN
BEFORE INSERT ON PROJ_TAETIGKEIT
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW WHEN (NEW.TAET_ID IS NULL)
BEGIN
-- Auto-Generated by Excel Generator
SELECT SEQ_PROJ_TAETIGKEIT.NEXTVAL
INTO :NEW.TAET_ID
FROM DUAL;
END PROJ_TAET_B_I_R_KEYGEN;
/

----------------------------------------------------------------
-- Comments
----------------------------------------------------------------

PROMPT COMMENT TABLE PROJ_TAETIGKEIT
BEGIN DBMS_APPLICATION_INFO.SET_MODULE ('COMMENT ', 'TABLE');DBMS_APPLICATION_INFO.SET_CLIENT_INFO('PROJ_TAETIGKEIT'); END;
/
COMMENT ON COLUMN PROJ_TAETIGKEIT.TAET_ID IS
'Primary Key';
COMMENT ON COLUMN PROJ_TAETIGKEIT.TAET_2_BEARB_ID IS
'Foreign Key auf die Tabelle PROJ_BEARBEITER';
COMMENT ON COLUMN PROJ_TAETIGKEIT.TAET_2_AUFG_ID IS
'Foreign Key auf die Tabelle PROJ_AUFGABE';
COMMENT ON COLUMN PROJ_TAETIGKEIT.TAET_DATUM IS
'Datum der Tätigkeit';
COMMENT ON COLUMN PROJ_TAETIGKEIT.TAET_DAUER_STUNDEN IS
'Dauer der Tätigkeit an dem Datum in Stunden';
COMMENT ON COLUMN PROJ_TAETIGKEIT.TAET_DAUER_TAGE IS
'Dauer der Tätigkeit an dem Datum in Personentagen';
COMMENT ON COLUMN PROJ_TAETIGKEIT.TAET_INTERN IS
'Flag ob Interner oder Externer Mitarbeiter/Bearbeitung';
COMMENT ON COLUMN PROJ_TAETIGKEIT.TAET_KURZBESCHREIBUNG IS
'Kurzbeschreibung';
COMMENT ON COLUMN PROJ_TAETIGKEIT.TAET_KOMMENTAR IS
'Zusätzlicher Kommentar';
COMMENT ON COLUMN PROJ_TAETIGKEIT.TAET_PROJEKT IS
'Zugehöriges Projekt';
COMMENT ON COLUMN PROJ_TAETIGKEIT.TAET_BEARBEITER IS
'Zugehöriger Bearbeiter';
COMMENT ON COLUMN PROJ_TAETIGKEIT.TAET_AUFGABE IS
'Zugehörige Aufgabe';
COMMENT ON COLUMN PROJ_TAETIGKEIT.TAET_SUMMARY IS
'Zusammenfassende Beschreibung';

--
-- OraTool Pro v 2.0.19.1 BETA DEBUG - (c) by Softwaredevelopment Jens Fudickar
-- Softwareentwicklung Jens Fudickar - 0000000001 (Unlimited)
--
-- Generator : Create Object
-- Objecttype : VIEW
-- Generated at : 07.07.2006
-- Generated by : PAS_PROJEKT
--

----------------------------------------------------------------
-- VIEW V_PROJ_TAETIGKEIT
----------------------------------------------------------------

CREATE OR REPLACE FORCE VIEW V_PROJ_TAETIGKEIT
AS
SELECT
-- Auto-Generated by Excel Generator
TAET_ID,
TAET_2_BEARB_ID,
TAET_2_AUFG_ID,
TAET_KURZBESCHREIBUNG,
TAET_KOMMENTAR,
TAET_DATUM,
TAET_START_UHRZEIT,
TAET_START_ZEITPUNKT,
TAET_ENDE_UHRZEIT,
TAET_ENDE_ZEITPUNKT,
TAET_DAUER_STUNDEN,
TAET_DAUER_TAGE,
TAET_PROJEKT,
TAET_BEARBEITER,
TAET_AUFGABE,
TAET_SUMMARY,
TAET_INTERN
FROM PROJ_TAETIGKEIT
WHERE TAET_2_BEARB_ID = PKG_PROJ_PRIVILEGES.CURRENT_BEARB_ID
;

----------------------------------------------------------------
-- Grants

GRANT SELECT ON V_PROJ_TAETIGKEIT TO PASREP;

GRANT DELETE ON V_PROJ_TAETIGKEIT TO PROJEKT_EDITOR_TAETIGKEIT;
GRANT INSERT ON V_PROJ_TAETIGKEIT TO PROJEKT_EDITOR_TAETIGKEIT;
GRANT SELECT ON V_PROJ_TAETIGKEIT TO PROJEKT_EDITOR_TAETIGKEIT;
GRANT UPDATE ON V_PROJ_TAETIGKEIT TO PROJEKT_EDITOR_TAETIGKEIT;

GRANT SELECT ON V_PROJ_TAETIGKEIT TO PROJEKT_READER;

GRANT DELETE ON V_PROJ_TAETIGKEIT TO PROJEKT_SUPERVISOR;
GRANT INSERT ON V_PROJ_TAETIGKEIT TO PROJEKT_SUPERVISOR;
GRANT SELECT ON V_PROJ_TAETIGKEIT TO PROJEKT_SUPERVISOR;
GRANT UPDATE ON V_PROJ_TAETIGKEIT TO PROJEKT_SUPERVISOR;

The Database is Oracle10, SQL*Net is Oracle 9

I'm working with DevExpress cxGrid-Component to edit the data.

Greetings and thanks
Jens

Plash · Post by **Plash** » Tue 11 Jul 2006 07:40

We cannot reproduce the problem. If it is possible send to ODAC support address complete sample that demonstrates the problem and include script to create server objects. Please specify your ODAC version and whether you use Net option to connect to database.