Hi Tim,

Please see the following database :-
http://files.pioneersoftware.co.uk/temp/SampleDB2.7z

and this test project (VS2008) :-
http://files.pioneersoftware.co.uk/temp/ElevateDB_Test2.7z

ElevateDB doesn't have cascading deletes, so I have introduced some code to
implement this myself. Basically, I have a class which handles deletions and
it knows all the inter-dependencies of my DB schema.

This class generates the required SQL SCRIPT for a cascaded deletion. The
SQL SCRIPT runs fine under EDB Manager, but under ADO.NET it throws :-

"ElevateDB Error #900 An error occurred with the statement at line 5 and
column 22 (Value cannot be null. Parameter name: dest)"

Here's the actual SQL SCRIPT being executed (for more detail, please see the
test project) :-
---------------------------
SCRIPT ()
BEGIN
  EXECUTE IMMEDIATE 'update link set ref_visnote_id=null where
ref_visnote_id in (select id from visnote where
cas_id=1260309130314523789)';
  EXECUTE IMMEDIATE 'delete from visnote where cas_id=1260309130314523789';
  EXECUTE IMMEDIATE 'update link set ref_visnote_id=null where
ref_visnote_id in (select id from visnote where sess_id in (select id from
sess where cas_id=1260309130314523789))';
  EXECUTE IMMEDIATE 'delete from visnote where sess_id in (select id from
sess where cas_id=1260309130314523789)';
  EXECUTE IMMEDIATE 'delete from custom_session_fields where sess_id in
(select id from sess where cas_id=1260309130314523789)';
  EXECUTE IMMEDIATE 'delete from custom_case_fields where sess_id in
(select id from sess where cas_id=1260309130314523789)';
  EXECUTE IMMEDIATE 'update link set ref_fileatt_id=null where
ref_fileatt_id in (select id from fileatt where sess_id in (select id from
sess where cas_id=1260309130314523789))';
  EXECUTE IMMEDIATE 'delete from fileatt where sess_id in (select id from
sess where cas_id=1260309130314523789)';
  EXECUTE IMMEDIATE 'delete from custom_patient_fields where sess_id in
(select id from sess where cas_id=1260309130314523789)';
  EXECUTE IMMEDIATE 'update link set ref_img_id=null where ref_img_id in
(select id from img where sess_id in (select id from sess where
cas_id=1260309130314523789))';
  EXECUTE IMMEDIATE 'delete from img where sess_id in (select id from sess
where cas_id=1260309130314523789)';
  EXECUTE IMMEDIATE 'update app set sess_id=null where sess_id in (select
id from sess where cas_id=1260309130314523789)';
  EXECUTE IMMEDIATE 'update link set ref_sess_id=null where ref_sess_id in
(select id from sess where cas_id=1260309130314523789)';
  EXECUTE IMMEDIATE 'delete from sess where cas_id=1260309130314523789';
  EXECUTE IMMEDIATE 'delete from custom_session_fields where
cas_id=1260309130314523789';
  EXECUTE IMMEDIATE 'delete from custom_case_fields where
cas_id=1260309130314523789';
  EXECUTE IMMEDIATE 'update link set ref_fileatt_id=null where
ref_fileatt_id in (select id from fileatt where
cas_id=1260309130314523789)';
  EXECUTE IMMEDIATE 'delete from fileatt where cas_id=1260309130314523789';
  EXECUTE IMMEDIATE 'delete from custom_patient_fields where
cas_id=1260309130314523789';
  EXECUTE IMMEDIATE 'update link set ref_img_id=null where ref_img_id in
(select id from img where cas_id=1260309130314523789)';
  EXECUTE IMMEDIATE 'delete from img where cas_id=1260309130314523789';
  EXECUTE IMMEDIATE 'update link set ref_cas_id=null where
ref_cas_id=1260309130314523789';
  EXECUTE IMMEDIATE 'delete from cas where id=1260309130314523789';
END
---------------------------

Hedley,

<< This class generates the required SQL SCRIPT for a cascaded deletion. The
SQL SCRIPT runs fine under EDB Manager, but under ADO.NET it throws :-

"ElevateDB Error #900 An error occurred with the statement at line 5 and
column 22 (Value cannot be null. Parameter name: dest)" >>

This has been fixed for the next build 11.

--
Tim Young
Elevate Software
www.elevatesoft.com