Icon View Incident Report

Serious Serious
Reported By: Hedley Muscroft
Reported On: 3/26/2009
For: Version 2.02 Build 10
# 2967 SQL Can Cause "Value Cannot Be Null" Error When Executed Using .NET Data Provider

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)"

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



Resolution Resolution
Fixed Problem on 3/27/2009 in version 2.02 build 11


Products Affected Products Affected
ElevateDB DAC Client-Server
ElevateDB DAC Client-Server with Source
ElevateDB DAC Standard
ElevateDB DAC Standard with Source
ElevateDB DAC Trial

Image