Hello Helen,

Again thanks for your time/effort/patients, I really appreciate it!
I have gone through your email and I am getting what you write 99%.

Unfortunately I am still getting the error 'Invalid custom DML column
reference: WWWCON_ID'
I do not know what is causing this.

I did notice your example using KeyLinks and so I also tried to apply that
too

IBO_QRY_GET_MY_PROJECTS.Keylinks = E_PROJECT.ROW_ID

but that gave me an invalid keylinks entry. I do not understand why this is.
Would you please
explain?

Kind regards,
Ronan van Riet


-----------------------------------
IBO_QRY_GET_MY_PROJECTS.INSERTSQL:
-----------------------------------

EXECUTE PROCEDURE INS_OR_UPD_OR_DEL_E_PROJECT('N', -1, :WWWCON_ID,
:WWWDESC_TEXT, :WWWCAD, :WWWPROJECT, :WWWPROJECTTYPE)

-----------------------------------
DDL: INS_OR_UPD_OR_DEL_E_PROJECT
-----------------------------------

CREATE PROCEDURE INS_OR_UPD_OR_DEL_E_PROJECT(
WWWDEL CHAR(1),
WWWROW_ID INTEGER,
WWWCON_ID INTEGER,
WWWDESC_TEXT VARCHAR(255),
WWWCAD BLOB SUB_TYPE 0 SEGMENT SIZE 80,
WWWPROJECT BLOB SUB_TYPE 0 SEGMENT SIZE 80,
WWWPROJECTTYPE VARCHAR(30))
AS
BEGIN
IF (:WWWDEL = 'Y') THEN -- it's a DELETE
UPDATE E_PROJECT
SET ACTIVE_FLG = 'N'
WHERE ROW_ID = :wwwROW_ID;
ELSE BEGIN -- test whether row already exists
IF (EXISTS (
SELECT 1 FROM E_PROJECT
WHERE ROW_ID = :wwwROW_ID)) THEN -- it 's an UPDATE
UPDATE E_PROJECT
SET PROJECT = :wwwPROJECT
WHERE ROW_ID = :wwwROW_ID;
ELSE BEGIN -- it's an INSERT
-- no existing row so get a new row_id
wwwROW_ID = GEN_ID(GEN_E_PROJECT_ROW_ID, 1);

INSERT INTO E_PROJECT (ROW_ID, CREATED_BY, LAST_UPD_BY, CST_CON_ID,
DESC_TEXT, CAD, PROJECT, PROJECTTYPE)
VALUES (:wwwROW_ID, :wwwCON_ID, :wwwCON_ID, :wwwCON_ID, :wwwDESC_TEXT,
:wwwCAD, :wwwPROJECT,

:wwwPROJECTTYPE);
END
end
END

-----------------------------------
Delphi code for New project:
-----------------------------------

procedure TDataModule2.waProjectExplorerNewProject(Sender: TWebTreeview;
value1, value2: String);
var
a0: string
begin
with pWebApp do begin
with IBO_QRY_GET_MY_PROJECTS do begin
if not prepared then prepare;

Insert;

FieldByName('wwwCON_ID').Value := Literal['CST_CON_ID'];
FieldByName('wwwDESC_TEXT').Value := value1;
FieldByName('wwwProjectType').Value := Literal['sbProjectType'];
a0 := 'c:\1.dxf';
tWebTreeview(sender).treeview.SaveFileToBlob(a0,
TIB_ColumnBlob(FieldByName('wwwCAD')));
if not assigned(FieldByName('wwwCAD')) then
FieldByName('wwwCAD').Clear; //ensures empty blob is null

tWebTreeview(sender).treeview.SaveTreeviewToBlob(tWebTreeview(sender).treeview,

TIB_ColumnBlob(FieldByName('wwwProject')));
if not assigned(FieldByName('wwwProject')) then
FieldByName('wwwProject').Clear; //ensures empty blob is null

// now (when the SP is fixed) we are ready to post:
try
post;
except
// RVR 31JAN2004 some exception handling here
end;
IB_Transaction.Commit;
end;
end;
end;


procedure TDataModule2.IBO_QRY_GET_MY_PROJECTSAfterInsert(
DataSet: TDataSet);
begin
with TIBOQuery(DataSet) do begin
ParamByName('WWWROW_ID').AsInteger :=
cnMain.Gen_ID('GEN_E_PROJECT_ROW_ID', 1);
end;
end;


-----------------------------------
IBO_QRY_GET_MY_PROJECTS.SQL:
-----------------------------------

SELECT O_ROW_ID, O_CREATED, O_CREATED_BY, O_LAST_UPD, O_LAST_UPD_BY,
O_PROJECTTYPE, O_DESC_TEXT, O_STAGE, O_CALCDATE
FROM GET_MY_PROJECTS(:WWWCON_ID)

-----------------------------------
DDL: GET_MY_PROJECTS
-----------------------------------

CREATE PROCEDURE GET_MY_PROJECTS(
WWWCON_ID INTEGER)
RETURNS (
O_ROW_ID INTEGER,
O_CREATED DATE,
O_CREATED_BY VARCHAR(50),
O_LAST_UPD DATE,
O_LAST_UPD_BY VARCHAR(50),
O_PROJECTTYPE VARCHAR(30),
O_DESC_TEXT VARCHAR(10),
O_STAGE INTEGER,
O_CALCDATE INTEGER)
AS
BEGIN
IF (WWWCON_ID = -1) THEN BEGIN
FOR SELECT prj.ROW_ID,
prj.created, creator.fst_name || ' ' || creator.last_name,
prj.last_upd, updator.fst_name || ' ' || updator.last_name,
prj.projecttype, prj.desc_text, prj.stage, prj.calcdate
FROM E_PROJECT prj,
E_CONTACT creator,
E_CONTACT updator
WHERE prj.created_by = creator.row_id
AND prj.last_upd_by = updator.row_id
AND prj.cst_con_id = :WWWCON_ID
AND prj.Active_FLG = 'Y'
ORDER BY prj.last_upd desc
INTO :o_ROW_ID, :o_created, :o_created_by, :o_last_upd, :o_Last_Upd_by,
:o_projecttype, :o_desc_text, :o_stage, :o_Calcdate DO
SUSPEND;
END
ELSE BEGIN
SELECT prj.ROW_ID,
prj.created, creator.fst_name || ' ' || creator.last_name,
prj.last_upd, updator.fst_name || ' ' || updator.last_name,
prj.projecttype, prj.desc_text, prj.stage, prj.calcdate
FROM E_PROJECT prj,
E_CONTACT creator,
E_CONTACT updator
WHERE prj.created_by = creator.row_id
AND prj.last_upd_by = updator.row_id
AND prj.cst_con_id = :wwwCON_ID
AND prj.Active_FLG = 'Y'
ORDER BY prj.last_upd desc
INTO :o_ROW_ID, :o_created, :o_created_by, :o_last_upd, :o_Last_Upd_by,
:o_projecttype, :o_desc_text, :o_stage, :o_Calcdate;
SUSPEND;
END
END

-----------------------------------
Delphi code for Before Open:
-----------------------------------

procedure TDataModule2.IBO_QRY_GET_MY_PROJECTSBeforeOpen(DataSet: TDataSet);
// we are using dataset cloning so we MUST 'go through'
webdatasource.dataset
begin
with TIBOQuery(wdsGET_MY_PROJECTS.DataSet), pWebApp do begin
if Active then Close;

if not Prepared then Prepare;
ParamByName('wwwCON_ID').Value := Literal['CST_CON_ID'];
end;
end;

_________________________________________________________________
Talk with your online friends with MSN Messenger http://messenger.msn.nl/